You’ll learn how to convert Strings to Long in this VBA tutorial. This VBA tutorial comes with an Excel worksheet that contains all of the macros, data, and calculations used in the examples below. For your better understanding, we will use the data set that contains Product ID, Product Name, and Quantity. Let’s see, Excel VBA Convert String to Long in 3 simple ways.
How to Convert String to Long Using VBA in Excel: 3 Ways
Method 1: Change String to Long Using CLng
In our first method, we will see the use of CLng to convert String into Long. As you can see from the dataset, the values in Quantity are in Text format, which is a string. We will convert it to Long.
Steps:
First, right-click on the sheet and go to View Code.
- After that, copy and paste the VBA code below.
VBA Code:
Sub longConvert()
Range("E5").Value = CLng(Range("D5"))
Range("E6").Value = CLng(Range("D6"))
End Sub
- In the end, to Run the code, press the F5 or play button. This will change the String format as a Long.
Through this code, we are telling Excel to convert Range D5 and D6 values into Long values in another column range E5 and E6.
As we just selected D5 and D6 to convert, that’s why only these two cells got converted. We can select as many cells as we want and convert the string into long.
Read more: How to Convert String to Number in Excel VBA
Method 2: Excel VBA Convert String to Long in Full Range
In our first method, we converted String to Long in Excel VBA manually inputting data. Now, we will see, how we can select a range and convert all strings into long easily.
Steps:
- First, right-click on the sheet and go to View Code.
- After that, copy and paste the VBA code below.
VBA Code:
Sub String_long_Convert()
For i = 5 To 9
Cells(i, 5).Value = CLng(Cells(i, 4).Value)
Next
End Sub
- In the end, to Run the code, press the F5 or play button. This will change the String format as a Long.
Through this code, we are telling Excel to convert range D5 to D9 values into Long values in another column range E5 to E9. By using For loop we are declaring the cell ranges first and the Next command will convert one data at a time and move to the next automatically.
As we can see, all the string values in range D5:D9 converted to Long Value.
Read more: How to Convert String to Double in Excel VBA
Similar Readings
- How to Convert Bulk Text to Number in Excel
- Convert Text to Number with Excel VBA
- How to Fix Convert to Number Error in Excel
- How to Fix All Number Stored as Text in Excel
Method 3: User Function for Excel VBA Convert String to Long
Now, we will get familiar with the Function method to convert string to long in VBA.
Steps:
- By the same token as the above methods, go to the Visual Basic editor by right-clicking on the worksheet > Click on View Code.
- Now, write down the following code.
VBA Code:
Function stringToLong(myString As Variant)
stringToLong = CLng(myString)
End Function
- In the end, press F5 and run the code. We will see the date in a number.
- Now, go to cell E5 and type the following formula =stringToLong, and we will see our created function like the following image.
Here, we created a user function through this code Which will help us to convert string to long and will work like any other function in Excel.
- Now, type the following
=stringToLong(D5)
- Finally, press ENTER key and we will get our desired result.
Read More: How to Convert Green Triangle to Number in Excel
Download Practice Workbook
Conclusion
That’s all for the article. We have shown you several ways to convert a string into a long using VBA. Hope this has been of use to you. Please let us know if you have any questions, recommendations, or criticism in the comments area.
Do the change persist? If I have a variable that was converted and then called by another function. Does it revert to its original type (string or long) or does it remain what I converted it to?
Thank you PETE BAGALAYOS for your comment.
Yes, the change persists. Once you convert a variable from string to long, it remains in the long format. Well, if you need the string format, you will have to convert it from long to string again.