In this article, we will learn the Variables, Data types and Constants.
What is Variable and how it works?
Variable is a storage location in our computer’s memory by using which we can assign our value to a variable. It is useful to save the memory our computer and also it provides required memory to the code.
There are some rules to define the variable while writing the VBA code:-
- It should be less than 255 characters.
- Space is not allowed while defining the variables
- Variable should not begin with number.
- Period is not allowed.
- Special declaration type of character is not allowed except (_).
In below mentioned table, you can see the example of Valid or Invalid Variable:-
|Valid||Invalid||What is invalid?|
|Apple_Phone||Apple.phone||(.) is invalid here instead of this we should use (_)|
|LastMonth||12Month||1st letter numerical is not allowed|
|Live_Long_Life||Live&Enjoylonglife||Special Characters are not allowed|
|Value78||Emp ID||Space are not allowed|
While writing the VBA code, we need to declare the variables before using them by assigning names and data types. There are many data types which are divided into two types, Numerical and Non-Numerical.
Numeric data types:-
For Numeric data types, follow below given types. It will help to calculate the numbers into needed memories: –
|Type||Bytes used||Range of Values|
|Byte||1 byte||0 to 255|
|Integer||2 bytes||(-32,768) to 32,767|
|Long||4 bytes||(-2147483648) to 2147483648|
|Single||4 bytes||(-3.402823E+38 to -1.401298E-45 )for negative values
1.401298E-45 to 3.402823E+38 for positive values.
|Double||8 bytes||(-1.79769313486232e+308 to -4.94065645841247E-324) for negative values
4.94065645841247E-324 to 1.79769313486232e+308 for positive values.
|Currency||8 bytes||(-922,337,203,685,477.5808) to 922,337,203,685,477.5807|
|Decimal||12 bytes||(+/- 79,228,162,514,264,337,593,543,950,335) if no decimal is use
+/- 7.9228162514264337593543950335 (28 decimal places).
Non-Numeric data types: –
For Non-Numeric data types, follow below given types mentioned in table: –
|Type||Bytes used||Range of Values|
|String (Fixed Length)||Length of String||1 to 65,400 characters|
|String (Variable Length)||Length + 10 bytes||0 to 2 billion characters|
|Date||8 bytes||January 1, 100 to December 31, 9999|
|Boolean||2 bytes||True or False|
|Object||4 bytes||Any embedded object|
|Variant (numeric)||16 bytes||Any value as large as Double|
|Variant (text)||Length + 22 bytes||0 to approximately 2 billion|
Now, we will learn how to mention the Variables while writing the code. Generally, variables declared at the beginning of the procedure. For Example: –
- Dim:- It will declare the Variable
- First_Name:- It is the name chosen for this variable (Spaces are not allowed)
- As:- Here, we will declare the type of Variable
- Integer:- Variable type
Variable mentioning is not at all necessary, but if you will take it in practice it makes it easier and can help to resolve problems. If you do not define the variables then by default, VB will declare it as variant.
Let’s take an example to write the VBA code by using variables: –
We want to enter the data in a manner so that Cell A1 contains name, Cell A2 contains Birthdate, and cell A3 contains Income.
Follow below given steps to write the code: –
- Open Excel file and press the key Alt+F11 on your keyboard
- Insert command button from the Developer tab
- Then drag the button in Excel, right click with mouse, and then click on Assign Macro.
- Assign Macro dialog box will appear. Now, give the Macro a name, and then click on OK.
- VBE page will get open.
- Write the below mentioned code: –
- When you will click on command button, you will get the result in Excel
This is the way we can define the variables in our code and take the benefit of it.