using VBA application.statusbar in preprocessor routine causes runtime error when compiling

Question

I have some code that uses the preprocessor commands: #If, #Else and #End If

basically I've got

#If Mac then
   msgbox "Can't update the list when running on a mac"

#Else
  Application.StatusBar = "updating names..."

  ***other code here****           

  Application.StatusBar = false
#End if

The code works fine on my machine but on others, when the spreadsheet is opened and the code compiles, an error message comes up:

It is an error that occurs when opening the spreadsheet in protected view. If not in protected view it doesn't show the error. It works fine once the sub is called after it is opened but when it compiles at the point of being opened it falls over.

If I remove the status bar commands the error does not happen.

I have tried adding an 'On Error Resume Next' but that doesn't stop the error.

So it appears that I cannot use the statusbar, within a preprocessor routine, and be certain it will work.

I use Excel 2016 and I have seen the error on Excel 2013. Both machine are 64 bit windows 8.1 and actually both machines are exactly the same spec ASUS UX305F with core M processor.

Can anyone shed any light into what might be happening?

Answer 1

I can't reproduce the problem, but assuming:

• Application.StatusBar works fine outside precompiler directives

And:

• Precompiler directives are only used for disabling the macro on a Mac environment

Just re-organize things a bit, so that the code destined to run in a Windows environment isn't enclosed in precompiler directives:

Public Sub Macro1()
    #If Mac Then
        MsgBox "Can't update the list when running on a Mac"
        Exit Sub
    #Else
        DoSomething
    #End If
End Sub

And then move the actual logic to DoSomething, leaving the actual code free of precompiler directives:

Private Sub DoSomething()
    Application.StatusBar = "updating names..."

    '***other code here****           

    Application.StatusBar = false
End Sub

Worst-case, the Mac user clicking your button will get a compile error and the code won't run... but it's not supposed to run anyway, so...