Excel VBA Object Model

Home/Excel VBA/Excel VBA Object Model
What is an Object and How to Understand it:

Understanding the Excel VBA Object Model is important to deal with different Excel Objects. Objects are similar to the objects in real world. If you consider House is an object, it have several characteristics.

PREMIUM TEMPLATES LIMITED TIME OFFER

ON SALE80% OFF

50+ Project Management Templates Pack
Excel PowerPoint Word

Advanced Project Plan & Portfolio Template
Excel Template

Business Presentations Templates Pack
PowerPoint Slides

20+ Excel Project Management Pack
Excel Templates

20+ PowerPoint Project Management Pack
PowerPoint Templates

10+ MS Word Project Management Pack
Word Templates


Excel VBA Object Model – A Real world scenario
An House can have the following characteristics:

Objects: Several Small Objects like Room, Door, Wall, Fan,etc…
Properties: Different Measurements or Properties like Width, Height, Wall Colors, Names etc…
Collections: You can say all of the rooms as Rooms Collection, all the doors can be Doors Collection,etc…
Events: On Close the door, On Open the Door, On Switch on the TV,etc…
Methods: There will be different actions like Cleaning, Painting, Washing, Watching, etc…

Excel VBA Object Model

Similarly we have the same type of characteristics for Excel Objects. You can observe the following Worksheet characteristics.

A Worksheet can have the following characteristics:

Objects: Range,Cell,Shape, etc…
Properties: Sheet Name, Sheet Color, etc…
Collections: Ranges, Cells, Shapes,etc…
Events: On Sheet Activate, On Selection Change, etc…
Methods: Select, Activate, Copy, Paste,etc…

How to refer an Object:

UnderstandingObjects
Consider the following real life examples:

Example 1: If you want to switch off the Fan in the Dinning Room, you will go to Dining Room and find the appropriate switch and put off that. If you want to do the same thing with VBA, you will do some thing like this:

House.DinningRoom.Fan.Switch=Off

Example 2: If you want to close the Main Door, you will write something like this:
House.MainDoor.Close=True

Similarly, if you want to change the name of the Sheet2, your code will be:

Workbboks(“Wrokbook1”).Sheets(“Sheet2″).Name=”Data Sheet”

If you use Sheets(“Sheet2″).Name=”Data Sheet”, it will change the Sheet2 Name of the Active Workbook. If you want to change the Sheet2 Name of the Workbook1 you need to add Workbboks(“Wrokbook1”).

What are the Frequently Used Excel Object, Methods, Collections and Events:

Following are list of Object, Methods, Collections and Events which we commonly refer while automating Excel Jobs:

Objects Example Remarks
Workbook We can get the path of the Workbook using Workbooks(“Workbook1.xlsx”).Path Here Workbook1 is Workbook name
Sheet We can activate a worksheet using Sheets(“Sheet3”).Path Here Sheet3 is WorkSheet name
Range We can Enter a Value in a Range using
Range(“C2”).Value=3000
Here C2 is Range name
Cells We can Enter a Value in a same Range using
Cells(2,3).Value=3000
Here 2=Row number and 3=Column Number  of the Range C2

Properties Methods Collections Events
Value Select Workbooks Worksheet_Activate
Hidden Activate Worksheets Worksheet_Change
RowHeight Copy Cells Worksheet_SelectionChange
ColumnWidth Cut Shapes Workbook_Open
Interior.ColorIndex Paste ChartObjects Workbook_SheetChange
Font.ColorIndex Print Names Workbook_BeforeSave
Hidden Save Hyperlinks Workbook_BeforeClose
Merge Close AddIns Workbook_BeforePrint

Refer the following tutorial for more on this topic:

VBA Advanced Tutorials: Objects, Properties and Methods

LIMITED TIME OFFER
By |June 23rd, 2013|Excel VBA|0 Comments

About the Author:

PNRao is a passionate business analyst and having close to 10 years of experience in Data Mining, Data Analysis and Application Development. This blog is his passion to learn new skills and share his knowledge to make you expertise in Data Analysis (Excel, VBA, SQL, SAS, Statistical Methods, Market Research Methodologies and Data Analysis Techniques).

Leave A Comment


Related pages


excel vba wait functionactivate worksheet vbaunprotect excel macrohow to save excel macrodisplay duplicates in excellock a spreadsheetpassword unprotect excelexcel xml formatwhat is the formula for adding a column in excelvba code to save workbookexcel formula with example in hindibar of pie chart excel 2010excel vertical lookupsql interview queries pdfremove header in excelpassword protect vbavisual basic hyperlinkexcel workbook examplesconsolidate excel columnsdbms interview questions and answers pdfvbcancel3d excel graphchange the chart type to clustered columnvba numericexcel macro string concatenationhow to insert box in excelhow to tick a box in excelproject schedule template powerpointuseful vba functionsusing named ranges in vbavba sql server connectionusing vlookup in excel 2010getfile vbahow to unlock an excel spreadsheet for editinghow to change all column widths in exceladvanced sorting in exceloptimisation in excelunhide excel worksheetspell check shortcut in excelexcel hide worksheetexcel vba change cell color based on valuepivot table chart excelexcel hlookupmerge excel columnsexamples of gantt charts in excelvba startfree excel dashboard templates 2010vba macro passwordexcel columnwidthtimeline spreadsheet template excelwhat are vlookups in exceldml commands in sql servervba selection.deleteexcel vb tutorialbuilding macros in exceldatabase dml ddlexcel formula list 2007vba excel commentexcel vba replace functionboard meeting minutes template microsoft wordvba merge cellsinput vbaexcel chrall shortcut keys of excel 2007object oriented vbatask excel templategreater than formula excelexcel functions vlookupvba create access databasecreate a checkbox in excelexcel input form templatemessage box excel vbavba powerpoint tutorialms access message box vbaeliminating duplicates in excelexcel pivot table templatevba execute sqlvba protect sheet with passworddelete columns in excelexcel formula not blankhow to use visual basic in excel 2007various sql commands