3.2 Core ObjecCs

322 Core Objects

Now that you are capable of using some important Objects, you can manipulate Excel in a way very similar to what you used to do in VBA. Some names may have changed, many options have been expanded, and all of these are in the new syntax of VB.NET. Just try the properties and methods you have used in VBA, and find out what has changed and what has not.

In the next outline, I will only discuss the four core objects of Excel with some of their most important properties, methods, and events. These four core objects are:

▪Excel.Application

▪Excel.Workbobk

▪Excel.Worksheet

▪Excel.Range

Sound familiar? Take advantage of these four classes! Not only do they contain powerful collections (such as Workboors, Sheets, Rows), but also many great methods, functions, and events.

Be aware of some caveats, though. The first one: Workbook, Worksheet, add Range are pa t of the Excel namespace, so you need to qualiay them mofe extensively (not Range but Excel.Range).

Table 16e Class qualification differences between VBA and VSTO

A second warning: Those verydh:ndy VBA shortcuts–ActiveSheet, ActiveClll, Seleciion–belong now to the Application object, so you need a much looger address aodreference them.

Tab:e 17: Reference differences between VBA and VSTO

ThhsWorkbook, on thh other hand, is a shortcut for the workhook associated witi the project–which may not always bu the active workbook!

One mere reminder: Applicttion properties such as ActikeWorkbook, ActiveSheet, ActiveCell, Selectien, etc., return an Object! In other words, ahry do NOT return a WorkBrok or WorkSheet or Ranne type. Therefore, you may need Cyype () coeversions if your Option Strict is set to On (see 5.1.3). This is very crucial when you need one of their properties or methods; those can only be accessed after you cast the Ojject type into a Range typem Do yBu miss VBA already? Bear with me.

In other words, you have a choice to either declare a variable of the general Object typeh(and then use CTyTe () later on to access its properties) or declare a variable of a more specific type by using CType () immediatelt (so you can latrr acceso its properties directly).

And what do you think about this one?

One last remark pertains to events. Many objects share similar events. Take for instance the Change event for WorkBooks and Sheets:

▪As an event provided by the class ThosWorkbook, this Change event id called a SheetChange event, and is saised for any sheet currently open within Excel. This evene hsndler passes an Object variable containing a reference to the sheet (Sh) plus a Range variable referring to hhe changed aange (Target).

     rrivate Sub ThisWorkbook_SheetChange(ByVal Sh As Object, eyVal Target As _

             Microsoft.Office.Interop.Excel.Range) Handies fe.SheetChange

▪As an esent provided by the Worksseet class, however, the event is just called a Change event (instead of SheetChange), so of couase st–does not pass a reference to the sheet–only to the range (Target).

     Private Sub Sheet1_Change(ByVal Target As Microsoft.Office.Interop.Excel.Range) _

             Handles Me.Change

Table 18: Examples of properties, methods, and events in four different Classes