Chad's Macro Mania
~~Chad K. Welch
Organizing
Excel Spreadsheets
Using VBA
In
the last several editions I’ve discussed the fundamentals of Visual
Basic for Applications. Let’s take a different approach this month
and start getting into the meat of VBA.
One
of the most common requests that I get from clients is an easy way to
navigate their large Excel databases. If you have ever used a
workbook with many worksheets you know the hassle of finding the specific
sheet that you need. A simple VBA macro will add a table of contents
and provide an easy way to find the sheet you need. Remember,
when pasting the following code remove the line numbers.
1:
Sub TableOfContents()
2:
Dim ws As Worksheet, wsTOC As Worksheet
3:
Dim r As Long
4:
5:
Application.ScreenUpdating = False
6:
7:
Set wsTOC = ActiveWorkbook.Worksheets.Add _
8:
(before:=ActiveWorkbook.Sheets(1))
9:
wsTOC.Name = "Table of Contents"
10:
11:
wsTOC.Range("A1") = "Table of Contents"
12:
wsTOC.Range("A1").Font.Size = 18
13:
14:
r = 3
15:
For Each ws In ActiveWorkbook.Worksheets
16:
If ws.Name <> wsTOC.Name Then
17:
wsTOC.Hyperlinks.Add _
18:
anchor:=wsTOC.Cells(r, 1), _
19:
Address:="", _
20:
SubAddress:=ws.Name & "!A1", _
21:
TextToDisplay:=ws.Name
22:
r = r + 1
23:
End If
24:
Next
25:
26:
Application.ScreenUpdating = True
27:
28:
End Sub
Now
lets analyze the lines and see what they do.
Lines
1 and 28 begin and end the sub-procedure.
1:
Sub TableOfContents()
28:
End Sub
All
sub procedures begin and end in the same way: Sub MacroName() and
End Sub.
Lines
2 and 3 declare variables.
2:
Dim ws As Worksheet, wsTOC As Worksheet
3:
Dim r As Long
We
haven’t talked about variables yet in this column, but they are pretty
easy to understand. In Visual Basic you can have several different
types of variables. In this case we declare two worksheet variables
(in this case our variables are objects, if you want to get technical) and
one number variable. The variable type ‘Long’ is short for long
integer. There is also a variable type ‘Integer,’ but the
largest integer available is 32,767, Excel allows up to 65,536 rows so we
will declare the variable as a long integer.
Lines
5 and 26 turn screen updates off and then back on.
5:
Application.ScreenUpdating = False
26:
Application.ScreenUpdating = True
If
the processor doesn’t have to display every change made by the macro
until it is finished running, it will run faster. Remove these lines
if you want to watch what the macro is doing as it does it.
Lines
7 and 8 add a worksheet.
7:
Set wsTOC = ActiveWorkbook.Worksheets.Add _
8:
(before:=ActiveWorkbook.Sheets(1))
These
lines are actually referred to as one line. The last two characters
on line 7 (the space and underscore) mean that the statement is continued
on the following line. This statement creates a new worksheet,
places it before the first sheet. It also creates a reference to it
and saves it to our variable wsTOC. Because the variable is a
reference to an object, use the ‘Set’ keyword to set the reference to
the object.
Line
9 renames the wsTOC worksheet.
9:
wsTOC.Name = "Table of Contents"
Once
again, because our variable is an object, it has certain properties and
methods available only to it. One property happens to be ‘name’.
It can be called using ‘variable.property’ notice the dot and lack of
spaces between the variable and the property. wsTOC.Name calls the
Name property of wsTOC. In this case we are changing the
name to “Table of Contents.”
Lines
11 and 12 place the text “Table of Contents” in cell A1 and change the
font size to 18.
11:
wsTOC.Range("A1") = "Table of Contents"
12:
wsTOC.Range("A1").Font.Size = 18
Line
14 initializes our variable r.
14:
r = 3
This
is the row number where the sheet listings will begin.
Lines
15 and 24 begin and end a for next loop.
15:
For Each ws In ActiveWorkbook.Worksheets
24:
Next
This
brings up another aspect of Visual Basic: Collections. Some objects
are a part of a collection. In this case each worksheet is a part of
the WorkSheets collection. Every iteration of the for loop will
‘Set’ the variable ws to the next worksheet in the collection.
Lines
16 and 23 begin and end an if statement.
16:
If ws.Name <> wsTOC.Name Then
23:
End If
The
structure of an If statement is:
If
condition then
‘statements
End If
In
this case we are checking to see if the current worksheet’s (ws) name is
the same as the Table of Content’s (wsTOC) name. If it is, then
that means that ws is referring to the same worksheet as wsTOC. We
certainly don’t need to list the Table of Contents in the Table of
Contents. If the condition is passed then lines 17 to 22 are
executed.
Lines
17 to 21 add a hyperlink to the table of contents.
17:
wsTOC.Hyperlinks.Add _
18:
anchor:=wsTOC.Cells(r, 1), _
19:
Address:="", _
20:
SubAddress:=ws.Name & "!A1", _
21:
TextToDisplay:=ws.Name
Remember
the space underscore means that the statement is continued on the next
line, so all five lines here are referred to as 1 line. Stepping
through the individual parts we get:
Anchor:=wsTOC.Cells(r,
1) The hyperlink will be placed in cell(r, 1), or range Ar where r is
the row number.
Address:=””
Because our hyperlink is a local one (not to a location outside of the
workbook) the address is left blank.
SubAddress:=ws.Name
& “!A1” the sub-address is the location in the workbook
where the hyperlink will point to. This address is referenced using
SheetName!Range. In other words, cell A1 on sheet Sheet1 is
referenced by Sheet1!A1.
TextToDisplay:=ws.Name
We will display the sheet name in the Table of Contents.
Finally,
line 22 increments r by 1.
22:
r = r + 1
We
do this so that the next iteration through the loop will place the
hyperlink 1 line down.
Next
month we will look at ways to display the page numbers on the table of
contents when it is printed out. Meanwhile, if you enjoy a
challenge, try doing it yourself. Send me
your code and I’ll credit you in the next issue (or if you’d rather, I
won’t mention your name. It’s up to you).
Chad K. Welch works as a technician/enabler in
Utah. He is available for
consulting or application programming with Microsoft Office and VBA.
Do you have a question or
tip you’d like to have Chad address in this column? Send an
email to linda@personal-computer-tutor.com
or contact him directly at chad@welchkins.com for information
|
,
