MsgBox fails with no error message

[OneSkyWalker]

Hello.

I have stumbled over a strange problem in Excel VBA.

Here is a VBA macro which works as I expect:

Option Explicit
Sub BadExampleFailsWithErrorMessage()
Dim rngTemp As Range
Set rngTemp = ActiveSheet.Cells(1, 1)
MsgBox "rngTemp: " & rngTemp.Address
Set rngTemp = rngTemp.Find("punt")
MsgBox "New rngTemp: " & rngTemp.Address
End Sub

If I open two additional workbooks (contents irrelevant) in addition to the workbook containing this macro, use Alt-F11 to get to VBA, and run this macro, VBA stops on the statement 'MsgBox "New rngTemp: " & rngTemp.Address' and throws an error message telling me that rngTemp is not a valid range. That is the behavior I expect.

Here is a VBA macro which fails in a way that I do not expect:

Option Explicit

Const iSeriesMax As Integer = 150   'Maximum number of series to be included in a single graph
Public wbThis As Workbook           'Workbook containing this macro
Public wbStat As Workbook           'New workbook being built
Public iLastRow As Long             'Last row of info loaded from .CSV file
Public iLastCol As Integer          'Last column of info loaded from .CSV file
Public objBarChoice() As Integer    'Array of Workbook indices on objBar Popup

Sub BadExampleNoErrorMessage()

Dim i As Integer                    'For loop control variable or temporary save area
Dim j As Integer                    'For loop control variable or temporary save area
Dim wb As Workbook                  'Temporary workbook
Dim wsTemp As Worksheet             'Temporary worksheet
Dim rngTemp As Range                'Temporary range
Dim MsgBoxResult As VbMsgBoxResult  'Result returned by MsgBox function

Set wbThis = ActiveWorkbook         'Save reference to workbook containing this macro

'Ask user to choose or verify the workbook to which graph should be addded
Select Case Workbooks.Count
  Case 1
    MsgBox "Please open workbook to which graph should be added, then run" & _
           wbThis.Name & " again.", vbExclamation
    End
  Case 2
    'For each open workbook ...
    For Each wb In Workbooks
      'The workbook to be used must be a workbook other than the one containing this VBA macro
      If wb.Name <> wbThis.Name Then
        'Allow user to answer this yes or no question
        MsgBoxResult = MsgBox("Okay to add graph to " & wb.Path & "\" & wb.Name & "?", vbYesNo, "Validate selection")
        Select Case MsgBoxResult
          'If user answer was 'no'
          Case vbNo
            MsgBox "Stopping now!", vbExclamation
            End
          'If user answer was 'yes'
          Case vbYes
            Set wbStat = wb
            wb.Activate
          Case Else
            MsgBox "Unexpected result '" & MsgBoxResult & "' from MsgBox!  Stopping now!", vbExclamation
            End
          End Select
      End If
    Next wb
  Case Else
    'Build an object from which user can select the workbook to which graph should be added
    Dim objBar As Object, objBtn As Object
    'Make sure object does not yet exist
    On Error Resume Next
    Application.CommandBars("wbNavigator").Delete
    Err.Clear
    'Create and populate object to display a pop-up list of open Excel workbooks from which user must choose
    ReDim objBarChoice(Workbooks.Count)     'Make objBarChoice array big enough
    Set objBar = Application.CommandBars.Add("wbNavigator", msoBarPopup)    'Create object for pop-up list
    
    'Attempt to set width of Application.CommandBars("wbNavigator") msoBarPopup - this does not seem to be working
    Application.CommandBars("wbNavigator").Width = 1002
    objBar.Visible = True
    objBar.Width = 1002
    Set objBtn = objBar.Controls.Add        'Add first row to pop-up list object\
    objBtn.Visible = True
    objBtn.Width = 1000
    
    'Populate first row in pop-up list object
    With objBtn                             'Specify that objBtn is to be referenced
      'First row in objBarChoice is a heading which it is not valid to choose
      .Caption = "Click on workbook to which graph should be added ..."
      .Style = msoButtonCaption
      .OnAction = "wbActivayte"
    End With
    i = 1                   'Index for entries in objBarChoice
    j = 0                   'Index of open Workbooks
    objBarChoice(i) = j     'There is no workbook associated with first row in objBarChoice because it contains the heading (above)
    'For each open workbook ...
    For Each wb In Workbooks
      j = j + 1             'Increment index to next open workbook
      'Do not present Excel workbook containing this VBA macro as a choice in the object being built
      If wb.Name <> wbThis.Name Then
        i = i + 1                           'Increment index in objBarChoice
        Set objBtn = objBar.Controls.Add    'Add next row to pop-up list object
        objBarChoice(i) = j                 'Save index of current open workbook in objBarChoice
        'Populate next row in pop-up list object
        With objBtn                         'Specify that objBtn is to be referenced
          .Visible = True                   'Set Visible = True in an attempt to allow Width to be set
          .Width = 1000                     'Attempt to set width of Application.CommandBars("wbNavigator") msoBarPopup - this does not seem to be working
          .Caption = wb.Name
          .Style = msoButtonCaption
          .OnAction = "wbActivayte"     'If/when this row is clicked in objBarChoice, invoke subroutine wbActivayte (below) to activate the chosen workbook
        End With
      End If
    Next wb
    
    'Display width of Application.CommandBars("wbNavigator") msoBarPopup
    i = objBar.Width
    j = objBtn.Width
    'MsgBox "objBar.Width = " & i & ", objBtn.Width = " & j
    
    'Display object - control returns when user selects an open worksheet
    Application.CommandBars("wbNavigator").ShowPopup
    Application.CommandBars("wbNavigator").Delete
End Select

Set wsTemp = Worksheets("Data").Activate

'Set range of county statistics loaded from .CSV file
'Note: UsedRange has a reputation for being buggy, but seems to work okay here when worksheet cells are first loaded.
'Please note that if wsTemp is not a valid worksheet, then 'MsgBox "rngTemp " & rngTemp.Address' will not display anything but will not generate an error message!
Set rngTemp = wsTemp.UsedRange

MsgBox "Please note that the next message box does not appear but VBA does not throw any error message."
MsgBox "rngTemp " & rngTemp.Address
MsgBox "Please note that the previous message box did not appear but VBA did not throw any error message."

End Sub

'Subroutine to activate workbook (needed by objBtn above)
Private Sub wbActivayte()
'MsgBox "Application.Caller(1) is " & Application.Caller(1)
If objBarChoice(Application.Caller(1)) = 0 Then
  MsgBox "Can not choose title in popup list!  Stopping now!", vbExclamation
  End
Else
  Set wbStat = Workbooks(objBarChoice(Application.Caller(1)))
  wbStat.Activate
End If
End Sub

If I have two additional workbooks (contents irrelevant) open in addition to the workbook containing this macro, use Alt-F11 to get to VBA, and run this macro, the VBA Application.CommandBars("wbNavigator").ShowPopup appears containing a list of the open workbooks. If I select either of the workbooks, the statement 'MsgBox "New rngTemp: " & rngTemp.Address' (near the end of the first subroutine) does not cause a MsgBox to pop up (I assume because rngTemp is invalid), yet VBA does not throw an error message telling me that rngTemp is not a valid range.

Is there something such as 'Option Explicit' that I can set which will cause Excel to throw an error message telling me that the rngTemp parameter on MsgBox is not a valid range?

This issue took me hours to debug.

I can provide a spreadsheet containing these two macros but I do not see any way to upload a spreadsheet.

 

[OneSkyWalker]

I am running Microsoft Excel for Microsoft 365 MSO (16.0.13029.20342) 64-bit.

 

[jasonb75]

The code contains the line On Error Resume Next which is telling it to ignore any errors encountered and continue with the next line of code. This instruction to ignore errors will remain active until the procedure ends or the instruction is reset to find errors with something like On Error Goto 0

 

[Fluff]

This line

On Error Resume Next

simply masks all error, which is why you don't get an error.
You should never use that as a general purpose error handler, if you do have to use it, you should always reset it as soon as possible like

   On Error Resume Next
    Application.CommandBars("wbNavigator").Delete
    On Error Goto 0

 

[smozgur]

What I can see quickly:
"Two additional workbooks" means Workbooks.count > 2, so it is caught by Case Else.
You have On Error Resume Next without resetting it after using. So, you won't see any error even there is one.