THE MAGAZINE FOR COMPUTER APPLICATIONS
Circuit Cellar Online offers articles illustrating creative solutions
and unique applications through complete projects, practical
tutorials, and useful design techniques.

DO YOU EXCEL IN ELECTRONICS?

Part 2: The Benefits of Using Excel
by Aubrey Kagan

Start ý Standard Values ý Saving Macros ý Simple Amplifier ý LM317 Calculation ý Goal Seek ý Use of Functions ý Additional Options ý Source and PDF

USE OF FUNCTIONS

You donýt have to be a specialist in Visual Basic for Applications (VBA) to figure out how to create a macro. The secret is to auto-record a similar function as a macro (stored as a VBA file), and then edit it. To do this, click the OK button in the Goal Seek Status box. Select the Tools/Macros/Record macro and name it anything you want, say LM317_solve. Now, go through the Goal Seek process again: Tools/Goal Seek. Click on the Set Cell bar and type "volt." Next, click on the To Value bar and enter "5." After that, click on the By changing cell bar and enter "re2." Click OK to run Goal Seek and then OK again to acknowledge the calculation. Then, go to Tools/Macro/Stop to stop recording (see Example3d.xls (download Excel files)).

Try running the macro. Note that the macro does not stop to prompt you for the target voltage, so you need to edit the macro to provide for this by going to Tools/Visual Basic Editor. (While you do this, you may want to have the NearestValues.xls worksheet closed as you may find it difficult to locate the macro. With more familiarity, this step can be avoided, which Iýll discuss later in the text.)

The macro as captured by the auto-record feature is shown in Listing 3. The InputBox function is the one you want to use. It automatically provides the prompt functionality that you want.

Modify the macro to look like what you see in Listing 4. The InBox function returns only a string value that needs to be conditioned to be a number. This number is then entered instead of the 5 in the Goal:= part of the line, which invokes the Solve For function. The Solve For invocation is contained in the true part of the If statement, so it isnýt executed if a nonnumeric character is entered (see Example3e.xls (download Excel files)).

PREVIOUS • NEXT