Some preliminary recording

Here's an example of using the macro recorder to learn about VBA. I started with a workbook that contained three worksheets. Then I turned on the macro recorder and specified my Personal Macro Workbook as the destination for the macro. With the macro recorder running, I dragged the third worksheet to the first sheet position. Here's the code that was generated by the macro recorder:

Sub Macro1()

Sheets("Sheet3").Select Sheets("Sheet3").Move Before:=Sheets(1) End Sub

I searched the VBA Help for Move, and discovered that it's a method that moves a sheet to a new location in the workbook. It also takes an argument that specifies the location for the sheet. Very relevant to the task at hand. Curious, I then turned on the macro recorder to see whether the Edit ^ Move or Copy Sheet command would generate different code. It didn't.

Next I needed to find out how many sheets were in the active workbook. I searched Help for the word Count and found out that it's a property of a collection. I activated the Immediate window in the VBE and typed the following statement:

? ActiveWorkbook.Count

Error! After a little more thought, I realized that I needed to get a count of the sheets within a workbook. So I tried this:

? ActiveWorkbook.Sheets.Count

Success. Figure 9-8 shows the result. More useful information.

Figure 9-8: Use the VBE Immediate window to test a statement.

Figure 9-8: Use the VBE Immediate window to test a statement.

What about the sheet names? Time for another test. I entered the following statement in the Immediate window:

? ActiveWorkbook.Sheets(1).Name

This told me that the name of the first sheet is Sheet3, which is correct. More good information to keep in mind.

Then I remembered something about the For Each-Next construct: It is useful for cycling through each member of a collection. After consulting the Help system, I created a short procedure to test it:

Sub Test()

For Each Sht In ActiveWorkbook.Sheets

MsgBox Sht.Name Next Item End Sub

Another success. This macro displayed three message boxes, each showing a different sheet name.

Finally, it was time to think about sorting options. From the Help system, I learned that the Sort method applies to a Range object. So one option was to transfer the sheet names to a range and then sort the range, but that seemed like overkill for this application. I thought that a better option was to dump the sheet names into an array of strings and then sort the array by using VBA code.

0 0

Post a comment