Vba datatype conversion in Excel

This topic is of much imporatnce and is most often used feature of VBA in Excel.

This is because, Excel sheet can store data in both text and numbers irrespective of the type of data.

So when a mathematical calculation has to be performed with some numbers that are stored as text,

the numbers has to be converted to correct datatype, else the program will return an error.

This is where the type conversion comes into the picture. The same is true for conversion from number to text.

The keywords associated with conversion are Cint,CStr,CDbl and Cdate

The following examples illustrates these

  1. Sub data_conv()
  2. Dim num As String
  3. num = "5"
  4. MsgBox "CInt " & CInt(num) + 2
  5. Dim num2 As String
  6. num2 = "5.87"
  7. MsgBox "CDbl " & CDbl(num2) + 4
  8. Dim num3 As String
  9. num3 = "654578"
  10. MsgBox "CLng " & CLng(num3) + 2
  11. End Sub

From the above example, it is seen that, the numbers are stored as string which are converted to corresponding numerical type before performing mathematical calculation.

A glimpse of the editor screenshot is shown below

excel vba datatype conversion

The output is shown below

excel vba datatype conversion

excel vba datatype conversion

excel vba datatype conversion


You can find similar Excel Questions and Answer hereunder

1) The scenario manager in Excel VBA allows to explore various scenarios in a very easy way

2) Can I change the way that huge numbers are displayed in Y-Axis so that they do not take up chart space?

3) How can I add a 3 Month Moving average Trend line to my line chart?

4) How can I make my macro wait for 5 secs before executing the next command?

5) How can I find the least common multiple using Excel?

6) How to enable or disable macros execution or loading in Excel

7) How to change desktop background in Excel

8) How to do date and time zone conversion in excel in Excel

9) How can I identify cells with Circular Reference in a WorkSheet?

10) How to create Pivot table in excel VBA


Here the previous and next chapter