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.xls.
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 ALTF11
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 served our
introductory purposes. There will be more interesting functions to challenge
you coming around the bend.
YOUR TURN
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:
 Write a function to
calculate the parallel combo of two resistors. You can name the function
something like "Function R_Par(R1, R2)".
 Make a function to
calculate the noninverting gain of an op amp.
Back to Topics
