VBA Convert Integer To String With Leading Zeros

VBA Convert Integer To String With Leading Zeros. when dealing with large data bases we commonly see numbers without leading zeros. But when we add in Excel sheet it doesn’t allow leading zeros. So we are converting to string by adding single quote. After that we can add zeros. It doesn’t show single quote in the Excel Sheet.

Table of Contents:

Objective
Convert Integer To String With Leading Zeros
Instructions to Run VBA Macro Code
Other Useful Resources

Convert Integer To String With Leading Zeros

Let us see an example how to convert an integer value to string with leading zeros. We can

'VBA Convert Integer To String With Leading Zeros
Sub VBAF1_Convert_Integer_To_String_With_Leading_Zeros()
    
    'Variable declaration
    Dim iValue As Integer, sResult As String
    
   'Assign value to a variable
    iValue = 484
    
    'Convert Integer To String by Leading with Zero
    sResult = "0" & CStr(iValue)
    
    'Display on the screen
    MsgBox sResult, vbInformation, "VBAF1"
    
    'Update in the Excel Sheet
    Sheets("Sheet2").Range("D26") = "'0" & CStr(iValue)
       
End Sub

VBA Convert Integer To String With Leading Zeros

Explanation: You can notice there is a difference between displaying on the screen and displaying on the worksheet. Which we displayed on the screen can’t display on the screen with leading zero. We need to add single quote as well.
You can also find following alternative methods.

1st Case
iResult = CStr(Format(484, "0000"))
Output: 0484
In the above method we have added 4 zero's to format. Here the value length is 3 and added one more leading zero to string. You can also add as many number of zeros.

Let us see another example..
iResult = CStr(Format(484, "000000"))
Output: 000484
You can observe difference between 2 examples.

2nd Case
iValue=844
Sheets("Sheet2").Range("A1") = Chr(39) & "0" & iValue
Output: '0844

You can see output in Sheet 2 at Range A1.