Microsoft Office Excel

From miki
Revision as of 10:44, 14 November 2013 by Mip (talk | contribs) (→‎UDF: modinv)
Jump to navigation Jump to search

User Defined Functions in VBA

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 As Long, b As Long)
    
    Dim result(3) As Long
    Dim bcopy, temp, quotient As Long
    
    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

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