-
Apr 14th, 2021, 04:22 PM
#1
Thread Starter
Fanatic Member
VBA Function returns 0
Hello:
I am trying to run a function in VBA for Excel, as shown below.
Code:
Public Function ExtractNumeric(ByVal n As String) As Integer
' Assumes numeric portion is at the beginning
Dim c As Integer
Dim cstop As Integer
Dim nlen As Integer
nlen = Len(n)
For c = nlen To 1 Step -1
Dim ch As String
ch = Mid(n, c, 1)
If IsNumeric(ch) = True Then
cstop = c
Exit For
End If
Next
Dim num As Integer
' This value is 53
num = CInt(Left(n, cstop))
End Function
Everything works well, but when I call the function, rather than returning 53, it returns 0.
Sub RenumCells()
Dim lastrow As Long
lastrow = ThisWorkbook.ActiveSheet.Cells(Rows.Count, "C").End(xlUp).Row
Code:
For i0 = 3 To lastrow
If IsNumeric(ThisWorkbook.ActiveSheet.Cells(i0, 7).Value) = True Then
ThisWorkbook.ActiveSheet.Cells(i0, 7).Value = i0 - 2
Else
' This value is '53A'
MsgBox (ThisWorkbook.ActiveSheet.Cells(i0, 7).Value)
' This value is 0
MsgBox (ExtractNumeric(ThisWorkbook.ActiveSheet.Cells(i0, 7).Value))
ThisWorkbook.ActiveSheet.Cells(i0, 7).Value = ExtractNumeric(ThisWorkbook.ActiveSheet.Cells(i0, 7).Value)
End If
Next
End Sub
I hope this makes sense. Thanks!
- A 'Hyperactive Member' trying to make a difference in a hyperactive world! And recently, I've been promoted to a 'Finatic Member,' whatever that means!
-
Apr 14th, 2021, 04:46 PM
#2
Re: VBA Function returns 0
Simply assigning a value to a variable as the last line of code in a function does not return the value of said variable.
Functions work like this:
Code:
Public Function myFunctionName() As Type
'Code
'More code
myFunctionName = 123 ' This returns the value 123 - note that the "variable" name is actually the name of the function
End Function
-
Apr 14th, 2021, 04:51 PM
#3
Re: VBA Function returns 0
That code has other issues also, but OptionBase1 is correct. Try the modification below:
Code:
Public Function ExtractNumeric(ByVal n As String) As Integer
' Assumes numeric portion is at the beginning
Dim c As Integer
Dim cstop As Integer
Dim nlen As Integer
nlen = Len(n)
For c = nlen To 1 Step -1
Dim ch As String
ch = Mid(n, c, 1)
If IsNumeric(ch) = True Then
cstop = c
Exit For
End If
Next
Dim num As Integer
' This value is 53
num = CInt(Left(n, cstop))
' Assign the value to the function
ExtractNumeric = num
End Function
-
Apr 15th, 2021, 01:42 AM
#4
Re: VBA Function returns 0
Assuming numeric Portion is at the beginning/left of the string:
No Error-Handling whatsoever!
Code:
Private Declare PtrSafe Function StrSpn Lib "SHLWAPI" Alias "StrSpnW" (ByVal lpSource As LongPtr, ByVal lpCharSet As LongPtr) As Long
Private Const NUM_CHAR As String = "1234567890"
Public Function ExtractNumeric(ByRef s As String) As Long
ExtractNumeric = Left$(s, StrSpn(StrPtr(s), StrPtr(NUM_CHAR)))
End Function
Sub Main()
Debug.Print ExtractNumeric("123456SomeText") '--> Returns 123456
End Sub
Last edited by Zvoni; Apr 15th, 2021 at 04:15 AM.
Reason: Code corrected
Last edited by Zvoni; Tomorrow at 31:69 PM.
----------------------------------------------------------------------------------------
One System to rule them all, One Code to find them,
One IDE to bring them all, and to the Framework bind them,
in the Land of Redmond, where the Windows lie
---------------------------------------------------------------------------------
People call me crazy because i'm jumping out of perfectly fine airplanes.
---------------------------------------------------------------------------------
Code is like a joke: If you have to explain it, it's bad
-
Apr 15th, 2021, 03:20 AM
#5
Lively Member
Re: VBA Function returns 0
@Zvoni
Should be
Code:
Public Function ExtractNumeric(ByRef s As String) As Long
''''MyFunction = Left$(s, StrSpn(StrPtr(s), StrPtr(NUM_CHAR)))
ExtractNumeric = Left$(s, StrSpn(StrPtr(s), StrPtr(NUM_CHAR)))
End Function
-
Apr 15th, 2021, 04:14 AM
#6
Re: VBA Function returns 0
Originally Posted by LeoFar
@Zvoni
Should be
Code:
Public Function ExtractNumeric(ByRef s As String) As Long
''''MyFunction = Left$(s, StrSpn(StrPtr(s), StrPtr(NUM_CHAR)))
ExtractNumeric = Left$(s, StrSpn(StrPtr(s), StrPtr(NUM_CHAR)))
End Function
Yes, you're right.
I edited my post, and i forgot that one
Last edited by Zvoni; Tomorrow at 31:69 PM.
----------------------------------------------------------------------------------------
One System to rule them all, One Code to find them,
One IDE to bring them all, and to the Framework bind them,
in the Land of Redmond, where the Windows lie
---------------------------------------------------------------------------------
People call me crazy because i'm jumping out of perfectly fine airplanes.
---------------------------------------------------------------------------------
Code is like a joke: If you have to explain it, it's bad
-
Apr 15th, 2021, 08:51 AM
#7
Thread Starter
Fanatic Member
Re: VBA Function returns 0
Thanks to everyone for the obvious. It's easy to forget VB6 when bouncing back and forth to .NET
- A 'Hyperactive Member' trying to make a difference in a hyperactive world! And recently, I've been promoted to a 'Finatic Member,' whatever that means!
-
Apr 15th, 2021, 09:09 AM
#8
Re: VBA Function returns 0
Which solution are you using?
If you use mine, you shouldn't assign the result of the StrSpn-API-Call directly to the Function-Result (as in my example, that was quick and extremely dirty!)
It would blow up with the Left$-Function if strspn returns zero, as in "String doesn't start with a number"
Meaning: Use local variable for the StrSpn-API-Call,
check if it's > 0
If Yes then do Left$-Function and assign to result
EDIT: Forgot something:
It obviously works only for positive numbers,
but it wouldn't be difficult, to pre-parse the text if it starts with a "-" or "+"
Last edited by Zvoni; Tomorrow at 31:69 PM.
----------------------------------------------------------------------------------------
One System to rule them all, One Code to find them,
One IDE to bring them all, and to the Framework bind them,
in the Land of Redmond, where the Windows lie
---------------------------------------------------------------------------------
People call me crazy because i'm jumping out of perfectly fine airplanes.
---------------------------------------------------------------------------------
Code is like a joke: If you have to explain it, it's bad
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|