Visual Basic for Applications: Difference between revisions
Content deleted Content added
m r2.7.2+) (Robot: Modifying ru:Visual Basic for Applications |
m Disambiguate Standalone software to standalone program; -some WP:REPEATLINK; -triviality; -erroneous lack of pointers (see talk page); -items in "See also" already covered; -Section "Object models" &al. (WP is not a how-to). |
||
| Line 6: | Line 6: | ||
| designer = |
| designer = |
||
| developer = [[Microsoft]] |
| developer = [[Microsoft]] |
||
| latest_release_version = 7.0 (Office 2010) |
| latest_release_version = 7.0 ([[Microsoft Office 2010|Office 2010]]) |
||
| latest_release_date = {{Start date and age|2010}}<!-- if known, please add |mm|dd --> |
| latest_release_date = {{Start date and age|2010}}<!-- if known, please add |mm|dd --> |
||
| latest_test_version = |
| latest_test_version = |
||
| Line 15: | Line 15: | ||
| influenced_by = [[QuickBASIC]], [[Visual Basic]] |
| influenced_by = [[QuickBASIC]], [[Visual Basic]] |
||
| influenced = |
| influenced = |
||
| operating_system = [[Microsoft Windows]], |
| operating_system = [[Microsoft Windows]], Mac [[OS X]] |
||
| license = [[proprietary software|Proprietary]] [[ |
| license = [[proprietary software|Proprietary]] [[End-user license agreement|EULA]] |
||
| wikibooks = |
| wikibooks = |
||
}} |
}} |
||
'''Visual Basic for Applications''' ('''VBA''') is an implementation of [[Microsoft]]'s [[event-driven programming]] language [[Visual Basic]] 6 and its associated [[integrated development environment]] (IDE), which are built into most [[Microsoft Office]] applications. VBA enables building user defined functions, automating processes and accessing [[Windows API]] and other low-level functionality through [[dynamic-link library|dynamic-link libraries]] (DLLs). It is also built into Office for Mac applications (apart from version 2008), other Microsoft applications such as [[Microsoft MapPoint]] and [[Microsoft Visio]]; as well as being at least partially implemented in some other applications such as [[AutoCAD]], [[WordPerfect]] and [[ArcGIS]]. It supersedes and expands on the abilities of earlier application-specific [[macro (computer science)|macro]] programming languages such as [[Microsoft Word|Word's]] WordBasic. It can be used to control many 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 [[OpenDocument]] (ODF). |
'''Visual Basic for Applications''' ('''VBA''') is an implementation of [[Microsoft]]'s [[event-driven programming]] language [[Visual Basic]] 6 and its associated [[integrated development environment]] (IDE), which are built into most [[Microsoft Office]] applications. VBA enables building user defined functions, automating processes and accessing [[Windows API]] and other low-level functionality through [[dynamic-link library|dynamic-link libraries]] (DLLs). It is also built into Office for Mac applications (apart from version 2008), other Microsoft applications such as [[Microsoft MapPoint]] and [[Microsoft Visio]]; as well as being at least partially implemented in some other applications such as [[AutoCAD]], [[WordPerfect]] and [[ArcGIS]]. It supersedes and expands on the abilities of earlier application-specific [[macro (computer science)|macro]] programming languages such as [[Microsoft Word|Word's]] WordBasic. It can be used to control many 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 [[OpenDocument]] (ODF). |
||
As its name suggests, VBA is closely related to |
As its name suggests, VBA is closely related to Visual Basic and uses the Visual Basic Runtime Library, but can normally only run code within a host application rather than as a [[standalone program]]. It can, however, be used to control one application from another via [[OLE Automation]]. For example, it is used automatically to create a [[Microsoft Word|Word]] report from [[Microsoft Excel|Excel]] data, in turn automatically collected by Excel from polled observation sensors. |
||
VBA |
VBA has the ability to use (but not create) ([[Component Object Model|ActiveX/COM]]) DLLs, and later versions add support for class modules. |
||
== Language == |
== Language == |
||
Code written in VBA is [[compiler|compiled]]<ref name="both">[http://support.microsoft.com/kb/109382 ACC: Visual/Access Basic Is Both a Compiler and an Interpreter]</ref> to a proprietary [[intermediate language]] called ''[[Microsoft P-Code|P-code]]'' (packed code), which the hosting applications ([[Microsoft Access|Access]], [[Microsoft Excel|Excel]], [[Microsoft Word|Word]] and [[Microsoft PowerPoint|PowerPoint]]) store as a separate [[stream (computing)|stream]] in [[COM Structured Storage]] files (e.g., <code>.doc</code> or <code>.xls</code>) independent of the document streams. The intermediate code is then executed<ref name="both"/> by a [[virtual machine]] (hosted by the hosting application). Despite its resemblance to many old [[BASIC]] dialects (particularly [[Microsoft BASIC]], from which it is indirectly derived), VBA is incompatible with any of them except [[Visual Basic]], where source-code of VBA modules and classes can be directly imported, and which shares the same library and virtual machine. Compatibility ends with Visual Basic version 6; VBA is incompatible with [[Visual Basic .NET]] (VB.NET). VBA is proprietary to Microsoft and, apart from the COM interface, is not an [[open standard]]. |
Code written in VBA is [[compiler|compiled]]<ref name="both">[http://support.microsoft.com/kb/109382 ACC: Visual/Access Basic Is Both a Compiler and an Interpreter]</ref> to a proprietary [[intermediate language]] called ''[[Microsoft P-Code|P-code]]'' (packed code), which the hosting applications ([[Microsoft Access|Access]], [[Microsoft Excel|Excel]], [[Microsoft Word|Word]] and [[Microsoft PowerPoint|PowerPoint]]) store as a separate [[stream (computing)|stream]] in [[COM Structured Storage]] files (e.g., <code>.doc</code> or <code>.xls</code>) independent of the document streams. The intermediate code is then executed<ref name="both"/> by a [[virtual machine]] (hosted by the hosting application). Despite its resemblance to many old [[BASIC]] dialects (particularly [[Microsoft BASIC]], from which it is indirectly derived), VBA is incompatible with any of them except [[Visual Basic]], where source-code of VBA modules and classes can be directly imported, and which shares the same library and virtual machine. Compatibility ends with Visual Basic version 6; VBA is incompatible with [[Visual Basic .NET]] (VB.NET). VBA is proprietary to Microsoft and, apart from the COM interface, is not an [[open standard]]. |
||
===Object models=== |
|||
To use VBA with an application such as [[Microsoft Access|Access]], [[Microsoft Word|Word]] or [[Microsoft Excel|Excel]], terminology and language constructions are needed to interact with the application.<ref name=Roman> |
|||
{{cite book |
|||
|author=Steven Roman |
|||
|title=Writing Excel Macros with VBA |
|||
|year= 2002 |
|||
|publisher=O'Reilly |
|||
|location=Sebastopol CA |
|||
|isbn=0596003595 |
|||
|pages=Chapter 15 |
|||
|url=http://books.google.com/books?id=m4_8FNfla0kC&pg=PA183&dq=%22Excel+Object+model%22#PPA183,M1 |
|||
|nopp=true}} |
|||
</ref><ref name=Roman2> |
|||
{{cite book |
|||
|author=Steven Roman |
|||
|title=Writing Word Macros |
|||
|year= 1999 |
|||
|publisher=O'Reilly |
|||
|location=Sebastopol CA |
|||
|isbn=1565927257 |
|||
|pages=Chapter 10 |
|||
|url=http://books.google.com/books?id=C7oZZMt9sqkC&pg=RA1-PA128&dq=%22Word+Object+model%22 |
|||
|nopp=true}} |
|||
</ref> This portion of VBA is called the ''Object Model'' for the application. A map of the object model is online for [http://msdn.microsoft.com/en-us/library/bb149081%28v=office.12%29.aspx Excel] and for [http://msdn.microsoft.com/en-us/library/bb244515%28v=office.12%29.aspx Word]. A listing of the object model is found by opening the Macro/VBA editor in the target application and then using "View" to open the "Object Browser" (F2). |
|||
Much of the difficulty in using VBA is related to learning the object model, which uses names invented by the originators of the model that may be less than transparent to a new user. One way to learn the terms and syntax of the object model is to use the ''macro recorder'' to record the steps taken to achieve a desired result using the mouse and menus of the application.<ref name=MacDonald> |
|||
{{cite book |
|||
|author=Matthew MacDonald |
|||
|title=Excel: The Missing Manual |
|||
|year= 2005 |
|||
|publisher=O'Reilly |
|||
|location=Sebastopol CA |
|||
|isbn=0596006640 |
|||
|pages=655 |
|||
|url=http://books.google.com/books?id=1TwjdmRI59AC&pg=PA655&dq=Excel+%22macro+recorder%22}} |
|||
</ref> Once this is done, the VBA code constructed by the recorder can be viewed in the VBA editor, and often greatly streamlined or generalized with only a modicum of understanding of VBA itself. The macro recorder does not always record everything (particularly for graphs), and some applications employing VBA do not provide a recorder at all. Use of debugging tools to discover VBA constructs for some cases where the macro recorder does not work are described by Jelen and Syrstad,<ref name=Jelen> |
|||
{{cite book |
|||
|author=Jelen, B., & Syrstad, T. |
|||
|title=VBA and macros for Microsoft Office Excel 2007 (business solutions) |
|||
|year= 2008 |
|||
|edition= Second edition |
|||
|publisher=Que |
|||
|location=Indianapolis, Ind |
|||
|isbn=0789736829 |
|||
|pages=Chapter 2; pp. 42–52 |
|||
|url=http://www.amazon.com/gp/reader/0789736829/ref=sib_dp_pt/104-3686210-0230303#reader-link |
|||
|nopp=true}} |
|||
</ref> but some steps may remain obscure. |
|||
A more complete description of the Visual Basic language is found in [[Visual Basic]]. |
|||
== Automation == |
== Automation == |
||
Interaction with the host application uses [[OLE Automation]]. Typically, the host application provides a [[data type|type]] library and [[application programming interface]] (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. |
|||
{{Main|OLE Automation}} |
|||
Interaction with the host application uses OLE Automation. Typically, the host application provides a [[data type|type]] library and [[application programming interface]] (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 cannot 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. |
VBA programs which are written to use the OLE Automation interface of one application cannot 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. |
||
Conversely, multiple applications can be automated from the one host by creating Application objects within the VBA code. |
Conversely, multiple applications can be automated 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 the 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, users automatically have access to the Access library. |
For example: In Microsoft Access, users automatically have access to the Access library. References to the Excel, Word and [[Microsoft Outlook]] libraries can also be created. This will allow creating 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 the original query through Outlook. |
||
VBA programs can be attached to a menu button, a |
VBA programs can be attached to a menu button, a [[macro (computer science)|macro]], a [[keyboard shortcut]], or an OLE/COM event, such as the opening of a document in the application. The language also provides a user interface in the form of UserForms, which can host [[ActiveX]] controls for added functionality. |
||
== Security concerns == |
== Security concerns == |
||
Like any common programming language, VBA macros can be created with a malicious intent. Using VBA, most of the [[computer security|security]] features lie in the hands of the user, not the author. The VBA |
Like any common programming language, VBA macros can be created with a malicious intent. Using VBA, most of the [[computer security|security]] features lie in the hands of the user, not the author. The VBA host application options are accessible to the user. The user who runs any document containing VBA macros can preset the software with user preferences. [[End-user (computer science)|End-user]]s can protect themselves from attack by disabling macros from running in an application, or only grant permission for a document to run VBA code if they are sure the source of the document can be trusted. |
||
==Named variables and user-defined functions== |
|||
[[File:Named Variables in Excel.PNG|thumb|400px|left|Use of named column variables ''x'' & ''y'' in [[Microsoft Excel]]; ''y = x*x'' is calculated using the formula displayed in the formula box, which is copied down the entire ''y''-column.]] |
|||
[[File:Functions in Excel.PNG|thumb|410px|Use of a user-defined function ''sq(x)'' of named variable ''x'' in Microsoft Excel. Function supplied automatically from the code in the ''Visual Basic for Applications'' editor.]] |
|||
A common use of VBA is to add functionality that may be missing from the standard [[user interface]]. Use of VBA is made much easier by using ''named variables'' on the spreadsheet, as shown at the left. The formula for ''y=x<sup>2</sup>'' resembles [[Fortran]] or [[BASIC]], and the ''Name Manager'' shows the definitions of column variables ''y'' and ''x''. |
|||
Using VBA, the user can add their own functions and subroutines that refer to these named ranges. In the figure at the right, the function ''sq'' is created in the ''Visual Basic'' editor supplied with Excel, and ''x'' & ''y'' are named variables in the spreadsheet. |
|||
{{clear}} |
|||
===Subroutines=== |
|||
[[File:Subroutine in Excel.PNG|thumb|410px|Subroutine in Excel calculates the square of named column variable ''x'' read from the spreadsheet, and writes it into the named column variable ''y''.]] |
|||
Functions themselves cannot write into the worksheet, but simply return their evaluation. However, in Microsoft Excel, subroutines can write values or text found within the subroutine directly to the spreadsheet. The figure shows the Visual Basic code for a subroutine that reads each member of the ''x''-column (named column variable ''x''), calculates its square, and writes this value into the corresponding ''y''-column (also a named column variable). The ''y''-column contains no formula because its values are calculated in the subroutine and simply written in. |
|||
{{clear}} |
|||
== Examples == |
|||
This macro provides a shortcut for entering the current date in [[Microsoft Word|Word]]: |
|||
<source lang="vb"> |
|||
Sub EnterCurrentDate() |
|||
Selection.InsertDateTime DateTimeFormat:="dd-MM-yy", InsertAsField:=False, _ |
|||
DateLanguage:=wdEnglishAUS, CalendarType:=wdCalendarWestern, _ |
|||
InsertAsFullWidth:=False |
|||
End Sub |
|||
</source> |
|||
VBA is useful for automating database tasks such as traversing a table: |
|||
<source lang="vb"> |
|||
Sub LoopTableExample |
|||
Dim db As DAO.Database |
|||
Dim rs As DAO.Recordset |
|||
Set db = CurrentDb |
|||
Set rs = db.OpenRecordset("select columnA, columnB from tableA") |
|||
Do Until rs.EOF |
|||
MsgBox rs!columnA & " " & rs!columnB |
|||
rs.MoveNext |
|||
Loop |
|||
rs.Close |
|||
Set db = Nothing |
|||
End Sub |
|||
</source> |
|||
VBA is useful for automating repeated actions in rows of a spreadsheet. For example, using the following code example, the built-in iterative solver ''Goal Seek'' is applied automatically to each row in a column array, avoiding repeated use of manual menu entry. Below a column variable "C_M" determines the values of another column variable "Target" in some nonlinear fashion. The built-in nonlinear solver ''Goal Seek'' is called to find the value of "C_M" that brings "Target" to value one. The subroutine is inserted into the workbook using the VBA editor and command ''Insert Module''. It is called directly from the VBA editor, or by using a "hot key" or keyboard shortcut. Values on the spreadsheet automatically update as the rows are scanned. |
|||
''Subroutines'' have the power to update the contents of the workbooks; ''functions'' do not - they simply report their evaluation. |
|||
The line ''Option Explicit'' is not part of the subroutine: it sets a compiler option that forces identification of all variables that have not been specified in ''Dim'' statements, which avoids difficult to detect debugging problems caused by typos. Notation ( ' ) in the following code denotes a comment, and ( _) line continuation. The code uses 'named' variables: a form of cell reference in which cells are assigned names of user choice, rather than the standard cell designation denoting specific row and column numbers. Naming is done on the worksheet via the Excel "Name Manager", or menu ''Insert Name: Create''. |
|||
<source lang="vb"> |
|||
Option Explicit |
|||
Sub SetTarget() |
|||
' |
|||
' SetTarget Macro |
|||
' |
|||
Dim J As Integer |
|||
Dim Size As Integer |
|||
' |
|||
' On the spreadsheet, array "C_M" is a NAMED column variable |
|||
' Its members use a row index taken as J |
|||
' Built-in function COUNT determines size of array "C_M" |
|||
' |
|||
Size = Range("C_M").Cells.Count |
|||
' |
|||
' Set initial value of all members of array |
|||
' C_M to 1E-06; J = row index |
|||
' |
|||
For J = 1 To Size |
|||
Range("C_M").Cells(J) = 0.000001 |
|||
Next J |
|||
'Or in case of constant value |
|||
Range("C_M") = 0.000001 |
|||
' |
|||
' "Target" is another NAMED array on the spreadsheet of |
|||
' dimension "Size"; the same size as array "C_M" |
|||
' |
|||
' Each "Target" entry in each row depends in a |
|||
' specified way upon the value of "C_M" in that row, |
|||
' for example, by a function such as: Target = C_M*C_M |
|||
' |
|||
' GOAL SEEK is a built-in iterative solver in Excel |
|||
' |
|||
' Call GOAL SEEK to set each "Target" member to unity: for example, |
|||
' taking J = row index, in row J the cell named "C_M" is changed |
|||
' by GOAL SEEK until "Target" in row J is one |
|||
' |
|||
' Syntax (aside from "for-next" details) found with macro recorder; |
|||
' underscore "_" is line continuation |
|||
' |
|||
For J = 1 To Size |
|||
Range("Target").Cells(J).GoalSeek Goal := 1, _ |
|||
ChangingCell := Range("C:M").Cells(J) |
|||
Next J |
|||
End Sub |
|||
</source> |
|||
In the example below VBA is used to get an ''array'' from a cell range, manipulate the ''array'', and then set the values back in a different range. This works many times faster than directly setting the cell values one-by-one. |
|||
<source lang="vb"> |
|||
Sub CalculateSquares(rinput As Range, routput As Range) |
|||
'Variable specifications |
|||
Dim values() As Variant |
|||
Dim i As Integer, N As Integer |
|||
'Count the rows to compute |
|||
N = rinput.Rows.Count |
|||
'Set values array from input range. |
|||
'Expected shape of array is (1 to N, 1 to 1) |
|||
values = rinput.Value |
|||
'Iterate through rows and set values |
|||
For i = 1 To N |
|||
values(i, 1) = values(i, 1) ^ 2 |
|||
Next i |
|||
'Export values back into the spreadsheet by setting the |
|||
'value property of the output range. |
|||
routput.Value = values |
|||
End Sub |
|||
</source> |
|||
VBA can be used to create a user defined function (UDF) for use in a [[Microsoft Excel]] workbook: |
|||
<source lang="vb"> |
|||
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 |
|||
</source> |
|||
Example of how to add an external application object (the user must have the application library referenced in the application before this): |
|||
<source lang="vb"> |
|||
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 |
|||
</source> |
|||
== Future ==<!-- This section is linked from [[VSTA]] --> |
== Future ==<!-- This section is linked from [[VSTA]] --> |
||
| Line 258: | Line 48: | ||
As of July 1, 2007, Microsoft no longer offers VBA distribution licenses to new customers. Microsoft intended to add .NET-based languages to the current version of VBA ever since the release of the [[.NET Framework]],<ref>[http://blogs.thinktecture.com/cnagel/archive/2005/09/14/414225.aspx Visual Studio for Applications]</ref> of which versions 1.0 and 1.1 included a scripting runtime technology named ''Script for the .NET Framework''.<ref>[http://msdn.microsoft.com/en-us/library/ms974548.aspx Introducing Visual Studio for Applications]</ref> Also, Visual Studio .NET 2002 and 2003 SDK contained a separate scripting IDE called ''Visual Studio for Applications'' (VSA) that supported VB.NET.<ref>[http://msdn.microsoft.com/en-us/library/ms974577.aspx Script happens .NET: MSDN]</ref><ref>[http://redmondmag.com/news/article.asp?EditorialsID=126 Microsoft Takes Wraps Off VSA Development Technology]</ref><ref name="VSA">[http://www.codeproject.com/csharp/vsascripting.asp VSA scripting in .NET]</ref> One of its significant features was that the interfaces to the technology were also available via [[Active Scripting]] ([[VBScript]] and [[JScript]]), allowing even .NET-unaware applications to be scripted via .NET languages. However, VSA was deprecated in version 2.0 of the .NET Framework,<ref name="VSA" /> leaving no clear upgrade path for applications desiring Active Scripting support (although "scripts" can be created in [[C Sharp (programming language)|C#]], [[VBScript]], and other .NET languages, which can be [[compiler|compiled]] and executed at run-time via [[library (computing)|libraries]] installed as part of the standard .NET runtime). |
As of July 1, 2007, Microsoft no longer offers VBA distribution licenses to new customers. Microsoft intended to add .NET-based languages to the current version of VBA ever since the release of the [[.NET Framework]],<ref>[http://blogs.thinktecture.com/cnagel/archive/2005/09/14/414225.aspx Visual Studio for Applications]</ref> of which versions 1.0 and 1.1 included a scripting runtime technology named ''Script for the .NET Framework''.<ref>[http://msdn.microsoft.com/en-us/library/ms974548.aspx Introducing Visual Studio for Applications]</ref> Also, Visual Studio .NET 2002 and 2003 SDK contained a separate scripting IDE called ''Visual Studio for Applications'' (VSA) that supported VB.NET.<ref>[http://msdn.microsoft.com/en-us/library/ms974577.aspx Script happens .NET: MSDN]</ref><ref>[http://redmondmag.com/news/article.asp?EditorialsID=126 Microsoft Takes Wraps Off VSA Development Technology]</ref><ref name="VSA">[http://www.codeproject.com/csharp/vsascripting.asp VSA scripting in .NET]</ref> One of its significant features was that the interfaces to the technology were also available via [[Active Scripting]] ([[VBScript]] and [[JScript]]), allowing even .NET-unaware applications to be scripted via .NET languages. However, VSA was deprecated in version 2.0 of the .NET Framework,<ref name="VSA" /> leaving no clear upgrade path for applications desiring Active Scripting support (although "scripts" can be created in [[C Sharp (programming language)|C#]], [[VBScript]], and other .NET languages, which can be [[compiler|compiled]] and executed at run-time via [[library (computing)|libraries]] installed as part of the standard .NET runtime). |
||
Support for VBA in the |
Support for VBA in the Mac [[OS X]] version of Microsoft Office was dropped (for one version) with the release of [[Microsoft Office 2008 for Mac]].<ref>{{cite web|url=http://www.macworld.com/news/2006/08/07/msuniversal/index.php|date=2006-08-07|publisher=[http://www.macworld.com Macworld]|title=WWDC: Microsoft updates Universal status of Mac apps|accessdate=2007-05-25}}</ref><ref>[http://www.microsoft.com/mac/developers/default.mspx?CTT=PageView&clr=99-21-0&target=7b1c718f-a611-4612-b3cf-f96d4babbf261033&srcid=ad148fd4-1f3c-4690-8198-9a137b91f09d1033&ep=7 Office 2008 without VBA]</ref> The official reason given was that VBA relied heavily on machine code written for the PowerPC architecture, and that rewriting this code for dual PowerPC/Intel architectures would have added another 2 years to the development of the suite. However, the Office suite can be automated via [[AppleScript]] to an extent. VBA was restored in [[Microsoft Office for Mac 2011]]. Microsoft has also clearly stated that they have no plans to remove VBA from the Windows version of Office.<ref>[http://blogs.msdn.com/vsto/archive/2008/01/16/the-reports-of-vba-s-demise-have-been-greatly-exaggerated.aspx The Reports of VBA's Demise Have Been Greatly Exaggerated]</ref><ref>[http://blogs.msdn.com/excel/archive/2008/01/16/clarification-on-vba-support.aspx Clarification on VBA Support]</ref> |
||
With Office 2010 Microsoft has introduced VBA7 which now contains a true pointer data type: LongPtr. This new data type allows referencing 64-bit address space. The 64-bit install of Office 2010 does not support common controls of MSComCtl (TabStrip, Toolbar, StatusBar, ProgressBar, TreeView, ListViews, ImageList, Slider, ImageComboBox) or MSComCt2 (Animation, UpDown, MonthView, DateTimePicker, FlatScrollBar) so legacy 32-bit code ported to 64-bit VBA code that depends on these common controls will not function. The 32 |
With [[Office 2010]], Microsoft has introduced VBA7 which now contains a true pointer data type: LongPtr. This new data type allows referencing 64-bit address space. The 64-bit install of Office 2010 does not support common controls of MSComCtl (TabStrip, Toolbar, StatusBar, ProgressBar, TreeView, ListViews, ImageList, Slider, ImageComboBox) or MSComCt2 (Animation, UpDown, MonthView, DateTimePicker, FlatScrollBar) so legacy 32-bit code ported to 64-bit VBA code that depends on these common controls will not function. The 32-bit version of Office 2010 is unaffected by this issue.<ref>http://msdn.microsoft.com/en-us/library/ee691831.aspx</ref> VBA7 includes no 64-bit version of the common controls, so it leaves developers with no means to migrate VBA applications to 64-bits. Microsoft suggests contacting the software vendor for 64-bit versions of VBA controls. |
||
=== Visual Studio Tools for Applications (VSTA) === |
=== Visual Studio Tools for Applications (VSTA) === |
||
| Line 269: | Line 59: | ||
== See also == |
== See also == |
||
* [[Visual Basic]] |
|||
* [[Visual Basic .NET]] |
|||
* [[Visual Studio Tools for Office]] |
* [[Visual Studio Tools for Office]] |
||
* [[Visual Studio Tools for Applications]] |
|||
* [[Microsoft Visual Studio]] |
* [[Microsoft Visual Studio]] |
||
* [[Microsoft Excel]] |
|||
* [[Microsoft Word]] |
|||
* [[Microsoft FrontPage]] |
* [[Microsoft FrontPage]] |
||
* [[Microsoft Access]] |
|||
* [[Microsoft PowerPoint]] |
|||
== References == |
== References == |
||
<references> |
|||
{{Reflist|2}} |
{{Reflist|2}} |
||
</references> |
|||
== External links == |
== External links == |
||
Revision as of 07:48, 30 March 2012
| Paradigm | Multi-paradigm |
|---|---|
| Developer | Microsoft |
| First appeared | 1993 |
| Stable release | 7.0 (Office 2010)
/ 2010 |
| Typing discipline | Static/Dynamic Hybrid, Strong/Weak Hybrid |
| OS | Microsoft Windows, Mac OS X |
| License | Proprietary EULA |
| Influenced by | |
| QuickBASIC, Visual Basic | |
Visual Basic for Applications (VBA) is an implementation of Microsoft's event-driven programming language Visual Basic 6 and its associated integrated development environment (IDE), which are built into most Microsoft Office applications. VBA enables building user defined functions, automating processes and accessing Windows API and other low-level functionality through dynamic-link libraries (DLLs). It is also built into Office for Mac applications (apart from version 2008), other Microsoft applications such as Microsoft MapPoint and Microsoft Visio; as well as being at least partially implemented in some other applications such as AutoCAD, WordPerfect and ArcGIS. It supersedes and expands on the abilities of earlier application-specific macro programming languages such as Word's WordBasic. It can be used to control many 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 OpenDocument (ODF).
As its name suggests, VBA is closely related to Visual Basic and uses the Visual Basic Runtime Library, but can normally only run code within a host application rather than as a standalone program. It can, however, be used to control one application from another via OLE Automation. For example, it is used automatically to create a Word report from Excel data, in turn automatically collected by Excel from polled observation sensors.
VBA has the ability to use (but not create) (ActiveX/COM) DLLs, and later versions add support for class modules.
Language
Code written in VBA is compiled[1] to a proprietary intermediate language called P-code (packed code), which the hosting applications (Access, Excel, Word and PowerPoint) store as a separate stream in COM Structured Storage files (e.g., .doc or .xls) independent of the document streams. The intermediate code is then executed[1] by a virtual machine (hosted by the hosting application). Despite its resemblance to many old BASIC dialects (particularly Microsoft BASIC, from which it is indirectly derived), VBA is incompatible with any of them except Visual Basic, where source-code of VBA modules and classes can be directly imported, and which shares the same library and virtual machine. Compatibility ends with Visual Basic version 6; VBA is incompatible with Visual Basic .NET (VB.NET). VBA is proprietary to Microsoft and, apart from the COM interface, is not an open standard.
Automation
Interaction with the host application uses OLE Automation. Typically, the host application provides a type library and application programming interface (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 cannot 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.
Conversely, multiple applications can be automated 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 the 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, users automatically have access to the Access library. References to the Excel, Word and Microsoft Outlook libraries can also be created. This will allow creating 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 the original query through Outlook.
VBA programs can be attached to a menu button, a macro, a keyboard shortcut, or an OLE/COM event, such as the opening of a document in the application. The language also provides a user interface in the form of UserForms, which can host ActiveX controls for added functionality.
Security concerns
Like any common programming language, VBA macros can be created with a malicious intent. Using VBA, most of the security features lie in the hands of the user, not the author. The VBA host application options are accessible to the user. The user who runs any document containing VBA macros can preset the software with user preferences. End-users can protect themselves from attack by disabling macros from running in an application, or only grant permission for a document to run VBA code if they are sure the source of the document can be trusted.
Future
As of July 1, 2007, Microsoft no longer offers VBA distribution licenses to new customers. Microsoft intended to add .NET-based languages to the current version of VBA ever since the release of the .NET Framework,[2] of which versions 1.0 and 1.1 included a scripting runtime technology named Script for the .NET Framework.[3] Also, Visual Studio .NET 2002 and 2003 SDK contained a separate scripting IDE called Visual Studio for Applications (VSA) that supported VB.NET.[4][5][6] One of its significant features was that the interfaces to the technology were also available via Active Scripting (VBScript and JScript), allowing even .NET-unaware applications to be scripted via .NET languages. However, VSA was deprecated in version 2.0 of the .NET Framework,[6] leaving no clear upgrade path for applications desiring Active Scripting support (although "scripts" can be created in C#, VBScript, and other .NET languages, which can be compiled and executed at run-time via libraries installed as part of the standard .NET runtime).
Support for VBA in the Mac OS X version of Microsoft Office was dropped (for one version) with the release of Microsoft Office 2008 for Mac.[7][8] The official reason given was that VBA relied heavily on machine code written for the PowerPC architecture, and that rewriting this code for dual PowerPC/Intel architectures would have added another 2 years to the development of the suite. However, the Office suite can be automated via AppleScript to an extent. VBA was restored in Microsoft Office for Mac 2011. Microsoft has also clearly stated that they have no plans to remove VBA from the Windows version of Office.[9][10]
With Office 2010, Microsoft has introduced VBA7 which now contains a true pointer data type: LongPtr. This new data type allows referencing 64-bit address space. The 64-bit install of Office 2010 does not support common controls of MSComCtl (TabStrip, Toolbar, StatusBar, ProgressBar, TreeView, ListViews, ImageList, Slider, ImageComboBox) or MSComCt2 (Animation, UpDown, MonthView, DateTimePicker, FlatScrollBar) so legacy 32-bit code ported to 64-bit VBA code that depends on these common controls will not function. The 32-bit version of Office 2010 is unaffected by this issue.[11] VBA7 includes no 64-bit version of the common controls, so it leaves developers with no means to migrate VBA applications to 64-bits. Microsoft suggests contacting the software vendor for 64-bit versions of VBA controls.
Visual Studio Tools for Applications (VSTA)
With the release of Visual Studio 2005, Microsoft announced Visual Studio Tools for Applications (VSTA). Independent Software Vendors may contact Microsoft through their vstainfo email address to inquire about licensing VSTA to provide their end-users with customization facilities within their application, in a similar way to VBA. However, VSTA uses the Visual Studio 2005 or Visual Studio 2008 development environment for programming against the .NET Framework.
VSTA was included in Office 2007 particularly for adding custom program code to InfoPath forms.
See also
References
- ^ a b ACC: Visual/Access Basic Is Both a Compiler and an Interpreter
- ^ Visual Studio for Applications
- ^ Introducing Visual Studio for Applications
- ^ Script happens .NET: MSDN
- ^ Microsoft Takes Wraps Off VSA Development Technology
- ^ a b VSA scripting in .NET
- ^ "WWDC: Microsoft updates Universal status of Mac apps". Macworld. 2006-08-07. Retrieved 2007-05-25.
{{cite web}}: External link in|publisher= - ^ Office 2008 without VBA
- ^ The Reports of VBA's Demise Have Been Greatly Exaggerated
- ^ Clarification on VBA Support
- ^ http://msdn.microsoft.com/en-us/library/ee691831.aspx
External links
- Excel Macro Tutorial for Beginners
- Learn Excel Macro
- MS Access VBA Examples
- A VBA Primer for mostly Microsoft Excel
Microsoft development tools | |||||||
|---|---|---|---|---|---|---|---|
| Development environments |
| ||||||
| Languages | |||||||
| APIs and frameworks |
| ||||||
| Database |
| ||||||
| Source control | |||||||
| Testing and debugging | |||||||
| Delivery | |||||||