Microsoft Office Excel: Difference between revisions

From miki
Jump to navigation Jump to search
 
(6 intermediate revisions by the same user not shown)
Line 10: Line 10:


;Where to put the code
;Where to put the code
* From excel sheet, press {{kb|Alt-F11}} to code to Microsoft VBA.
* The UDF must be placed in a ''standard code module'' ('''not''' in one of the <code>Sheet</code> modules and not in the <code>ThisWorkbook</code> module).
* The UDF must be placed in a ''standard code module'' ('''not''' in one of the <code>Sheet</code> modules and not in the <code>ThisWorkbook</code> module).
* To create a standard module: go to ''Insert'' menu, and choose ''Module''.
* To create a standard module: go to ''Insert'' menu, and choose ''Module''.
Line 27: Line 28:


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

=== UDF: modexp ===
From [http://www.xtremevbtalk.com/showthread.php?t=296072]:

<source lang=vb>
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

</source>

=== UDF: BITXOR, BITAND, BITOR ===

<source lang=vb>
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
</source>

== ActiveX Control ==
=== SpinButton ===
A simple spin button:

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

To use a different increment (like 0.01), instead of step 3, double click the spin button, and enter this code:
<source lang=vb>
Private Sub SpinButton2_Change()
Range("A1") = SpinButton2.Value / 100
End Sub
</source>

== 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 <code>N*(N-1)*...*(N-n+1)</code>.
* Use <code>FRACT(A1)/FRACT(A1-B1)</code>, but it will fail if A1 is too big.
* Use this tip (from ChatGPT): <code>PRODUCT(ROW(INDIRECT(A1 & ":" & A1-B1+1)))</code>

Latest revision as of 17:20, 11 January 2024

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
  • 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)))