Sometimes you may want to convert Integer and Long values to strings. This is so that you can use string manipulation functions on these values. This tutorial explains how to convert Integer and Long values to strings using Excel VBA.
Convert Integer and Long Values to Strings Using VBA
In our illustration, we will use the following data set that has Integer and Long values.
We use the following steps:
- Right-click the tab of the worksheet that contains the data set. Choose View Code on the shortcut menu.
- Type the following procedures in the opened object module. Remember to change the data range in the example to your data range.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 |
Sub convertToString(ByRef xRange As String, Optional ByVal WSheet As Worksheet) Dim temp As Long Dim dataRange As Range Dim xCell As Object If WSheet Is Nothing Then Set WSheet = ActiveSheet Set dataRange = WSheet.Range(xRange).SpecialCells(xlCellTypeVisible) For Each xCell In dataRange If Not IsEmpty(xCell.Value) And IsNumeric(xCell.Value) Then temp = xCell.Value xCell.ClearContents xCell.NumberFormat = "@" xCell.Value = CStr(temp) End If Next xCell End Sub Sub xMacro() Call convertToString("C2:C10", ActiveSheet) End Sub |
- Place the cursor inside the second xMacro procedure and press F5 to run the code.
- Press Alt + F11 to switch back to the active worksheet.
The values in range C2:C10 are now left-aligned meaning they have been converted to text or strings.
Explanation of the procedures
- The convertToString sub-procedure takes a maximum of two arguments. The xRange argument is of String data type and is required. This argument is passed to the procedure by reference meaning that the original values are referenced. The WSheet argument is of worksheet data type and is optional. It is passed to the procedure by value which means that only a copy of the worksheet is passed to the procedure.
- Three variables are declared. The temp variable is of the Long data type and can accommodate both Integer and Long numeric values. The dataRange variable is of Range data type and will contain the data range C2:C10 when assigned to it. The xCell variable is of Object data type. It will hold in turn each cell in the range C2:C10 at each passing of the For Each Next loop.
- Using the Set keyword, the data range is assigned to the dataRange variable.
- The IsEmpty and IsNumeric functions check that each cell in the data range has a numeric value.
- The numeric value in each cell is in turn assigned to the temp variable.
- The value in each cell is in turn deleted using the ClearContents method.
- The text format is applied to the cells in the range.
- The CStr function converts the number passed to it to a string. This string is then assigned to the relevant cell.
- The convertToString procedure called by the xMacro procedure converts to strings the values in the range C2:C10.
Conclusion
You may want to convert integer values to strings so that you can use string manipulation functions on the values. This tutorial explains how to convert Integer and Long values to strings using Excel VBA.