Strange issue with str function versus Str

Question

I have one of the stranges problems I have encountered for many years. I have a Workbook with a lot of code that validates the users data and has been used for many years now. A user reported that the latest version of the file was crashing excel and giving him an "automation error". To my knowledge the changes made to the most recent file were minor and should not have caused this. On analysis the cause of the problem was straight forward, but how it happened, why it happened and how to fix it, I do not know. The issue occurred due to the below line of code, which is expecting a numeric, but the user supplied a string:

Ltrim(Str(Usersdata(UsersDataRow,UUID_Col)))

Note the upper case S on str. The previous version of the workbook has the same line but str is in all lower case, and does NOT crash excel. Both files syntax check perfectly. But the strangest thing is when I have both old and new version of the file open in the VBA editor. If I edit the line in the old file and change str to Str, the editor autocorrects it back to all lower case str. If I edit the new workbook and change Str to str, the editor autocorrects it back to init cap Str. So as it stands, I cannot correct the new file. This behaviour is very strange and am hoping someone can tell me how it has happened and possibly how to solve it?

Answer 1

Regarding fixing the main problem I suggest replacing the line

Ltrim(Str(Usersdata(UsersDataRow,UUID_Col)))

with a more thorough input validation that can handle alphanumeric values

---

.

As Siddharth Rout suggested in the comments:

The symptoms you describe indicate that the VBA name space has been corrupted

The most common source are variable names like "str", "val", "name", "file", "count", "cell", "row" etc

A quick way to check name conflicts is to click inside the variable name and press F1;

• the Help should show "Keyword Not Found"

---

.

The experiment bellow can demonstrate the problem: open a new Excel, and Alt + F11 for VBA

1. paste this code in a standard module:

---

Sub test1()

    Dim txt As String   'valid variable name

    txt = Str("123")    'Str() remains with a capital S

End Sub

---

.

2. Now replace the code with this (obvious problem):

---

Sub test2()

    Dim str As String   'invalid variable name

    str = str("123")    'Str() is converted to lower case "s"

End Sub

---

.

VBA is now corrupted and here is one way to fix it:

• close the file
• reopen it, and do not allow macros to run
• open VBA editor (Alt + F11)

• perform a Search and Replace in all VBA modules for "str" (replace "str" with "Str")

  • Match Case
  • Find Whole Word Only
  • Current Project <-- most important setting

The Replace operation will be performed only once, because VBA will automatically convert all other instances of "str" to "Str" before any other replacements

Name space is now restored the next time you open the file

(the procedure forces a recompilation of the P-code generated for all modules)

Another way to re-generate clean P-code is to export each individual standard module as *.bas files, *.cls for Class modules, and *.frm for user forms code, and import all into a new Excel file