Results 1 to 8 of 8

Thread: VBA Function returns 0

  1. #1

    Thread Starter
    Hyperactive Member
    Join Date
    Mar 2013
    Location
    Minneapolis, MN
    Posts
    460

    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!

  2. #2
    Frenzied Member
    Join Date
    Nov 2017
    Posts
    1,510

    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

  3. #3
    Frenzied Member jdc2000's Avatar
    Join Date
    Oct 2001
    Location
    Idaho Falls, Idaho USA
    Posts
    1,831

    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

  4. #4
    PowerPoster Zvoni's Avatar
    Join Date
    Sep 2012
    Location
    To the moon and then left
    Posts
    2,436

    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
    One System to rule them all, One IDE to find them,
    One Code 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.
    ---------------------------------------------------------------------------------
    For health reasons i try to avoid reading unformatted Code

  5. #5
    Member
    Join Date
    Nov 2020
    Posts
    32

    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

  6. #6
    PowerPoster Zvoni's Avatar
    Join Date
    Sep 2012
    Location
    To the moon and then left
    Posts
    2,436

    Re: VBA Function returns 0

    Quote Originally Posted by LeoFar View Post
    @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
    One System to rule them all, One IDE to find them,
    One Code 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.
    ---------------------------------------------------------------------------------
    For health reasons i try to avoid reading unformatted Code

  7. #7

    Thread Starter
    Hyperactive Member
    Join Date
    Mar 2013
    Location
    Minneapolis, MN
    Posts
    460

    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!

  8. #8
    PowerPoster Zvoni's Avatar
    Join Date
    Sep 2012
    Location
    To the moon and then left
    Posts
    2,436

    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 "+"
    One System to rule them all, One IDE to find them,
    One Code 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.
    ---------------------------------------------------------------------------------
    For health reasons i try to avoid reading unformatted Code

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width