Developer Tab and VBA Editor

An Excel macro is a set of programming instructions that automates tasks in Excel. Visual Basic for Applications (VBA) is the programming language used to code macros. It has its origins in the Microsoft BASIC programming but evolved into an object-based language. VBA can be used with most Microsoft Office applications (e.g. Word, PowerPoint), but this training is limited to Excel VBA only. Using Excel VBA macros have the following main advantages:

• Saving time
• Reducing errors
• Enforcing Standards
• Integration with other applications

Excel Developer Tab

The Excel Developer Tab gives access to the VBA programming environment and some additional VBA developer tools. Add the Developer Tab to the Ribbon in Excel (if you don’t have it already) in one of these two ways:

• Right-click the Excel ribbon and select “Customize Ribbon”, then tick the box for Developer and click OK.

OR

• Go to File -> Options -> Customize Ribbon, and then tick the box for Developer and click OK.

The Developer Tab allows to launch the Visual Basic editor, where we can create or edit macros. It also gives access to the list of available macros, the macro recorder and some macro security settings, the Add-Ins browser, Forms and ActiveX controls, and XML data manager.

Excel VBA Editor

Select the Developer tab and click Visual Basic (or press Alt + F11) to open the Excel VBA editor window. The VBA editor picture below shows the navigation tab on the top and the standard toolbar just below. We can show additional toolbars by right-clicking anywhere in the toolbar and selecting the toolbar needed (Edit, Debug, and UserForm). On the left hand side is the VBA project explorer, where initially we just see folders for each sheet in the workbook and the folder ‘Thisworkbook’ (we will talk more about that in the next lesson).

The navigation tab includes folders similar to those of other Microsoft applications. We will go through most of them in the course of this training. For now we will just have a look at the Tools folder, where we can set some basic VBA Project properties such as name, description, and password (in case we want to protect the macro), and change formatting options of the editor window and some code settings.

The Properties Window

The Properties Window can be added to the layout (or accessed when needed) by clicking the hand envelope icon in the standard toolbar or pressing F4. It shows properties of the selected item in the project explorer window. In the example below, it shows properties of Sheet1. It is quite useful when working with Userforms and we will see that later in the training (lesson 9).

Save Macros

Excel macro-enabled files are saved with the .xlsm extension instead of .xlsx. If not done so, Excel shows a warning alert indicating that you can lose the entire VBA project/macro code if not saved as macro-enabled.

Next >>>