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

xperson

New Member
Joined
May 6, 2022
Messages
2
Office Version
  1. 365
Platform
  1. Windows
  2. MacOS
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:

VBA Code:
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:

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

Excel Facts

Did you know Excel offers Filter by Selection?
Add the AutoFilter icon to the Quick Access Toolbar. Select a cell containing Apple, click AutoFilter, and you will get all rows with Apple
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.
 

Forum statistics

Threads
1,216,499
Messages
6,131,012
Members
449,613
Latest member
MedDash99

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top