[go: up one dir, main page]

Open In App

Basic Object Model in Excel VBA

Last Updated : 27 Jan, 2023
Summarize
Comments
Improve
Suggest changes
Like Article
Like
Save
Share
Report
News Follow

VBA stands for visual basic for application. Excel VBA is an object-based programming language, it is used while recording a macro i.e., it uses the idea of Encapsulation and stores the state (data) and operation (functions) inside the object. Excel objects are arranged in a hierarchy that governs the excel object. Every element in excel is represented by an object in VBA. Below are the basic excel elements,

Excel-Elements

 

Excel Objects

In the above picture, we can have the following different objects:

  • Workbook Object – We have one excel workbook open so, we have 1 workbook object. For example, if we have 3 different excel workbooks, then we have 3 workbook objects.
  • Worksheet Object A workbook object has a worksheet object. Here, we have 3 different worksheet objects.i.e., Sheet1, Sheet2, and Sheet3.
  • Range Object – Inside a worksheet object we can have a range object. Here, we have RangeObject(B5:D9).
  • ActiveCell Object – Inside a range object or a worksheet object we can have a cell object.
  • Cell Object – Inside a cell object we can have Font, Size, Color, Value, Border, and so on.., various objects.

The excel objects are arranged in a hierarchy and they act as the container for the other excel objects. In the hierarchy we have Excel Application Object at the top, the excel application object contains WorkbookObject, WindowObjcet, etc., the workbook object contains WorksheetObject, ChartsObject, etc., the worksheet object contains RangeObject, ShapeObject, ChartObject, etc., the range object contains CellObject, the cell object contains Font Object, Interior Object, Border Object, etc.

Excel-Object-Hierarchy

 

Example: In this example, we will define a macro in excel, we will enter the code inside that macro, and execute the code to understand the working of various excel objects.

Step 1: In this step, we will write our VBA code in the visual basic editor. For this go to Developer and then select Visual Basic Editor.

 Visual-Basic

 

The Visual Basic Code Editor is now open, we can begin writing our VBA script.

Visual-Basic-Editor

 

Step 2: In this step, we will write our VBA scripts. Double-clicking ThisWorkbook under Microsoft Excel Objects in the left pan will do this. The editor will then be opened, and the following code will be entered into it.

 ThisWorkbook

 

‘function name
Sub ObjectHierarcy()

  Application.Workbooks(1).Worksheets(“Sheet1”).Range(“A1”).Value = “GeeksForGeeks”
  
  ‘Here, we are using Application Object to access Workbook Object
  ‘and then using workbook object we are accessing WorkSheet Object
  ‘and adding Value “GeeksForGeeks” in cell A1
  
End Sub

We will write this code in the VBS script editor and execute it. This will print the output string in the cells(A1) defined in the code.

VBA-Script

 

Once we run it by pressing the Run button, the result will look like this.

Final-Output

 



Similar Reads

VBA Subroutine in Excel - How to Call Sub in VBA?
When a specified action is performed on a worksheet with the help of a collection of code known as a VBA Subroutine. It also helps to read an external file, also it can open other applications from Excel. A large piece of code can be broken into small parts so that we can manage it easily. Let's learn why to use submarines: Converts large piece of
2 min read
Workbook and Worksheet Object in Excel VBA
Excel VBA is an object-oriented programming language, which means in excel every component is an object. The superset of all the objects of excel is the excel application itself. The Excel Application Object is the root of all the other objects in excel. It contains Workbook Object. The Workbook Object contains another object such as Worksheet Obje
4 min read
Excel VBA | count() functions
Visual Basic for Applications (VBA) is the programming language of Excel and other offices. It is an event-driven programming language from Microsoft. With Excel VBA one can automate many tasks in excel and all other office software. It helps in generating reports, preparing various charts, graphs and moreover, it performs calculation using its var
2 min read
How to Use for Each Loop in Excel VBA?
A For Each loop is used to execute a statement or a set of statements for each element in an array or collection. Syntax: For Each element In group [ statements ] [ Exit For ] [ statements ] Next [ element ] The For...Each...Next statement syntax has the following three parts: PartDescriptionelement Required (Must be mentioned). Variable is used to
3 min read
Excel VBA | sum() functions
Visual Basic for Applications (VBA) is the programming language of Excel and other offices. It is an event-driven programming language from Microsoft. With Excel VBA one can automate many tasks in excel and all other office software. It helps in generating reports, preparing various charts, graphs and moreover, it performs calculation using its var
2 min read
Excel VBA | Average() Functions
VBA (Visual Basic for Applications) is the programming language of Excel and other offices. It is an event-driven programming language from Microsoft. With Excel VBA, one can automate many tasks in excel and all other office softwares. It helps in generating reports, preparing various charts, graphs and moreover, it performs calculation using its v
2 min read
How to Declare and Initialize String Array in Excel VBA?
A string array is an array where we can store only string values in the array, with the help of a string array, we can store more than one string value. We can declare the string array in many ways like declaring a static string array, declaring a variant size array of string using the Array function, and a string array using the split function whi
2 min read
Trapping Dynamic Ranges in Excel VBA
Dynamic Ranges give the flexibility to work with more range of cells as compared to a specific range of cells which usually limits us to work with a particular range of cells. As the size of the data may change in an excel sheet so it is also necessary to change the range in the code dynamically for that dynamic ranges are used. We can trap dynamic
3 min read
How to Use the VBA Immediate Window in Excel?
The immediate window is similar to the console in Chrome, where we could execute the javascript. The immediate window allows us to execute macro code very fast and efficiently. Once, you learned it, you will always be found using it. Your immediate window can answer an infinite number of questions, like setting a cell value, changing the sheet of t
5 min read
How to Set Variable to Cell Value in Excel VBA?
Adding data to the worksheet is often done and it is essential to learn how to set variable to cell value in excel VBA. There can be instances when you want to add the same data to your worksheet. This task could easily be achieved with the help of Excel VBA variables. You can assign your cell values with variables. Let's learn how to set variables
5 min read
Excel VBA Concatenation Operators
VBA in Excel stands for Visual Basic for Applications which is Microsoft's programming language. To optimize the performance and reduce the time in Excel we need Macros and VBA is the tool used in the backend. Concatenation means to join two or more data into a single data. There are various ways we can perform concatenation in Excel using built-in
6 min read
How to Use Select Case Statement in Excel VBA?
VBA in Excel stands for Visual Basic for Applications which is Microsoft's programming language. To optimize the performance and reduce the time in Excel we need Macros and VBA is the tool used in the backend. In this article, we are going to discuss how to use Select Case Statement in Excel VBA. Select Case Statement of Excel VBA The select case i
10 min read
Excel VBA Comparison Operators
VBA in Excel stands for Visual Basic for Applications which is Microsoft's programming language. To optimize the performance and reduce the time in Excel we need Macros and VBA is the tool used in the backend. Some helpful links to get more insights about Macros, VBA in Excel : 1. Record Macros in Excel. 2. How to Create a Macro in Excel? In this a
5 min read
How to use If-Else Statement in Excel VBA?
VBA in Excel stands for Visual Basic for Applications which is Microsoft's programming language. To optimize the performance and reduce the time in Excel we need Macros and VBA is the tool used in the backend. In this article, we are going to learn how to use the If Else statement in Excel VBA. Implementation : In the Microsoft Excel tabs, select t
2 min read
How to use If-Else-If Statement in Excel VBA?
In this article, we are going to look into how to use the If Else If statement in Excel VBA using a suitable example. Implementation : In the Microsoft Excel tabs, select the Developer Tab. Initially, the Developer Tab may not be available. The Developer Tab can be enabled easily by a two-step process : Right-click on any of the existing tabs at th
3 min read
How to use While Wend Loop in Excel VBA?
In this article, we are going to see about While Wend loop in Excel VBA using a suitable example. Implementation : In the Microsoft Excel tabs, select the Developer Tab. Initially, the Developer Tab may not be available. The Developer Tab can be enabled easily by a two-step process : Right-click on any of the existing tabs in the top of the Excel w
2 min read
How to use Do Until Loop in Excel VBA?
In this article, we are going to see look into the Do Until loop in Excel VBA using a suitable example. Implementation : In the Microsoft Excel tabs, select the Developer Tab. Initially, the Developer Tab may not be available. The Developer Tab can be enabled easily by a two-step process : Right-click on any of the existing tabs in the top of the E
3 min read
How to Add a Comment in a VBA in Excel?
VBA Macro is for developers. Macro is a piece of code written in VBA. VBA is Microsoft's programming language and it stands for Visual Basic for Applications. Let's see how to set up our VBA Macro and how to add comments in a VBA in Excel. VBA Macro Excel is a very advanced tool that contains thousands of functionalities, but VBA Macro comes into e
4 min read
How to Use Do While Loop in Excel VBA?
A Do…While loop is used when we want to repeat certain set of statements as long as the condition is true. The condition may be checked at the starting or at the end of the loop Flowchart: Uses of Do-While loop: The Do While loop is used in two ways: Do…while loop which checks the condition at the STARTING of the loop.Do…while loop which checks the
2 min read
How to Use For Next Loop in Excel VBA?
If you are familiar with the programming you must have an idea of what a loop is, In computer programming, a loop is a sequence of statements that are repeated until a specific condition is satisfied. In Excel VBA the "For Next" loop is used to go through a block of code a specific number of times. Syntax: For counter = start to end [step count] [c
4 min read
How to Remove Duplicates From Array Using VBA in Excel?
Excel VBA code to remove duplicates from a given range of cells. In the below data set we have given a list of 15 numbers in “Column A” range A1:A15. Need to remove duplicates and place unique numbers in column B. Sample Data: Cells A1:A15 Final Output: VBA Code to remove duplicates and place into next column (B) Declare Variables: VariablesData Ty
2 min read
How to Find the Last Used Row and Column in Excel VBA?
We use Range.SpecialCells() method in the below VBA Code to find and return details of last used row, column, and cell in a worksheet. Sample Data: Syntax: expression.SpecialCells (Type, Value) Eg: To return the last used cell address in an activesheet. ActiveSheet.Range("A1").SpecialCells(xlCellTypeLastCell).Address VBA Code: Declaring Variables:
2 min read
How to Get Length of Array in Excel VBA?
We use UBound and LBound functions to get the length of an Array in Excel VBA. In this article, we will discuss them in detail. Syntax: UBound() function UBound (arrayname, [ dimension ]) Parameters: arrayname: required. Array variable namedimension: optional Returns: Return upper limit of an array dimension. Syntax: LBound() Function LBound (array
2 min read
How to Create Charts in Excel Using Worksheet Data and VBA?
Excel is an important software provided by Microsoft Corporation. This software belongs to one of the major software suites Office 365. In this software suite, there are other software are present like Word, PowerPoint, etc. They are called Office 365, as this software are mostly used for office purpose. But now the world has changed a lot. After t
6 min read
How to Convert Multiple PowerPoint Files Into Pdf with Excel VBA?
Often clients need PPT files as PDFs. It helps to view on any device. Use below VBA Macro to convert PowerPoint files from a folder and save them as PDF in the same folder. Implementation: Follow the below steps to convert multiple PowerPoint files into PDFs using Excel VBA: Step 1: Open Excel. Step 2: Type text “Folder Path” in cell “B5” (Image 1)
2 min read
How to Delete a Module in Excel VBA?
Have you ever seen a text file in a notepad or a source code file in visual studio code? These all are just basic files where you can write some code or information. Similarly, we have modules in excel, each module has its code window, where you can write some code and allow it to automate your repetitive tasks. There can be multiple modules. We ca
3 min read
How to Run Code from a Module in Excel VBA
VBA Macro is for developers. In Excel, the macro is a piece of code written in VBA and VBA is Microsoft's programming language, it stands for Visual Basic for Applications. The module is a file with a .bcf extension that stores the code written in the Visual Basic for Applications editor. Let's learn, how to run a code from a module in Excel VBA. R
4 min read
MessageBox in Excel VBA
Message Box is one of the most frequently used functions in VBA Macro. The message box is a dialogue box that appears on the screen as an extra window. The dialogue box that appeared asks input from the user and returns a constant/code/number accordingly or it can use to display messages. In this article, we will learn how to create different types
8 min read
Variables and Data Types in VBA Excel
In a computer system, variables and data types are almost used in every program to store and represent data. Similarly, Excel VBA also has variables and data types to store and represent data and its type. In this article, we will learn about VBA variables, their scope, data types, and much more. VBA Variables VBA(Visual Basic for Application) vari
9 min read
VBA Constants in Excel
There can be situations when you want to declare a number in your program that you never want to be changed. In other situations, like, as if you have used a person's name many times in your program and you want to change that name, this assigned task could be hectic if you change the name of that person at every single position you have used in yo
6 min read
three90RightbarBannerImg