Having trouble making UDF work with reference to Personal.xlsb

[gmittar]

Hi All,

VBA novice here, just getting into learning about User Defined Functions. I have a function (listed below), that if called from the sheet that I'm working in (inserted into a local module), works fine. However, if it's referred to in my Personal.xlsb, gives me an error message in VBE "Compile Error: Variable not defined" and highlights the "i" in "For i = 1 To StringLength"

I'm calling the function with =personal.xlsb!getnumeric(f4)

'This VBA code will create a function to get the numeric part from a string
Function GetNumeric(CellRef As String)
Dim StringLength As Integer
StringLength = Len(CellRef)
For i = 1 To StringLength
If IsNumeric(Mid(CellRef, i, 1)) Then Result = Result & Mid(CellRef, i, 1)
Next i
GetNumeric = Result
End Function

Appreciate your help.

 

[Fluff]

Add this line

Function GetNumeric(CellRef As String)
[COLOR=#0000ff]Dim i as Long[/COLOR]
Dim StringLength As Integer

 

[Marcelo Branco]

Try

Function GetNumeric(CellRef As String)
Dim StringLength As Integer,[COLOR=#ff0000] i As Long, Result As String[/COLOR]
StringLength = Len(CellRef)
For i = 1 To StringLength
If IsNumeric(Mid(CellRef, i, 1)) Then Result = Result & Mid(CellRef, i, 1)
Next i
GetNumeric = Result
End Function

M.

 

[gmittar]

Perfect, that did it. Thank you both!