Definition of Excel VBA Variable and Datatype

Today, We will know the Definition of Excel VBA Variables and Datatype. We will also explain the variable and their data type. Read: How to Write Macros in Excel and Use

Variables are used in almost all computer programs and VBA is no different. It’s a good practice to declare a variable at the beginning of the procedure. It is not necessary, but it helps to identify the nature of the content (text, data, numbers, etc).

VBA Variables

Variables are specific values that are stored in a computer memory or storage system. Later, you can use that value in code and execute. The computer will fetch that value from the system and show it in the output. Each of the Excel VBA variable types must be given a name.

To name the variable in VBA, you need to follow the following rules.

  • It must be less than 255 characters
  • No spacing is allowed
  • It must not begin with a number
  • Period is not permitted

Here is some example for Valid and Invalid names for variables in VBA.

VBA Data Types, Variables & Constant Valid NamesVBA Data Types, Variables & Constant Invalid Names
My_CarMy.Car
MyNewCar11_MyNewCar (not begin with number)
EmployeeIDEmployee ID ( Space not allowed)
Excel VBA Variable Example

In VBA, we need to declare the variables before using them by assigning names and data types.

In VBA, Variables are either declared Implicitly or Explicitly.

  • Implicitly: Below is an example of a variable declared Implicitly.
  • label=Eduguru
  • volume=7
  • Explicitly: Below is an example of variable declared Explicitly. You can use “Dim” keyword in syntax
  • Dim Num As Integer
  • Dim password As String

VBA variable is no different than other programming languages. To declare a variable in VBA you use the keyword “Dim.”

The syntax for VBA Variable,

To declare a variable in VBA, type Dim followed by a name:

Sub Exercise ()
        Dim <name>
End Sub

Before we execute the variables we have to record a macro in Excel. To record, a macro do the following –

Step 1): Record the Macro 1

Step 2) : Stop Macro 1

Step 3): Open the Macro editor, enter the code for variable in the Macro1

Step 4): Execute the code for Macro 1

For example, for VBA Variable

Sub Macro1()
	Dim Num As Integer
	Num = 99
	MsgBox " Eduguru" & Num
End Sub

Run this code and See what the Output is coming.

Excel VBA Data Types

The computer cannot differentiate between the numbers (1,2,3..) and strings (a,b,c,..). To make this differentiation, we use Data Types.

VBA data types can be segregated into two types

  • Numeric Data Types
TypeStorageRange of Values
Byte1 byte0 to 255
Integer2 bytes-32,768 to 32,767
Long4 bytes-2,147,483,648 to 2,147,483,648
Single4 bytes-3.402823E+38 to -1.401298E-45 for negative values 1.401298E-45 to 3.402823E+38 for positive values.
Double8 bytes-1.79769313486232e+308 to -4.94065645841247E-324 for negative values
4.94065645841247E-324 to 1.79769313486232e+308 for positive values.
Currency8 bytes-922,337,203,685,477.5808 to 922,337,203,685,477.5807
Decimal12 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
Data TypeBytes UsedRange of Values
String (fixed Length)Length of string1 to 65,400 characters
String (Variable Length)Length + 10 bytes0 to 2 billion characters
Boolean2 bytesTrue or False
Date8 bytesJanuary 1, 100 to December 31, 9999
Object4 bytesAny embedded object
Variant(numeric)16 bytesAny value as large as Double
Variant(text)Length+22 bytesSame as variable-length string
VBA Data Type

In VBA, if the data type is not specified, it will automatically declare the variable as a Variant.

Constant in VBA

Constant is like a variable, but you cannot modify it. To declare VBA constants, you can use the keyword const.

There are two types of constant,

  • Built-in or intrinsic provided by the application.
  • Symbolic or user defined

You can either specify the scope as private by default or public. For example,

Public Const DaysInYear=365

Private Const Workdays=250

Leave a Reply