Finacial-Edu: Your Financial Knowledge Source
Accounting & Finance
Asset Management
Investment Tools
Project Management
- more
Guides & Courses
Excel & VBA
Trading & Investing
- more
Investing & Trading
Futures & Options
IRA + 401k
Quotes & Data
Real Estate & REITS
Software & Tools
- more
Investing & Trading Software
- more
Tools & Models
Forms & Templates
Graphing & Charting Tools
Risk & Portfolio Models
Trading Models
- more
  Print this page      

Visual Basic for Applications

Author: Wikipedia

Visual Basic for Applications (VBA) is an implementation of Microsoft's Visual Basic, an event driven programming language and associated development environment which is built into most Microsoft Office applications (including Apple Mac OS versions), some other Microsoft applications such as Microsoft MapPoint and Microsoft Visio - a former independent application which was acquired by Microsoft; as well as being at least partially implemented in some other applications such as AutoCAD, WordPerfect and ESRI ArcGIS. It supersedes and expands on the capabilities of earlier application-specific macro programming languages such as Word's WordBasic, and can be used to control almost all aspects of the host application, including manipulating user interface features such as menus and toolbars and working with custom user forms or dialog boxes. VBA can also be used to create import and export filters for various file formats, such as ODF.

VBA itself is an interpreted language. As its name suggests, VBA is closely related to Visual Basic, but can normally only run code from within a host application rather than as a standalone application. It can however be used to control one application from another using OLE Automation (for example, automatically creating a Word report from Excel data).

VBA is functionally rich and extremely flexible but it does have some important limitations, including limited support for function pointers which are used as callback functions in the Windows API. It has the ability to use (but not create) (ActiveX/COM) DLLs and later versions add support for class modules.


VBA is an interpreted language, meaning its instructions are run, or interpreted, when the source code is run. Despite its resemblance to many old BASIC dialects, VBA is not compatible with any of them except Microsoft Visual Basic, where source-code of VBA Modules can be directly imported. Compatibility ends with Visual Basic Version 6; VBA is not compatible with VB.NET. VBA is proprietary to Microsoft and is not an Open standard.

For a more complete description of the strengths and weakness of the Visual Basic language see Visual Basic.


Interaction with the host application uses OLE Automation. Typically the host application provides a type library and API documentation which document how VBA programs can interact with the application. This documentation can be examined from inside the VBA development environment using the Object Browser.

VBA programs which are written to use the OLE Automation interface of one application can't be used to automate a different application even if that application hosts the Visual Basic runtime because the OLE Automation interfaces will be different. For example, a VBA program written to automate Microsoft Word can not be used with a different word processor, even if that word processor hosts VBA.

VBA programs or macros can be attached to a menu, button a keyboard shortcut or an event in the application like the opening of the document. The language also provides a user interface in the form of UserForms, which can host ActiveX controls for added funtionality.

Security Concerns

In VBA, most of the security features lie in the hands of the user, not the author. Any function of the application is accessible to the user running any document containing VBA macros and its operation level is determined by user preferences, much like those for internet browsers.

As with any programming language, there can be programs that are created with a malicious intent. Therefore it is recommended to safeguard your system with the following steps:

1) Set your office software to use medium or high macro security.
2) Only execute documents with macros if the author is known.
3) Utilize real-time virus protection software.


A common use of VBA is to add functionality that may be missing from the standard user interface. This macro provides a shortcut for entering the current date in Word:

Sub EnterCurrentDate()
' EnterCurrentDate Macro
' Macro recorded 15/03/2005 by UserName
    Selection.InsertDateTime DateTimeFormat:="d-MMM-yy", InsertAsField:=False, _
         DateLanguage:=wdEnglishAUS, CalendarType:=wdCalendarWestern, _
End Sub

VBA is useful for automating database tasks such as traversing a table:

Sub LoopTableExample

    Dim db As DAO.Database
    Dim rs As DAO.Recordset

    Set db = CurrentDb
    Set rs = db.OpenRecordset("SELECT * FROM tblMain")

    Do Until rs.EOF
        MsgBox rs!FieldName

    Set rs = Nothing
    Set db = Nothing
End Sub

VBA can be used to create a user defined function (UDF) for use in a Microsoft Excel workbook:

Public Function BusinessDayPrior(dt As Date) As Date

    Select Case Weekday(dt, vbMonday)
        Case 1
            BusinessDayPrior = dt - 3      'Monday becomes Friday
        Case 7
            BusinessDayPrior = dt - 2      'Sunday becomes Friday
        Case Else
            BusinessDayPrior = dt - 1      'All other days become previous day
    End Select
End Function


Microsoft plans to eventually replace VBA with Visual Studio Tools for Applications (VSTA), an application customization toolkit based on the .NET Framework. From Dr Ex's article:

VSTA now replaces VSA [Visual Studio for Applications] as the technology ISVs will use to provide customization capabilities in their applications. [...] VSA was a thin-client, server-driven alternative to VBA built on Visual Studio. The server approach was viewed by ISVs as less useful than a rich-client model, so based on their feedback, Microsoft began development of VSTA. There are ISVs successfully using VSA today, but we found that its applicability was limited. Some of the technology developed for VSA is incorporated within VSTA. VSTA application customization takes better advantage of rich client functionality than VSA, offering a highly optimized environment for application customization on both the client and the server.
However, these technologies are still under construction, and it appears that at least Office 2007 will continue to utilize the legacy VBA engine.

Author URL:

SHARE THIS ARTICLE: Digg this Netscape reddit Fark Slashdot
Excel Real Estate Model
Home | Guides + Courses | Tools + Models | Articles Library | Site map
Copyright 2000-2013 All rights reserved.