Chapter 7: Control Visual Basic

Chapter at a Glance

In this chapter, you will learn to:

• Use conditional statements.

• Create loops using three different blocks.

• Retrieve the names of files in a folder.

• Create breakpoints to debug long loops.

• Show progress while a macro executes a loop.

The first successful underwater tunnel ever built was begun in 1825. It is the Thames Tunnel. It was a financial disaster at the time, but amazingly it is still in use as part of the London Underground system. The genius behind the the tunnel’s engineering was a man named Marc Brunel. Twenty years before launching the Thames Tunnel, Brunel made a name for himself by devising a way of inexpensively producing the pulley blocks needed to build ships for the British shipping industry. Brunel’s technique later came to be known as an “assembly line,” and Henry Ford turned the invention into an industry, supplying America with Model T cars that cost only $3,500 in today’s dollars.

Repetition can have a dramatic effect on efficiency. Computer programs-including macros that you write-become more powerful when you add a multiplier effect. In this chapter, you’ll learn how to add loops to your macros. And to make those loops more effective, you’ll learn how to create conditional expressions that let the macro make decisions.

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 the Flow.xlsx workbook, the Flow.txt text file, and the Orders.xlsx workbook. These practice files are located in the Documents\MSP\ExcelVBA07SBS folder. The Flow text file contains some initial macros that you will copy into your workbook and modify during 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 Flow.xlsx workbook as a macro-enabled workbook named Chapter07.xlsm in the trusted location you created in Chapter 1.

OPEN the Flow text file. Then open the Chapter07 workbook, right-click any sheet tab, and click View Code to open the Microsoft Visual Basic editor. In the Visual Basic editor, from the Insert menu, click Module to create a new module for your macros, and then save the file. Arrange the Microsoft Office Excel 2007 and Visual Basic editor windows so that you can see both of them side by side.