Visual Basic for Applications has a unique system of data types. One of the most fundamentally important concepts to understand is the difference between values and references in VBA, but it is very difficult to find clear explanations of this concept. I attempt to present one here. In this article I describe the difference between values and references, and between value types and reference types. In another article I will describe how the ByRef and ByVal keywords work.
Synonyms
When trying to make sense of this article in comparison with other articles and documentation, keep in mind that other sources sometimes use different terms:
- Value types are sometimes called primitive or simple types
- Reference types are sometimes called object types.
Values and value types
A value is piece of literal information, like the number 8 or the string “Hello”. Value type variables contain literal values; a variable of type Integer could contain the number 8, and a variable of type String could contain the value “Hello”. The formal way of saying this is that value types use value semantics, which means: “Whenever I type the name of the variable, I really mean the value of the variable”. This might seem rather obvious.
Consider the following example:
|
1 2 3 4 5 6 |
Dim int1 As Integer, int2 As Integer int1 = 17 int2 = int1 Debug.Print "int1 ="; int1; ", int2 ="; int2 int2 = 2 Debug.Print "int1 ="; int1; ", int2 ="; int2 |
First we assign the value 17 to the variable called “int1”. Then we assign int1 to int2. We obviously mean to copy the value of int1 (which is 17) to int2. This is value semantics in action. As a result, the first Debug.Print call shows that both int1 and int2 have the value of 17. Next, we assign the literal value 2 to the variable int2. The second Debug.Print call shows that int1 still has a value of 17 while int2 now has a value of 2.
This probably all seems blindingly obvious. The reason it matters is that it is completely different for references and reference types, as described next.
References and reference types
A reference is an indirect way to refer to something. The title of a book or the postal address of a building are references. They allow us to refer to big or complicated chunks of information with a small shorthand label. I can refer to the book “Moby Dick” just by those 9 characters; I don’t have to recite the entire contents of the book. At the same time “Moby Dick” is not the content itself, but only a reference. If I want to read the famous first sentence of the book I will have to use my reference (the title) at a library or bookstore to look up the actual content.
It’s the same for references and reference type variables in VBA. They don’t contain the content of the target. Instead they contain a reference to the target. Reference types use reference semantics, which means: “When I type the name of the variable, I really mean a reference to something else.”
Consider a worksheet in Excel. Its literal content is all the values, formulas, and formatting of all the cells in the sheet. But if we declare a variable of type Worksheet in Excel and then assign the first worksheet in the current workbook to that variable we definitely do not mean to copy the entire content of the worksheet to the variable. Instead, we mean only to copy the reference to the worksheet. If we then assign the first variable to another variable of type Worksheet we again copy only the reference to the worksheet, not the entire contents of the worksheet.
|
1 2 3 |
Dim ws1 As Worksheet, ws2 As Worksheet Set ws1 = ThisWorkbook.Worksheets(1) Set ws2 = ws1 |
Two references with the same target point to the same information. As a result, a change made to the target via one reference will be visible through all other references to that same target. In the following example all three variables are set to be references to the same worksheet. When we then change the name of the worksheet via just one of the variables, that change is immediately visible through all three variables.
|
1 2 3 4 5 6 7 8 |
Dim ws1 As Worksheet, ws2 As Worksheet, ws3 As Worksheet Set ws1 = ThisWorkbook.Worksheets(1) Set ws2 = ws1 Set ws3 = ws2 ws3.Name = "A new name" Debug.Print "ws1.Name = "; ws1.Name; _ ", ws2.Name = "; ws2.Name; _ ", ws3.Name = "; ws3.Name; |
The value of a reference
In my opening analogy I explained how I can use the title “Moby Dick” as a reference to the actual book. The content of the reference itself (the title) is the 9 character string “Moby Dick”. So what is the actual content of a reference in VBA? The answer is that it is nothing intelligible to a human, because it doesn’t need to be. It is not as simple as a memory address (a pointer), either. In fact the actual implementation of a reference is a complicated affair, but if you’re really curious you can get a taste from the following article on MSDN: Managing Object Lifetimes Through Reference Counting.
Further differences between value and reference types
Assignment and binding in VBA
In VBA, the difference between value types and reference types is made explicit by requiring the keyword Set when assigning a reference. In addition, you will often see assigning a reference referred to as “binding”.
|
1 2 3 4 5 |
Dim myInt As Integer, myWs As Worksheet ' Assigning a value to a value-type variable: myInt = 3 ' Assigning (or "binding") a reference to a reference-type variable: Set myWs = ThisWorkbook.Worksheets(1) |
All reference types are Objects in VBA
In VBA reference types are called Objects. Any reference type passed to the VarType function will return the vbObject constant, any reference type passed to the IsObject function will return True, and any reference can be assigned to a variable declared simply as type Object.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 |
Dim db As Database, td As TableDef, c As Collection, obj As Object Dim vType As VbVarType, bResult As Boolean vType = VarType(db) ' vbObject vType = VarType(td) ' vbObject vType = VarType(c) ' vbObject vType = VarType(obj) ' vbObject bResult = IsObject(db) ' True bResult = IsObject(td) ' True bResult = IsObject(c) ' True bResult = IsObject(obj) ' True Set obj = db ' Ok Set obj = td ' Ok Set obj = c ' Ok |
Everything else is a value type
All the other built-in data types in VBA are value types1. In addition, user defined types declared with the Type keyword, all enums, and all arrays are value types.
Got that? Arrays are value types!
The fundamental impact is that assigning an array copies the entire contents of the array. It also means that it is very difficult (or impossible) to work with array-typed members of objects or collections. This will be described in detail in a future post.
Conclusion
There are two fundamental groups of data types in VBA: Values and references. Data types that contain values are called value types. Data types that contain references are called reference types, object types, or simply objects. All reference types are compatible with the Object type. Unlike many other languages, arrays in VBA are value types, which means assignment causes the entire source array to be copied.
1 Not including the Variant type. The Variant type is not really a type itself, though, but a wrapper that can contain any of the other types.