Excel VBA Objects – This article is the 9th in a series that discusses Excel VBA Macros. Before continuing to read, we recommend that you have finished studying the previous 8 Excel VBA tutorials.
In this series, the Excel Class will discuss the Excel VBA Object.
About Object Oriented Programming (OOP)
You need to know that Excel VBA is an object-oriented programming language or OOP for short. For those who are familiar with programming, I’m sure the term OOP is familiar.
For those who are new, maybe they will ask ” What is object-oriented programming? “
OOP ( Object Oriented Programming ) or object-oriented programming (PBO) is an object-oriented programming method. The purpose of OOP is to make it easier for developers to develop a program or application by following a model that already exists in everyday life.
In everyday life, you can imagine an object is an object with a specific function. Cell phones as a means of communication for example. An object can be composed of several other smaller objects. In the example of a cellphone, for example, it is made of a keypad, monitor, battery, casing, and other small devices, each of which of course also has other constituent parts.
Regarding OOP ( Object Oriented Programming ) please search for more information on Google .
Microsoft Excel Object
Object in Excel is a form of object in the Excel application that contains a set of data and reflects something and the ability to do something or respond to treatment as a whole.
Excel object forms that will often be used include workbooks , worksheets, ranges , cells, charts and so on.
Member Object
Each object in Excel VBA generally has certain characteristics, potentials and responses which are interrelated with one another.
This component of an object is called a member . The compiler can be in the form of certain attribute values, other objects, procedures to do something or to receive a treatment response to the object.
The object members can be grouped into 3 parts, namely:
- property
Data that shows a special feature or part of an object. Usually used to set or retrieve the value of an object.
- Methods
Is a procedure or function to perform a specific task on an object
- Events
Is a procedure or function that is triggered when an event, certain events or activities occur on an object.
A worksheet for example has:
- Properties : Name, Columns, Rows, Range, Cells, Comments, PageSetup, AutoFilter and so on.
- Methods : Activate, Calculate, Copy, Paste, PasteSpecial, Delete, Move, PrintOut, PrintPreview, Protect , etc.
- Events : Activate, BeforeDelete, BeforeDoubleClick, Change, Deactivate, SelectionChange , etc.
You can easily find each object and its members by opening the Object browser in VBE using the F2 shortcut or the buttons available on the standard toolbar .
We have discussed about VBE before. If you haven’t read it, you can learn about it on the following page: Visual Basic Editor .
You can identify each type of member in the browser object above from the icons next to the name of the member concerned. You can read an explanation of the other icons on this browser object on this Microsoft Docs page .
We will discuss this member in more detail in the next series. Soo, please subscribe to our articles so that they are always updated.
VBA Collections
When opening the Object Browser in VBE don’t be surprised if you find there are Worksheet objects ( without “s” ) and Worksheets ( with “s” ) or Workbooks and Workbooks .
In addition to existing objects, Excel also recognizes objects in the form of containers or collections of the same object. Such an object is known as a collection .
So Collection is also an object whose purpose is to group other objects that have the same Class.
Examples of Collections in VBA Excel that are commonly used include:
- Workbooks : A collection of one or more workbooks that are currently open.
- Worksheets : A collection of one or more worksheets of an excel workbook.
- Charts : A collection of charts in a workbook.
- Sheets : A collection of worksheets and also includes charts in a workbook.
VBA Object Hierarchy
Objects in Microsoft Excel are arranged in a hierarchy from larger or outer parts to smaller (deeper) constituent parts of all objects that you can use in Excel VBA programming.
The largest or outermost object of an application is the Application or the application itself which in this case is Microsoft Excel itself. However, it does not mean that Application is the highest object because Excel itself can basically interact with other applications.
Object Application (Excel) contains several other objects for example: AddIn , Windows and Workbooks .
Each of these objects can contain several other objects. For example, the Workbook object can contain objects:
- Charts containing chart objects
- Names containing object names
- VBProjects that represent projects in the currently open workbook.
- Windows which at this level contains the Window object in a particular workbook.
- Worksheets containing Worksheet objects.
Furthermore, a worksheet object can contain other objects, for example:
- Comment : contains cell comments.
- Hyperlink : load hyperlink
- Name : Contains the name of the range or defined name.
- PageSetup : Loads print page setup information.
- PivotTables : Loads PivotTable objects.
- Range : Contains cells, rows, columns, selections and others.
The hierarchy above is of course just an example, in fact there are quite a few other objects, one of which you can learn about on the following Microsoft page: Object Model .
Understanding the flow of this object hierarchy is quite important because this hierarchy will determine how our vba code is structured to use each object we need so that it doesn’t get the wrong address.
For example, how to manipulate cell A1 for us to fill in the text ” Learn VBA ” on sheet1, lest it ends up getting lost in Cell A1 on Sheet2. Also what if we want to enter the text ” Excel Class ” in cell A1 on any worksheet that is currently active.
Then how to use these Excel VBA objects?
How to Use Excel VBA Objects
Understanding how to reference an object when compiling Excel VBA code is very important. Because after all when writing Excel VBA code, you must be able to identify each object that will be manipulated or used.
As explained earlier that objects have a hierarchy from the outermost to the innermost according to the list of members and the map of the object .
Each use of the property or method of the object to be used is always preceded by mentioning the name of the outermost object to the innermost or smaller object until it arrives at the required property or method . To connect each object use a dot .
The syntax is as follows:
Outer Object.Object1.Object12.Object123.Others.(property/methods)
For example to arrive at the Activate method on the Range object you need to access the following code:
Application.Workbooks.Worksheets.Range.Activate
Where Application is the outermost object of excel then Workbooks is an object inside Application and so on until the Activate method which is owned by Range.
The dot (.) character in the above code is a code to connect one object to another object in the hierarchy below it.
Don’t rush just yet. if you really write the code above the result is an error. The code above is just a basic outline to help understand how to refer to an Excel VBA object.
Reference to Collection Object
Most likely when creating or writing VBA code you will often come into contact with collections.
In addition to making references to a collection of objects in their entirety, you can also choose to refer to a portion of the contents of the collection. At least you can do this in 2 ways: Using the object name or its index number on the collection .
Using Object Name
To access the Collection with the object’s name, the syntax is as follows:
NameCollections(Object_Name)
- Write the Collection Name followed by parentheses ()
- The name of the object is enclosed in parentheses, enclosed in quotation marks .
For example, in a workbook with the name “Report.xlsm” there are 3 worksheets with the names Sheet1, Sheet2 and Sheet3. To use the Activate method in sheet 2 the code is:
Application.Workbooks("Report.xlsm").Worksheets("Sheet2").Activate
Or
Application.Workbooks("Report.xlsm").Sheets("Sheet2").Activate
The purpose of the code above is to activate Sheet2 in the currently open Report.xlsm file.
Using Number Index Object
In addition to using the object name, you can also access the Collection by its index number. The syntax is as follows:
NamaCollections(NO_Index_Object)
- Write the Collection Name followed by parentheses ()
- Index number is written in parentheses without quotes .
With an Index number, for the previous example you could use the following code:
Application.Workbooks("Report.xlsm").Worksheets(2).Activate
or
Application.Workbooks("Report.xlsm").Sheets(2).Activate
Simplify Writing References to Objects
If a reference to an object is written completely as above, of course the VBA code that we write will be very long. In addition to slowing down the writing of code, the possibility of such a model will also make it difficult for us to read the code. Although in certain cases it may be the other way around.
For that, there are several ways to simplify writing code or making references to an Excel VBA object.
The simple concept is that if an object reference is not written entirely from the outermost object, then VBA will assume you mean to refer to the currently active objects.
Application Objects
When working with excel, the default object is Application. Most likely what you mean here is excel itself so you don’t need to reference the Application object.
To simplify writing the previous code you can omit the Application . It is enough to write as follows:
Workbooks("Reports.xlsm").Worksheets("Sheet2").Activate
The Active Workbook and Worksheet
If you are writing Excel VBA code in the standard module, by default VBA will assume that the external object you are aiming for is the currently active workbook. So you can simplify the previous code to:
Worksheets(
"Sheet2").Activate
If you intend to refer to other workbooks of course it should be written more fully.
Likewise if you intend to work with a Range object. If written in simple terms, VBA by default assumes that you are referring to the range on the active worksheet, for example:
Range(
"A1") =
"Infact Daily"
This code when executed will fill cell/range A1 on the active worksheet with the text “Excel Class”.
This simplification model certainly has a risk, namely the occurrence of the wrong address when the code is executed. For that you have to be really observant in writing VBA code.
In addition, the above simplification only applies to the Standard module. If you write code in a Sheet or Workbook module , VBA by default always assumes you are targeting the worksheet or workbook where the code is written.
Another way to simplify writing VBA code is to use the With… End With command block code which will be explained later.
For the VBA tutorial series on Objects this time, I think that’s enough for now. If there are still questions, please submit them through the comments column provided.
See you in the next series and please bookmark it and don’t hesitate to click share so that more of your friends will benefit.
Have a good study.
Greetings Excel Class.