- Post navigation
- Excel VBA Application Object - the Default Object in Excel
- CreateObject function (Visual Basic for Applications) | Microsoft Docs
- VBA - Excel Objects
This can be any workbook you have created. The Workbook object contains other objects, such as the Worksheet object. The Worksheet object contains other objects, such as the Range object. The Create a Macro chapter illustrates how to run code by clicking on a command button. We used the following code line:. Fortunately, we do not have to add a code line this way. That is because we placed our command button in create-a-macro. Be aware that if you want to change things on different worksheets, you have to include the Worksheet object. Read on.
You may have noticed that Workbooks and Worksheets are both plural. That is because they are collections. The Workbooks collection contains all the Workbook objects that are currently open. The Worksheets collection contains all the Worksheet objects in a workbook. You can refer to a member of the collection, for example, a single Worksheet object, in three ways. In the Project Explorer, the first name is the CodeName. The second name is the worksheet name Sales. There is one disadvantage, you cannot use the CodeName if you reference a worksheet in a different workbook.
Now let's take a look at some properties and methods of the Workbooks and Worksheets collection. The workbook property Fullname is also a read-only property. Properties can also Set and Get objects. For example, the Worksheet has a UsedRange property that return a Range object. You will notice we used the Set keyword here. We will be looking at this in detail later in the post. Methods are used to perform some action to do with the object data. With a Collection, the main data is the group of items we are storing.
You can see that the Add, Remove and Count methods all perform some action relating to this data. Visual Basic is an event-driven language. What this means is that the code runs when an event occurs. Common events are button clicks, workbook Open, worksheet Activate etc. In the code below we display a message each time Sheet1 is activated by the user.
This code must be placed in the worksheet module of Sheet1. We create an object using the New keyword. If we try to use an object before it is created we will get an error. For example, take a look at the code below. When we reach the Add line no Collection has been created. We can perform these steps in one line using Dim and New together.
Alternatively, we can declare the variable in one line and then create and assign the object in another line using Set. Using code like does not provide much flexibility. It will always create exactly one Collection when we run our code. In the next section we will look at Set. This allows us to create objects based on conditions and without having to declare a variable for each new object.
Excel VBA Application Object - the Default Object in Excel
We can declare an object variable in one line and then we can use Set to create and assign the object on another line. This provides us with a lot of flexibility. In the code below we declare the object variable using Dim. We then create and assign it using the Set keyword. We use Set in this way when the number of objects can vary. Using Set allows us to create multiple objects.
In other words, we can create objects as we need them. Later in this post we will see some examples of using Set to create objects. There are some subtle differences between using New with Set and using New with Dim. If you put a breakpoint on the Add line and check the variable value you will see the following message. When the Add line runs, the Collection will be created and the variable will now show a Collection with one item. The reason for this is as follows. A Dim statement is different to other VBA lines of code.
CreateObject function (Visual Basic for Applications) | Microsoft Docs
It allocates memory based on the items in the Dim statements. It is not in a position to run any code at this point. Creating an object requires more than just allocating memory. It can involve code being executed. So VBA must wait until the code in the Sub is running before it can create the object. Using Set with New is different in this regard to using Dim with New. There is another subtlety to keep in mind using New. If we set the object variable to Nothing and then use it again, VBA will automatically create a new object e.
It also creates the worksheet object for each worksheet in that workbook. This is great news. VBA is doing all the work for us. So when we use Workbooks. Open , VBA opens the file and creates the workbook object for the workbook. An important point to remember is that there is only one object for each workbook. If you use different variables to reference the workbook they are all referring to the same object e. There are some very useful libaries that are not part of Excel VBA.
These are written using COM interfaces. The beauty of COM is that we can easily use these libraries in our projects. The first method is referred to as Early Binding and the second is referred to as Late Binding see Early versus Late Binding for more details. The Let keyword is optional so nobody actually uses it. However, it is important to understand what it is used for. When we assign a value to a property we are using the Let Property. When we assign an object variable we use the Set keyword instead of the Let keyword. Using the Set keyword is mandatory.
If we forget to use Set we will get the error below.
VBA - Excel Objects
It may look like Let and Set are doing the same thing. But they are actually doing different things:. To understand more about this we need to take a peek pun intended: Pragmatists suffer it. Some can avoid it. To understand what New and Set are doing we need to understand how variables are represented in memory. When we declare variables, VBA creates a slot for them in memory.
You can think of the slot as an Excel cell in memory. When we assign values to these variables, VBA places the new values in the appropriate slots. This line creates the object in memory. It stores the address of the object in the variable. In programming, this is known as a Pointer. Because VBA handles this seamlessly it can seem as if the object variable and the object are the same thing. Once we understand they are different it is much easier to understand what Set is actually doing. Only one Collection has been created here.
So coll1 and coll2 refer to the same Collection. When we use Set we are copying the address from coll1 to coll2. Earlier in the post we looked at Workbook variables. When we open the workbook Accounts. When we assign the workbook variables in the code above, VBA places the address of the workbook object in the variable. VBA uses the address in wk1 to determine the workbook object to use. It does this seamlessly so when we use a workbook variable it looks like we are referring directly to the object. When we pass an object to a procedure only the address passed.
When we pass an object from a Function Subs cannot return anything only the address of the object is passed back. In the code below we have one collection. It is the address that gets passed to and from the function.
When we pass a simple variable to a procedure we can pass using ByRef or ByVal. ByRef means we are passing the address of the variable.
If the variable changes in the procedure the original will also be changed. ByVal means we are creating a copy of the variable. If the variable changes in the procedure the original will not be changed. What this means is that if we change the object in the procedure it will be changed outside it — this is regardless of whether you use ByVal or ByRef.
For example, in the code below we have two procedures that change the Collection. One uses ByRef and one uses ByVal. In both cases the Collection has changed when we return to the TestProcs Sub. In this example, we get different results from ByVal and ByRef. In the PassByVal Sub, a copy of the original object variable is created. So it is this copy that points to the new Collection. So our original object variable is not affected. In the PassByRef Sub we are using the same object variable so when we point to the New Collection, our original object variable is now pointing to the new collection.
Imagine you had a Collection with entries. Think how inefficient it would be to create multiple copies of this Collection when your application was running. Think of it like a library which is a real world collection of books. We can put the Library address in directories, newspapers etc. A person simply uses the address to go to the Library and add and remove books. There is one Libary and the address is passed around to anyone who needs to use it.
If we wanted a second library we would create a new library. It would have a different address which we could also pass around. The code below uses. But what is interesting is when we compare the addresses. Use LongPtr instead of Long if you are using a 64 bit version of Excel. Address of the variable coll1 is Address of the variable coll2 is Address coll1 collection is Address coll2 collection is This shows that we have two different variables which contain the address of the same Collection.
So what happens if we set a variable to a New object multiple times?
In the code below we use Set and New twice for the variable coll. In this example, we created two new Collections in memory. When we created the second collection we set coll to refer to it. This means it no longer refers to the first collection. In fact, nothing is referring to the first Collection and we have no way of accessing it. In VBA however, this memory will be cleaned up automatically. This is known as Garbage Collection. Let me clarify this point. If an object has no variable referring to it, VBA will automatically delete the object in memory.
By placing a message box in this event we can see exactly when it occurs. Step through the following code using F8. When you exit the function you will get the message saying Jill was deleted. VBA automatically deletes objects when they go out of scope. As we have seen VBA will automatically delete the object as soon as we go out of scope. So in most cases setting the object to Nothing is not doing anything.
An example would be emptying a Collection. Imagine the following project. You open a workbook and for each worksheet you read all the customer data to a collection and process it in some way. First, we create a very simple class module called clsCustomer and add the following code. In our first scenario, we are reading from a list of customers from a worksheet.
The number of customers can vary between 10 and Not only is it a lot of wasteful code, it also means we can only deal with maximum customers.
What we do first is to get the count of rows with data. Then we create a customer object for each row and fill it with data. We then add this customer object to the collection. We then add the customer to the Collection. What happens here is that VBA creates a copy of the object variable and places it in the collection. Imagine we have a fixed number of customers but only want to read the ones whose name starts with the letter B. We only create a customer object when we find a valid one.
This concludes my post on VBA Objects.