Assigning values to variables

A variable declaration tells VBA what type of data a variable can hold, but the purpose of creating variables is to store values in them (see Table 5.2). This section shows how values can be assigned to a variable. The following examples illustrate how variables of different types can be assigned values. Note that literal strings are enclosed in "double quotes". Dates are enclosed in #hashes#. Values of a Boolean variable are assigned as True or False.

NameOfVariable = value

NoOfNectarPoints = 5000 'Integer assignment

MyDaughtersName = "Rhiannon" 'String assignment lastDayOfTerm = #3 Apr 2003#

'To enter literal date values, place # before and after the date studentPresent = TRUE ' Boolean assignment

Set TopCell = Range("A1")

'This assignment is slightly different - note the use of Set 'this associates a worksheet range with TopCell

Take care when using assignment statements. To understand why, consider the statements x = y and y = x. While being algebraically the same, they are not the same assignments and will produce different results. For example, if the content of variable x is 10 and the content ofy is 25, then x = y will result in both variables taking the value 25, since x = y means x is assigned the content ofy (25). On the other hand, y = x will result in both variables taking the value 10, since y is assigned the content of x which is 10.

Table 5.2 Summary of how variables are declared and assigned in VBA.




Type declaration in VBA

How assigned in VBA



3 Apr 2003

Dim LastDayOfTerm As Date

LastDayOfTerm = #3/4/ 2003#




Dim MyDaughtersName as String

MyDaughtersName = "Rhiannon"




Dim NoOfNectarPoints As Integer

NoOfNectarPoints = 5000



Cell A1

Dim TopCell As Range

Set TopCell = Range("A1")

The following example declares a date variable called LastDayOfTerm and assigns it the value 3/4/2003 (3rd April 2003). The date is then displayed in a MsgBox (see Figure 5.2).

Sub dateShow ()

Dim LastDayOfTerm As Date LastDayOfTerm = #3/4/2003# MsgBox LastDayOfTerm


Figure5.2 The Date MsgBox Output in American Format

0 0

Post a comment