Variables, Data types and Constants

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 First_Name as Integer

  • 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

image 1

  • 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: –

Sub Command()
Dim Name As String
Dim Birthdate As Date
Dim Income As Currency
Name = “John”
Birthdate = “31 March 1989”
Income = 10000
Range(“A1”) = Name
Range(“A2”) = Birthdate
Range(“A3”) = Income
End Sub

image 2

  • When you will click on command button, you will get the result in Excel

image 3

This is the way we can define the variables in our code and take the benefit of it.

Previous Chapter Next Chapter

Leave a Reply