How Do I Convert an Integer to a String in Excel VBA?
How do I convert the integer value "45" into the string value "45" in Excel VBA?
CStr(45) is all you need (the Convert String function)
Try the CStr() function
Dim myVal as String; Dim myNum as Integer; myVal = "My number is:" myVal = myVal & CStr(myNum);
Read more... Read less...
Most times, you won't need to "convert"; VBA will do safe implicit type conversion for you, without the use of converters like
The below code works without any issues, because the variable is of Type String, and implicit type conversion is done for you automatically!
Dim myVal As String Dim myNum As Integer myVal = "My number is: " myVal = myVal & myNum
"My number is: 0"
You don't even have to get that fancy, this works too:
Dim myString as String myString = 77
The only time you WILL need to convert is when the variable Type is ambiguous (e.g., Type Variant, or a Cell's
Value (which is Variant)).
Even then, you won't have to use
CStr function if you're compounding with another String variable or constant. Like this:
Sheet1.Range("A1").Value = "My favorite number is " & 7
"My favorite number is 7"
So, really, the only rare case is when you really want to store an integer value, into a variant or Cell value, when not also compounding with another string (which is a pretty rare side case, I might add):
Dim i as Integer i = 7 Sheet1.Range("A1").Value = i
Dim i as Integer i = 7 Sheet1.Range("A1").Value = CStr(i)
In my case, the function CString was not found. But adding an empty string to the value works, too.
Dim Test As Integer, Test2 As Variant Test = 10 Test2 = Test & "" //Test2 is now "10" not 10
If the string you're pulling in happens to be a hex number such as E01, then Excel will translate it as 0 even if you use the CStr function, and even if you first deposit it in a String variable type. One way around the issue is to append ' to the beginning of the value.
For example, when pulling values out of a Word table, and bringing them to Excel:
strWr = "'" & WorksheetFunction.Clean(.cell(iRow, iCol).Range.Text)
If you have a valid integer value and your requirement is to compare values, you can simply go ahead with the comparison as seen below.
Sub t() Dim i As Integer Dim s As String ' pass i = 65 s = "65" If i = s Then MsgBox i End If ' fail - Type Mismatch i = 65 s = "A" If i = s Then MsgBox i End If End Sub