Truly bad book or simple misunderstanding?
There are a lot of authors (some with books in the works) that maintain blogs that grace the pages of OfficeZealot.com including Chris Webb (book in the works), Mauro Cardarelli (book in the works), Ed Bott, Eric Carter, Andrew Whitechapel and yes, yours truly. There is even an editor – Melanie Spiller who can attest to the following: a lot of effort and eyeballs go into any book. Nonetheless, errors are bound to make it into the printed version. Also, most authors I know are perfectly happy to answer any questions readers have about a book, either responding to errors, helping clarify sections of the book, or answering other questions.
That said, on behalf of everyone who has spent ungodly hours writing a book (for not a whole lot of money I might add), I’d ask one favor of those folks who choose to leave comments on Amazon and Barnes and Noble. Please be factual and give the author a chance to clarify something if you find an error or problem. I’ll be the first to admit that when I’m learning something new, chances are that when I’m having problems it is due to incomplete knowledge, misunderstanding, or misinterpretation.
Recently, a reader of my Excel VBA book got me a bit flustered when he left borderline defamatory comments on Amazon and Barnes and Noble. To make things worse, he also contacted me and asked me his question. It’s perhaps this fact that got me so flustered considering I promptly (within an hour) and politely (I thought anyway) answered his question. I thought I did the right thing, but perhaps the reader just has an ax to grind. His email to me said:
I just got your book: “Mastering Excel 2003 Programming with VBA”, and I think it's great. I tried to implement the program you have listed on page 147 (Listing 7.9: Alphabetizing Worksheets in a Workbook). I keyed in the macro word for word, and could not get it to work. Excel could not “see” the macro if (wb As Workbook) was in the parens instead of (). So, I downloaded the example files off of dakotatechgroup.com, and tried to run listing 7.9. Although it was in the module if I engaged Alt-F11, Excel could not “see” the macro and thus not run it.
Can you tell me what I need to do to run the listing 7.9 macro?
Thank you!
The listing in question simply alphabetizes worksheets in a workbook using a simple bubble sort.
' performs a simple bubble sort to
' sort the worksheets in the workbook
Sub AlphabetizeWorksheets(wb As Workbook)
Dim bSorted As Boolean
Dim nSheetsSorted As Integer
Dim nSheets As Integer
Dim n As Integer
nSheets = wb.Worksheets.Count
nSheetsSorted = 0
Do While (nSheetsSorted < nSheets) And Not bSorted
bSorted = True
nSheetsSorted = nSheetsSorted + 1
For n = 1 To nSheets - nSheetsSorted
If StrComp(wb.Worksheets(n).Name, _
wb.Worksheets(n + 1).Name, _
vbTextCompare) > 0 Then
' out of order - swap the sheets
wb.Worksheets(n + 1).Move _
before:=wb.Worksheets(n)
bSorted = False
End If
Next
Loop
End Sub
I took the time to respond to him that since this procedure requires a parameter, it can not be executed directly from the Macro dialog box, you need to call it from another procedure such as:
Sub Test()
AlphabetizeWorksheets ThisWorkbook
End Sub
Given his email, I can only assume that his simple misunderstanding of how parameterized procedures are used and the fact that they do not show up in the Macro dialog box caused him to conclude that none of the procedures in the book worked because they wouldn’t show up in the Macro dialog box. He must have skipped over the section of the book that talked about using parameters with procedures and other basic VBA coverage.
On a side note, I would recommend that as a rule of thumb, most of the procedures in your VBA project should NOT be visible in the Macro dialog box. The only procedures that should be visible in the Macro dialog box are procedures that you want the user to directly execute. Even then, I typically hide these procedures, preferring instead to attach them to a user interface element. In order to prevent non-parameterized sub routines from showing up in the Macro dialog box you can declare the sub routine as Private such as:
Private Sub Test()
AlphabetizeWorksheets ThisWorkbook
End Sub
Alternatively, you can use the Option Private Module statement at the top of the module. Note that even if you use Option Private Module, you can still hook up user interface elements to the sub routines in the module that are not marked as Private and do not contain parameters.
Oh well. I guess there is something to be learned about this. While Amazon’s “Real-Name” feature is a step in the right direction, you still have to take these things with a grain of salt.
Anyway, here is an open invitation to Mr. Reynolds – I’m sorry my book wasn’t helpful to you. Send it back to me and I will personally refund your money including the cost of you shipping it to me:
13335 Palomino Drive Suite 204
Apple Valley, MN 55124