Introduction to the Developer tab

What is VBA?

Visual Basic for Applications (VBA) is a programming language that is built into the Excel spreadsheet application and other Microsoft Office applications. It extends Excel so it can perform tasks that can’t be done with standard Excel tools, and provides the capability to automate many routine tasks.

In fact, VBA is also the language that manipulates Microsoft Office applications in Access, Word, PowerPoint, and Outlook. For the purposes here, VBA is the tool you use to develop macros and manipulate objects to control Excel and to control other Office applications from Excel.

You don't need to buy anything, you only need to buy an Office suite (or a single application) to have VBA. If you have Excel on your computer, you have VBA on your computer.

What Can You Do With VBA

You can use Excel according to your needs. All people who use VBA have one thing in common, that is the need to automate certain frequently encountered tasks, which are either too time-consuming or too cumbersome to continue manual operations. This is the role of VBA.

You can do anything with VBA, but VBA allows you to do it faster and minimize the risk of human error. Many things that Excel does not allow you to operate manually, you can use VBA to do it.

The following list contains just a few things that you can do with VBA macros.

• Automate the procedures you perform frequently. For example, you may need to prepare a month-end summary. If this task is simple, you can develop a macro to complete it for you.
• Create custom add-ins for Excel. Most of the add-ins provided with Excel are created with Excel macros.
• Insert template text. If you need to enter standard text in a range of cells, you can create a macro to type for you.
• Create custom commands. For example, you can combine several Excel commands so that they can be executed with a single button or a mouse click.
• Create a simplified "front end" for users who don't know Excel well. For example, you can set up a foolproof data entry template.
• Develop new worksheet functions. Although Excel includes a variety of built-in functions, you can create custom functions to greatly simplify your formulas.
• Automation of repetitive operations. If you need to perform the same operation in 10 different workbooks, you can record a macro when you perform a task, and then let this macro repeat your operation in other workbooks.
• Create a complete macro-driven application. Excel macros can display custom dialog boxes and react to new commands added to the ribbon.

Technical Requirements

This course was written and designed to be used with Excel 2019, installed on either Windows 8, 8.1, or 10. All examples are demonstrated using Excel 2019, although most examples are also applicable to earlier versions of Excel.

Enable the Developer tab

Before you can start using Excel VBA, you must first enable it in Excel. By default, Microsoft hides VBA tools. You need to change the settings to access the Developer tab.

1. Launch Excel and select Blank workbook
   
2. Select the File tab on the Excel Ribbon
   
3. Next, select the Options item
   
4. In the Excel Options dialog, select the Customize Ribbon item on the left-hand pane
5. To the far right, under the Main Tabs heading, look for the Developer option. Select the checkbox
   
6. Click OK to return to Excel, and Excel will now display the Developer tab
   

The Group of the Developer tab

The Code group

The Code group on the Developers tab contains icons for recording and running VBA macros, as follows.

Visual Basic

Opens the Visual Basic editor. Or press Shortcut key: Alt + F11

Macros

Displays the Macro dialog box, where you can select from a list of macros to run or edit. Or press Shortcut key: Alt + F8

Record Macro

Starts the process of recording a macro.

Use Relative References

Switches between using relative or absolute records. Use relative references so that macros are recorded with actions relative to the initial selected cell. For instance, if you record a macro in cell Al which moves the cursor to A3 with this option turned on, running the resulting macro in cell J6 would move the cursor to J8. If this option was turned off when the macro was recorded, running it in cell J6 would move the cursor to A3.

Macro Security

Access the Trust Center, where you can choose to allow or disallow macros to run on this computer.

The Add-ins group

The Add-ins group provides icons for managing general add-ins and COM add-ins.

The Controls group

The Controls group of the Developer tab contains an Insert menu where you can access the various programming controls that can be placed on the worksheet. Other icons in this group enable you to use form controls. The Run Dialog button enables you to display a custom dialog box or user form that you have designed in VBA.

The XML group

The XML group of the Developer tab contains tools for importing and exporting XML documents.