| Visual Basic For Applications |
Article Index for Visual Basic |
Website Links For Visual Basic |
Information AboutVisual Basic For Applications |
| CATEGORIES ABOUT VISUAL BASIC FOR APPLICATIONS | |
| basic programming language family | |
| microsoft office | |
|
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 Pointer s which are used as Callback functions in the Windows API . It has the ability to use (but not create) ( ActiveX/COM ) DLL s, and later versions add support for class modules. 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 . 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. On the reverse of this, you can automate multiple applications from the one host by creating Application objects within the VBA code. References to the different libraries must be created within the VBA client before any of the methods, objects, etc. become available to use in your application. These application objects create the OLE link to the application when they are first created. Commands to the different applications must be done explicitly through these application objects in order to work correctly. For example: In Microsoft Access, you automatically have access to the Access library. You may also create references to the Excel, Word, and Outlook libraries. This will allow you to create an application that runs a query in Access, exports the results to Excel, formats the text, then writes a Mail Merge document in Word that it automatically e-mails to each member of your original query through Outlook. It is important to note that Outlook does contain a security feature that forces a user to allow, disallow, or cancel an e-mail being sent through an automated process with a forced 5 second wait. Information on this can be found at the Microsoft website. VBA programs, or '' Macro s'', 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 Control s for added functionality. 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 Browser s. 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:
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
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 Example of how to add an external application object (You must have the application library referenced in your application before this): Public Sub Example() Dim XLApp As Excel.Application Dim WDApp As Word.Application Set XLApp = CreateObject("Excel.Application") set WDApp = createObject("Word.Application") ' ...your code here... XLApp.Quit WDApp.Quit Set XLApp = Nothing Set WDApp = Nothing End Sub 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 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 Office 2007 continues to use the legacy VBA engine. However, support for VBA in Microsoft Office for Mac will be dropped with the release of version 12, in 20081. REFERENCES EXTERNAL LINKS |
|
|