Working With The Excel Object Model

fclta: Besides maintaining a list of all objects with their corresponding properties and methods, the Object Browser keeps track of the constant values assigned to object properties. It also keeps track of parameter values for various object methods and VBA functions. For example, the ChartType property enables you to specify the type for a chart. You need to use one of the XlChartType constant values as the value for this property. For example, ThisWorkbook. Chart.Type = xlPie creates a pie chart. The ChartType property accepts only one of these constant values. You can view the list of available XlChartType constants within the Object Browser by typing ChartType in the Search Text field and clicking the Search button (H). If you select the XlChartType value in the Classes list box, you see all of the chart type constant values within the Members list box.

You can quickly find more information about an object, property, or method selected on the Object Browser window by pressing F1. When you press F1, the Microsoft Visual Basic Online Help displays help for the item selected on the Object Browser window.

1-IJIXI

File Edit View Insert Format Debug Run lools Add-Ins Window Help

I Type a question For help

I I !JP| Sheet2 (Sheet2)_^J kij] 5heet3 (Sheet3) 1—0 ThisWorkbook B-Ç3 Modules

Modulel

VBAProject (PERSONS

Modulel

Sheet3 Worksheet 3

I Categorized | 5heet3 Dis pi ayP age Break False ;playRightToLel False EnableAutoFilter False EnableCalculation True EnableOutlining False

5heet3

^J J Calculate_E«penses

| Classe

5 Addln B Addlrs S Adjustments ¡3 AllowEditRange B AllowEditRangeE S Application B Areas

HÜ* ActiveCel Activée h ! [É? ActivePrin lä? AciiveS he eS* ActiveWin eSF ActiveWor US' Addlns Ei? Appiicatio

Library Excel CTrogram FileslWIicrosofl OtticelQUicel OIE Microsoft Excel 10.0 Object Library a»_iLj

Type the search string in the field under the libraries.

JS12

U Click the Search button ®).

1-IJIXI

I Type a question For help

■ The corresponding properties and methods display in the Members of window.

-■ The Search Results window displays the objects that match the specified text.

LQ Select the desired search results.

■ The corresponding properties and methods display in the Members of window.

CREATE AN OBJECT VARIABLE

You can simplify your VBA code by creating object variables. Creating object variables enables you to reference a specific object within your code. Although you do not need to use an object variable, VBA enables you to reference objects directly by typing the complete object reference each time you want to work with an object;not only is this method more cumbersome, but it also makes you code run more slowly. Using object variables, on the other hand, greatly simplifies your code because object variables are typically shorter than complete object references. Also, VBA code typically runs faster when you use object variables in your code.

You declare object variables in much the same fashion as a standard variable. You use the Dim statement to declare the variable and the As statement to identify the variable as an object variable. The data type for the variable is the corresponding object type.

For example, the statement Dim ObjectVar As Worksheet creates an object variable named ObjectVar that is a Worksheet object. You can create object variables for each of the objects in the Excel Object Model.

After you create an object variable, you assign a specific object reference to th variable. You assign an object to a variable in basically the same fashion as with standard variables. The difference is that the Set statement must precede the assignment statement. The following statement sets the value of ObjectVar to point to Sheet1 in the workbook: Set ObjectVar =

ActiveWorkbook.Worksheets( "Sheetl" ). Also, when you assign an object to a variable, you are only assigning a reference to the object to the variable and not the actual object value. In other words, in the sample line of code, ObjectVar simply points to Sheetl within the active workbook.

CREATE AN OBJECT VARIABLE

pa it View Insert Format Debug Run Tools Add-Ins Window Help i1s-h !. p ot • ► n • ¡¡£ | « ti * | 0 |

È-Q Microsoft Excel Obje Modules J Modulel — * Module2

i-iji»i it View Insert Format Debug Run Tools Add-Ins Window Help i1s-h !. p ot • ► n • ¡¡£ | « ti * | 0 |

È-Q Microsoft Excel Obje Modules J Modulel — * Module2

Module3 Module 3

Detic J categorised |

Module3 Module 3

Detic J categorised |

fcH-iLj

□ Click to place I after the Sub statement.

'—B Type Dim VarName As ObjectType, replacing VarName with the name of the object variable and ObjectType with the Excel object type.

< Press Enter.

-r ■■ i -^-i - - L -- v.-- ;----:.- :r- ; " l " | I

35-iJ_l

1-IJI*I

-r ■■ i -^-i - - L -- v.-- ;----:.- :r- ; " l " | I

35-iJ_l

ExcelObject, replacing VarName with the variable name and ExcelObject with the object assigned to the variable.

Kxl

ra

If you want to refer to the currently selected worksheet in a workbook, you can do so by using the ActiveSheet property. You use this property in place of an object reference to a specific worksheet, such as Worksheets(l), which refers to the first worksheet in a workbook. Using the ActiveSheet property, you can reference whichever worksheet is active at the time your procedure executes. For example, SheetName = ActiveSheet.Name assigns the name of the currently active worksheet to the SheetName variable.

When you create object variables you are essentially just creating object pointers. Unlike a standard variable that is the name of a memory location containing the variable's value, an object variable actually points to the memory location that stores a pointer to the object. For example, in the following code ObjVar stores the pointer to cell B2 in the worksheet.

Dim ObjVar As Range

Set ObjVar = ActiveSheet.Cells(2, 2)

The ActiveSheet property refers to any type of sheet within a workbook. Therefore, if the currently selected sheet is actually a Chart sheet, the ActiveSheet property returns a reference to the appropriate chart sheet. See Chapter 10 for more information on working with worksheets.

1-IjIXI

: File Edit View Insert Format Debug Run lools fidd-Ins Window Help

B a ■ Q s % e m " ™ > Ii ■ M * e" w * 131 •■

: File Edit View Insert Format Debug Run lools fidd-Ins Window Help

B a ■ Q s % e m " ™ > Ii ■ M * e" w * 131 •■

Type MsgBox(VarName), replacing VarName with the variable created in step 2.

□ Switch to Excel and run the corresponding macro.

Type MsgBox(VarName), replacing VarName with the variable created in step 2.

□ Switch to Excel and run the corresponding macro.

-■ The message box displays the contents of the object variable.

ou can change the value of an object, its appearance, and so on, by modifying the properties associated with an object. When working with objects, you do not change the object directly; instead, you make changes to the object by altering the values of the properties associated with the object. For example, when working with a cell on a worksheet, you use the Value property to change the value of the cell. If you change to the font style, however, you modify the properties for the Font object, such as the Bold, Italic, Underline, and Size properties.

When you make several property changes to the same object, doing so typically requires repeating the name of the object each time. Even if you have assigned the object to an object variable, you must repeat the variable name. For example, if you use the statement Set CellFont = ActiveSheet.Cells(1,1).Font as the object variable for the Font object, you still need to reference the Font object variable each time you change a font attribute. To set the font to bold, you type CellFont.Bold = True. Then, if you want to set the font size, you again reference the Font object by typing CellFont.Font.Size = 12.

Even with the use of an object variable, you must repeat the object variable name each time you change a font setting, making the code complex. You can simplify this type of code with the With statement. Instead of typing the object variable reference, you simply type With CellFont followed by each property statement. For example, to underline values in the cell you type .Underline = True. When you complete you list of property settings, you type End With to mark the end of the With statement.

The With statement enables you to specify statements that refer to the same object. You need only to specify the object name with the With statement to apply all statements to that object.

CHANGE THE PROPERTY OF AN OBJECT

I File Edit View Insert Format Debug Run lools Add-Ins Window Help

H a - D X * a « " . II ■ M H & B 3? ® Ln5,Coll

CHANGE THE PROPERTY OF AN OBJECT

I File Edit View Insert Format Debug Run lools Add-Ins Window Help

H a - D X * a « " . II ■ M H & B 3? ® Ln5,Coll

Module4 Module Alphabetic | Categorized |

L-D Type Dim FirstCell As Range, replacing FirstCell with the variable to be used as the Range object.

L-D Type Dim FirstCell As Range, replacing FirstCell with the variable to be used as the Range object.

File Edit View Insert Format Debug Run lools Add-Ins Window Help

Sub Cliange_Ob;jVar ()

Dim FirstCell As Range

Set FirstCell = ActiveSheet.Cells(1, 1)

End Sub st.Ce 11 I

File Edit View Insert Format Debug Run lools Add-Ins Window Help

Module4 Module Alphabetic | Categorized |

Sub Cliange_Ob;jVar ()

Dim FirstCell As Range

Set FirstCell = ActiveSheet.Cells(1, 1)

End Sub st.Ce 11 I

L0 Type Set FirstCell = ActiveSheet.Cells(1,1), replacing FirstCell with the variable in step 1 and ActiveSheet.Cells(1,1) with the appropriate range of cells.

Note: See the section "Create an Object Variable " for more information.

'-0 Type With FirstCell, replacing FirstCell with the variable created in step 1.

Til Some objects, such as the Font object, provide a Color property that determines the color of the object. The RGB function works well for specifying the font color. When you use this function, you select the desired color by indicating the amount of red, green, and blue in the color. You specify the color values with an integer value between 0 and 255. For example, you type (0,0,0) for the color black.

COLOR

RED VALUE

GREEN VALUE

BLUE VALUE

Black

G

G

G

Blue

G

G

255

Cyan

G

255

255

Green

G

255

G

Magenta

255

G

255

Red

255

G

G

White

255

255

255

Yellow

255

255

Sub Chaage_C"b jVac () Dim FirstCell As Range Set FirstCell = ActiveSl

Uith FirstCell_

.Value =345

.Font.BoId = True

.Borders.LineStyle = xlDoutole

^ Modulei

Module4 Module ~*1

Alphabetic | Categorized |

Sub Chaage_C"b jVac () Dim FirstCell As Range Set FirstCell = ActiveSl

Uith FirstCell_

.Value =345

.Font.BoId = True

.Borders.LineStyle = xlDoutole a»_iLj

—0 Change the object's properties.

■ You can type Font.X = True, replacing X with Bold, Italic or Underline.

■ You can type .Font.Color = RGB(X, Y, Z), replacing X, Y, and Z with RGB values.

■ You can type code to specify a desired line style constant.

-0 Type End With.

H]

'"il" ü™1».» 'i„s

rt Format

Took Data Window

Help

| Type a quest

on for help

! D

i H -

© m a VI X % m | « •

g

E - él

1

100% I- ® ,

«"

i

• l-l*

, îil

iw iW

_T <äi, A ..

A1

f* 345

A

B

c

D

E

F

G

H

1

J

K

L

-

1

345

?

3

1

6

7

F;

9

i!

13

14

1b

1b

1/

18

19

20

21

22

23

24

25

-

H

» n|\ Sheetl / Sheet2 \Sheet3 /

<1 1 H

Ready

□ Switch to Excel and run the macro.

The content of the first cell is changed to specified value and the specified font and border attributes are applied.

COMPARE OBJECT VARIABLES

You can use object comparison to determine if two object variables reference the same object. Unlike standard variables, which actually contain values that you can compare, the object variable does not contain the object, but references it. That being the case, when you compare two object variables, you are really checking if they point to the same object. For example, you may want to check if the currently active workbook is the first workbook. You accomplish this using object comparison.

When you compare standard variables, you use the = (equals sign) operator to determine if they are the same. For example, If Valuel = Value2 Then compares two standard variables. See Chapter 3 for more information on working with standard variables.

When comparing objects, instead of the = operator, you use the Is operator. For example, you write an If Then statement to compare two variables as follows: If ObjVall Is ObjVal2 Then. This statement looks at the object referenced by ObjVall and checks if it is the same as the object referenced by ObjVal2.

Besides comparing the values of two different operators, you can also use the Is operator to determine if an object variable has an assigned value. To do this, the Is operator checks if the variable has a value of Nothing, as shown in the following example: If ObjVall Is Nothing Then. When you use this type of comparison, the comparison statement returns a value of True if the object variable does not point to an object. If the object variable references a specific object, the comparison statement returns a value of False.

COMPARE OBJECT VARIABLES

COMPARE OBJECT VARIABLES

□ Create a new subrouti

-0 Assign each variable object to point to the same object using the Set command.

□ Create a new subrouti

Note: See Chapter 3 for information on creating subroutines.

L0 Type Dim WSRefl As Worksheet, replacing WSRefl with the variable name and Worksheet with the object type.

< Type Dim WSRef2 As Worksheet, replacing WSRef2 with the variable name and Worksheet with the object type.

—Q Type Dim Result As Boolean, replacing Result with the comparison variable.

-0 Assign each variable object to point to the same object using the Set command.

0 0

Responses

  • elanor
    Where in object browser are charttype constants listed?
    8 years ago

Post a comment