Visual Basic for Applications

From Wikipedia, the free encyclopedia

You have new messages (last change).
VBA logo is the four colored boxes

Visual Basic for Applications (VBA) is an implementation of Microsoft's Visual Basic, an event driven programming language and associated integrated development environment (IDE) which is built into most Microsoft Office applications. It is also built in to Office applications for Apple Mac OS, 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, it is used to automatically create 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 in Word 2003
VBA in Word 2003

Contents

[edit] Language

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 weaknesses of the Visual Basic language, see Visual Basic.

[edit] Automation

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 its 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 cannot be used with a different word processor, even if that word processor hosts VBA.

For a more complete description of how VBA programs interact with their host application see , see OLE Automation.

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

[edit] 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 a system which will run VBA applications with the following steps:

  • Set the MS Office software to use medium or high macro security.
  • Only execute documents with macros if the author is known.
  • Use real-time virus protection software.

[edit] Examples

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:="dd-MM-yy", InsertAsField:=False, _
         DateLanguage:=wdEnglishAUS, CalendarType:=wdCalendarWestern, _
        InsertAsFullWidth:=False
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
        rs.MoveNext
    Loop

    rs.Close
    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

[edit] Future

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 use the legacy VBA engine. Support for VBA in Microsoft Office for Mac will be dropped with the release of version 12, in 2008.

[edit] External links

Retrieved from "http://en.wikipedia.org../../../v/i/s/Visual_Basic_for_Applications_2b65.html"