I have code in an Excel workbook which is in Modules and which is implemented via a Userform. The workbook is a template workbook so when colleagues use it they take a copy and work in their own copies of it. They use a separate copy for each client so in essence, there are hundreds of copies of it in use at any one time.
The interface and code have been working fine for about 2 years but in the past week I have had a 2 cases flagged to me with various errors occuring. When I've stepped through the code it has been throwing errors in different places.
In the first incident, a string variable wasn't retaining the correct value:
sSQL = "SELECT * FROM table WHERE name is '" & name & "';"
After this line executing, the value of sSQL was "SELECT * FROMJohn Johnson", i.e. it had lopped off parts of the string. No error was thrown at this point but in the next line the sSQL string variable is an argument in a recordset.open function call. As sSQL is an incoherent select statement, rs.open threw a SELECT statement error.
This was confusing as nothing had changed in the code and it had always worked before. I copied and repasted the line and this fixed the problem - the second line ran without issue, sSQL had the correct value so rs.open worked.
The day after I had another case flagged to me - so a copy of the same template workbook but a different copy from the one the previous day. The error was the same and it was being caused by another sSQL string not retaining the correct value but in a different Sub elsewhere in the code. I fixed this in the same way - by copying and repasting the line which assigns the value to sSQL. Again this fixed the issue.
However, in the same workbook I encountered another error further on in the code. This time it was an If statement causing the error:
Private Sub setAuthoriser(Optional s As String)
'sets or clears the Authoriser name on signoff data sheet
If IsMissing(s) Or s = "" Then
Sheet3.Cells(16, 6).ClearContents
Else
Sheet3.Cells(16, 6).Value = s
End If
End Sub
When If IsMissing(s) Or s = "" Then executes I get the following error:
Run-time error '-2147417848 (80010108)': Automation error The object invoked has disconnected from its clients.
Whether I click Degug or End on the warning message, the workbook then closes.
Again, code has worked consistently before and can be fixed by copying and repasting the code and deleting the original lines.
Does anyone have any idea what is happening here or have you encountered something similar before? I'm really stuck...