Visual Basic for Applications (VBA) is an implementation of Microsoft
Microsoft
Microsoft Corporation is an American public multinational corporation headquartered in Redmond, Washington, USA that develops, manufactures, licenses, and supports a wide range of products and services predominantly related to computing through its various product divisions...
In computer programming, event-driven programming or event-based programming is a programming paradigm in which the flow of the program is determined by events—i.e., sensor outputs or user actions or messages from other programs or threads.Event-driven programming can also be defined as an...
Visual Basic is the third-generation event-driven programming language and integrated development environment from Microsoft for its COM programming model...
An integrated development environment is a software application that provides comprehensive facilities to computer programmers for software development...
Microsoft Office is a non-free commercial office suite of inter-related desktop applications, servers and services for the Microsoft Windows and Mac OS X operating systems, introduced by Microsoft in August 1, 1989. Initially a marketing term for a bundled set of applications, the first version of...
applications. VBA enables building user defined functions, automating processes and accessing Windows API
Windows API
The Windows API, informally WinAPI, is Microsoft's core set of application programming interfaces available in the Microsoft Windows operating systems. It was formerly called the Win32 API; however, the name "Windows API" more accurately reflects its roots in 16-bit Windows and its support on...
Dynamic-link library , or DLL, is Microsoft's implementation of the shared library concept in the Microsoft Windows and OS/2 operating systems...
(DLLs). It was also built into Office applications (apart from version 2008 for Apple's Mac OS X
Mac OS X
Mac OS X is a series of Unix-based operating systems and graphical user interfaces developed, marketed, and sold by Apple Inc. Since 2002, has been included with all new Macintosh computer systems...
Microsoft MapPoint is both a technology and a specific software program created by Microsoft that allows users to view, edit and integrate maps. The software and technology are designed to facilitate the geographical visualization and analysis of either included data or custom data...
Microsoft Visio , formerly known as Microsoft Office Visio, is a commercial diagramming program for Microsoft Windows that uses vector graphics to create diagrams.- Features :...
; as well as being at least partially implemented in some other applications such as AutoCAD
AutoCAD
AutoCAD is a software application for computer-aided design and drafting in both 2D and 3D. It is developed and sold by Autodesk, Inc. First released in December 1982, AutoCAD was one of the first CAD programs to run on personal computers, notably the IBM PC...
WordPerfect is a word processing application, now owned by Corel.Bruce Bastian, a Brigham Young University graduate student, and BYU computer science professor Dr. Alan Ashton joined forces to design a word processing system for the city of Orem's Data General Corp. minicomputer system in 1979...
ArcGIS is a suite consisting of a group of geographic information system software products produced by Esri.ArcGIS is a system for working with maps and geographic information...
. It supersedes and expands on the abilities of earlier application-specific macro programming languages such as Word's
Microsoft Word
Microsoft Word is a word processor designed by Microsoft. It was first released in 1983 under the name Multi-Tool Word for Xenix systems. Subsequent versions were later written for several other platforms including IBM PCs running DOS , the Apple Macintosh , the AT&T Unix PC , Atari ST , SCO UNIX,...
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
OpenDocument
The Open Document Format for Office Applications is an XML-based file format for representing electronic documents such as spreadsheets, charts, presentations and word processing documents....
(ODF).
As its name suggests, VBA is closely related to Visual Basic
Visual Basic
Visual Basic is the third-generation event-driven programming language and integrated development environment from Microsoft for its COM programming model...
and uses the Visual Basic Runtime, but can normally only run code within a host application rather than as a standalone application. It can, however, be used to control one application from another via OLE Automation
OLE Automation
In Microsoft Windows applications programming, OLE Automation , is an inter-process communication mechanism based on Component Object Model that was intended for use by scripting languages – originally Visual Basic – but now are used by languages run on Windows...
. For example, it is used automatically to create a Word
Microsoft Word
Microsoft Word is a word processor designed by Microsoft. It was first released in 1983 under the name Multi-Tool Word for Xenix systems. Subsequent versions were later written for several other platforms including IBM PCs running DOS , the Apple Macintosh , the AT&T Unix PC , Atari ST , SCO UNIX,...
Microsoft Excel is a proprietary commercial spreadsheet application written and distributed by Microsoft for Microsoft Windows and Mac OS X. It features calculation, graphing tools, pivot tables, and a macro programming language called Visual Basic for Applications...
data, in turn automatically collected by Excel from polled observation sensors. The VBA IDE is reached from within an Office document by pressing the key sequence Alt+F11.
VBA is functionally rich and flexible but it does have some important limitations, such as restricted support for function pointer
Function pointer
A function pointer is a type of pointer in C, C++, D, and other C-like programming languages, and Fortran 2003. When dereferenced, a function pointer can be used to invoke a function and pass it arguments just like a normal function...
In computer programming, a callback is a reference to executable code, or a piece of executable code, that is passed as an argument to other code. This allows a lower-level software layer to call a subroutine defined in a higher-level layer....
functions in the Windows API. It has the ability to use (but not create) (ActiveX/COM
Component Object Model
Component Object Model is a binary-interface standard for software componentry introduced by Microsoft in 1993. It is used to enable interprocess communication and dynamic object creation in a large range of programming languages...
) DLLs, and later versions add support for class modules.
In computer science, an intermediate language is the language of an abstract machine designed to aid in the analysis of computer programs. The term comes from their use in compilers, where a compiler first translates the source code of a program into a form more suitable for code-improving...
Microsoft's P-Code, short for packed code, is an intermediate language that provides an alternate binary format to native code for any compiled binary . Its primary goal is to produce smaller files. P-Code binaries require an additional runtime library to execute...
(packed code), which the hosting applications (Access
Microsoft Access
Microsoft Office Access, previously known as Microsoft Access, is a relational database management system from Microsoft that combines the relational Microsoft Jet Database Engine with a graphical user interface and software-development tools. It is a member of the Microsoft Office suite of...
Microsoft Excel is a proprietary commercial spreadsheet application written and distributed by Microsoft for Microsoft Windows and Mac OS X. It features calculation, graphing tools, pivot tables, and a macro programming language called Visual Basic for Applications...
Microsoft Word is a word processor designed by Microsoft. It was first released in 1983 under the name Multi-Tool Word for Xenix systems. Subsequent versions were later written for several other platforms including IBM PCs running DOS , the Apple Macintosh , the AT&T Unix PC , Atari ST , SCO UNIX,...
Microsoft PowerPoint, usually just called PowerPoint, is a non-free commercial presentation program developed by Microsoft. It is part of the Microsoft Office suite, and runs on Microsoft Windows and Apple's Mac OS X operating system...
) 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 by a virtual machine
Virtual machine
A virtual machine is a "completely isolated guest operating system installation within a normal host operating system". Modern virtual machines are implemented with either software emulation or hardware virtualization or both together.-VM Definitions:A virtual machine is a software...
(hosted by the hosting application). Despite its resemblance to many old BASIC
BASIC
BASIC is a family of general-purpose, high-level programming languages whose design philosophy emphasizes ease of use - the name is an acronym from Beginner's All-purpose Symbolic Instruction Code....
Microsoft BASIC was the foundation product of the Microsoft company. It first appeared in 1975 as Altair BASIC, which was the first BASIC, and the first high level programming language available for the MITS Altair 8800 hobbyist microcomputer....
, from which it is indirectly derived), VBA is incompatible with any of them except Visual Basic
Visual Basic
Visual Basic is the third-generation event-driven programming language and integrated development environment from Microsoft for its COM programming model...
, 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
Visual Basic .NET
Visual Basic .NET , is an object-oriented computer programming language that can be viewed as an evolution of the classic Visual Basic , which is implemented on the .NET Framework...
(VB.NET). VBA is proprietary to Microsoft and, apart from the COM interface, is not an open standard
Open standard
An open standard is a standard that is publicly available and has various rights to use associated with it, and may also have various properties of how it was designed . There is no single definition and interpretations vary with usage....
Microsoft Office Access, previously known as Microsoft Access, is a relational database management system from Microsoft that combines the relational Microsoft Jet Database Engine with a graphical user interface and software-development tools. It is a member of the Microsoft Office suite of...
Microsoft Word is a word processor designed by Microsoft. It was first released in 1983 under the name Multi-Tool Word for Xenix systems. Subsequent versions were later written for several other platforms including IBM PCs running DOS , the Apple Macintosh , the AT&T Unix PC , Atari ST , SCO UNIX,...
Microsoft Excel is a proprietary commercial spreadsheet application written and distributed by Microsoft for Microsoft Windows and Mac OS X. It features calculation, graphing tools, pivot tables, and a macro programming language called Visual Basic for Applications...
, terminology and language constructions are needed to interact with the application. This portion of VBA is called the Object Model for the application. A map of the object model is online for Excel and for 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. 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, but some steps may remain obscure.
A more complete description of the strengths and weaknesses of the Visual Basic language is found in Visual Basic
Visual Basic
Visual Basic is the third-generation event-driven programming language and integrated development environment from Microsoft for its COM programming model...
.
VBA Coding Practices
VBA is generally used to save time by automating repetitive tasks in Microsoft Office
Microsoft Office
Microsoft Office is a non-free commercial office suite of inter-related desktop applications, servers and services for the Microsoft Windows and Mac OS X operating systems, introduced by Microsoft in August 1, 1989. Initially a marketing term for a bundled set of applications, the first version of...
Microsoft Excel is a proprietary commercial spreadsheet application written and distributed by Microsoft for Microsoft Windows and Mac OS X. It features calculation, graphing tools, pivot tables, and a macro programming language called Visual Basic for Applications...
Microsoft Office Access, previously known as Microsoft Access, is a relational database management system from Microsoft that combines the relational Microsoft Jet Database Engine with a graphical user interface and software-development tools. It is a member of the Microsoft Office suite of...
Microsoft Word is a word processor designed by Microsoft. It was first released in 1983 under the name Multi-Tool Word for Xenix systems. Subsequent versions were later written for several other platforms including IBM PCs running DOS , the Apple Macintosh , the AT&T Unix PC , Atari ST , SCO UNIX,...
Microsoft PowerPoint, usually just called PowerPoint, is a non-free commercial presentation program developed by Microsoft. It is part of the Microsoft Office suite, and runs on Microsoft Windows and Apple's Mac OS X operating system...
. But an improper style of coding, poor logic and lack of knowledge could introduce memory issues (sometimes even memory leaks
Memory leak
A memory leak, in computer science , occurs when a computer program consumes memory but is unable to release it back to the operating system. In object-oriented programming, a memory leak happens when an object is stored in memory but cannot be accessed by the running code...
) into the system, degrading overall performance. Common VBA practices include :
Analyze the logic : Before optimizing the syntax, pay more attention in optimizing the logic. Without good logic, a well written VBA macro program has no value. So streamline your program logic and get the best performance of macros. Applies to Excel, Access, Word and Powerpoint.
Turn off Screen Updating : The ScreenUpdating property controls most display changes on the monitor while a procedure is running. When screen updating is turned off, toolbars remain visible and Word still allows the procedure to display or retrieve information using status bar prompts, input boxes, dialog boxes and message boxes. You can increase the speed of some procedures by keeping screen updating turned off. You must set the ScreenUpdating property to True when the procedure finishes or when it stops after an error. To turn off screen updating, use the following code. Applies to Excel, Access, Word and Powerpoint. Application.ScreenUpdating = False
Set Calculations in Manual Mode : Whenever content(s) of a cell or range of cells are changed, the formulas dependent on them and Volatile functions are recalculated. You can increase the speed of some procedures by keeping automatic calculations turned off. You must set the Calculation property to automatic when the procedure finishes or when it stops after an error. To turn off the automatic calculation, use the following code. Applies to Excel only. Application.Calculation = xlCalculationManual To turn on the automatic calculation, use the following code. Application.Calculation = xlCalculationAutomatic
Disable Page breaks : When we run a Microsoft VBA macro in a later version of Microsoft Excel, the macro may take longer to complete than it does in earlier versions of Excel. For example, a macro that required several seconds to complete in an earlier version of Excel may require several minutes to complete in a later version of Excel. To hide page breaks, use the following code. Applies to Excel, Access, Word and Powerpoint. ActiveSheet.DisplayPageBreaks = False
Release memory from object variables : Whenever an object is created in VBA, two things are created 1. Object 2. Pointer (called an object reference). Some people might say, "VBA does not use pointers", but it is not true. "VBA does not let you manipulate pointers" is more precise. Behind the scenes, VBA still makes extensive use of pointers. To destroy an object in VBA, you set it to Nothing. But wait a minute. If all we ever use are object pointers, how can we set the object itself to Nothing? The answer is: We can't. When we set a reference to Nothing, something called the garbage collector kicks in. This little piece of software tries to determine whether or not an object should be destroyed. There are many ways to implement a garbage collector, but VBA uses what is called the reference count method. When VB interprets the last line(where we generally sets our objects to Nothing), it will remove the existing reference. At this point, if the object has no more references, the garbage collector will destroy the object and deallocate all its resources. If any other references point to the same object, the object will not be destroyed. Applies to Excel, Access, Word and Powerpoint.
Automation
Interaction with the host application uses OLE Automation. Typically, the host application provides a type
Data type
In computer programming, a data type is a classification identifying one of various types of data, such as floating-point, integer, or Boolean, that determines the possible values for that type; the operations that can be done on values of that type; the meaning of the data; and the way values of...
An application programming interface is a source code based specification intended to be used as an interface by software components to communicate with each other...
(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
Microsoft Excel
Microsoft Excel is a proprietary commercial spreadsheet application written and distributed by Microsoft for Microsoft Windows and Mac OS X. It features calculation, graphing tools, pivot tables, and a macro programming language called Visual Basic for Applications...
Microsoft Word is a word processor designed by Microsoft. It was first released in 1983 under the name Multi-Tool Word for Xenix systems. Subsequent versions were later written for several other platforms including IBM PCs running DOS , the Apple Macintosh , the AT&T Unix PC , Atari ST , SCO UNIX,...
Microsoft Outlook is a personal information manager from Microsoft, available both as a separate application as well as a part of the Microsoft Office suite...
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
Mail merge
Mail merge is a software function which allows to create multiple documents from a single template form and a structured data source.-History:This technique of merging data to create gave rise to the term mail merge....
document in Word that it automatically e-mails to each member of the original query through Outlook. (In this example, Microsoft Outlook contains 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 can be attached to a menu button, a macro, a keyboard shortcut
Keyboard shortcut
In computing, a keyboard shortcut is a finite set of one or more keys that invoke a software or operating system operation when triggered by the user. A meaning of term "keyboard shortcut" can vary depending on software manufacturer...
, 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
Computer security
Computer security is a branch of computer technology known as information security as applied to computers and networks. The objective of computer security includes protection of information and property from theft, corruption, or natural disaster, while allowing the information and property to...
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, much like those for web browser
Web browser
A web browser is a software application for retrieving, presenting, and traversing information resources on the World Wide Web. An information resource is identified by a Uniform Resource Identifier and may be a web page, image, video, or other piece of content...
s. End-users can protect themselves from attack by disabling macros from running in an application if they do not intend to use documents containing them, or only grant permission for a document to run VBA code if they are sure the source of the document can be trusted. However, if the author is known VBA code is no more dangerous than any other.
Named variables and user-defined functions
A common use of VBA is to add functionality that may be missing from the standard user interface
User interface
The user interface, in the industrial design field of human–machine interaction, is the space where interaction between humans and machines occurs. The goal of interaction between a human and a machine at the user interface is effective operation and control of the machine, and feedback from the...
. Use of VBA is made much easier by using named variables on the spreadsheet, as shown at the left. The formula for y=x2 resembles Fortran
Fortran
Fortran is a general-purpose, procedural, imperative programming language that is especially suited to numeric computation and scientific computing...
BASIC is a family of general-purpose, high-level programming languages whose design philosophy emphasizes ease of use - the name is an acronym from Beginner's All-purpose Symbolic Instruction Code....
, 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.
Subroutines
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.
Examples
This macro provides a shortcut for entering the current date in Word
Microsoft Word
Microsoft Word is a word processor designed by Microsoft. It was first released in 1983 under the name Multi-Tool Word for Xenix systems. Subsequent versions were later written for several other platforms including IBM PCs running DOS , the Apple Macintosh , the AT&T Unix PC , Atari ST , SCO UNIX,...
:
Sub EnterCurrentDate
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 columnA, columnB from tableA")
Do Until rs.EOF
MsgBox rs!columnA & " " & rs!columnB
rs.MoveNext
Loop
rs.Close
Set db = Nothing
End Sub
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.
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
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.
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
VBA can be used to create a user defined function (UDF) for use in a Microsoft Excel
Microsoft Excel
Microsoft Excel is a proprietary commercial spreadsheet application written and distributed by Microsoft for Microsoft Windows and Mac OS X. It features calculation, graphing tools, pivot tables, and a macro programming language called Visual Basic for Applications...
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 (the user must have the application library referenced in the 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
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
.NET Framework
The .NET Framework is a software framework that runs primarily on Microsoft Windows. It includes a large library and supports several programming languages which allows language interoperability...
, of which versions 1.0 and 1.1 included a scripting runtime technology named Script for the .NET Framework. Also, Visual Studio .NET 2002 and 2003 SDK contained a separate scripting IDE called Visual Studio for Applications (VSA) that supported VB.NET. One of its significant features was that the interfaces to the technology were also available via Active Scripting
Active Scripting
Active Scripting is the technology used in Windows to implement component-based scripting support. It is based on COM and allows installation of additional scripting engines in the form of COM modules.-Uses and history:The Active Scripting technologies were first released in 1996, with the...
VBScript is an Active Scripting language developed by Microsoft that is modeled on Visual Basic. It is designed as a “lightweight” language with a fast interpreter for use in a wide variety of Microsoft environments...
JScript is a scripting language based on the ECMAScript standard that is used in Microsoft's Internet Explorer.JScript is implemented as a Windows Script engine. This means that it can be "plugged in" to any application that supports Windows Script, such as Internet Explorer, Active Server Pages,...
), allowing even .NET-unaware applications to be scripted via .NET languages. However, VSA was deprecated in version 2.0 of the .NET Framework, leaving no clear upgrade path for applications desiring Active Scripting support (although "scripts" can be created in C#, VBScript
VBScript
VBScript is an Active Scripting language developed by Microsoft that is modeled on Visual Basic. It is designed as a “lightweight” language with a fast interpreter for use in a wide variety of Microsoft environments...
Mac OS X is a series of Unix-based operating systems and graphical user interfaces developed, marketed, and sold by Apple Inc. Since 2002, has been included with all new Macintosh computer systems...
Microsoft Office 2008 for Mac is a version of the Microsoft Office productivity suite for Mac OS X. It supersedes Office 2004 for Mac and is the Mac OS X equivalent of Office 2007. Office 2008 was developed by Microsoft's Macintosh Business Unit and released on January 15, 2008...
. 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
AppleScript
AppleScript is a scripting language created by Apple Inc. and built into Macintosh operating systems since System 7. The term "AppleScript" may refer to the scripting system itself, or to particular scripts that are written in the AppleScript language....
Microsoft Office for Mac 2011 is the most recent version of the Microsoft Office productivity suite for Mac OS X. It is the successor to Microsoft Office 2008 for Mac and is comparable to Microsoft Office 2010 for Windows....
. Microsoft has also clearly stated that they have no plans to remove VBA from the Windows version of Office.
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. 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 is a set of tools that independent software vendors can use to build customization abilities into their applications for both automation and extensibility...
Independent software vendor is a business term for companies specializing in making or selling software, designed for mass marketing or for niche markets...
s 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.
Visual Basic is the third-generation event-driven programming language and integrated development environment from Microsoft for its COM programming model...
Visual Basic .NET , is an object-oriented computer programming language that can be viewed as an evolution of the classic Visual Basic , which is implemented on the .NET Framework...
Visual Studio Tools for Office is a set of development tools available in the form of a Visual Studio add-in and a runtime that allows Microsoft Office 2003 and later versions of Office applications to host the .NET Framework Common Language Runtime to expose their functionality via the .NET...
Visual Studio Tools for Applications is a set of tools that independent software vendors can use to build customization abilities into their applications for both automation and extensibility...
Microsoft Visual Studio is an integrated development environment from Microsoft. It is used to develop console and graphical user interface applications along with Windows Forms applications, web sites, web applications, and web services in both native code together with managed code for all...
Microsoft Excel is a proprietary commercial spreadsheet application written and distributed by Microsoft for Microsoft Windows and Mac OS X. It features calculation, graphing tools, pivot tables, and a macro programming language called Visual Basic for Applications...
Microsoft Word is a word processor designed by Microsoft. It was first released in 1983 under the name Multi-Tool Word for Xenix systems. Subsequent versions were later written for several other platforms including IBM PCs running DOS , the Apple Macintosh , the AT&T Unix PC , Atari ST , SCO UNIX,...
Microsoft FrontPage was a WYSIWYG HTML editor and web site administration tool from Microsoft for the Microsoft Windows line of operating systems. It was branded as part of the Microsoft Office suite from 1997 to 2003...
Microsoft Office Access, previously known as Microsoft Access, is a relational database management system from Microsoft that combines the relational Microsoft Jet Database Engine with a graphical user interface and software-development tools. It is a member of the Microsoft Office suite of...
Microsoft PowerPoint, usually just called PowerPoint, is a non-free commercial presentation program developed by Microsoft. It is part of the Microsoft Office suite, and runs on Microsoft Windows and Apple's Mac OS X operating system...
