Disclosure: This post may contain affiliate links, meaning when you click the links and make a purchase, we receive a commission.

How to Convert String to Long Using VBA in Excel (3 Ways)

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.

Convert String To Long in Excel VBA


Download Practice Workbook


3 Methods to Convert String to Long Using Excel VBA

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.

Convert String To Long in VBA

Steps:
First, right-click on the sheet and go to View Code.

Convert String To Long manually VBA

  • 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.

 

Convert String To Long in VBA ranges

  • 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

Convert String To Long in ranges using vba

  • 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


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

Convert String To Long user 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.

Convert String To Long User Function

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.

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.


Further Readings

Mahbub
We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo