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

how are you kicking the code off? is it an ActiveX button click event? — sous2817, Jun 14 '17 at 19:15
this may be worth a read. Seems that if you're using an ActiveX button, it won't work on a Mac (https://www.mrexcel.com/forum/excel-questions/546289-office-mac-2011-excel-command-buttons.html) — sous2817, Jun 14 '17 at 19:17
Just tested it on Mac. it was giving me the error which you got but now it is not. Can you delete your code and re paste it and try again. I did just that and it worked — Siddharth Rout, Jun 14 '17 at 19:21
In the Windows version it is a command button in a user form, for Mac (after my first few attempts) I moved the code directly to a button on the worksheet (inserted through developer tab). Neither options worked so far. — Jascha Ell, Jun 14 '17 at 19:22
@SiddharthRout I have no idea what happened but it works now as well. — Jascha Ell, Jun 14 '17 at 19:26
I have realised why you were getting the error... Posting an answer — Siddharth Rout, Jun 14 '17 at 19:32

Siddharth Rout · Answer 1 · 2017-06-14T19:44:12.140

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

Compile error: Expected Function or variable

1 Answers1