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

STANDARD VALUES

The three macros are merely variations on a theme, so I will only describe NearestResistorB() for looking up 1% standard values. I originally created this function using the VLOOKUP function, but I decided that it was inelegant because every resistor value has to be entered as part of the macro. I decided to express every resistor value in scientific notation (a mantissa between one and 9.99999) multiplied by an exponent (multiple powers of 10). Therefore, I would only have to key in decade values once. I could then generate the nearest value in one range and multiply by powers of 10.

I intended to use a FOR construct to search through an array of constants (being the mantissa of standard resistor values) to find the nearest standard value. I consulted many books on Visual Basic but could not find any way to initialize an array (akin to the C construct int AnArray[93]={0,1,2,3}, or even the original MBASICýs DATA instruction). Itýs possible to store the values in a file, but that means that the files would have to be transported from computer to computer as well as the macro file. (No doubt someone will contact me with a simple way of doing this now that the hard work is done! That is, if that someone is still awake at this point.)

In the end, the resulting macro does it by brute force. I simply created a range between each standard resistor value (at least the mantissa) and, in each possible range, set two variables corresponding to the lower and upper values. I then checked to see whether the mantissa of the calculated value was closer to the lower or upper and selected the result accordingly. The number being converted is passed as a parameter and would normally be a cell reference. The function returns a value that is the nearest standard resistor value.

I have not tried to format this value as 5.1K or similar because I may want to use this number in a further calculation. Listing 1 is only a partial listing to give you an idea of what is involved (see NearestValues.xls (download Excel files ) for a detailed listing). One of the advantages of this method is that it is easy to edit for other values. The number of standard values for resistor arrays is fewer and some organizations may only keep certain values. It can also be adapted for capacitors with ease. Iýll show you how to use these macros in the following examples.

In order to get the resistor value in the 4.75-kilohm format, I created another macro. To create this macro, click on a value in a cell and run a macro that will then pop up a message box with the nearest standard value (for the B series). At the end of the calculation that uses the NearestResistorB function, simply click on the resultant value and run the LookupStandardResB macro to get the nearest value (in this case, the exact value) expressed as ohms, kilohms, or megohms (see Listing 2).

PREVIOUS • NEXT