[go: up one dir, main page]
Open In App

Basic Object Model in Excel VBA

Last Updated: 27 Jan, 2023

A

Ankur Pal
Summarize
Comments
Improve
Suggest changes
Like Article
Like
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

 



Article Tags :

A

Ankur Pal
News
Improve
Discuss
Do you want to advertise with us?Click here to know more

A

Ayush Dey

VBA Subroutine in Excel - How to Call Sub in VBA?

article_img
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 codes into small parts so that the computer ignores all kind of complexities that arises because of large codesReusability of code suppose we in a program have to access the database frequently so instead of writing the code again and again we can create a function to access the databaseSubroutines are self-documenting functions which means a coder can easily say what the program does by looking into the name of the function Naming Rules of SubroutinesIt can start with a letter or an underscore but it cannot start with a number or a special character.It cannot contain any space in the name.The name of the subroutine cannot be a keyword like Private, Sub, End, etc. Syntax Private Sub function_name( ByVal arg1 As String, ByVal arg2 As String) End Sub Syntax Explanation Code Action "Private Sub function_name(...)"Private is the keyword which
Read More

A

Ankur Pal

Workbook and Worksheet Object in Excel VBA

article_img
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 Object which includes all the worksheets present in a particular workbook. The Worksheet Object contains Cells Object. To perform any task or operation in excel we need to follow this excel object hierarchy. Workbooks Object In excel VBA, the workbooks object represents the collection of the workbook which are open in the excel application at the same time. This is the reason we write it as plural- Workbooks Object, which means it contains more than one workbook. In order to access a single workbook from the workbooks object, we can use the following VBA to access it from its name. How to Refer a Workbook in VBA? To refer a workbook in excel VBA, we can use the following different methods: By Name We can easily refer to a workbook using its name. We need to define a procedure in VBA and declare our excel workbook name. Make sure that the user
Read More

A

Aishwarya

Excel VBA | count() functions

article_img
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 various functions. Let’s see Count() functions in Excel. COUNT: It allows to count the number of cells that contain numbers in a range. One can use the COUNT function to calculate the total number of entries in an array of numbers. Syntax:=COUNT(value1, value2…) value1: The very first item required to count numbers. value2: It can be specified upto 255 other entries which you want to count. Note: -> Only those arguments are counted that are numbers or text enclosed between quotation marks, as, “2”. -> Arguments like logical values and text representation of numbers that are typed directly are counted. -> Any values that cannot be translated to numbers are ignored. Example: Output: COUNTA: This counts only those range of cells which are not empty. Syntax:=COUNTA(value1, value2…) value1: It is the first argument to be counted. value2: All addi
Read More

A

Akshit

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 iterate through the elements of the collection or array. For collections, the element can only be a Variant variable, a generic object variable, or any specific object variable. For arrays, the element can only be a Variant variable.groupRequired(Must be mentioned). Name of an object collection or array (except an array of user-defined types).statementOptional (May or may not be mentioned). One or more statements are executed on each item in the group. There are 4 basic steps to writing a For Each Next Loop in VBA: Declare a variable.Write the For Each Line with the variable and collection references.Add line(s) of code to repeat for each item in the collection.Write the Next line to terminate the loop. The For…Each block is entered if there is at least one element in the group. Upon entering the loop, all the statements in the loop are
Read More

A

Aishwarya

Excel VBA | sum() functions

article_img
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 various functions. Let's see Sum functions in Excel. SUM: Adds all the numbers in a range of cells. Syntax: =SUM(number1, number2…) Here, number 1: This is the first number in your cell to add. You can specify it upto 255 numbers. number 2: This is an Optional field. You can add upto 255 numbers. Example: Output: SUMIF: Allows to add the cells depending upon the criteria you’ve provided. Syntax: =SUMIF(range, criteria, sum_range) range: It is the range of cells which you want to evaluate. The cells in range can be numbers, names or arrays or any other reference that contains numbers. criteria: It should be in the form of a number or any expression which defines which all cells to be added. For instance, “mango”, C6, “<35” sum_range: This field is Optional. If this is not mentioned, Excel will add only those cells on which criteria is appl
Read More

A

Aishwarya

Excel VBA | Average() Functions

article_img
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 various functions. Let's see Average functions in Excel. AVERAGE It returns the arithmetic mean of all of its arguments. Syntax: =AVERAGE(number1, number2, …) Here, number 1: This is the first number in your cell. You can specify it upto 255 numbers. number 2: This is an Optional field. You can specify it upto 255 numbers. Example: Output: AVERAGEIF: It calculates the average of only those values which meet a certain criteria. Syntax: =AVERAGEIF(range, criteria, average_range) range: It is the range of cells to be evaluated. The cells in range can be numbers, names, arrays or any other reference that contains numbers. Blank and text values are not considered. criteria: It should be in the form of a number or any expression which defines which all cells to be averaged. For instance, “mango”, C6, “<35” average_range: [Optional] If this is
Read More

A

Ayush Dey

How to Declare and Initialize String Array in Excel VBA?

article_img
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 which we will discuss in this article Declaring a Static String Array A static Array is an array whose size is fixed and it can be declared in two ways one is declared implicitly and another one is explicit. The following code is to declare a string array implicitly. The following code is to declare a string array explicitly. Declaring a Variant Size Array of String using Array Function In the following code an array is declared with variant size and string values are initialized using the array function: If we want to access the strings in the array then we have to write, Declaring a String Array using Split Function The following code is to declare an array without any fixed size and a split function is used to assign the string values. If we want to access the strings in the array then we have to write, By default, the lower bound of an a
Read More

A

Ayush Dey

Trapping Dynamic Ranges in Excel VBA

article_img
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 ranges in different ways: Last Row in ColumnLast Column in Row Special cells Used RangeCurrent RegionNamed Region TablesLast Row in Column This is used to get the number of the last row in the column by using two methods i.e Range() and End(). Range("x1048576") is the syntax where x represents the name of the cell for which we are trapping the last row and 1048576 is the total number of rows in the sheet. End(Direction as xlDirection) where we are searching for the last row we will mention xlUp to go in the last row of the worksheet. The following code is written to get the number of the last row in cell "C" Last Column in Row This is used to get the number of the last column in the row by using two methods i.e Range() and End(). Range("XFDz") is the syntax where XFD is the last column of the worksheet and z represents the number of the
Read More

G

Gautam goel

How to Use the VBA Immediate Window in Excel?

article_img
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 the name, counting the number of sheets, finding the active cell of the current worksheet, etc. In this article, we will learn different ways to use the immediate Windows in excel. Opening Immediate Window By default, the immediate window does not appear in the visual basic editor, but, could be activated by following these steps: Step 1: Go to the developer tab, under the Code section. Click on Visual Basic(Alt + F11). Step 2: VBA editor is opened. Click on View, in the menu bar. Select Immediate Window. Step 3: The Immediate window(Ctrl + G), is opened in the VBA editor. Different Ways to Use Immediate Window There can be a huge number of cases where you could use immediate windows which can make your work faster and easy. An immediate window can run a macro, set variable values, debug, ask different questions using (?), and many more. U
Read More

G

Gautam goel

How to Set Variable to Cell Value in Excel VBA?

article_img
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 to cell values in Excel VBA. Declaring Variables in VBA Before learning the assignment of variables, one needs to know what are variables and why they are used? Like any other programming language, VBA has variables. Variables are the containers to store data in them. There are some rules for storing data in them. Rules for naming Variables The variable name cannot start with a number.While naming the variables you cannot have space between the names which means a space character (' ') is prohibited.The length of the variable name should be less than Two-hundred and fifty-five (255) characters. Implicit and Explicit Declaration In Excel VBA, variables can be classified into 2 categories implicit and explicit, Implicit Declaration The implicit declaration declares a variable of data type variant. When we create variables implicitly, we ne
Read More
three90RightbarBannerImg
We use cookies to ensure you have the best browsing experience on our website. By using our site, you acknowledge that you have read and understood our Cookie Policy & Privacy Policy
Lightbox