This is the third part of a tutorial about modeling logic gates in MS Excel 2003. This first model includes no delay or loading for the gates.
This section creates user defined functions for each gate and uses these functions to upgrade the model. An auxiliary macro is created which measures the speed of the model in iterations/second.
The model with user defined functions proves to be much slower than the model using plain built-in spreadsheet functions.
Introduction to Digital Electronics – Ideal Logic Gates – Part#3
by George Lungu
– This tutorial is the third part of the series introducing the most common logic gates and
their implementation in Excel, first as ideal static models and later using more realistic
animated models which include propagation delay, loading, strength, power supply voltage.
– In the first part we talked about common logic gates (INV, AND, NAND, OR, NOR, XOR,
XNOR), their logic equations, their truth tables and then we went forward in the second part
to implementing a first iteration of a model in Excel 2003 together with a simple animation.
– In this section the model is upgraded by employing user defined functions.
Create a new worksheet:
– In the original Excel file (named Logic_Gates.xls) make a
copy of the last worksheet (Basic_3) and rename it
“Basic_4”.
– In the new worksheet insert a new parameter called
“vdd” which stands for the power supply voltage: cell A17:
“Vdd [V]” (this is a just a label) => cell A18: “1”
– Give cell A18 the name “vdd” by highlighting the cell and
typing in the name box (upper left corner) the name “vdd”
(without quotation marks).
– If the name is not accepted it means you might have used
it in a different place already and you can remove it first by
clicking in any cell => Insert (top menu) => Name => Define
=> select the name from the pop up list => Delete
<excelunusual.com>
1
What is a user defined function?
– MS Excel has a large number of built in functions but sometime we might need to create a
special function that does not exist in the library of existing functions.
– The user defined functions will be much easier to use in the spreadsheet by simplifying the
formulas but, when used widely within the worksheet, at the penalty of speed.
Create a user defined function for the inverter:
– Bring up the visual basic editor:
Tools => Macro => Visual Basic Editor
(or simply type Alt F11)
– The code for a function has to
always be stored in a “Module” for
the function to work.
– At the left (in the VBAProject
area) click right => Insert => Module
– Move the cursor to the right (the
edit area) and type the code seen in
the next snapshot =>>>
– A, and vdd are called the
arguments of the function (the input
and the voltage supply in this case)
– In certain cases the type (integer, double, Boolean, etc) must be specified for both the function
return (INV_GATE) and the arguments (A and vdd) as we will see in the next page.
<excelunusual.com>
2
A more complete syntax:
Function INV_GATE(A As Double, vdd As Double) As Double
If A < vdd / 2 Then
– This is the same function as the one
INV_GATE = vdd
in the previous page except that the
Else
type of each argument and the type
INV_GATE = 0
of return are explicit. End If
End Function
Create the functions AND, NAND, OR and NOR
Function AND_GATE(A, B, vdd) As Double Function NAND_GATE(A, B, vdd) As Double
If A > vdd / 2 And B > vdd / 2 Then If A > vdd / 2 And B > vdd / 2 Then
AND_GATE = vdd NAND_GATE = 0
Else
Else
AND_GATE = 0 NAND_GATE = vdd
End If End If
End Function End Function
Function OR_GATE(A, B, vdd) As Double Function NOR_GATE(A, B, vdd) As Double
If A < vdd / 2 And B < vdd / 2 Then If A < vdd / 2 And B < vdd / 2 Then
OR_GATE = 0 NOR_GATE = vdd
Else Else
OR_GATE = vdd NOR_GATE = 0
End If End If
End Function End Function
Function XOR_GATE(A, B, vdd) As Double
Create XOR function:
If (A < vdd / 2 And B > vdd / 2) Or (A > vdd / 2 And B < vdd / 2) Then
XOR_GATE = vdd
Else
XOR_GATE = 0
End If
End Function
Function XNOR_GATE(A, B, vdd) As Double
Create XNOR function:
If (A < vdd / 2 And B > vdd / 2) Or (A > vdd / 2 And B < vdd / 2) Then
XNOR_GATE = 0
Else
XNOR_GATE = vdd
End If
End Function
Using the custom functions in the worksheet:
– Modify the following cell formulas to include the new user defined functions:
– Cell D37: “=INV_gate(B37,vdd)”
– Cell E37: “=AND_gate(B37,C37,vdd)”
– Cell F37: “=NAND_gate(B37,C37,vdd)”
– Cell G37: “=OR_gate(B37,C37,vdd)”
– Cell H37: “=NOR_gate(B37,C37,vdd)”
– Cell I37: “=XOR_gate(B37,C37,vdd)”
– Cell J37: “=XNOR_gate(B37,C37,vdd)”
after which, copy range D37:J37 down to row 837
<excelunusual.com>
4
The final model:
– Using the new setup involving custom
functions, we can run the macro by
clicking the “Animation_On/Off” button.
We see that the model is running but it is
much slower than the previous one (using
built-in cell formulas)
A special macro for measuring
the speed of the model:
Sub speed_test()
N = Not N
– We used a special macro which runs exactly
[A25] = 0
for 60 seconds then stops the model and during
Measurement_Start = Timer()
all this time it records the number of iterations Do Until N = False Or (Timer() – Measurement_Start) > 60
p = p + 0.1
in cell A25.
DoEvents
– The time function “Timer()” records the
[A25] = [A25] + 1
number of seconds since midnight
[A8] = 10 * (4 + 2 * Sin(p / 10))
DoEvents – “Measurement_Start” is a variable defined as
[A11] = 10 * (2.2 + 2 * Sin(p / 7))
type double and it is initialized with the
DoEvents
number of seconds passed since midnight at the
Loop
moment of starting the macro
End Sub
<excelunusual.com>
5
Speed test results:
Model speed 7500
– There were a two different tests run,
7000
one in which both models (one with
6500
custom functions and one with built-in
functions) were run with the waveform 6000
chart visible. As expected, the speed was 5500
pretty low for both cases but lower for
5000
the custom function model. It seems that
4500
Custom_functions
live char-ting consumes a significant
4000
amount of computer resources.
Built-in functions
3500
– For the second test the chart was 3000
moved out of sight so that a pure 2500
computation speed could be measured.
2000
Here the speed difference was more than
1500
an order of magnitude in favor of the
1000
model with built-in functions.
500
0
– As a result I highly recommend refraining from using (or sparingly using)
user defined functions in any animated
model that require speed !!!
The end
<excelunusual.com>
6
