Convert Text to Number VBA

One of the little known ways to convert a text string which is a number but has been entered as text is to multiply the text by 1.  For example if cell A1 was:

"100" 

To change this cell to be equal to 100 this formula:

=A1 * 1

would result in the text being equal to a number (100).  

The same can be done with VBA.  Try the following.  It will replicate the formula with an Excel VBA procedure.

Sub texttoNum() 'Basic Excel VBA procedure of a text to a number
Dim i

i="100"
i=i * 1
MsgBox Application.IsNumber(i)

End Sub

I have declared i as a variant above to keep the variant type open.  If I had declared the variable (i) as a String or an Integer then the procedure would have fallen over.  

Excel now has an inbuilt procedure which will change a whole batch of numbers from text to a number.  This has made things a lot easier when changing data in one simple action.  There is usually a green symbol in the top left of cell.  Highlight the whole range and click on this green text symbol

Notice the green symbol in the corner?  After highlighting the column click on the exclamation mark at the top.

Click on Convert Text to Number.  

The VBA equivalent is as follows.  It will run very quickly as it runs in memory. 

Sub texttoNum2() 'Excel VBA static example of the text to numbers procedure.
Dim ar As Variant
Dim var As Variant
Dim i As Integer

ar=[d2:d13]
ReDim var(1 To UBound(ar), 1 To 1)

For i=1 To UBound(ar) 'Start of VBA loop
var(i, 1)=ar(i, 1) * 1
Next i
[e2:e13]=var
End Sub

To add a bit of flexibility to the code. The ranges are dynamic in the following example.

Sub texttoNum3() 'Excel VBA dynamic example of the text to numbers procedure.
Dim ar As Variant
Dim var As Variant
Dim i As Integer

ar=Range("a11", Range("A" & Rows.Count).End(xlUp))

ReDim var(1 To UBound(ar), 1 To 1)

For i=1 To UBound(ar) 'Start of VBA loop
var(i, 1)=ar(i, 1) * 1
Next i
Range("C11:C" & UBound(ar) + 10)=var
End Sub

The following Excel file outlines the VBA procedure in a practical example.