Excel

VBA and Macro

Visual Basic for Applications (VBA) is an event-driven programming language developed by Microsoft to automate operations in Microsoft Office applications, such as Excel, Word, PowerPoint, Word and Outlook. VBA use to develop macros to automate tasks and manipulate objects to control Office applications.

VB vs. VBA

Visual Basic and VBA are not same, though both derived from BASIC language and created by Microsoft, they are otherwise very different. VB creates standalone executable applications without requiring a host application while VBA cannot create standalone applications, and it exists within a host application such as Word, Access, or Excel. To execute VBA code, a file must be open on its host application (Word, Excel, Access etc.).

What you can do with VBA

  • Analyze data
  • Automate recurring tasks
  • Automate repetitive tasks
  • Create invoices
  • Create forms
  • Run macros if certain conditions met
  • Make charts from data
  • Make custom functions
  • Customize the user interface
  • Access and control other Office applications, such as, control Word from the Excel
  • and many more

Excel and Word includes a tool "Macro Recorder", using this tool you can easily create VBA coding automatically by recording your actions. When we record a macro its actually writing our actions into VBA code and execute the code when we run that macro.

Creating a Macro

To understand how VBA works we’ll create a very basic Macro.

Record Macro

For recording a macro you need to open Macro Recorder. Use any of the following methods to open the Macro Recorder:

  1. Go to View > Macros > Record Macro. Click on View tab, click Macros drop down menu and click Record Macro from the menu, as shown in below image:
    View tab - Macros

  2. Or go to Developer tab and click Record Macro button as shown in following figure (By default the Developer tab is not available on the ribbon. See how to show Developer Tab on the ribbon.):
    Macro Recorder button on Developer Tab

  3. Or click the Macro Record button on the status bar at the bottom of the application:
    Macro Recorder button on Status Bar

All above methods will open Record Macro dialog box, enter todayDate in Macro name field and click ok to start recording:

Record Macro

Write =today() and press Shift + Enter (to prevent next cell selection or the next cell selection will also recorded). Now stop the macro recording.

Stop Macro Recording

To stop Macro recording you can use any of the following methods:

  1. Click View tab, click Macros drop-down and then click Stop Recording button (View tab > Macros > Stop Recording):
    Stop macro recording from the View tab

  2. Or go to Developer tab and click Stop Recording:
    Stop macro recording from the developer tab

  3. Or click the square black button that appears during recording on the status bar, down on the bottom left of the status bar:
    Stop macro recording from the status bar

Run a Macro

You have recorded a macro which enters the today’s date on current cell. Let’s run the todayDate macro on different cells to see the result. To run a Macro you can use any of the following methods:

  1. Go to View tab and click Macros drop-down then click View Macros button (View > Macros > View Macros)

  2. Or go to Developer tab and click Macros button

  3. Or press Alt + F8 shortcut keys

The Macro dialog box will appear:
Macro Dialog Box

Select Macro todayDate from the Marco dialog box and click Run button. Whenever you run the todayDate macro it’ll write current date on the selected cell. You can view the macro code in the Visual Basic Editor (VBE) and modify it if you wish.

VBE (Visual Basic Editor)

The VBE is an environment which lists all VBA procedures and macros code that you created. Seeing the macros code improve your understanding of the VBA language, especially when you start to edit existing macros or create new macros without the Macro Recorder. To open VBE use any of the following methods:

  1. Go to View > Macros > View Macros (or Alt + F8) and then edit an existing macro from Macro dialog box

  2. Or go to Developer tab and click Visual Basic button as shown in following figure:
    Developer Tab

Let's edit the todayDate macro from the Macro dialog box, this will open the macro in Visual Basic Editor (VBE) window:

Visual Basic Editor

How to show Developer tab

By default the Developer tab is not available on the ribbon. To make it available you can customize the ribbon through Excel (or Word, Access options) Options by performing the following steps:

  • Go to File > Options (Or press Alt + f + t shortcut keys) from any Microsoft Office application, such as Excel
  • Select Customize Ribbon at the left
  • Select the Main Tabs item from the Customize the Ribbon drop-down.
  • Check the Developer box and click OK

Office options