If you are wanting to learn VBA and you own a Mac or you already know how to write VBA and you are considering whether to buy a Mac the obvious question is will VBA work on Mac. Are there any problems associated with switching over?
VBA for the most part works pretty much the same on a Mac in Excel. The commands used to automate the Mac are identical to those used on other PCs in Excel but the only downside is that you cannot use ActiveX controls as they are not supported by Apple.
However, to make sure that you do not run into compatibility issues it is best to develop code on a Mac if it is going to be used on a Mac going forward rather than creating it on a conventional PC in a Windows environment as there will be the odd occasion where incompatibilities will arise and the code will not run as expected.
What Are The Alternatives To ActiveX Controls?
Having Activex controls not available on the Mac is problematic particularly if you like to have your Macros triggered by a button. So what are the alternatives?
The easiest way to trigger a macro in a similar way to a button on a spreadsheet is to place an object into the spreadsheet such as a shape or even an image which can be used to trigger macros. The steps on how to do this are below.
Step 1: Go to the insert tab and select Illustrations
Step 2: Select Shapes
Step 3: Select a shape
Step 4: Right click on the shape and select assign macro
The other alternative to triggering macros automatically without having to resort to clicking on the ribbon is to set the macros up to trigger upon the change of a value of a cell, which is a method that works well in some circumstances. How to do this is shown below.
Step 1: Open the vba editor and select the sheet in which has the cell you want to trigger the macro with.
Step 2: Paste in the code below. You will need to modify the address in the code below to the relevant cell and also change the name of the macro called.
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$A$1" Then
Call Mymacro
End If
End Sub
Relevant Articles
Can VBA Be Used In Google Sheets?
What Are The Drawbacks Of Recording Macros In Excel?
Co-authoring Excel Spreadsheets: What Are The Problems And Limitations?