Dot on i

There will be times within your Excel VBA code where you want to lock down the value of a variable but make it a variable nevertheless. For example, if your application makes reference to a tax rate, having the rate in a variable will offer you a mechanism for changing the rate wherever you have used it in your application. Thus, instead of entering code like “TaxAmount = SubTototal * 0.15″, you would use statements like “TaxAmount = SubTototal * TaxRate”, where tax rate would be a constant.

Constants have to be declared and initialised in one statement: for example “Const TaxRate as Currency = 0.15″. This is the only time that a value can be placed in the constant and herein lies one of its key benefits; the value it contains cannot later be accidentally overwritten because the programmer confuses this variable with another. The other key benefit is the ease with which constants allow us to update our applications. Thus, in the tax rate example, when the tax rate changes, we only need to modify one line of code and that change will update our entire application.

Another variable technique which allows us to “tighten up” the values which may be entered into a variable is to use User Defined Types (UDTs) which, essentially, allow you to define your own variable types. You can then declare as many variables of this given type as you need. Type definitions must be placed at the top of a module; they cannot be placed inside sub routines or functions. The definition consists of a code block inside which the various elements of the type are detailed as well as the data type associated with it. Here is an example of a type definition.

Type Employee

Name As String

Department As String

JobTitle As String

DateOfBirth As Date

StartDate As Date

Salary As Long

End Type

To use your UDT in our code, we use statements like the following:

Dim empNew As Employee

With empNew

.Name = “Gillian Spencer”

.Department = “Information Technology”

.JobTitle = “Web Developer”

.DateOfBirth = 12/05/1982

.StartDate = 25/03/2010

.Salary = 27500

End With

UDTs provide a very useful way of storing related variables in one place. In this regard, they resemble arrays; but, unlike arrays, the elements they contain are named rather than simply referred to by a numerical index.

You can get up to date information on Excel VBA training courses, visit Macresource Computer Training, an independent computer training company offering Excel VBA Classes in London and throughout the UK.