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
– sous2817, Commented Jun 14, 2017 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 (mrexcel.com/forum/excel-questions/…) — sous2817
– sous2817, Commented Jun 14, 2017 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
– Siddharth Rout, Commented Jun 14, 2017 at 19:21

Siddharth Rout · Accepted Answer · 2017-06-14 19:44:12Z

2

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

edited Jun 14, 2017 at 19:44

answered Jun 14, 2017 at 19:10

Siddharth Rout

150k18 gold badges212 silver badges258 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Compile error: Expected Function or variable

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related