Accessing Excel objects from compiled VBA code

VBA Code Protection > Accessing Excel objects from compiled VBA code

The compiler requires use of the Application object if you want to access Excel objects.

Application’s properties and methods accessible by the XLS Padlock VBA compiler are described in Microsoft Office Dev Center documentation: http://msdn.microsoft.com/en-us/library/office/ff194565.aspx

Examples

The Range object is the representation of a cell (or cells) on your worksheet. Use Application.Range

In VBA: Range("A1:A4").Value = 2

In compiled VBA: Application.Range("A1:A4").Value = 2

Excel worksheet functions can be used thanks to Application.WorksheetFunction

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

Excel functions like InputBox require all parameters to be specified (default parameters are ignored). For InputBox, three parameters are required.

Original code:

Sub test()

Dim qty As Integer

Dim price, amount As Single

Range("A5") = "Item"

Range("B5") = "UnitPrice"

Range("C5") = "Quantity"

Range("D5") = "Amount"

ActiveCell.Offset(1, 0).Select

ActiveCell = InputBox("Enter the name of item")

ActiveCell.Offset(0, 1).Select

price = InputBox("Enter the price")

ActiveCell = price

ActiveCell.Offset(0, 1).Select

qty = InputBox("Enter the qty")

ActiveCell = qty

ActiveCell.Offset(0, 1).Select

amount = price * qty

ActiveCell = amount

ActiveCell.Offset(0, -3).Select

End Sub

Code entered in the XLS Padlock VBA editor and modified to be compatible with XLS Padlock compiler:

Sub test(Param1)

Dim qty As Integer

Dim price, amount As Single

Application.Range("A5").Value = "Item"

Application.Range("B5").Value = "UnitPrice"

Application.Range("C5").Value = "Quantity"

Application.Range("D5").Value = "Amount"

Application.ActiveCell.Offset(1, 0).Select

Application.ActiveCell = Application.InputBox("Enter the name of item", "Name", "")

Application.ActiveCell.Offset(0, 1).Select

price = Application.InputBox("Enter the price", "Price", "")

Application.ActiveCell = price

Application.ActiveCell.Offset(0, 1).Select

qty = Application.InputBox("Enter the qty", "Qty", "")

Application.ActiveCell = qty

Application.ActiveCell.Offset(0, 1).Select

amount = price * qty

Application.ActiveCell = amount

Application.ActiveCell.Offset(0, -3).Select

End Sub

Then in the original VB module, the sub test() is replaced by:

Sub test()

res = CallXLSPadlockVBA("test", "")

End Sub

Another example which requires all parameters:

Application.Worksheets("My data").Protect(1454511212,False,False,False,True,False,False,False,False,False,False,False,False,False,False,False)

Keywords and operators