Creating Boolean Functions

  • Thread starter Thread starter Professor James E. Hicks
  • Start date Start date
P

Professor James E. Hicks

My Problem:

I want to create functions in Excel that can be used in
the exact same way as built-in functions are used.

For example:
to use the AND built-in function all I need to do is to
type the following into a cell:
=AND(TRUE,FALSE) ..... or
=AND(A1,B1), where cell A1 & B1 contains Boolean values.

The following functions do not exist as built-in
functions in Exce: XNOR & NAND. I would like to write
these functions and access them in the exact same manner
as the AND function:
=XNOR(TRUE,FALSE) or XNOR(A1,B1).

Please help (e-mail address removed)
 
Hi:

XNOR(A1,B1) can be written as:

=OR(AND(A1,B1),AND(AND(NOT(A1),NOT(B1))))

NAND(A1,B1) can be written as:

=NOT(AND(A1,B1))

These can quite easily be written as UDFs.

Function XNOR(A as Boolean, B as Boolean) As Boolean
XNOR = (A And B) Or (Not A And Not B)
End Function

Function NAND(A as Boolean, B as Boolean) As Boolean)
NAND = Not (A And B)
End Function

Regards,

Vasant.
 
Nice Vasant!

Liked it so much, I extended to multiple arguments

Function XNOR(ParamArray args()) As Boolean
Dim i As Long
Dim params1, params2

For i = LBound(args) + 1 To UBound(args)
params1 = args(i - 1) And args(i)
params2 = Not args(i - 1) And Not args(i)
Next i

XNOR = params1 Or params2
End Function



Function NAND(A As Boolean, B As Boolean) As Boolean
Dim i As Long
Dim params1

For i = LBound(args) + 1 To UBound(args)
params1 = args(i - 1) And args(i)
Next i

NAND = Not params1
End Function

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)
 
or even ...

Function XNOR(ParamArray args()) As Boolean
Dim i As Long
Dim params1, params2

params1 = args(1)
For i = LBound(args) + 1 To UBound(args)
params1 = params1 And args(i)
params2 = Not params2 And Not args(i)
Next i

XNOR = params1 Or params2
End Function



Function NAND(A As Boolean, B As Boolean) As Boolean
Dim i As Long
Dim params1, params2

params1 = args(1)
For i = LBound(args) + 1 To UBound(args)
params1 = params1 And args(i)
Next i

NAND = Not params1
End Function


--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)
 
Thanks for the nice enhancement, Bob -- I just didn't have the energy to
come up with a multi-argument solution! Let's hope the OP appreciates it.

Regards,

Vasant.
 
You have some nice help writing the functions, but I think in order to
access them from the list of functions, you will need to create an add-in.
 
Mike,

<<You have some nice help writing the functions, but I think in order to
access them from the list of functions, you will need to create an add-in.>>

No, just make sure that they are public.

Regards,
Kevin
 
Back
Top