Invoking Compiled VBA Code at Runtime

To execute your compiled VBA code, you must call it from your workbook’s regular VBA code using a helper function.

First, open the Visual Basic Editor and paste the following helper function into a module in your workbook:

' This function lets you call VBA macros compiled with XLS Padlock
Public Function CallXLSPadlockVBA(ID As String, Param1 As Variant) As Variant
    Dim XLSPadlock As Object
    On Error Resume Next
    Set XLSPadlock = Application.COMAddIns("GXLSForm.GXLSFormula").Object
    CallXLSPadlockVBA = XLSPadlock.PLEvalVBA(ID, Param1)
End Function

The CallXLSPadlockVBA helper function takes two parameters:

• ID: The name of the compiled Sub or Function you want to invoke.
• Param1: An optional parameter you want to pass to the compiled code.

Imagine your original VBA module contains the following code:

Sub Calculate()
    Range("A4") = "Tom"
    Range("B4") = 5000
    Range("C4") = Range("B4") * 0.5
    Range("D4") = Range("C4") + Range("B4")
End Sub

After moving this code into the VBA Compiler, you would replace the original sub with a call to the helper function:

Sub Calculate()
    Dim res As Variant
    res = CallXLSPadlockVBA("Calculate", "")
End Sub

Here, we provide the name of the protected sub, Calculate, and pass an empty string for the second parameter because it is not used in this case. The res variable will hold the result of the call, which is primarily useful if the invoked code is a Function that returns a value.

👉 See also: Learn how to pass more than one parameter to your compiled code.