Excel Visual Basic (VBA) Tutorial - Part 2

Visual Basic Variables

Of course Excel macros can do more than just repeat actions which you have previously recorded. The Visual Basic language includes a full set of looping commands, string operations, mathematical functions and so on.

A variable is a lump of data with a label attached to it so you can refer to it. In Excel a value in a cell can be a number, a string of characters, a date and so on. Each cell has a label attached as well, such as A1, so that you can refer to it in other cells or in formulas. VB lets you create your own variables in your programs, so you can store values (such as tax rates or prices, for example) and use them in your code.

And, like all programming languages, it features a full set of data types to give your macros maximum flexibility. A data type represents one specific type of thing, be it a type of number, a string of characters to make words, values which represent whether something is true or false, and so on. VB includes a full range of data types such as Boolean, Double, Integer, Long, Single and String. Excel also includes data types for Currency and Date. As you grow more proficient in VB you'll need to look into these in more detail, for now it's important to know that your programs can contain variables of different types.

Additionally Visual Basic supports a data type called Variant which is used to contain data that has not been explicitly declared as some other data type. So, for example, a variant can contain an integer, double, date, string and so on. If you do not specify a data type when you declare a variable then Visual Basic will automatically assign it as a variant. This is fine for most small macros and Excel applications, however a variant data type requires more memory than using one of the other explicitly defined types which may cause problems at some point.

The simplest method of declaring a variable is to use the 'Dim' statement within your procedure. For example to declare an integer variable called number we would include the following declaration in our code:

	Dim number As Integer

Variables can be declared in a number of different ways, depending on the scope of the variables and their lifetime. Scope refers to where the variables are 'seen' (accessed) - either within the procedure or function in which they are declared, within the module in which they are declared or finally, they can be seen or accessed by all modules within a workbook. For procedure level scope we simply declare the variable within the procedure. For example:

	Sub HelloWorld()
		Dim Msg As String
		Msg="Hello World!"
		MsgBox Msg
	End Sub

If we had wanted the string variable to be available to all of the procedures within our module, the variable declaration would have been made outside of the procedure:

	Dim Msg As String
	Sub HelloWorld()
		Msg="Hello World!"
		MsgBox Msg
	End Sub

Finally, to make the string variable available to all modules within the workbook we need to use the 'Public' keyword and to declare the variable outside of a procedure:

		Public Msg As String

The life-time of a variable refers to the time during which the variable retains its value. Normally a procedure level variable is assigned a value when the procedure is called and once the procedure ends that value disappears. However there are times when you may want to keep the values of the variables between procedure calls, so that they are 'remembered' by your macro or program and can be accessed again. This can be achieved by using the 'Static' keyword instead of 'Dim' when declaring your variables. If you have a number of different variables you want remembered then you can declare an entire procedure or function to be static.

Now that we know a little more about Visual Basic we can extend our initial sample macro so that it creates a second column with a day number next to each of the days. Although there are different ways to do this, we'll take the opportunity to use what we've learned about variables and also to introduce the concept of looping. Let's define an integer variable to hold the day number and then use a loop to increment the day by one and to place it in the correct cell on the worksheet.

Sub Example2()

Dim number As Integer
Dim finished as String
  
  Sheets.Add
  ActiveCell.FormulaR1C1 = "Monday"
  Selection.AutoFill Destination:=Range("A1:A7"), Type:=xlFillDefault
  
  For number = 1 To 7
    ActiveSheet.Cells(number, 2) = number
  Next number
  
  finished = "Finished processing sheet!"
  MsgBox finished

End Sub

In addition to declaring an integer variable to hold the day number we have also declared a string variable to contain a message to tell us that the macro has finished executing. This string variable is passed as an argument to the MsgBox function which will flash it up on the screen when the macro is finished. The integer variable 'number' is used in a For…Next loop, and is assigned the values from 1 through to 7. These day numbers are then placed in the second column and the corresponding row number by assigning them to the ActiveSheet.Cells property.

Return to home page