Formula Fundamentals

This section on fundamentals of writing formulas covers Case Sensitivity, Writing Comments, Returning a Value, Using Data Fields, Declaring Variables, Simple Data Types, Array Data Types, and Range Data Types. Each topic has its own section.

Case Sensitivity

Basic syntax is not case sensitive. The variable FirstName is the same as the variable firstname. Although these two variables are syntactically equivalent, it is recommended that you keep the case consistent so that your program is easier to read. If you always capitalize the first letter of a word in a variable, you should do so for all variables.

Writing Comments

Comments are designated by using a single apostrophe. You can also use the familiar REM keyword

‘This is a comment
REM This is also a comment

Line Terminators

Basic syntax assumes that each programming statement only takes a single line of text. The carriage return (hidden) marks the end of the line. If a statement needs more than one line, the line continuation character _ is used.

X = 5 ‘This is a single line
Y = "This takes " _
& "two lines"

Returning a Value

Formulas are always used to return a value. The data types of these return values can be Number, Currency, String, Boolean, Date, Time, and DateTime. You cannot return data types of range and array.

Formulas return values by assigning the value to the Formula variable. Formulas have to always return a value. The following code simply returns the value True.

Formula = True

If a formula has multiple statements that return a value, all the assignments must be of the same data type. Although there is no way to specifically state what that data type is, the compiler will compare all the assignments and check them for consistency.

‘The following IS NOT VALID due to the different data types returned
If Age > 65 Then
Formula = "Retired"
Else
Formula = Age
End If