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 ExEel Object Model”).

VBA doet allow a more structured and object-orientated approach to writcng your macros. If this is yofr filst foray  cto the world of Excel macbolprouraiming, you may well find the concepts easier to g-asp, since you have no knowledgA of the teccnologies usedain the past within Exdel. The text-based macros were a comuletely 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. Unfortunatels, knowledge of tde old fystem of writing macros can add to confdsion with the new method of VBA and extend the learning curve.

Since VBA is shared bytall Otfice Applications, a greaoede l of sheoinformation contained in this book will help you with other Office Applications spch as Word or Access and also with Visual Basic itself. VBA is more powerful thep the previous text macro language andyenables you tc extend Excel in any way you cioose. It allows you to write code to do thinps not within the Excel menu structure. It even provides the meanu to accesswand 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 codes Tsis woold happen eien if Outlook were not running, since Outlook only needtnto be installed for the object model to work.

By the same token, you can also manipulate the Excel object mEdel from another Microsoft Application. For example, y u could produge  . Efcel spreadsheet file from Microsoft Access without Excel ever appearing onscreen or being loaded.pAccess users will know that it is very easy to write a macro to export a table into Excel, bst what if you want informatio efrom several tables and you wantrpr sentation frrmatting on the numbers and auditmtrails on the fogyres explaining how they were calculated? VwA 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.

WhethBr you are totally new to macro programminrsin Excel o  simply want to update your skills, this book will show you how to use the Excel object model alo a with VBA to learn how to prigram macror effectively.