Convert Number to String in Excel VBA Macro Code

Number to String Conversion

No, this is not about converting 1 to ‘One’. If you are looking for such conversion, it is right here.

This article in this page is about converting a Number in a variable of data type integer/double/float to convert it in a data type of string.

If we do a direct comparison of a numeric data type variable to string variable, it will result in incorrect answer.

Here is an example code for this.

Sub Number_To_String_Error()
    Dim iNum As Variant
    Dim sStr As Variant
    
    'Assign number to string
    iNum = 2
    sStr = "2"
    
    'or
    If sStr = iNum Then
        MsgBox "This line will not occur."
    End If
    
End Sub

Using VBA.cStr to convert Number to String

In such cases use the function cSTR. This will convert the integer numeric to a string equivalent.
You can try the below example and see the different in the output.

Sub Number_To_String_Error()
    Dim iNum As Variant
    Dim sStr As Variant
    
    'Assign number to string
    iNum = 2
    sStr = "2"
    
    'or
    If sStr = VBA.CStr(iNum) Then
        MsgBox "This line will occur."
    End If
    
End Sub

Now, CSTR converts the number to a string. So, inside the if condition, both are treated as values of same data type & hence the IF condition is true.