3/6/2018 Excel VBA Variables, Data Types & Constant

Home
Testing
SAP
Web
Must Learn!
Big Data
Live Projects
Blog

Excel VBA Variables, Data Types & Constant

Details
Last Updated: 10 January 2018

Variables are used in almost all computer program 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.)

In this tutorial, you will learn-

VBA Variables
VBA Data-Types
Constant in VBA

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 in the output. Each variable
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 are some example for Valid and Invalid names for variables in VBA.

Valid Names Invalid Names

My_Watch My.Watch

https://www.guru99.com/vba-data-types-variables-constant.html 1/11
3/6/2018 Excel VBA Variables, Data Types & Constant

NewCar1 1_NewCar (not begin with number)

EmployeeID Employee ID ( Space not allowed)

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

In VBA, Variables are either declared Implicitly or Explicitly.

Implicitly: Below is an example of a variable declared Implicitly.

label=guru99
volume=4

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."

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

Example, for VBA Variable

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

When you run this code, you will get the following output in your sheet.

https://www.guru99.com/vba-data-types-variables-constant.html 2/11
3/6/2018 Excel VBA Variables, Data Types & Constant

Excel VBA Data-Types

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

Type Storage Range of Values

Byte 1 byte 0 to 255
Integer 2 bytes -32,768 to 32,767
Long 4 bytes -2,147,483,648 to 2,147,483,648
-3.402823E+38 to -1.401298E-45 for negative values 1.401298E-45 to 3.402823E+38 for
Single 4 bytes
positive values.
-1.79769313486232e+308 to -4.94065645841247E-324 for negative values
Double 8 bytes
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
12 +/- 79,228,162,514,264,337,593,543,950,335 if no decimal is use +/-
Decimal
bytes 7.9228162514264337593543950335 (28 decimal places)

Non-numeric Data Types

Data 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
Boolean 2 bytes True or False
Date 8 bytes January 1, 100 to December 31, 9999
Object 4 bytes Any embedded object
Variant(numeric) 16 bytes Any value as large as Double
Variant(text) Length+22 bytes Same as variable-length string

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

Let see an example, on how to declare variables in VBA. In this example, we will declare three types of
variables string, joining date and currency.

Step 1) Like, in the previous tutorial, we will insert the commandButton1 in our Excel sheet.

https://www.guru99.com/vba-data-types-variables-constant.html 3/11
3/6/2018 Excel VBA Variables, Data Types & Constant

Step 2) In next step, right-click on the button and select View code. It will open the code window as shown
below.

Step 3) In this step,

Save your file by clicking on save button

Then click on Excel icon in the same window to return the Excel sheet.
You can see the design mode is "on" highlighted in green

Step 4) Turn off design mode, before clicking on command button

https://www.guru99.com/vba-data-types-variables-constant.html 4/11
3/6/2018 Excel VBA Variables, Data Types & Constant

Step 5) After turning off the design mode, you will click on commandButton1. It will show the following
variable as an output for the range we declared in code.

Name
Joining Date
Income in curreny

Constant in VBA
https://www.guru99.com/vba-data-types-variables-constant.html 5/11
3/6/2018 Excel VBA Variables, Data Types & Constant

Constant is like a variable, but you cannot modify it. To declare a constant in VBA you use 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

Download Excel containing above code

Download the above Excel Code

Summary:

Variables are specific values that are stored in a computer memory or storage system.
You can use "Dim" keyword in syntax to declare variable explicitly
VBA data types can be segregated into two types
Numeric Data Types
Non-numeric Data Types
In VBA, if the data type is not specified. It will automatically declare the variable as a Variant
Constant is like a variable, but you cannot modify it. To declare a constant in VBA you use keyword
Const.

Prev
Report a Bug
Next

YOU MIGHT LIKE:

VBSCRIPT VBA VBA

Top 25 VBScript VBA Excel Form How to Create

Interview Control & ActiveX Visual Basic for
Questions & Control Applications (VBA)
Answers in Excel with Examples

VBA VBA VBA

Excel VBA VBA Logical VBA String

Function Tutorial: Operators: AND, Operators

https://www.guru99.com/vba-data-types-variables-constant.html 6/11
3/6/2018 Excel VBA Variables, Data Types & Constant

Return, Call, Examples OR, NOT

VBA Tutorial
1) How to Write Macros in Excel
2) Visual Basic for Applications
3) Data Types, Variables, Constant
4) VBA Arrays
5) VBA Excel Form Control
6) VBA Arithmetic Operators
7) VBA String Operators
8) VBA Comparison Operators
9) VBA Logical Operators
10) Excel VBA: Call a Subroutine
11) Excel VBA Function
12) VBA Range Objects

Top Tutorials
About
About US
Advertise with Us
Write For Us
Contact US

Career Suggestion
SAP Career Suggestion Tool
Software Testing as a Career
Top Tools List
Certificates

Selenium

Testing
https://www.guru99.com/vba-data-types-variables-constant.html 7/11
3/6/2018 Excel VBA Variables, Data Types & Constant

Hacking
Interesting
Books to Read!
Suggest a Tutorial
Blog
Quiz

SAP

Java

Python
Execute online
Execute Java Online
Execute Javascript
Execute HTML
Execute Python

Jmeter

https://www.guru99.com/vba-data-types-variables-constant.html 8/11
3/6/2018 Excel VBA Variables, Data Types & Constant

Informatica

JIRA

© Copyright - Guru99 2018

Privacy Policy

https://www.guru99.com/vba-data-types-variables-constant.html 9/11
3/6/2018 Excel VBA Variables, Data Types & Constant

https://www.guru99.com/vba-data-types-variables-constant.html 10/11
3/6/2018 Excel VBA Variables, Data Types & Constant

https://www.guru99.com/vba-data-types-variables-constant.html 11/11