A variable is an object that you create and in which you can store text (STRING), dates (DATE), numbers (INTEGER, LONG, SIMPLE, DOUBLE) or almost anything else (VARIANT).

I create variables that I use as and with counters. Copy/Paste the following procedure in a module and try it step by step:

Sub proTest()
Dim varCounter as Double
Dim varNumber as Double

     Range("A1").Select
     varNumber = 1
     For varCounter = 1 to 25
         Selection.Value=varNumber * 3
         varNumber =Selection.Value
         Selection.Offset(1,0).Select
    Next
End Sub

Sometimes I will count the number of rows, store the result in a variable and then do something as many time as there are rows:

Sub proTest()
Dim varCounter as Double
Dim varNbRows as Double
     Here I count the number of rows
     For varCounter = 1 to varNbRows
         Do this and this
    Next
End Sub

When I create a new workbook from a procedure in another workbook and I want to save the new workbook in the same directory as the workbook I am working in, I use a variable that I name "varPath". The reason I do this is that if the original workbook has been opened through Excel the default directory is the directory of the open workbook but if the original workbook has been opened through Explorer, the directory by default is "My Documents". SO to make sure that the new workbook is saved in the right directory, I sue the following cod:

Sub proTest()
Dim varPath as String
    varPath = ThisWorkbook.Path
    I then create a new workbook and when it is time to save it
    Activeworkbook.SaveAs varPath & "\newWorkbook.xls"
End Sub

I use variables in 99% of my procedures and you will do the same to reduce the number of hard coded values. Maintenance becomes so much simpler.

Option Explicit

When you open a module in the VBE the sentence "Option Explicit" should be the first line of code at the top. This means that you MUST declare all the variables that you use and there are many advantages to this obligation.

To activate or activate this option go to Tools/Options/Editor in the VBE and check the item "Require Variable Declaration". Now at the top of all code windows you will see "Option Explicit".

Why should this option be activated? Let's say your are using a variable named "varMyVar" and that you write vayNyVar = 32. You have misspelled the name of the variable and you will go on thinking that varMyVar 's value is 32 and IT IS NOT. If the Option Explicit is activated the VBE will tell you that your are using a non-existent variable "varNyVar". You will hence avoid a lot of possible errors.

Variable Declaration

Declare all your variables at the beginning of your procedures and always use the prefix "var" and one or many upper case letters within the name:
Dim varThisVariable as STRING
if you misspell the name of your variable within the procedure, Excel wont capitalize the letters telling you that there is something wrong. The prefix "var" will make your code easier to read.

Data Types

Because I want to keep things simple, I essentially use four types of variables:

STRING

Text, up to 65,000 characters.

I use it for text but also file names, path, worksheet names, workbook names, cells' addresses.

DOUBLE

Number with or without decimals

I could also use "Bytes", Integer", "Long" and "Single" but they all have limits.

DATE

If you use the STRING type for dates, you won't be able to perform calculations on them so use the "Date" type.

VARIANT

I use the VARIANT type each time I have a very large number of calculations to perform (more than 2,000 formulas). I bring the range into a VARIANT array, execute the calculations and bring back the range on the worksheet.

I have created this very simple procedure that executes 1,000,000 calculations in less than 5 seconds.

Public and Private Variables

When you declare a variable with the code Dim varMyVar as String it can only be used in the procedure from where it is declared. When the procedure ends, the variable disappear.

If you want to use a variable in many procedures you must declare it at the top of a module this way:
Public varMyVar as String
You must remember that this variable disappears only when the workbook is closed and that until then it carries the last value that you have stored in it. To clear the value you must use the code:
varMyVar = "" or varMyVar = Empty

I feel uncomfortable making a variable public so I use other approaches to carry the value of a variable from one procedure to the other.

Carrying the value of a variable from one procedure to the other

The easy way to carry the value of a variable from one procedure to the other is by storing this value in any cell of the workbook:
in the first procedure:
Range("A3456").Value=Variable1
in the second procedure:
Variable1=Range("A3456").Value

You can also carry the variable itself from one procedure to the another procedure that you call from the first one. For example:

Sub proTest1()
Dim varMyVar As Double
    varMyVar = 3
    Call proTest2(varMyVar)
End Sub

Sub proTest2(varMyVar)
    varMyVar = varMyVar * 2
    Range("A1").Value = varMyVar
End Sub

The variable is declared only in the first procedure. A value is stored in it (3). A second procedure is then called with the variable as argument. After the execution of the two procedures, the value of range A1 should be 6.

Array Variables

An ARRAY VARIABLE is a multidimensional variable that you can size to your liking: myVariable(3) is a variable consisting of 4 different values, myVariable(5,10) is a variable consisting in 66 values, 6 rows and 11 columns and myVariable(5,10,10) can carry 726 values, etc...

When you declare an array variable, the first element bears the number "0". The Variable varMyVariable(3) includes 4 elements from "0" to "3". If like me you are uncomfortable with a variable varMyVariable(0), you can impose that the first element bears the number "1". In the general declaration (where you find the Option Explicit), write:
Option Base 1
In this situation, myVariable(3) contains only three elements.

You can carry the value of a variable from one procedure to the other by stocking this value in any cell in the workbook. ex:
in the first procedure:
Range("A3456").Value=Variable1
in the second procedure:
Variable2=Range("A3456").Value