Microsoft Office Excel

User Defined Functions in VBA

• Reference: [1]

Example of function

Function RectangleArea(Height As Double, Width As Double) As Double
    RectangleArea = Height * Width
End Function

Where to put the code

• The UDF must be placed in a standard code module (not in one of the Sheet modules and not in the ThisWorkbook module).
• To create a standard module: go to Insert menu, and choose Module.
• Rename the module by pressing F4 and display the Properties, and change the Name property.

Calling the UDF from the same workbook

• Simply use the function name. For instance, type in cell A3:

=RectangleArea(A1,A2)

Testing UDF from VBA

• Press Ctrl-G to bring Immediate window.
• Type in the window:

Debug.Print(RectangleArea(2,3))

UDF: modinv

The following function computes the modular inverse a^-1 such that a.a^-1 mod b = 1 (from extended euclidian algorithm in [2]).

Function modinv(base As Long, modulus As Long)
    
    Dim result(3) As Long
    Dim b, temp, quotient As Long
    
    b = modulus
    x = 0: y = 1: result(1) = 1: result(2) = 0: result(3) = base
    
    Do While b <> 0
       temp = b
       quotient = Int(result(3) / b)
       b = result(3) Mod b
       result(3) = temp
       temp = x
       x = result(1) - quotient * x
       result(1) = temp
       temp = y
       y = result(2) - quotient * y
       result(2) = temp
    Loop
    
    If result(1) < 0 Then result(1) = result(1) + modulus
    modinv = result(1)
End Function

UDF: modexp

From [3]:

Public Function modexp(base As Long, exponent As Long, modulus As Long)

    Dim product As Long
    Dim result As Long
    Dim power As Long
    
    power = 1
    product = base
    
    product = product Mod modulus

    result = IIf(exponent And power, product, 1)
    
    While power < exponent
        power = power * 2
        product = (product * product) Mod modulus
        If (exponent And power) Then result = (result * product) Mod modulus
    Wend
    
    modexp = result
End Function

UDF: BITXOR, BITAND, BITOR

Public Function BITXOR(x As Long, y As Long)
    BITXOR = x Xor y
End Function

Public Function BITAND(x As Long, y As Long)
    BITAND = x And y
End Function

Public Function BITOR(x As Long, y As Long)
    BITOR = x Or y
End Function