Errors in Chapter 9, Pages 142-143 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
This is the second and the last post about the errors I have encountered in Chapter 9 of the Kindle version of the book "Microsoft Excel VBA and Macros (Office 2021 and Microsoft 365)” by Bill Jelen and Tracy Syrstad. See “Hidden Error in Chapter 9, Page 141” for the first post.

Pages 142-143 (Kindle Version)
The standard module procedure EmployeesPayUsingCollection() given starting towards the end of page 142 in the subsection “Creating a collection in a class module” has the following errors:

a) A procedure with the same name already exists on page 141. Another procedure with the same name causes confusion when placed in the same Excel file and also when studying the book. The procedure’s name, set out towards the end of page 142, should be changed, for example, as follows:

From:
VBA Code:
Sub EmployeesPayUsingCollection()

To:
VBA Code:
Sub EmployeesPayUsingClassCollection()

b) The procedure includes the following three lines of code towards its end on page 143:

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

There is an error in each of these three lines. In the first and third lines, objects in a collection class are being referred to incorrectly as objects in a regular collection; this causes the Run-time error '438': Object doesn't support this property or method error when the procedure is run. The second line has a hidden error: while it finds out the first name of the employee "1651" correctly, it returns, when specified, the first names of most other employees from the Excel table given earlier in the code incorrectly either as truncated or with trailing spaces (the same error I mentioned for another procedure in the book in the first post). Changing the three lines of code, for example, to the following fixes all of these problems.

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

Excel Facts

Remove leading & trailing spaces
Save as CSV to remove all leading and trailing spaces. It is faster than using TRIM().

starl

Administrator
Joined
Aug 16, 2002
Messages
6,009
Office Version
  1. 365
Platform
  1. Windows
  2. MacOS
Still waiting to hear back from publisher on updating things, but if I can:
1. Rename Sub that uses the Class Collection
2. Fix the references to the collection (e.g.
VBA Code:
FullName = colEmployees.Item("1651").EmployeeName
)
3. Fix extraction of the first name

thanks again
 

Forum statistics

Threads
1,171,185
Messages
5,874,225
Members
433,038
Latest member
petercannas

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
Top