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

• From excel sheet, press Alt-F11 to code to Microsoft VBA.
• 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

ActiveX Control

SpinButton

A simple spin button:

1. Go to developer ribbon, select Insert, Spin Button (ActiveX control)
2. Enable Design Mode
3. Open the properties, and edit LinkedCell (type in cell name or range), Max, Min, etc
4. Disable Design Mode

To use a different increment (like 0.01), instead of step 3, double click the spin button, and enter this code:

Private Sub SpinButton2_Change()
  Range("A1") = SpinButton2.Value / 100
End Sub

Tips

Disable Paste Options Popup

Very annoying when doing frequent copy/paste with the mouse, here how to disable the annoying popup: How to prevent options from appearing

• Click the Microsoft Office Button, and then click Excel Options.
• Click Advanced.
• Under Cut, copy, and paste, use one or both of the following procedures: To turn off Paste Options and Auto Fill Options, click to clear the Show Paste Options buttons check box. ...
• Click OK.

Compute truncated fractorials

Say we want to compute "truncated" factorials like N*(N-1)*...*(N-n+1).

• Use FRACT(A1)/FRACT(A1-B1), but it will fail if A1 is too big.
• Use this tip (from ChatGPT): PRODUCT(ROW(INDIRECT(A1 & ":" & A1-B1+1)))