What is Visual Basic for Applications (VBA)
and How Can It Help You?
- Visual Basic for Applications or VBA is a computer programming
language which is used to control Microsoft Excel's functionality or any other program for
that matter that is VBA compatible.
- VBA programs are also referred to as Excel Macros, VBA Macros, Macros...
- Microsoft Excel as well as all major Microsoft Office products like Word,
PowerPoint, Access, Outlook come standard with VBA. You do not have to purchase it.
- Do not confuse VBA with VB.Net. VB.Net is VBA's "big brother"
and while it shares many of VBA's good attributes, it is a lot more difficult to use and requires a
lot of time and knowledge to master.
- VBA on the other hand is very easy to operate and learn and
can be used as a jumping off point to get into VB.NET once you have used it for a bit.
- VBA is used for tasks that are
impossible to do with formulas or Excel's built in features
- Using Excel VBA automation, you
- Data analysis tools that scan
resizing data on multiple worksheets, in multiple
workbooks, in multiple folders, perform math tasks on
the data and create reports.
- Advanced visual interfaces
created from automating chart construction, drawing
shapes, pictures and worksheet cells.
- Data fusion tools that fuse
data from multiple sources including text files,
databases and the web making it act as one.
- Modeling programs that
combine advanced numerical techniques, advanced custom
worksheet functions, sophisticated lookups with the
ability to upload and download data.
- Report generation tools that
reorganize and analyze data, generate report sheets in
separate workbooks and output results to PowerPoint and
- VBA controls Microsoft Excel by means of writing and running a procedure which is also referred to as a macro.
- 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.
- Advanced formula construction beyond the basics is not required since VBA basically eliminates the need to create those 1000 character mega formulas.
Where are Excel Macros Stored and Run?
- VBA code is typed in the VBA Editor in what are called modules (pictured below)
- 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
For example, in the computer code below, a Sub procedure in a module reads the
Data worksheet and if it finds 2016 in column B on that sheet, it adds the qty from column C to a running total and then outputs the result to cell F1. You write these procedures in the Visual Basic Editor in what are called modules
which can be viewed by pressing Alt+F11 in Microsoft Excel.
Need Help? Please call us at 1.866.924.6244
Copyright © 2002-2017
EMAGENIT All Rights Reserved