Working With Worksheets

As you work with Excel objects in VBA, especially collection objects that contain several values, such as the Sheets Collection, you frequently must determine the number of objects within the collection. Because the number of objects in a collection varies based upon what you have currently open, you need to determine the number of objects as your code runs. The best method for this is the Count property which works with virtually all VBA collection objects to return a value that specifies the number of objects within the current collection:

Example:

NumSheets = Worksheets.Count

The Count property is read-only, meaning that you cannot use it to change the number of sheets in a workbook. But you should use it at any point where the number of items in a collection may change. For example, you may know that the Excel workbooks on your system all have at least three pages because you have set up your defaults to always create a new workbook with three pages. Even if this is the case, you should not assume that you always have that many pages in each workbook you open.

□ Type Sheets(1) replacing Sheets(1) with a reference to the sheet to

□ Switch to Excel and run the macro.

□ Type Sheets(1) replacing Sheets(1) with a reference to the sheet to

□ Switch to Excel and run the macro.

-

¡If] File Edit

View Insert Format

Tools Data Windov

Help

| Type a question for help

- . B

I □

¿Bö® &

m I «

& S

ái m

3 >>p

ial

g B

H9

JS-

A

B

c

D

E

F

G

H

I

J

K

L

1

Monthly Expenses

2

Rent

Groceries

Fuel

Electricity Child Car

Misc.

3

January

$750.00

$214.00

$75.00

$87.00

$110.00

$175.00

$1,411.00

4

February

75D

311

88

89

110

145

1493

5

March

75C

245

90

99

110

123

1417

G

April

75C

25S

79

103

110

211

1509

7

May

$750.00

$307.00

$100.00

$110.00

$110.00

$345.00

$1,722.00

8 9

June

75C

350

85

120

110

149

July

750

310

94

145

110

190

10

August

75C

299

110

13C

110

256

11

Septemb

75C

245

120

111

110

298

12

October

750

280

99

96

110

245

13

Novembe

750

400

110

88

110

405

14

Decembe

75C

415

130

84

110

605

15

16

17

18

19

20

21

J

22

23

24

25

26

I

i >ir

k- HN Sheet2 / bheetü \sheetl /

<

Ready

-■ Excel moves the specified sheet to the end of the workbook.

'—0 Type After:=Sheets (LastSheet), replacing After with Before if you want to place the sheet before the specified sheet.

move.

0 0

Post a comment