Excel VBA Run-time error 438 first time through code

Question

I'm a novice self-taught VBA programmer knowing just enough augment Excel/Access files here and there. I have a mysterious 438 error that only popped up when a coworker made a copy of my workbook (Excel 2013 .xlsm) and e-mailed it to someone.

When the file is opened, I get a run time 438 error when setting a variable in a module to a ActiveX combobox on a sheet. If I hit end and rerun the Sub, it works without issue.

Module1:

Option Private Module
Option Explicit
Public EventsDisabled As Boolean
Public ListBox1Index As Integer
Public cMyListBox As MSForms.ListBox
Public cMyComboBox As MSForms.Combobox
Public WB As String

Sub InitVariables()
 Stop '//for breaking the code on Excel open.
 WB = ActiveWorkbook.Name
 Set cMyListBox = Workbooks(WB).Worksheets("Equipment").Listbox1
 Set cMyComboBox = Workbooks(WB).Worksheets("Equipment").Combobox1 '//438 here
End Sub

Sub PopulateListBox() '//Fills list box with data from data sheet + 1 blank
 Dim y As Integer
 If WB = "" Then InitVariables
 ListBox1Index = cMyListBox.ListBoxIndex
 With Workbooks(WB).Worksheets("Equipment-Data")
  y = 3
  Do While .Cells(y, 1).Value <> ""
   y = y + 1
  Loop
 End With
 Call DisableEvents
 cMyListBox.ListFillRange = "'Equipment-Data'!A3:A" & y
 cMyListBox.ListIndex = ListBox1Index
 cMyListBox.Height = 549.75
 Call EnableEvents
End Sub
...

PopulateListBox is called in the Worksheet_activate sub of the "Equipment" sheet.

All my code was in the "Equipment" sheet until I read that was bad form and moved it to Module1. That broke all my listbox and combobox code but based on the answer in this post I created the InitVariables Sub and got it working.

I initially called InitVariables once from Workbook_open but added the If WB="" check after WB lost its value once clicking around different workbooks that were open at the same time. I'm sure this stems from improper use of Private/Public/Global variables (I've tried understanding this with limited success) but I don't think this is related to the 438 error.

On startup (opening Excel file from Windows Explorer with no instances of Excel running), if I add a watch to cMyComboBox after the code breaks at "Stop" and then step through (F8), it sets cMyComboBox properly without error. Context of the watch does not seem to affect whether or not it prevents the error. If I just start stepping or comment out the Stop line then I get the 438 when it goes to set cMyComboBox.

If I add "On Error Resume Next" to the InitVariables then I don't error and the project "works" because InitVariables ends up getting called again before the cMyComboBox variable is needed and the sub always seems to work fine the second time. I'd rather avoid yet-another-hack in my code if I can.

Matt

Answer 1

Instead of On Error Resume Next, implement an actual handler - here this would be a "retry loop"; we prevent an infinite loop by capping the number of attempts:

Sub InitVariables()
    Dim attempts As Long
    On Error GoTo ErrHandler     
    DoEvents ' give Excel a shot at finishing whatever it's doing
    Set cMyListBox = ActiveWorkbook.Worksheets("Equipment").Listbox1
    Set cMyComboBox = ActiveWorkbook.Worksheets("Equipment").Combobox1
    On Error GoTo 0
    Exit Sub
ErrHandler:
    If Err.Number = 438 And attempts < 10 Then
        DoEvents
        attempts = attempts + 1
        Resume 'try the assignment again
    Else
        Err.Raise Err.Number 'otherwise rethrow the error
    End If
End Sub

Resume resumes execution on the exact same instruction that caused the error.

Notice the DoEvents calls; this makes Excel resume doing whatever it was doing, e.g. loading ActiveX controls; it's possible the DoEvents alone fixes the problem and that the whole retry loop becomes moot, too... but better safe than sorry.

That said, I'd seriously consider another design that doesn't rely so heavily on what appears to be global variables and state.