xperson
New Member
- Joined
- May 6, 2022
- Messages
- 2
- Office Version
- 365
- Platform
- Windows
- 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
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:
To:
b) The procedure includes the following three lines of code towards its end on page 143:
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
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").EmployeeWeeklyPayThere 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
