What is VBA


What is Visual Basic for Applications?

  • Visual Basic for Applications or VBA is a computer programming language which is used to control Microsoft Excel's functionality
  • VBA can also be used to control Microsoft Word, PowerPoint or other programs
  • All major Microsoft Office products come standard with VBA
  • VBA controls Microsoft Excel by means of macros which are also called procedures
  • In order to command Microsoft Excel effectively using Visual Basic for Applications (VBA), Microsoft Excel’s operational capabilities must be well understood along with its program elements

Where are Excel Macros Stored and Run?

  • VBA code is typed in the VBA Editor in what are called modules (pictured to the right).

  • A module resembles a Word document in both organization and typing

  • You type commands in these modules then run them to control Microsoft Excel. Can you read the commands in the picture and figure out what Excel element is being commanded?

  • The VBA modules themselves are organized in what is called a VBA project

  • A VBA project is defined as a collection of modules and other programming elements

  • When a new workbook file is created in Microsoft Excel, a new VBA project is automatically created and associated with that workbook

  • A workbook can contain only 1 VBA project

  • You add VBA modules, code and other elements to a VBA project when you need them

  • You run your macros from the workbook they were created in. You need workbook open in Excel to run its code

How is VBA used in Microsoft Excel

VBA allows a user to:
  • Automate report generation in Microsoft Excel
  • Automate chart creation in Microsoft Excel
  • Perform data processing in Microsoft Excel
  • Automate communication between Microsoft Excel and databases
  • Build professional grade wizards (i.e. floating dialog boxes) rapidly
  • Integrate legacy code written in C, Perl, Apple Script, FORTRAN and VB with Excel
  • Perform analysis tasks in Microsoft Excel (modeling, optimization...)