Excel VBA - Why doesn't sheet code recognize my global range variable defined in a module?

Question

Full disclosure, I've done basic programming in a few languages but this is me dipping the toe back in VBA after a long time. I may have missed very basic concepts or syntax...

The purpose of the code is to check a set of cells to make sure they are completed on an excel pseudo form before saving. I define the various cell ranges as public range variables and union various subsets together depending on a check box and radio button.

I want to use the checkbox click sub to set some variables to use for this union and to automatically adjust the conditional format of blank cells that must be completed. That's where I'm running into a problem where the public variables aren't being recognized in the Click Sub. It's probably a simple variable definition error but I'd love an assist on this.

I get a:

Run-Time error '91':

Object variable or With block variable not set

on the Motor call in the checkbox sub. If I explicitly call out the range in one spot, it gives me the same error on the next motor.

Module Code For Defining Variables:

Option Explicit

Public DriveChk As String
Public StageChk As Integer
Public Fixed As Range
Public Motor As Range
Public Engine As Range
Public Stage1 As Range
Public Stage2 As Range
Public Stage3 As Range
Public Required As Range
Public Stage As Range
Public Drive As Range

Code for Workbook

Private Sub Workbook_Open()

'Ensure that the workbook stays protected but that the VBA script has full access to change cells.
ThisWorkbook.Worksheets("Field Service Report").Protect Password:="xxxxx", UserInterfaceOnly:=True

'Message to field service rep
MsgBox "Please complete FSR on the day of service." & vbNewLine & "All fields highlighted in red must be completed in order to save." & vbNewLine & "Please submit completed FSR to xxxxx", vbOK + vbInformation, "REMINDERS"

'Set all of the cell check and formatting ranges
Set Fixed = ThisWorkbook.Worksheets("Field Service Report").Range("E6,Q6,Z6,J7,AB7,J8,D28,O28,D29,A46,A58,F58,L58,P58,U57,V58,Y58")
Set Motor = ThisWorkbook.Worksheets("Field Service Report").Range("Z28,AI28,Z29,AF29,AL29,Z30,AI30,AB31")
Set Engine = ThisWorkbook.Worksheets("Field Service Report").Range("D15,M15,T15,AG15,AL15,T17,AB17,AE17,J23,J24,J25,AB20,AB21,AB22,AB23,AB24,AB25,AL20,AL21,AL22,AL23,AL24,AL25")
Set Stage1 = ThisWorkbook.Worksheets("Field Service Report").Range("I38,O38,AC38,AI38,I39,L39,O39,R39,AC39,AF39,AI39,AL39")
Set Stage2 = ThisWorkbook.Worksheets("Field Service Report").Range("I40,O40,AC40,AI40,I41,L41,O41,R41,AC40,AI40,AC41,F41,AI41,AL41")
Set Stage3 = ThisWorkbook.Worksheets("Field Service Report").Range("I42,O42,AC42,AI42,I43,O43,R43,AC43,AI43,AL43")

End Sub

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)

'Create the final range of cells for checking
If DriveChk = "G" Then
    Set Drive = Engine
ElseIf DriveChk = "E" Then
    Set Drive = Motor
End If

If StageChk = 1 Then
    Set Stage = Stage1
End If

If StageChk = 2 Then
    Set Stage = Application.Union(Stage1, Stage2)
End If

If StageChk = 3 Then
    Set Stage = Application.Union(Stage1, Stage2, Stage3)
End If

Set Required = Application.Union(Fixed, Drive, Stage)

'Check if all required cells are filled in
If WorksheetFunction.CountA(Required) < Required.Count Then
    Cancel = True
    MsgBox "Please Completed Shaded Cells!", vbOK + vbExclamation, "SAVE CANCELLED"

End If

'Set the report date before saving
Range("AG60") = Format(Now(), "mm-dd-yyyy hh:mm:ss AM/PM")

'Old code probably to ensure saving macro enabled that is currently commented out
'  Dim sFN As String
 '   If SaveAsUI Then
  '      Cancel = True
   '     sFN = Application.GetSaveAsFilename(, _
    '       fileFilter:="Excel Macro-Enabled Workbooks (*.xlsm),*.xlsm")
     '   Debug.Print sFN
      '  If sFN <> "False" Then _
       '     ThisWorkbook.SaveAs sFN, xlOpenXMLWorkbookMacroEnabled
    'End If
End Sub

Sheet Code

Option Explicit

Private Sub CheckBox26_Click()
If CheckBox26.Value = True Then
    'MsgBox text to verify sub is working
    MsgBox "CB26 is true"
    DriveChk = "E"
    Motor.FormatConditions _
        .Delete
    With Motor.FormatConditions _
        .Add(Type:=xlBlanksCondition)
        .Interior.ColorIndex = 30
        .StopIfTrue = True
    End With
Else
    'MsgBox text to verify sub is working
    MsgBox "CB26 is false"
    DriveChk = "Z"
    Motor.FormatConditions _
        .Delete
    With Motor.FormatConditions _
        .Add(Type:=xlBlanksCondition)
        .Interior.ColorIndex = 5
        .StopIfTrue = True
    End With
End If
End Sub

Thanks for the help! Sorry, I don't fully understand the shorthand that will keep long commands from wrapping. I tried to figure it out and my code wouldn't work right.

Yes Motor.FormatConditions.Delete errors. If I substitute the full range definition for Motor it errors with the same error at With Motor.FormatConditions _ — jimm, Nov 13 '19 at 13:32
Also, I originally defined the variables within the workbook but I read that its best practice to put your global variables in a separate module. I got the same error either way. — jimm, Nov 13 '19 at 13:33
Not sure your CF syntax is quite right (though wouldn't give an RTE 91 error), e..g see https://stackoverflow.com/questions/13661965/conditional-formatting-using-excel-vba-code — SJR, Nov 13 '19 at 13:42
I wondered too. It's very hard to find USEFUL examples on here or in Microsoft's documentation. But...I just substituted the full range definition for "Motor" in all the appropriate spots in the checkbox sub and the CF worked exactly as it should. So it's definitely an issue with my defined range variables. — jimm, Nov 13 '19 at 13:51

score 0 · Accepted Answer · answered Nov 13 '19 at 13:57

0

Your module variables are public but still local to module so should be prepended with module name:

Module1.Motor

answered Nov 13 '19 at 13:57

avb

1,743
1
13
23

I presume then, the same concept would apply if I defined them on Workbook open as well? Meaning, they would need to be called as ThisWorkbook.Motor – jimm Nov 13 '19 at 14:00
Well, I tried that suggestion. Still no go. Using Module1.Motor didn't work. – jimm Nov 13 '19 at 14:14
did you run your `Workbook_Open()` before further testing? if error occurs Excel drops all variables, I reconstructed your code and it works. Maybe even prefixing variables could be omitted, they were just not initialized – avb Nov 13 '19 at 14:26
I'm not sure I follow your suggestion. Are you asking if I moved the declarations to workbook and used ThisWorkbook.Motor? Are you asking if I reran Workbook_Open() after changing the checkbox sub to Module1.Motor? Which error causes Excel to drop all variables? – jimm Nov 13 '19 at 14:46
each error that stops code execution, so Workbook_Open() should be rerun or Workbook reopened - does not matter which module your variables are stored – avb Nov 13 '19 at 14:51
Thanks! That did the trick. I thought I had closed and reopened for just that reason, but perhaps I did not. I've run into a possibly related issue. Should I start another question or continue here? (Different code dealing with range calls) – jimm Nov 13 '19 at 15:13
ask another one – avb Nov 13 '19 at 15:20

Excel VBA - Why doesn't sheet code recognize my global range variable defined in a module?

1 Answers1