Visual Basic for Applications

from Wikipedia, the free encyclopedia

Visual Basic for Applications ( VBA ) is a scripting language that was originally developed to control processes in the Microsoft Office family of programs. It was derived from the BASIC dialect Visual Basic (VB) developed by Microsoft .

Availability

VBA is currently available in the Microsoft Office programs Word (from version 97), Excel (from version 95), Access (from version 95), Project , PowerPoint , FrontPage , Visio (from version 2000) and Outlook . Since the mid-1990s, VBA has replaced the incompatible macro languages ​​of the individual Microsoft Office programs. In addition, VBA is licensed from Corel Corporation and is available in Corel Draw and Corel Photo-Paint and the iGrafx range of products. VBA is also used as a macro language in AutoCAD , ArcGIS , ARIS , CATIA , SolidWorks , MindManager and many other applications. Microsoft Office 2008 for the Apple Macintosh did not offer VBA support, but this is integrated again in the 2011 version.

language

VBA is regarded as a powerful scripting language and is the most common way of creating programs based on Microsoft Office applications (Excel, Word, Access, etc.). VBA is an interpreted programming language whose syntax corresponds to that of Visual Basic (both Visual Basic Classic and Visual Basic .NET ). However, the possibilities and the performance of VBA are significantly reduced compared to Visual Basic. For example, a VBA script is precompiled in order to build variable and constant tables and to carry out syntactic checks, but it is not possible to compile it down to executable machine code .

VBA is primarily designed for procedural programming . Although classes and objects can be represented syntactically and implemented, they are subject to the restrictions that also exist in Visual Basic Classic (lack of implementation inheritance ); however, other features such as data encapsulation , interface inheritance and runtime polymorphism can be used.

Access from VBA to the respective host program (Word, Excel etc.) takes place via a programming interface . In addition to the VBA core (control structures, data types, mathematical functions, file system), special objects of the host program are available in the respective applications. These objects make it possible to automate processes of the respective host program. Since this interface is implemented via the Component Object Model (COM), a VBA application can also access other COM components than those provided by the host program.

Compatibility with VB.NET

Up to Visual Basic Version 6.0 it is possible to exchange modules and forms between VBA and VB without any problems. Since the conversion from Visual Basic to the .NET framework, this is no longer possible because the file format has changed. However, modules (i.e. source code files) can be transferred to the .NET data structures by renaming and making targeted adjustments. This is not possible with forms, they have to be created from scratch. However, it is possible to develop add-ins for the Windows version of MS Office in Visual Studio.NET with the Visual Studio Tools for Office System (VSTO), which can perform the same tasks as classic macros.

safety

The uses of VBA are not limited to just automating the host applications. The scope of performance of VBA also includes essential functions of Visual Basic and all COM-based applications of the executing PC. Therefore, like many other Windows applications, VBA applications can contain malicious code.

Instead of an a priori security concept in which potentially dangerous functions are restricted during the language design or the macro execution is shielded from the rest of the system by means of a sandbox , Microsoft Office only provides retrospective methods for safeguarding the program execution, such as the possibility of checking any existing macro certification . These factors, combined with the easy learnability of VBA, led to the spread of macro viruses . Newer MS Office versions ask before running VBA programs and recommend that you only allow macros that are trustworthy and have verifiable certificates. The VBA code can be protected by a password to protect it from being viewed. In the past, this protection could sometimes be easily canceled, e.g. B. by importing it into the equivalent application of the OpenOffice.org package.

use

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

Numerous websites and newsgroups deal with VBA.

advantages

  • General: The possibility of programming macros increases the usefulness of the associated basic applications, which can then be expanded with user-specific functions.
  • Comparatively easy to learn language ( BASIC was designed for programming beginners);
  • large range of functions in the office area due to the many functions of the basic applications;
  • cross-application via COM , which is also offered by many non-Microsoft applications;
  • Due to its widespread use, there is a large number of free program collections, literature and commercial offers;
  • Since the programs can be encapsulated in their own add-ins, they can be loaded for use as required.

disadvantage

If the aim is to create a clean and long-term usable programming, then it makes sense that the scripts or macros developed with VBA and their structure correspond to the standards of software development. For this reason, it is necessary to design VBA projects in the same way as other software projects and to store the ad-hoc programming style otherwise common with macros in favor of a well-planned software design.

The existing restrictions in VBA make it difficult for experienced developers to use modern programming and design techniques such as those possible in VB.net . After a while, VBA can create software systems in practice that can hardly be further developed in a meaningful way, since their structure is too complex and barely transparent.

example

Example of a user-defined function in Excel that returns the previous business day.

VBA from Office 97

 1 Public Function VorherigerGeschaeftstag(dt As Date) As Date
 2     Dim wd As Integer
 3 
 4     wd = Weekday(dt)  ' Weekday liefert 1 für Sonntag, 2 für Montag usw.
 5     Select Case wd
 6         Case 1
 7             ' Auf Sonntag wird Datum vom letzten Freitag zurückgegeben
 8             VorherigerGeschaeftstag = dt - 2
 9         Case 2
10             ' Auf Montag wird Datum vom letzten Freitag zurückgegeben
11             VorherigerGeschaeftstag = dt - 3
12         Case Else
13             ' Andere Tage: vorheriges Datum wird zurückgegeben
14             VorherigerGeschaeftstag = dt - 1
15     End Select
16 End Function

VBA under Office 95

VBA was localized in Excel 95, the code example on a German-language installation of Excel 95 would look like this: 

 1 Funktion VorherigerGeschaeftstag(dt Als Datum) Als Datum
 2     Dim wd Als integer
 3 
 4     wd = Wochentag(dt) ' Wochentag liefert 1 für Sonntag, 2 für Montag usw.
 5     Prüfe Fall wd
 6         Fall 1
 7             ' Auf Sonntag wird Datum vom letzten Freitag zurückgegeben
 8             VorherigerGeschaeftstag = dt - 2
 9         Fall 2
10             ' Auf Montag wird Datum vom letzten Freitag zurückgegeben
11             VorherigerGeschaeftstag = dt - 3
12         Fall Sonst
13             ' Andere Tage: vorheriges Datum wird zurückgegeben
14             VorherigerGeschaeftstag = dt - 1
15     Ende Prüfe
16 Ende Funktion

See also

literature

  • Axel Buhl, Petra Strauch: VBA basic course. Introduction to program development with Visual Basic for Applications in Excel . 2005, ISBN 978-3-486-57889-8 .
  • Bernd Held: Excel VBA. Compendium. With over 1000 macros for Excel 97 up to 2007 . Markt + Technik, ISBN 978-3-8272-4117-7
  • Andreas Janka: VBA in Word . Galileo Press, 2004, ISBN 3-89842-469-3 .

Web links

Wikibooks: VBA in Excel  - learning and teaching materials