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