Electronic Design with Excel
 

  

   VBA Intro, Examples | VBA Basics | Excel Calc Series | eCircuit Home

       

VBA BASICS

 

WHAT IS VBA?

Microsoft Excel includes a built in version of the programming language BASIC. Specifically, VBA (Visual Basic with Applications) allows users to customize Excel spreadsheets (add special functions, buttons, print outs, reports) for various applications (engineering, sciences, payroll, purchasing, accounting, etc.)

I'm so glad I discovered VBA! Its a great tool if you're interested in electronics design and analysis! While Excel is a workhorse of the the electronics industry, it's not visually friendly when it comes to displaying equations or working with algorithms.

 But, VBA let's you write equations with meaningful variables. And to boot, you only have to write the equation once. Then simply call the function where you need it. Made a mistake? You only have to correct it once in the function.


VBA EXAMPLE

You can down load the spreadsheet EE_VBA_Basics_Res_Divider.xlsm.

To get a first view at VBA, we'll use a simple resistor divider. The input voltage vs, gets divided by R1 and R2 to produce an output voltage
       vo = vs * R2 / (R1 + R2).

 
Input Signal    
Vs 10  
   
Resistor Divider  
R1 10000  
R2 20000  
   
Gain and Output of Divider
K Rdiv 0.667  
Vo 6.667  

 

The red cell holds the function call

=K_Rdiv1(B17,B18)

What does the VBA code look like? Hit ALT-F11 and double click on the Modules > Modules1 in the VBA Project window. This opens the code window for this module.

  Function K_Rdiv1(R1, R2)
   ' Gain of resistor divider
   K_Rdiv1 = R2 / (R2 + R1)

End Function

You can down load the spreadsheet EE_VBA_Basics_Res_Divider.xls.

 

INSIDE THE FUNCTION

What makes up this function? The first and last lines bookend the function to let the VBA compiler know which statements make up this function. The text following the ' is ignored by the compiler. This is the place for your comments! Take some time to note what you did and why. For more complex functions, you'll be desperately scanning these comments later when you're reusing or debugging some code and trying to figure out - what was I thinking?

The meat of the function is the actual calculation of the resistor attenuation. Note that the result is placed in a variable with the same name as the function, K_Rdiv1. This is no accident. When executed, the value of K_Rdiv1 gets placed into cell from which it was called. Cool!

Okay, this ultra simple function did not showcase some of VBA's more advanced capabilities. But, it's an easy introduction for more complex and interesting functions you may encounter.

 

TRY IT!

The best part of VBA is that it lets you play with the code. Once you see some code, jump right in and start messing with it or write a new function altogether. Just copy the existing code to a space just below the original. Then start modifying the name and calculations. Here's a few ideas you can try:

  1. Write a function to calculate the parallel combo of two resistors. You can name the function something like "Function R_Par(R1, R2)".
  2. Make a function to calculate the non-inverting gain of an op amp.

 

Back to Topics

 
 

©  2008   eCircuit Center