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(a, b)
    Dim result(3)
    bcopy = b
    x = 0: y = 1: result(1) = 1: result(2) = 0: result(3) = a
    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) + bcopy
    modinv = result(1)
End Function