Writing assignment statements

In VBA, there are two types of assignment, value assignment and object assignment. Here I will discuss value assignment. Value assignment means that a variable or the property of an object is given a value, for example strName = "Jean" or s = l * w. The characteristic of an assignment is that it has a source on the right-side, a destination on the left, and the '='-sign in between. For sure, this is the most common type of statement in any program. The following shows different ways to assign values.

run-time error Type mismatch

Warning: not all assignments will be acceptable. What would be the result of Dim i As Integer: i = "a"? Explicitly typing variables helps prevent such errors. If instead we would have coded Dim i: i = "a", leaving the type out, you will stay in blissful ignorance until you try to use variable i in multiplication at which point runt-time error 'Type mismatch' appears.

Using literals

A literal is value that is expressed as itself rather than as a variable's value or the result of an expression, such as the number 3 or the string "Hello". In the examples direct below I precede the assignment statement by a declaration statement to show the correct way to code literals depending on intended type. Note that I use the colon symbol ':' as line-continuation character, which allows me to write the two statements, declaration and statement on a single line, making the code more compact.


Dim i As Integer: i = 1
Dim strGreeting As String: strGreeting = "Hello!"
Dim datBirth As Date: datBirth = #12/25/92# 

Copy the value from another variable

Generally you want to limit the number of variables, but there may be specific reasons why you may want to make copies. For example, in a loop you may want to detect if a value is different from the previous.


strPreviousValue = strValue

Obtaining the value from an object property

Although using a property value directly is the preferred approach, you may sometimes want to store the value in a variable if you don't want to retrieve the same value from a much used property repetitively.


strCustomerID = Customer.ID

Giving an object property a value

An object property, assuming it is defined as 'writable' can be assigned a value same way as a variable.


Customer.ID = strCountryCode & "-" & strCustomerCode

Value returned from a function procedure

The most common use of a variable is to store the result of some function call, maybe a calculation or the content of a line in a file.


dblSurface = dblLength * dblWidth
Line Input #iFileNumber, str

Image below shows context sensitive IntelliSense letting you select a VBA function -or any of the other ways of assigning a value discussed above- that will return a type Long value from a shortcut menu. This feature is made available from the Code VBA add-in.

Value assignment