Visual Basic for Applications

From Free net encyclopedia

1 Description
2 Usage
3 Literature
4 Examples
5 External links

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 and WordPerfect. 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.

[edit]

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.

[edit]

Literature

Andrea Janka, VBA mit Word, Galileo Press, Bonn 2004, 965 Seiten, ISBN 3898424693 147 pages extract (2,55 MB) as PDF from the book VBA mit Word (in German).

[edit]

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

[edit]