- HubPages»
- Technology»
- Computers & Software»
- Computer Science & Programming»
- Programming Languages
VBA: How to Declare & use Variables in Excel
Prerequisite Courses
What is a Variable?
Think of a variable as a container for holding data within VBA.
Like in real life the type and size of the container you use depends on what you want it to hold.
This hub will focus on the four types of variables I use most often:
String: This type of variable is used to store text.
Integer: This variable type is used to store whole numbers from 32,768 to 32,767.
Long: This type of variable is used to store numbers ranging from -2,147,483,648 to 2,147,483,647
Date: This type of variable is used to store dates. When using the date variable enclose a date with "#" signs.
The first step in writing code is declaring your variables.
Declaring Variables
Variables should be declared at the beginning of a macro.
You can use a variable without first declaring it, but I highly discourage this. If a variable is not declared excel will store it as a Variant (the largest type of variable).
You can prevent excel from allowing you to use an undefined variable by prefacing your module with "option explicit" Option Explicit should be included a the first line of code at the top of your module (not within a subroutine)
Defining your variable a very easy task. It is composed of three elements:
Dim VariableName as VariableType
ex:
Dim MyString as String
This would create a string variable name my string.
Note: A variable can contain no spaces. It is helpful to include upper and lowercase letters. When you reference your variable again VBA will mirror the case. This makes it easier to tell you have typed your variable name in correctly.
Assigning data to your variable
Data is assigned to your macro using the "=" operator. Below are two ways to assign data to a variable.
MyString="Test"
MyString=Range("A1").text
MyInteger=10
MyInteger=Range("A1").value
Returning Your Variable to a Cell
To return your variable to a cell, reference the cell followed by the "=" operator and the variable name. Do not enclose your variable name in quotes
Range("A1")=MyString
Cells(1,1)=Mystring
Let's Build a Macro to Test Our Variables
Option Explicit
Sub TestVariables()
'define variables
Dim MyString As String
Dim MyInteger As Integer
Dim MyLong As Long
Dim MyDate As Date
'assign values
MyString = "Congratlations! You have used your first variables."
MyInteger = 10
MyLong = 10700.7
MyDate = #01/01/2012#
'return variables to cells
Range("A1")=MyString
Cells(2,1)=MyLong
Range("C1")=MyDate
End SubReady for More?
In the next module, we will be working with ranges. Click here when you are ready to proceed.
Related
Excel VBA - Guide To Creating A New Sheet Everyday Automatically
Programming in Java Netbeans - A Step by Step Tutorial for Beginners: Lesson 4
Gun Rights: Part 2: Violent Crime: Will Reasonable Gun Control Save Lives?
Learn To Program: Variables and User Input
Excel VBA - Guide to Create a Login Form
