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...)

The Excel program pictured below is a simple example built in our Advanced Excel VBA for Business workshop and demonstrates how to build a multi- chart / multi-worksheet data processing program in Excel. It takes two master worksheets and develops n worksheets from those sheets evaluating statistical information and then charting the results automatically. A very common and time consuming task facing the business world today. The program pictured reduced an all day task to 20 sec.

The Excel Configuration tool pictured below evaluates a trade study of Total Gross Weight of an aircraft versus payload. It automatically charts its results and is capable of rapidly evaluating different aircraft configurations. Rapid design and analysis tools for engineering can be easily constructed in Excel. This program can be seen in our Advanced Excel VBA for Engineers and Scientist workshop.