Listing A Procedure That Stores the Names of the Worksheets in a Dynamic Array

Sub StoreWorksheetNames()

Dim sheetNames() As String Dim totalSheets As Integer Dim sheet As Worksheet Dim i As Integer Dim strMessage As String

' Store the total number of worksheets 1 that are in the current workbook totalSheets = ActiveWorkbook.Worksheets.Count

' Now redimension the dynamic array

ReDim sheetNames(totalSheets)

1 Loop through the worksheets to store the names in the array

For i = 1 To totalSheets sheetNames(i - 1) = ActiveWorkbook.Worksheets(i).Name Next 'i

' Loop through the array to add the names to a string strMessage = "Here are the worksheet names:" & vbCrLf For i = 0 To totalSheets - 1

strMessage = strMessage & sheetNames(i) & vbCrLf Next 'i

1 Display the worksheet names

MsgBox strMessage End Sub

This procedure begins by declaring sheetNames as a dynamic array. It then uses the totalSheets variable to store the total number of worksheets that are in the current workbook. The procedure then sets the size of the array based on the totalSheets value: ReDim sheetNames(totalSheets)

The procedure then uses one loop (see Chapter 6, "Controlling Your VBA Code") to store the worksheet names in the array and a second loop to add the worksheet names to the strMessage variable, which is a String value. Finally, the procedure uses the MsgBox function to display the string, as shown in Figure 3.1.

Figure 3.1

The results of the dynamic array procedure in Listing 3.2.

IV) c

luptciOJ

. -

*

A

B

c

D

t

F G

H i

J

K

L

M

'«Kluct

Jon

lob

Mor

Aw

»■lay Jun

Jul Aun

Sep

Oct

rrov

Dec

3

23,500

23,000

24,000

26,100

24,000 25,400

26.000

24,0«

24,000

26,000

24,000

24,000

^

Sflftwn rc

7B.750

?7 BOO

?9.SOO

31 000

.10 GOO 30.000

31 000

29.500

?9.fi00

3? 000

?9.SOO

?9 500

CO ROM*

J-i .100

000

M.M0

JG 600

?7 OCO JG.7G0

77 000

J5.740

7S.J50

7SOOO

JS.M0

JG.JG0

I

Mil rimifl fxc rl

8

Hffe sib the »orifcshte Sires ihtttl iheitJ JJlMtl

names:

10

11 12 11

14

lb 16 17

|j « l|

IB

l'J

» Sah

S , Exp-srrse

SU««)

5h««2

5h«t3 ,

l"J '

0 0

Post a comment