SOLVED

# Understanding Macros Function

Copper Contributor

# 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?

2 Replies
best response confirmed by ayush_amateur (Copper Contributor)
Solution

# Re: Understanding Macros Function

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.

# Re: Understanding Macros Function

Thank You @NikolinoDE for such a clear explanation. Appreciate it.
1 best response

Accepted Solutions
best response confirmed by ayush_amateur (Copper Contributor)
Solution

# Re: Understanding Macros Function

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.