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

 


Copyright © 2021 G.D.G. Software