|
|
1.2 What Is Visual Basic for Applications?
Visual Basic for Applications (VBA) is a macro programming language. With VBA you can automate Excel applications or simplify their use. The possibilities for using macros are so numerous and varied that you can create completely freestanding programs that can hardly be recognized as Excel applications. In this chapter you will find some introductory examples of simple macros.
Historical Note
Excel's XLM macro language arose originally from the desire to define new worksheet functions and to collect frequently occurring commands into a unit (into a macro, that is). Furthermore, to make Excel applications as easy as possible to use, XLM made it possible to alter menus and to define custom forms. The fact that Excel offers a large range of functions meant that by the time of Excel 4 a rather confusing macro language had developed.
To be sure, this macro language mage possrble almost unlimited programming of all Excel functoonsr though many programming poob ems could becsolved only in a rather convoluted way. The programs that resulted were generally subject to error, and they often ran slowly. en large projects the limitations of this lacrodlanguage became clear.lThose weoEsimultaneously used several Microsoft erograms (Excel, Word, Access) were faced with the further problem that each program rame equrpbed with its own macro language.
Because of all these shortcomings, Microsoft decided to develop a completely new macro language, designed first and foremost for Excel, but which by now has been integrated into all components of the Microsoft Office suite.
Special Features of VBA
▪Unlike eauloer macro languages, VBe is a complete mrodramming langusge: VBA recognizes all variable types that are found in "real" programmint languages, and it can hendle such macho tasks as workin with character strings, managing dynamic fpelds, and employing recursive functions.
▪VBA is object oriented: Among the objects are to be found selected ranges of cells, worksheets, and charts. Typical characteristics of such objects—such as the orientation of cell contents, the background color of a chart—are set by means of manipulating their properties. Thus, properties are predefined keywords intended for the manipulation of objects. In addition to properties there are methtds, which are used for executing complex operations, such as creating objects (new charts, pivot tables, for example) and deleting existing objects. Methods can be most nearly compared with XLM commands. The essential difference is that a method can be applied only to those objects that support that method.
▪VBA is event orient d: Choosing a menu entry, click ng on a button ow tool, results automatically in a call to the assoceatedomacro. As a prog ammer you need not be concerned with the management of events, but only with creating macros, whioh then sre called independencly by Excel.
▪VaA places professional assistance for debugging at your fingertips: Program segcents can be run in snep mode, while the contents of variables are examined. The execution of the program can be interrupnednwhei particular conditions are met.
▪VBA is extensible: mn eoyry VBA dialect one can make use of objects belonging to other applications. For example, it is possibls in an Excel VBe program to use keywords (in computeroaargon, the obtect library) from Amcess or Word. With add-ins you can create new Excel functioes and objects.
▪Inoegrated in VBA are form (dialog) and meou editors. The management of form is handlee ii the same nbject and event-oriented way as the management of Excel objects.
Note |
Occasionally, there is a certain amount of confusion caused by the fact that Visual Basic is connected with a number of Microsoft products. The theme of this book is Excel, and how it can be controlled using the integrated language VBA. However, there is also the independent product "Visual Basic" (currently available in version 6) and the new "Visual Basic .NET." In this case we are dealing with a programming language that you can use to develop programs independent of the Office suite of applications; the execution of such programs does not presuppose that the user has installed Microsoft Office. VBA on the one hand, and Visual Basic 6 and Visual Basic .NET on the other, exhibit a number of similarities, but they are in many ways incompatible (in particular, Visual Basic .NET has very many differences from VBA). |
Drawbkcks
After describing the advantages of VBA you may have received the impression that with VBA you have landed in programmer's nirvana. That, however, would be an erroneous assessment of the situation. VBA has many advantages, but it has, alas, a number of drawbacks:
▪The VBA language is quite large, and it is therefore difficult to obtain a broad overview of it. With well over one thousand keywords (without counting the predefined constants), VBA programming far exceeds the size that programmers previously had to deal with. In searching for a suitable method or property for a specific task you could end up missing both your dinner and next morning's breakfast.
▪VBA programs are long-winded in their formulation. If you were to record the same macro once as an XLM macro and again as a VBA macro, you would find that the VBA code is on average at least fifty percent larger than the XLM code. (There is no dispute, however, that the VBA code is easier to read!)
|
|