Invoking compiled VBA code at runtime |
XLS Padlock provides you with VBA functions to invoke the compiled VBA code in your workbook.
Open the Visual Basic Editor and copy/paste the following helper function into a module of your workbook:
' This function lets you call VBA macros compiled with XLS Padlock
Public Function CallXLSPadlockVBA(ID As String, Param1)
Dim XLSPadlock As Object
On Error Resume NextSet XLSPadlock = Application.COMAddIns("GXLSForm.GXLSFormula").Object
CallXLSPadlockVBA = XLSPadlock.PLEvalVBA(ID, Param1)
End Function
Note: this function can be automatically inserted in your active VB module with the “Insert VBA Helper in VBE” button of the XLS Padlock’s VBA editor.
The inserted CallXLSPadlockVBA helper function here takes two parameters:
•ID is the name of the compiled sub or function you want to invoke (the one you pasted in the VBA compiler).
•Param1 is an optional parameter you want to pass to the compiled code.
Example: to invoke the “calculate” protected sub defined above in the VBA compiler, we open the original VBA module in Excel that 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
And we replace it with a call to the CallXLSPadlockVBA helper function:
Sub calculate()
res = CallXLSPadlockVBA("calculate", "")
End Sub
As you can see, we provide the name of the protected sub we want to call and leave the second parameter to "" because it is not used.
The res variable in the code above holds the result of the call. It is only really used if the invoked code is a function. In that situation, it will contain the result of the function.
If you click Run to execute calculate() in VBE, calculate() sub still works, even if you replaced it. It is because XLS Padlock can execute protected code even at design time.
Note: the helper uses the Application.COMAddIns("GXLSForm.GXLSFormula") object. This COM object is always made accessible in a protected workbook on any computer, even if XLS Padlock is not installed.
