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.
- Value assignment
- Using literals
- Copy the value from another variable
- Obtaining the value from an object property
- Value returned from a function procedure
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.
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.