Visual Basic for Applications

Visual Basic for Applications (VBA ) is a scripting language for controlling runs the Microsoft Office family of programs. It is derived from the model developed by Microsoft BASIC dialect Visual Basic ( VB) and triggered since the mid-1990s among themselves incompatible macro languages ​​of the different Microsoft Office programs from.

Availability

Currently, VBA is in the Microsoft Office programs Word (since version 97), Excel ( since version 95), Access ( since version 95), Project, PowerPoint, FrontPage, Visio available ( since version 2000) and Outlook. In addition, VBA is licensed by the Corel Corporation and is available in Corel Draw and Corel Photo-Paint and the iGrafx product range. VBA is used also as a macro language in AutoCAD, ArcGIS, ARIS, CATIA, SolidWorks, MindManager and many other applications. Microsoft Office 2008 for the Apple Macintosh offered no VBA support, but this is re-integrated into the 2011 version.

Language

VBA is considered as a powerful scripting language and is the most common option, point to Microsoft Office applications (Excel, Word, Access, etc.) based programs to create. VBA is an interpreted programming language, its syntax is equivalent to the Visual Basic. The possibilities and the power of VBA, however, are against Visual Basic significantly reduced. For example, a Visual Basic script, although pre-compiled to establish variable and constant tables and implement syntactical checks, however, a compiling to executable machine code is not possible.

VBA is primarily designed for procedural programming. Although classes and objects can be represented and implemented syntactically, but subject to the existing and Visual Basic Classic limitations (lack of implementation inheritance ); other features such as data encapsulation, interface inheritance and Laufzeitpolymorphie other hand, can be used.

Is accessed via VBA to the respective host program (Word, Excel, etc. ) via a usually well- documented programming. In the respective applications in addition to the VBA - core ( control structures, data types, mathematical functions, file system ) special objects of the host program are available. These objects make it possible to automate sequences of the respective host program. Since this interface via the Component Object Model (COM) is realized, a VBA application can also access other COM components identified as the host of the program.

Compatibility with VB.NET

Until the Visual Basic version 6.0, it is easily possible to replace modules and forms between VBA and VB. Since the conversion from Visual Basic to. NET Framework, this is no longer possible because the file format has changed. However, can be converted by renaming and targeted adjustments to the. NET data structures modules ( source files so ). For forms, this is not possible, they must be created from scratch. However, there is the possibility, in Visual Studio.NET with the Visual Studio Tools for Office System ( VSTO ) add-ins for MS Office to develop, which can assume the same responsibilities as classical macros.

Security

The application possibilities of VBA are not only limited to the automation of host applications. The scope of VBA also includes essential features of Visual Basic and all based on COM applications of the operating PC. Therefore, VBA applications, as well as many other Windows applications that contain malicious code.

Instead of an a priori security concept, in which potentially dangerous functions already restricted in language design or execution of the macro is shielded by a sandbox from the rest of the system, Microsoft Office, only in retrospect policies for hedging program execution, such as a possibility of examination of any existing macro certification available. These factors, combined with the ease of learning VBA, led to the spread of macro viruses. Ask Newer MS Office versions before running VBA programs after and advised to allow only trusted and equipped with verifiable certificates macros. To protect against inspection of VBA code can be protected by a password. This protection could be partially circumvented quite easily in the past, for example by importing it into the equivalent application of the OpenOffice.org package.

Dissemination

In companies VBA is often used to implement or to evaluate prototypes for later native software applications quickly smaller IT solutions.

Many Web sites and newsgroups dealing with VBA.

Benefits

  • General: The ability to program macros increases the utility of the associated base applications that this will add extensibility with user-specific functions.
  • Relatively easy to learn language ( BASIC was designed for programming beginners );
  • Already included with the widely used Microsoft Office package;
  • High Functionality in the office area through the many functions of the base applications;
  • Across applications via COM, which is also offered by many non - Microsoft applications;
  • There due to its spread a variety of free program collections, literature and commercial offers;
  • Because the programs can encapsulate in their own add-ins, they can be loaded as needed to apply.

Disadvantages

If the creation of a clean and long-term usable programming aimed, then it makes sense that developed with VBA scripts or macros and their structure meet the standards of software development. For this reason it is necessary to VBA projects as well as other software to design projects and the usual macros in ad-hoc programming style in favor of a well-planned software draft store.

The restrictions in VBA difficult even for experienced developers using modern programming and design techniques, as they are possible in VB.net. After some time, can with VBA in practice, therefore, the software systems created, which could be further developed useful little more, because their structure is too complex and hardly transparent.

Example

Example of a custom function in Excel that returns the previous business day.

VBA from Office 97

Public Function VorherigerGeschaeftstag ( dt As Date ) As Date      '      Dim wd = Weekday (dt) ' Weekday returns 1 for Sunday, 2 for Monday, and so      Select Case wd          Case 1              ' On Sunday date will be returned by the last Friday              VorherigerGeschaeftstag dt = - 2          Case 2              ' On Monday Date is returned by the last Friday              VorherigerGeschaeftstag dt = - 3          Case Else              ' Other days: previous date will be returned              VorherigerGeschaeftstag dt = - 1      end Select      ' end Function VBA in Office 95

VBA was localized in Excel 95, the code sample on a German installation of Excel 95 would look like:

Function VorherigerGeschaeftstag ( dt As Date ) As Date ' Dim wd = Weekday (dt) ' weekday returns 1 for Sunday, 2 for Monday, and so Check case wd case 1 ' On Sunday date will be returned by the last Friday VorherigerGeschaeftstag dt = - 2 case 2 ' On Monday Date is returned by the last Friday VorherigerGeschaeftstag dt = - 3 Otherwise case ' Other days: previous date will be returned VorherigerGeschaeftstag dt = - 1 Check the end ' end function

Microsoft Access Microsoft Visio Mindjet (software) Interpreter (computing) Compiler Encapsulation (object-oriented programming) Polymorphism (computer science) .NET Framework Sandbox (computer security)
807056
de