Hidden Error in Chapter 9, Page 141 of "Microsoft Excel VBA and Macros (Office 2021 and Microsoft 365)"

[xperson]

Hi everyone!

This is my first post in this forum.

I bought the Kindle version of Bill & Tracy's good book "Microsoft Excel VBA and Macros (Office 2021 and Microsoft 365)" two months ago through Amazon. Having started from the first chapter, I have been studying it since then in my free time. Though the book turned out to be a bit different than I expected (it is organized a bit too much like a cookbook for my taste), I still like it and have learned quite a bit of VBA from it.

I have just completed Chapter 9 "Creating classes and collections" and would like to mention in two separate posts in this forum the few minor errors I think I have encountered in this chapter. Below is the first one: (the page numbers are based on the Kindle version; I am not sure if the Kindle version has the same page numbers as the print or PDF versions.)

Page 141 (Kindle Version)
The standard module procedure EmployeesPayUsingCollection() given in the subsection “Creating a collection in a standard module” of Chapter 9 has the following three lines of code towards the end of page 141:

FullName = colEmployees("1651").EmployeeName
MsgBox Left(FullName, Len(FullName) - InStr(1, FullName, " ") - 2) & _
    "'s Weekly Pay: $" & colEmployees("1651").EmployeeWeeklyPay

I understand the Left(FullName, Len(FullName) - InStr(1, FullName, " ") - 2) part is intended to return the first name of a specified employee based on the respective full name string obtained from the Excel table specified earlier in the code. While it works for the specified employee in the example, colEmployees("1651"), it is actually incorrectly set up, because it is only designed to return one character less than the length of the employee’s last name from the left of the string with employee’s full name, rather than as many characters as the length of the employee’s first name. The output of this part of the code, as far as the acquisition of a first name is concerned, is actually even less useful because, while some of the full names in the Excel table have redundant trailing spaces, others don’t.

All this can be fixed, for example, by incorporating the Trim function and reorganizing the above three lines of code as follows:

FullName = Trim(colEmployees("1651").EmployeeName)
MsgBox Left(FullName, InStr(1, FullName, " ") - 1) & "'s Weekly Pay: $" & _
    colEmployees("1651").EmployeeWeeklyPay

 

[starl]

Thanks for catching that, @xperson . I'll contact the publisher and see what we can do about replacing that line in the next reprints. I don't know if they auto-update the ebooks or not.