Using VBA functions in MS Excel

[Diarmuid]

Hi,

I've just strarted using vba function to automate some of my spreadsheets.
I've created a function in a worksheet, but no matter what I try I can't seem
to access the function from a cell - any help would be greatfully accepted

 

[Susan]

welcome to VBA!
you need to post your function's code in order for somebody to help
you.

susan

 

[Niek Otten]

<I've created a function in a worksheet>

If you mean in a Worksheet Module, then that's the problem.
The function should be in a standard module (from Insert>Module)

 

[Diarmuid]

Hi,

Thanks for the reply


I'll be using lots of functions and I've checked the functions themselves to
make sure they work - the problem comes when I try to use them in a
worksheet. When I enter them into a cell in the same way as I would use any
other worksheet function I just get a #NAME? error displayed in the cell.
I've checked the spellings of the functions and have even tried setting the
security to low (as well as having the functions signed) and it doesn't seem
to make any difference.

Alot of what I'm doing is extracting information from CSV files that have
been created using older systems. The following is just a simple function
that I use to get a number out of a string - its probably messy code but it
should work!

Public Function ExtractNumber(strValue As String) As Single
' Read in a string of text and extract a number contained within it
Dim bytPointer1, bytPointer2 As Byte
Dim strReturn As String
Dim sngReturn As Single
Dim bytCntr As Byte
bytCntr = 49

Do
bytPointer1 = InStr(1, strValue, Chr(bytCntr))
bytCntr = bytCntr + 1

Loop Until bytPointer1 > 0

bytPointer2 = InStr(bytPointer1, strValue, " ")

If bytPointer2 > 0 Then
bytPointer2 = bytPointer2 - bytPointer1
strReturn = LTrim$(RTrim$(Mid$(strValue, bytPointer1, bytPointer2)))
Else
strReturn = LTrim$(RTrim$(Mid$(strValue, bytPointer1)))
End If

sngReturn = Val(strReturn)

ExtractNumber = sngReturn
End Function

 

[Diarmuid]

Thanks very much for the help!

Boy do I feel sheepish :{


Its amazing how simple the solution is when somebody points it out to you!
--
Just getting used to VBA!!!

<I've created a function in a worksheet>

If you mean in a Worksheet Module, then that's the problem.
The function should be in a standard module (from Insert>Module)