Chapter 8: Extend Excel and Visual Basic

Chapter at a Glance

In this chapter, you will learn to:

• Create and use custom functions.

• Handle errors that occur while a macro is running.

A bacteria cell doesn’t have a nucleus. A cell without a nucleus is called a prokaryote. Prokaryotes are very important. The first known fossils, dating from three and a half billion years ago, are all prokaryotes. They are also very small. In 1999, Heide Schulz discovered the prokaryote species Thiomargarita namibiensis whose individual organisms grow to be as large as 0.75 millimeter-about the size of the head of a fruit-fly. This is astonishingly large for a bacteria but doesn’t seem very large compared to, say, a puppy.

A single-cell organism with a nucleus is called a eukaryote. Eukaryotes can become much larger than prokaryotes, because they have internal structure. Probably the largest single-celled organism is Caulerpa taxifolia. It is a type of seaweed-algae, really- that has been an uncontrollable weed in the Mediterranean Sea since 1984 and was discovered off the US coast near San Diego, California, in June 2000. One Caulerpa plant-consisting of a single cell-can grow to just under a meter in length. In the grand scheme of things, a single-celled Caulerpa is not very big, but it is orders of magnitude larger than a single-celled Thiomargarita. The difference is due to internal structure. The Caulerpa is a eukaryote with a nucleus, and the structure that comes with it. Structure enables functional size.

Recorded macros are like prokaryotes. The macro recorder puts everything you do intoa single, unstructured procedure. And, like prokaryotes, single-procedure macros should be small. Large, sophisticated applications are more like eukaryotes. To get a large, sophisticated application to work properly, you must give it an internal structure- you must break it up into smaller procedures. And just as large, complex organisms need an immune system to deal with diseases, sophisticated applications need a mechanism for dealing with error conditions.

In this chapter, you’ll learn how to create custom functions, use arguments in procedures, and handle errors-tools you’ll need to create more powerful applications.

On The CD-Important 

Before you complete this chapter, you need to install the practice files from the book’s companion CD to their default locations. See “Using the Book’s CD” on page xv for more information.

USE a new blank, macro-enabled workbook and the Structure.txt _file. This practice file is located in the Documents\MSP\ExcelVBA07SBS folder. The Structure text file contains some initial macros that you will copy into your workbook and modify in the exercises in this chapter. The initial macros are stored in a simple text file so that you can be certain there is no malicious code before you put the code into a trusted location.

BE SURE TO save the new macro-enabled workbook as Chapter08.xlsm in the trusted folder location that you created in Chapter 1.

OPEN the Chapter08.xlsm workbook, and then rename Sheet1 to TestFunction.