1.2 What Is Visual Basic for Applications?


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 made possible almost unlimited programming of all Excel functions, though many programming problems could be solved only in a rather convoluted way. The programs that resulted were generally subject to error, and they often ran slowly. In large projects the limitations of this macro language became clear. Those who simultaneously used several Microsoft programs (Excel, Word, Access) were faced with the further problem that each program came equipped 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 earlier macro languages, VBA is a complete programming language: VBA recognizes all variable types that are found in "real" programming languages, and it can handle such macho tasks as working with character strings, managing dynamic fields, 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 methods , 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 oriented: Choosing a menu entry, clicking on a button or tool, results automatically in a call to the associated macro. As a programmer you need not be concerned with the management of events, but only with creating macros, which then are called independently by Excel.

  • VBA places professional assistance for debugging at your fingertips: Program segments can be run in step mode, while the contents of variables are examined. The execution of the program can be interrupted when particular conditions are met.

  • VBA is extensible: In every VBA dialect one can make use of objects belonging to other applications. For example, it is possible in an Excel VBA program to use keywords (in computer jargon, the object library) from Access or Word. With add-ins you can create new Excel functions and objects.

  • Integrated in VBA are form (dialog) and menu editors. The management of forms is handled in the same object 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).

Drawbacks

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!)




Definitive Guide to Excel VBA
Linux Application Development For The Enterprise (Charles River Media Programming)
ISBN: 1584502533
EAN: 2147483647
Year: 2002
Pages: 134

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net