Excel VBA Object Oriented

 in Excel  tagged select / object oriented / oop / Excel VBA by
This entry is part 5 of 9 in the series Excel VBA

Excel VBA

VBA is an object oriented programming language. It has individual objects, each of which has its own set of features and uses. An Excel application contains workbooks, worksheets, cells, charts, pivot tables, shapes, and the list goes on. Each object has its own set of features, which are called properties, and its own set of uses, called methods.

Excel thinks about these internally as all part of a hierarchical model called the Excel Object Model.

To point to a specific object in VBA, you can traverse the object model.

  • Application
  • Workbook
  • Worksheet
  • Range

Select

For example, to get to cell A1 on Sheet 1, you can enter this code: 

Application.ThisWorkbook.Sheets("Sheet1").Range("A1").Select

Check out the article at Microsoft called How to select cells/ranges by using Visual Basic procedures in Excel.

In most cases, the object model hierarchy is understood, so you don’t have to type every level. Entering this code also gets you to cell A1 because Excel infers that you mean the active workbook, and the active sheet: 

Range("A1").Select

If you have your cursor already in cell A1, you can simply use the ActiveCell object, negating the need to actually spell out the range. 

Activecell.Select

' select row 5 column 4, which is D5
ActiveSheet.Cells(5, 4).Select

How do you select an entire row and make it bold? Instead of using Cells, we use Range. 

Sub BoldRow1AllWorksheets()
    'make the first row bold in all worksheets
    Dim ws As Worksheet
    For Each ws In Worksheets
         With ws.Range("1:1")
            .Font.Bold = True
         End With
    Next ws
End Sub

Collections

Many of Excel’s objects belong to collections and Excel considers collections to be objects themselves. In each Workbook object, you have a collection of Worksheets. The Worksheets collection is an object that you can call upon through VBA. Each worksheet in your workbook lives in the Worksheets collection.

If you want to refer to a worksheet in the Worksheets collection, you can refer to it by its position in the collection (as an index number starting with 1), or by its name, as quoted text. These lines of code both do the same thing: 

Worksheets(1).Select
Worksheets("MySheet").Select

If you want to refer to a worksheet in the Worksheets collection, you can refer to it by its position in the collection, as an index number starting with 1, or by its name, as quoted text. If you run these two lines of code in a workbook that has only one worksheet called MySheet, they both do the same thing: 

Workbooks("MyData.xlsx").Worksheets("MySheet").Select

Looping Through All Worksheets

Below is an example of looping through all of the worksheets. 

Sub S03_SelectA2CellinAllWorksheets()
    ' do this before freezing the top row
    Dim ws As Worksheet
    For Each ws In ActiveWorkbook.Sheets
       ws.Select
       Range("A2").Select
    Next ws
End Sub
Series Navigation<< Excel Macro LibraryExcel VBA Properties >>