PDA

View Full Version : Solved: Difference between UDF & sub routine



Digita
07-25-2007, 12:17 AM
Hi everyone,

Just a curious question. I have a UDF to extract numbers (digits 0-9) from a string.



Function XtractNumbers(ByVal Str As String) As Long
Dim i As Long, k As Variant, j As Variant
For i = 0 To Len(Str)
k = Mid(Str, i + 1, 1)
If Not k >= 0 Or k <= 9 Then
j = j + k
End If
Next
XtractNumbers = j
End Function


The above UDF returns #Value! when the cell contains "asdfds 17/8 15/05/2007"

I tried another tactic by writing a sub to do the same thing.



Sub test()
Dim i As Integer, k As Variant, j As Variant
mystring = "asdfds 17/8 15/05/2007"
For i = 0 To Len(mystring)
k = Mid(mystring, i + 1, 1)
If Not k >= 0 Or k <= 9 Then
j = j + k
End If
Next
MsgBox j
End Sub


The sub provides the correct result "17815052007" in the msgbox. Why does the UDF not work?

Thanks & regards


KP

Charlize
07-25-2007, 12:50 AM
Function XtractNumbers(ByVal Str As String)
Dim i As Long, k As Variant, j As Variant
For i = 0 To Len(Str)
k = Mid(Str, i + 1, 1)
If Not k >= 0 Or k <= 9 Then
j = j + k
End If
Next
XtractNumbers = Val(j)
End Function

Bob Phillips
07-25-2007, 01:07 AM
The problem is that Mid returns a string, and even though you use + this is not addition but concatenation of a string. You then try and drop it into a lomg, voila, #VALUE

An alternative to Charlize's solution



Function XtractNumbers(ByVal Str As String) As Double
Dim i As Long, k As Variant, j As Double, l As Long
For i = Len(Str) To 1 Step -1
k = Mid(Str, i, 1)
If Not k >= 0 Or k <= 9 Then
j = j + k * 10 ^ l
l = l + 1
End If
Next
XtractNumbers = j
End Function

anandbohra
07-25-2007, 05:28 AM
:hi: XLD & Charlize

what might be reason for this output (based on your codes)
the value i had given is this in cell A1
anand1deepaki2165a4sdf59as7dfas4d5f4a6sdf46sd4f3asdf1asdf7as6d54f6asd4f3a2s 3

=xtractnumbers(A1)
i got this output

121654597454646000000000000

rory
07-25-2007, 06:56 AM
You only get 15 digits of precision in Excel. If you need more than that you will need to use a string and parse and work with it (if possible) accordingly.
Regards,
Rory

Norie
07-25-2007, 07:04 AM
The problem is caused by an overflow error.

Either Charlize's or xld's suggestions should solve it.

Digita
07-25-2007, 04:23 PM
Thanks Charlize & XLD. You're awesome.

Have a great day.

KP:hi: