Convert Number to String in Excel VBA Macro Code

Number to String Conversion

When we say converting a “number to string”, it is not about changing a number 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 as in the below code.

Sub Dont_Do_Number_To_String_Conversion_Like_this()
    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()
    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 (data type: Integer/float/double) to a string data type. So, in the above code, inside the if condition, both variable fields are treated as values of same data type & hence the IF condition is true.