Saturday, June 22, 2019

excel - How to force vba compile of worksheet code on worksheet.activate

I have inherited maintenance many excel sheets that are used by many people in my company. Recently one user has started getting application-defined errors which do not occur on other computers.
I've debugged on his machine, and they happen when a worksheet is activated. It only occurs on worksheets with VBA in the sheet, and where there is an error in an unused routine.
eg.



option explicit


sub RunReport()
...
Setup.Activate 'compilation error occurs here when debugging
'I assume that this is the cause of the application-defined error which occurs when not debugging
...
end sub


workbook named Setup




Option Explicit

.... other code

Private Sub ListBox1_Initialize()'Listbox1 has been deleted from the sheet so this is never called
Dim allReports() As String
allReports = Split(ALL_LOCS, DELIM)
ListBox1.list = allReports 'compile error here because listbox1 doesn't exist
End Sub



I assume that there is a setting that is calling a full compile of the code when the worksheet is activated. I want to turn it on on my dev environment so I can investigate these issues locally.
Where can I find this setting?
(I am using office 2013)



Edit:
For clarifcation based on answers.
Option Explicit is set at the start of the code. I have autosyntax checking and require variable declaration set on my dev setup that does not experience this issue.

No comments:

Post a Comment

plot explanation - Why did Peaches' mom hang on the tree? - Movies & TV

In the middle of the movie Ice Age: Continental Drift Peaches' mom asked Peaches to go to sleep. Then, she hung on the tree. This parti...