excel - EXtracting number in VBA while using IF - REFRESH

Question

I want to extract a number in a String and apply this number to the IF function, but if the source row changes the If function that contains the VBA function does NOT refresh.

Exemple> Source row= 10VBA

Public Function JustNumber(ByVal vValor As String) As String
   Application.Volatile True
    
    Dim vQtdeCaract As Long
    Dim vControle   As Boolean
    
    vQtdeCaract = Len(vValor)
    vControle = False
    
    For i = 1 To vQtdeCaract
        
        If IsNumeric(Mid(vValor, i, 1)) Then
            If vControle = True And JustNumber <> vbNullString Then
                JustNumber = JustNumber + "/"
            End If
            vControle = False
            JustNumber = JustNumber & Mid(vValor, i, 1)
        Else
            vControle = True
        End If
    Next
End Function

returns the number 10

=IF(JustNumber(source row)=10;"True";"False")

Returns "True"

But, if I change the source row the IF fuction doesn't change, there is a way to refresh the function after the change in the Source row to 15VBA, making the function = "False" ?

Answer 1

Some suggestions:

• Name your procedures and variables to something meaningful
• Use Option Explicit at the top of your modules

Some adjustments:

• You're getting a string from the function result (you either can change the function result or evaluate the number as string in the Excel formula)
• I simplified the loop to extract the numbers

Code:

Public Function numbersFromString(ByVal evalString As String) As Double

    Application.Volatile True
    
    ' Get string length
    Dim stringLength As Integer
    stringLength = Len(evalString)
    
    ' Loop through characters in string and extract numbers
    Dim resultString As String
    Dim counter As Long
    For counter = 1 To stringLength
        If IsNumeric(Mid(evalString, counter, 1)) Then resultString = resultString & Mid(evalString, counter, 1)
    Next counter
    
    ' Check if there are any numbers
    If Len(resultString) > 0 Then
        ' Convert to number
        numbersFromString = CDbl(resultString)
    End If

End Function