Gourt Home::Gourt :: Visual Basic for Applications
Description
Visual Basic for Applications (VBA) is an implementation of Microsoft's Visual Basic which is built into all Microsoft Office applications (including Apple Mac OS versions), some other Microsoft applications such as 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.
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 (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 callback functions. It has the ability to use (but not create) (ActiveX/COM) DLL's and later versions add support for class modules.
Usage
Most software products (Autodesk AutoCAD / Microsoft Office / Adobe Illustrator) provide an 'Object Model' to the Visual Basic Environment allowing the user to create anything from small macros that perform repetitive tasks to extensive programs that add functionality to the host program.
Examples
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 db.Close 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 Case 7 BUSINESSDAYPRIOR = dt -2 Case Else BUSINESSDAYPRIOR = dt -1 End Select End Function
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 (linked below):
VSTA now replaces VSA 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 ISV's 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.
External links
- VBA Home on Microsoft
- VBA coding tools and fragment library by CODE-VB
- Utter Access Forums
- Code Site from James Igoe
- vbnumericalmethods.com, code for finance applications
BASIC programming language family | Microsoft Office
Visual Basic for Applications | Visual Basic for Applications | Visual Basic for Applications | VBA | Visual Basic for Applications | Visual Basic for Applications | Visual Basic for Applications | Visual Basic for Applications | Visual Basic for Applications | Visual Basic for Applications | Visual Basic for Applications | VBA
"Visual Basic for Applications"