This post will guide you how to declare, initialize and display a variable value in MS excel VBA.
Declare a VBA variable
If you want to declare a variable as Integer, just type the below code line:
Dim myNum as Integer
The above code declare a variable “myNum” as integer type.
Initialize a VBA variable
It’s very simple to initialize a VBA variable, just following the below code:
Dim myNum as Integer myNum = 28
You must to declare the variable firstly, then initialize its value.
The data types contains Byte, Boolean, Integer, Long, Currency, Decimal, Single, Double, Date, String, Object, Variant and user defined type. Each data type have different range of values, see below table:
|Type||Range of Values|
|Byte||0 to 255|
|Boolean||True or False|
|Integer||-32768 to 32767|
|Long||-2,147,483,648 to 2,147,483,648|
|Single||-3.402823E+38 to -1.401298E-45 for negative values
1.401298E-45 to 3.402823E+38 for positive values.
|Double||-1.79769313486232e+308 to -4.94065645841247E-324 for negative values
4.94065645841247E-324 to 1.79769313486232e+308 for positive values.
|Currency||-922,337,203,685,477.5808 to 922,337,203,685,477.5807|
|Decimal||+/- 79,228,162,514,264,337,593,543,950,335 if no decimal is use
+/- 7.9228162514264337593543950335 (28 decimal places).
|String (fixed length)||1 to 65,400 characters|
|String (variable length)||0 to 2 billion characters|
|Date||January 1, 100 to December 31, 9999|
|Object||Any embedded object|
|Variant (numeric)||Any value as large as double|
|Variant (text)||Same as variable-length string|
Example1: Variable declaration and initialization
1# open visual Basic Editor, then insert a module and name as :myVariableDecIniDemo1
2# enter into the below VBA codes in code window.
Sub myVariableDecIniDemo1() Dim myNum As Integer myNum = 45 Dim myStr As String myStr = "excelhow.net" Dim birthD As Date birthD = 31 / 7 / 2017 MsgBox "myNum is" & myNum & Chr(10) & "my string is " & myStr & Chr(10) & "my birthday is " & birthD End Sub
3# back to workbook and run the above macro.