VBA Compiler Syntax Reference

This document provides a reference for the VBA syntax supported by the XLS Padlock VBA compiler.

Script structure is made of function and sub declarations.

SUB DoSomething
   CallSomething
END SUB

FUNCTION MyFunction
   MyFunction = "Ok!"
END FUNCTION

Statements in a single line can be separated by the : character.

Comments can be inserted inside a script. You can use the ' character or REM. The comment will extend to the end of the line.

' This is a comment before ShowMessage
ShowMessage("Ok")

REM This is another comment
ShowMessage("More ok!")

Identifier names (variables, functions, procedures, etc.) must begin with a character (a-z, A-Z) or an underscore _. They can be followed by alphanumeric characters or underscores. Names cannot contain any other character or spaces.

There is no need to declare variables in a script, but if you want to, you can declare a variable using the DIM directive.

SUB Msg(Param1)
   DIM S
   S = "Hello world!"
   ShowMessage(S)
END SUB

You can also declare global variables as PRIVATE or PUBLIC.

PRIVATE A
PUBLIC B
B = 0
A = B + 1
ShowMessage(A)

Variables declared with DIM are public by default. Private variables are not accessible from other scripts. Variables can also be initialized upon declaration:

DIM A = "Hello world"
DIM B As Integer = 5

Assign statements use the = operator to assign a value or expression result to a variable or object property.

MyVar = 2
Application.Range("C4").Value = "This " + "is ok."

The compiler offers basic support for array constructors and variant arrays. To construct an array, use [ and ] characters. You can construct a multi-index array by nesting array constructors.

Arrays in the compiler are 0-based index.

NewArray = [ 2,4,6,8 ]
Num = NewArray[1] 'Num receives 4

MultiArray = [ ["green","red","blue"] , ["apple","orange","lemon"] ]
Str = MultiArray[0,2] 'Str receives "blue"
MultiArray[1,1] = "new orange"

Dynamic arrays:

' Create a dynamic array
DIM PTIM = VarArrayCreate([0,3000,0,5], 12)
' Assign a value:
PTIM[1,2] = 1530

Strings, arrays, and array properties can be indexed using [ and ] characters. For example, if Str is a string variable, the expression Str[3] returns the third character in the string.

MyChar = MyStr[2]
MyStr[1] = "A"
MyArray[1,2] = 1530

The Basic syntax supports:

• Declarations: SUB...END SUB, FUNCTION...END FUNCTION
• Directives: BYREF, DIM
• Conditionals: IF...THEN...ELSE...ELSEIF...END IF, SELECT CASE...END SELECT
• Loops: FOR...TO...STEP...NEXT, DO...WHILE...LOOP, DO...LOOP...WHILE, DO...UNTIL...LOOP, DO...LOOP...UNTIL
• Operators: ^, *, /, AND, +, -, OR, <>, >=, <=, =, >, <, DIV, MOD, XOR, SHL, SHR
• Error Handling: TRY...EXCEPT, TRY...FINALLY
• Other: EXIT, array constructors [1, 2, 3], object access ObjectName.Property

There are two forms: IF...THEN...END IF and IF...THEN...ELSE...END IF. If the expression is true, the THEN statements are executed. If it is false, the ELSE statements are executed (if present).

IF J <> 0 THEN
   Result = I/J
END IF

IF J = 0 THEN
   Exit
ELSE
   Result = I/J
END IF

If the statement is on a single line, you don’t need END IF:

IF J <> 0 THEN Result = I/J

If the selectorExpression matches one of the caseexpr expressions, the respective statements will be executed. Otherwise, the CASE ELSE statement will be executed.

SELECT CASE uppercase(Fruit)
  CASE "lime"
    ShowMessage("green")
  CASE "orange"
    ShowMessage("orange")
  CASE ELSE
    ShowMessage("black")
END SELECT

The FOR statement repeats execution of statements until a counter reaches a final value.

FOR counter = initialValue TO finalValue STEP stepValue
  Statements
NEXT

The STEP part is optional; if omitted, the step value is 1.

FOR c = 1 TO 10 STEP 2
 a = a + c
NEXT

A WHILE statement repeats statements as long as a control condition is true. The condition is evaluated before the statements are executed.

WHILE (Data[I] <> X)
 I = I + 1
END WHILE

Statements are executed WHILE an expression is true, or UNTIL an expression is true. The condition can be tested before or after the iteration.

' Condition tested after
DO
   K = I mod J
   I = J
   J = K
LOOP UNTIL J = 0

' Condition tested before
DO WHILE I < 0
   ...
LOOP

Declarations are similar to standard Basic. To return a value from a function, assign it to the implicit variable with the same name as the function, or use the Return statement. Parameters can be passed by reference using the BYREF directive.

SUB HelloWord
   ShowMessage("Hello world!")
END SUB

FUNCTION Max(A,B)
   IF A > B THEN
      MAX = A
   ELSE
      MAX = B
   END IF
END FUNCTION

SUB SwapValues(BYREF A, B)
  DIM TEMP
  TEMP = A
  A = B
  B = TEMP
END SUB

Subs and functions are public by default but can be declared as PRIVATE.