Excel 2011 for Mac VBA El Capitan Having 'upgraded' to a iMac (Retina 5K, 27-inch, Late 2015) running El Capitan 10.11.3, we now find macros failing with Date functions misbehaving. And that's just the first VBA bug we've come across. Greetings, Is there a Visual Basic for Applications (VBA) forum for Excel 2011 on the Mac? I tried posting in the Windows forum, but was told I couldn't be helped. Locate the IDAutomationUniversalFontVBA.bas file in the Universal Fonts download (found in IDAutomation Universal Font Advantage.zip IDAutomationUniversalFontAdvantage Integration Excel & Access VBA IDAutomationUniversalFontVBA.bas). In Mac Excel 2011: Go to Tools - Macro.
The first option on the View menu of the Office 2011 for Mac Visual Basic Editor (VBE) is the Code option, which is grayed out until you make at least one Code window visible in the VBE. A common way to open a new code window is to use Insert→Module. A module is like a new word processing document window — it’s blank. Nothing interesting happens until you start writing. The same VBE features apply to Excel, Word, and PowerPoint.
Exploring the Office 2011 VBE Code Window
Notice that horizontal lines separate the declarations section and each of the two macros. The lines are drawn automatically by the VBE. Each macro section is called a procedure. More about Code windows:
Object pop-up menu: This is the pop-up menu on the left side of the window; it provides a list of shortcuts to objects.
Procedure pop-up menu: This is the pop-up menu on the right side of the window; it provides a list of shortcuts to procedures and declarations. When an object is chosen in the Object pop-up menu, the procedures that are applicable to the selected object are displayed.
Declarations section: (Optional) Public declarations appear at the top of a Code module.
Macro modules: Each macro, or procedure, is separated by a line.
Procedure view button: Click to limit the display to the declaration or procedure section that has the insertion cursor.
Full Module view button: Click to display all Code window contents.
Writing VBA code in Office 2011 for Mac
As with a word processor, in a Code window the editor knows which commands are legal and which are not, and the editor gives you feedback as you type. New for VBA (Visual Basic for Applications) in Office 2011 is Intellisense, a feature that helps you automatically complete your code as you type. To see how Intellisense works, declare a public variable in an empty Code window:
Choose Insert→Module to display a blank Code window.
Type the following code exactly as you see it here and press Return at the end of the line:
Notice that after you type as, a pop-up menu appears. This is Intellisense at work. If you know what you want to type, you can keep right on typing. But if you want to see all the possible options, use the pop-up menu.
Getting help with VBA code in Office 2011 for Mac
You can rely on the following five principal sources of code examples, syntax help, and general programming assistance:
Macro recorder: In Excel and Word, you can record your actions and then look at the code that was recorded inside the modules.
Object browser: A handy treasure trove of information about the objects, methods, and properties of Office.
Help: The Help system within the VBE is minimal. Microsoft plans to provide VBA help for Excel only.
Internet: Search the Internet for VBA code examples.
Books on VBA: You can find plenty of books about VBA.
Introduction: How to get started
Goal of this chapter
This chapter provides a high-level description of:
- What is Excel VBA?
- What prior knowledge you are expected to have when starting the tutorial
- How to get Excel VBA to work in your Microsoft Office version in case it doesn't do so already
- A note on the appearance of different versions
What is Excel VBA?
Excel Visual Basic for Applications (VBA) is the programming language embedded in Microsoft Excel that can be used to make sophisticated workbooks or even user-defined applications. Though at first sight the VBA editing window is less straightforward than the standard worksheet Excel user-interface, in comparison it offers a nearly unlimited level of flexibility and much greater calculation power. As it is a version of Visual Basic specific for Excel there are many pre-defined parts and pieces available for quick and easy programming.
Prior knowledge
The only knowledge assumed necessary to follow this tutorial is the normal use of Excel. As long as you are reasonably familiar with Excel then it should be easy to follow the instructions of the tutorial to move around Excel. In case you do not know Excel at all, it is probably better to first explore the already extensive possibilities that Excel offers without the use of VBA. This tutorial is especially aimed at those with good knowledge of Excel who can substantially upgrade their work with only a bit of VBA knowledge.
Preparations
Three things you should check before starting the tutorial:
Firstly, you need to have Visual Basic for Excel installed on your computer. In my experience it is included already when you have followed the standard installation of Excel, for both Windows and Mac versions. Only in Windows Excel 2007 you might need to add it from the installation disc.
A quick way to test on either operating system if you have Visual Basic installed is to open a blank workbook and press ALT+F11 for Windows or Option+F11 on Mac. If the VBA editing window opens you are ok.
Alternatively, on Mac you can click “Tools” in the top Excel menu, followed by clicking “Macro” and then “Visual basic editor” (going forward I write such a menu sequence as “Tools -> Macro -> Visual basic Editor”).
If ALT+F11 did not work on Windows and you want to try accesssing the VBA Editor in a similar way on Windows as described for Mac then you probably first need to activate the Developer ribbon (see paragraph below). If you cannot complete those steps in your Windows version then you probably will have to install VBA. For Windows Excel 2007 you can do that in the Office installation, which you reach (on Windows itself, not in Excel) via “Control Panel -> Add/Remove Software -> Change Microsoft Office -> Office Shared Features” and follow the instructions.
Secondly, you need to be able to see the Developer ribbon, which may not be the case immediately in Excel versions since 2007. To add the Developer ribbon if required:
- For Windows Excel go to the “File tab -> Options (under Help) -> Customize ribbon” and there under “Main Tabs” tick the check box of the Developer ribbon on the right-hand side.
- For Mac 2011 and Mac Office 365 go to “Excel -> Preferences... -> Ribbon (in Office 365 Ribbon & Toolbar)” and tick “Developer”.
Through the Developer ribbon you can access the VBA Editor. Click “Developer -> Visual Basic”.
Thirdly, the macro security level needs to be set low enough in order for macros to be permitted to run on your computer. Depending on the version of Excel this is either:
- Windows Excel 2003 and before, go to “Tools -> Options -> Security -> Macro Security” and set the security level to medium so you can indicate whether or not to permit the execution of macros depending on the workbook you are opening.
- Windows Excel 2007 and later go to “Developer -> Macro Security” and tick the box of “Disable all macros with notification”.
- Excel for Mac 2011 go to “Excel -> Preferences... -> Security -> Macro Security” and tick the box “Warn before opening a file that contains macros”.
- Mac Office 365 go to “Excel -> Preferences... -> Security” and under “Macro Security” tick the box “Disable all macros with notification”.
A note on the appearance of different versions
Excel Vba For Mac 2011 Download
Windows Microsoft Excel 2010 & 2013 and Microsoft Excel for Mac in Office 365 are the versions used in the tutorial for the illustrative images. The exact version of Excel you are using should generally be irrelevant when following this tutorial. The only difference between the Windows versions is the user interface of Excel where you might find buttons in a different place (but they should all be there) while the Visual Basic editor is the same for all Windows Excel versions.
Excel Vba For Mac 2011 Product
The VBA editor does look slightly different in Excel for Mac, but also has the same functionality. Where commands or menus in Excel for Mac 2011 or Mac Office 365 are siginificantly different to those in the Windows version I have provided additional instructions for Mac.
Next: Chapter 1: How to record a macro
Excel Vba For Mac 2011 Version
Easy Excel VBA Tutorial: Contents