Visual Basic for Applications
From Wikipedia, the free encyclopedia
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.
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 weakness 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 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.
For a more complete description of how VBA programs interact with their host application see OLE Automation
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.
[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 your system with the following steps:
- Set your office software to use medium or high macro security.
- Only execute documents with macros if the author is known.
- Utilize 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:="d-MMM-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 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
[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 utilize the legacy VBA engine.
[edit] External links
- VBA Home on Microsoft
- Utter Access Forums
- vbnumericalmethods.com, code for finance applications
- Tutorial site for VBA in Excel
- Collection of Code Snippets