ArtsAutosBooksBusinessEducationEntertainmentFamilyFashionFoodGamesGenderHealthHolidaysHomeHubPagesPersonal FinancePetsPoliticsReligionSportsTechnologyTravel
  • »
  • Technology»
  • Computers & Software»
  • Computer Science & Programming»
  • Programming Languages

VBA: How to Declare & use Variables in Excel

Updated on September 11, 2012

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 Sub

Ready for More?

In the next module, we will be working with ranges. Click here when you are ready to proceed.

Comments

    0 of 8192 characters used
    Post Comment

    • jonhaus profile image
      Author

      jonhaus 5 years ago from Kansas City, Mo

      Thanks. Hopefully you do. This has proved to be the most valuable skill I have learned so far in my career.

    • aisha91 profile image

      Rasna Aisha 5 years ago from Manila, Philippines

      Hi jonhaus,

      I don't know VBA yet, but it seems really interesting...

      Hopefully I will learn this language too.

      Rated it useful...:)

      Welcome here on HP and happy writing :)