Forum Discussion

ayush_amateur's avatar
ayush_amateur
Copper Contributor
Jan 14, 2024
Solved

Understanding Macros Function

Hi,
I need to split the data in a cell into texts and numbers separately using VBA. I came across a function that does that but I have a question regarding that. I'm very new to Macros and I'm trying to understand every nuances of it.
Here is the function to get text

Function GetText(CellRef As String)
Dim StringLength As Integer
StringLength = Len(CellRef)
For i = 1 To StringLength
If Not (IsNumeric(Mid(CellRef, i, 1))) Then Result = Result & Mid(CellRef, i, 1)
Next i
GetText = Result
End Function

 

And here is the function to get numbers

 

Function GetNumber(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
GetNumber = Result
End Function


I need to understand how we are using Result here as a variable without defining it first? 
Before this I've used variables only after first defining them.
Can someone help explain this? 

Formulas and Functions
Macros and VBA
  • NikolinoDE's avatar
    NikolinoDE
    Jan 14, 2024

    ayush_amateur 

    In VBA, when you use a variable without explicitly declaring it, it's implicitly declared as a Variant. A Variant is a data type that can hold any type of data.

    In the functions you have provided, Result is implicitly declared as a Variant. When you do not explicitly declare a variable, VBA assumes it to be of type Variant. The Variant type is flexible and can store numbers, strings, dates, or even objects.

    Here is what happens in your functions:

    Vba code:

    Function GetText(CellRef As String)
    Dim StringLength As Integer
    StringLength = Len(CellRef)
    
    ' Result is implicitly declared as Variant
    For i = 1 To StringLength
        If Not (IsNumeric(Mid(CellRef, i, 1))) Then
            Result = Result & Mid(CellRef, i, 1)
        End If
    Next i
    
    ' GetText is assigned the value of Result
    GetText = Result
End Function

    In the above code, Result is implicitly declared and used to store the characters extracted from the input CellRef that are not numeric. The final result is then assigned to the function name (GetText) so that the function returns that value.

    The same concept applies to the GetNumber function.

    However, it's generally considered good practice to explicitly declare variables using Dim before using them. This helps in avoiding potential issues related to variable scope and makes the code more readable. For example:

    Vba Code:

    Function GetText(CellRef As String) As String
    Dim StringLength As Integer
    Dim Result As String  ' Explicitly declared as String
    
    ' Rest of the code remains the same
End Function

    By explicitly declaring Result as a String, you make it clearer to yourself and others reading your code what type of data the variable is intended to hold. The text and information were created with the help of AI.

     

    Hope I was able to help you with this information.

  • NikolinoDE's avatar
    NikolinoDE
    Gold Contributor
    Jan 14, 2024

    ayush_amateur 

    In VBA, when you use a variable without explicitly declaring it, it's implicitly declared as a Variant. A Variant is a data type that can hold any type of data.

    In the functions you have provided, Result is implicitly declared as a Variant. When you do not explicitly declare a variable, VBA assumes it to be of type Variant. The Variant type is flexible and can store numbers, strings, dates, or even objects.

    Here is what happens in your functions:

    Vba code:

    Function GetText(CellRef As String)
    Dim StringLength As Integer
    StringLength = Len(CellRef)
    
    ' Result is implicitly declared as Variant
    For i = 1 To StringLength
        If Not (IsNumeric(Mid(CellRef, i, 1))) Then
            Result = Result & Mid(CellRef, i, 1)
        End If
    Next i
    
    ' GetText is assigned the value of Result
    GetText = Result
End Function

    In the above code, Result is implicitly declared and used to store the characters extracted from the input CellRef that are not numeric. The final result is then assigned to the function name (GetText) so that the function returns that value.

    The same concept applies to the GetNumber function.

    However, it's generally considered good practice to explicitly declare variables using Dim before using them. This helps in avoiding potential issues related to variable scope and makes the code more readable. For example:

    Vba Code:

    Function GetText(CellRef As String) As String
    Dim StringLength As Integer
    Dim Result As String  ' Explicitly declared as String
    
    ' Rest of the code remains the same
End Function

    By explicitly declaring Result as a String, you make it clearer to yourself and others reading your code what type of data the variable is intended to hold. The text and information were created with the help of AI.

     

    Hope I was able to help you with this information.

Resources