Contents
In VBA (Visual Basic for Applications), the Set statement is used to assign a reference to an object. This includes instances of classes, forms, controls, or any other object types. The Set keyword is essential when you are dealing with objects because it tells VBA that you want to set a reference to the object, rather than assigning a value.
Here’s how to use the Set statement:
Basic Syntax
Set objectVariable = object- objectVariable: This is the variable that will hold the reference to the object.
- object: This is the object that you want to assign to the variable.
Example Usage
Suppose you have an Excel workbook and you want to set a reference to one of its worksheets:
Dim ws As Worksheet
Set ws = ThisWorkbook.Sheets("Sheet1")In this example, ws is now a reference to “Sheet1” in the workbook.
Using Set with Collections
If you are working with a collection of objects, you use Set to assign a specific item from the collection to an object variable. For example:
Dim cell As Range
Set cell = Range("A1")Here, cell refers to the range “A1” in the active worksheet.
Releasing Object References
To release an object reference, set the object variable to Nothing using Set:
Set ws = NothingThis is particularly important in large applications or when dealing with COM objects to ensure that memory is managed efficiently.
Objects in Custom Classes
If you create a custom class in VBA, you use Set to assign an object to a property or variable of that class:
Dim myObject As MyClass
Set myObject = New MyClassDifference Between Set and Let/Scalar Assignment
- Use Set for objects.
- Use Let (or simply an assignment without Let or Set) for basic data types like Integer, String, Double, etc.
Error Handling
When assigning objects, it’s a good practice to include error handling to manage cases where the object might not be set as expected (for instance, if a specified worksheet does not exist).
Common Use Cases
- Assigning forms, controls, Excel ranges, Word documents, Access records, or DAO/ADO recordsets.
- Working with any external library that returns objects, such as FileSystemObject for file system access.
Remember that the Set statement only works with object variables, and trying to use it with non-object variables will result in a compile-time error.
Unlock Your Potential
Excel
Access
Power BI
