Compile error: Expected Function or variable

Question

I have this macro (written in Excel 2016/Windows) that acts a very simple reservation tool, checking if an asset is currently booked or free. Depending on this, it either writes when the booked period will end or when the next booked period will start in another worksheet:

Sub Schaltfläche1_Klicken()

Worksheets("Tabelle10").Activate                                        
With Columns(4)                                                         
.Find(what:="*", after:=.Cells(1, 1), LookIn:=xlValues).Activate    
End With

ActiveCell.Offset(0, -3).Select                                    

If Selection.Value = "TODAY AM" Then
Sheets("HTML Output").Range("B3").Value = "Desk booked from this afternoon. Next availability"

ActiveCell.Offset(0, 3).Select
Do Until IsEmpty(ActiveCell)            
ActiveCell.Offset(1, 0).Select
Loop  

ActiveCell.Select

ActiveCell.Offset(0, -2).Select

Selection.Copy

Sheets("HTML Output").Range("C3").PasteSpecial xlPasteValues

ActiveCell.Offset(0, 1).Select

Selection.Copy

Sheets("HTML Output").Range("D3").PasteSpecial xlPasteValues

ElseIf Selection.Value = "TODAY PM" Then
Sheets("HTML Output").Range("B3").Value = "Desk booked from this afternoon. Next availability"

ActiveCell.Offset(0, 3).Select

Do Until IsEmpty(ActiveCell)            
ActiveCell.Offset(1, 0).Select
Loop

ActiveCell.Select

ActiveCell.Offset(0, -2).Select

Selection.Copy

Sheets("HTML Output").Range("C3").PasteSpecial xlPasteValues

ActiveCell.Offset(0, 1).Select

Selection.Copy

Sheets("HTML Output").Range("D3").PasteSpecial xlPasteValues

ElseIf Selection.Value = "TOMORROW AM" Or Selection.Value = "TOMORROW PM" Or  Selection.Value = "FUTURE" Then

Sheets("HTML Output").Range("B3").Value = "Desk free until (including)"

ActiveCell.Offset(-1, 1).Select

Selection.Copy

Sheets("HTML Output").Range("C3").PasteSpecial xlPasteValues

ActiveCell.Offset(0, 1).Select

Selection.Copy

Sheets("HTML Output").Range("D3").PasteSpecial xlPasteValues

End If

End Sub

This works perfectly fine in Office 2016 (Windows 10) but results in a Compile error: Expected Function or variable when I try to run it in Office 2011 for Mac or Office 2015 for Mac.

Can anyone point me in the right directions as for the reason(s) for this or tell me how to change the code to make it work?

Thanks in advance! Jascha

Answer 1

The error handling in VBA Excel 2011 is not as great as it's Window's counterpart.

You were getting that error because you were using With/End With with ActiveCell.Offset(0, 1).Select

The best way to reproduce that error is paste this code in a module

Sub Schaltfläche1_Klicken()
    With ActiveCell.Offset(0, 1).Select
    End With
End Sub

Note: You and I didn't get that error later because you modified your post which we both tested :)

Interesting Read