Introduction


Overview

Spreadsheet macro programming has changed enormously within the last 12 years , when we went from text-based macros to VBA. There was also a major change in Office 97, when macros went to VBA worksheet modules in a separate environment accessed via the Visual Basic Editor. It used to be fairly basic: code was entered onto a specially designated worksheet. Although the language was powerful in its own right, it was not a structured language and could certainly not be described as Object-Oriented. The number of commands was limited and a fair amount of ingenuity was required to do certain tasks . The main advantage of it was that it was easy to learn and understand; many programmers cut their teeth initially by writing spreadsheet macros.

However, it was also quite difficult to document and for other programmers to understand, since code could be placed anywhere on the macro spreadsheet, and blocks of code were only defined by a range name . This meant that code could be all over the place and following the flow of the program around the macro spreadsheet could become very complicated. Of course, professional developers could comment their code by using a cell to the right and could organize their code and its placement on the worksheet. However, it was still a complicated and unstructured process. If anyone other than the original author examined the code, it could take days to find out exactly how it worked and what it was doing. Commercial companies frequently found that when the author of a complicated macro left the company, that macro had to be rewritten from scratch because of the time involved in assessing what it was doing.

Since the advent of Excel 5.0, Microsoft has introduced a new programming language called Visual Basic for Applications (VBA). VBA is a more intuitive and robust programming language using an object-oriented design. It has a great deal of similarity with its older and larger cousin, Visual Basic (VB). Once you learn VBA, you will have a fair understanding of how Visual Basic itself operates.

VBA is extremely different from the old macro language ‚ if the older language is what you are used to, it will involve a total rethinking of how you write and structure your code. The concepts of Object Orientated Programming (OOP) are as different as chalk and cheese to the old macro language, but there is a huge advantage in terms of what you can achieve on a spreadsheet. With Object Orientated Programming you are dealing with the concept of objects. To use an example, the workbook you load is an object. The worksheet where you enter your data is another object. The command bar (menu) across the top is the printer and the screen. All have properties, events, and methods (discussed later in the book). You will start to see Object Oriented Programming in more detail when you reach Chapter 12 ( ‚“The Excel Object Model ‚½).

VBA does allow a more structured and object-orientated approach to writing your macros. If this is your first foray into the world of Excel macro programming, you may well find the concepts easier to grasp, since you have no knowledge of the technologies used in the past within Excel. The text-based macros were a completely different language in terms of how and where you entered it. The concept of VBA is unique and cannot be compared to text-based macros. Unfortunately, knowledge of the old system of writing macros can add to confusion with the new method of VBA and extend the learning curve.

Since VBA is shared by all Office Applications, a great deal of the information contained in this book will help you with other Office Applications such as Word or Access and also with Visual Basic itself. VBA is more powerful then the previous text macro language and enables you to extend Excel in any way you choose. It allows you to write code to do things not within the Excel menu structure. It even provides the means to access and manipulate other Microsoft Office Applications under software control. For example, by using the Outlook object model you could copy address lists onto your spreadsheet merely by executing VBA code. This would happen even if Outlook were not running, since Outlook only needs to be installed for the object model to work.

By the same token, you can also manipulate the Excel object model from another Microsoft Application. For example, you could produce an Excel spreadsheet file from Microsoft Access without Excel ever appearing onscreen or being loaded. Access users will know that it is very easy to write a macro to export a table into Excel, but what if you want information from several tables and you want presentation formatting on the numbers and audit trails on the figures explaining how they were calculated? VBA allows you to do this.

VBA is a full object-oriented language that needs a totally different viewpoint from the old text-based macro programming. All objects can have Events, Methods, and Properties and these can be manipulated to assist your programs. The objects are arranged in a hierarchy (Application Object Workbook Worksheet). This concept is explained in more detail in Chapter 12.

Whether you are totally new to macro programming in Excel or simply want to update your skills, this book will show you how to use the Excel object model along with VBA to learn how to program macros effectively.




Excel VBA Macro Programming
Excel VBA Macro Programming
ISBN: 0072231440
EAN: 2147483647
Year: 2004
Pages: 141

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