Accessing Excel Objects from Compiled VBA Code

When writing code for the VBA compiler, you must explicitly use the Application object to access Excel’s object model.

For a detailed reference of the available properties and methods, see the Microsoft Office Dev Center documentation.

Examples #

Referencing a Range #

To reference a cell or a range of cells on your worksheet, you must prefix the Range object with Application.

• Standard VBA: Range("A1:A4").Value = 2
• Compiled VBA: Application.Range("A1:A4").Value = 2

Using Worksheet Functions #

To use Excel worksheet functions, you must use the Application.WorksheetFunction object.

Str1 = Application.WorksheetFunction.VLookup(Param1.ComboBox1, Application.Range("A2:C8"), 2, False)

Full Example: Converting a Macro #

Here is a standard VBA macro:

Sub test()
    Dim qty As Integer
    Dim price As Single, amount As Single
    Range("A5").Value = "Item"
    ActiveCell.Offset(1, 0).Select
    ActiveCell.Value = InputBox("Enter the name of item")
    ActiveCell.Offset(0, 1).Select
    price = InputBox("Enter the price")
    ActiveCell.Value = price
    ' ... and so on
End Sub

Here is the same code modified for the XLS Padlock VBA compiler. Note the addition of the Application object and the explicit parameters for InputBox.

Sub test(Param1)
    Dim qty As Integer
    Dim price As Single, amount As Single
    Application.Range("A5").Value = "Item"
    Application.ActiveCell.Offset(1, 0).Select
    Application.ActiveCell.Value = Application.InputBox("Enter the name of item", "Name", "")
    Application.ActiveCell.Offset(0, 1).Select             
    price = Application.InputBox("Enter the price", "Price", "")
    Application.ActiveCell.Value = price
    ' ... and so on
End Sub

Your original VBA module would then be modified to call the compiled version:

Sub test()
    res = CallXLSPadlockVBA("test", "")
End Sub