More Excel Visual Basic Programming Basics

In this post, we will give you some more of the basic (forgive the pun) building blocks of the Visual Basic programming language. We will cover formulas, assigning values, logical comparison, constants, comments, and continuation lines. We conclude with two small examples. One of them demonstrates our first Error Trap.

1.     Formulas

We have already used formulas in Excel spreadsheets, for example, =A1*B1.  Formulas in code follow similar rules.

The precedence of operators is

( )           Operations in brackets (parentheses) are performed first
^             Exponentiation (raising to a power)
–              Negation (changing sign)
* /           Multiplication and division
\              Integer division (result is an integer)
Mod        Modulo arithmetic (remainder: e.g.: 5 Mod 3 =2)
+ –           Addition and subtraction
&            String concatenation

2.     Assigning Values

You can assign the result of a formula to a variable using Let and “=” (equals), or simply “=”, e.g.:

Let r = Diam / 2
Area = PI * r ^ 2

The “=” is also used in logical comparisons:

3.     Logical Comparison

=            Equal to
<>          Not equal to
<            Less than
>            Greater than
<=          Less than or Equal to
>=          Greater than or Equal to
Like        Compare strings with wildcards, e.g.: Like “My*File”
Is            Compare Objects

You may use the Logical operators Not, And, Or, and Xor (exclusive Or).  There are also the lesser-used Eqv (logical equivalence) and Imp (implies).

4.     Constants

We may have certain variables that always take fixed values.  To stop them from being changed, you can instead define them, not as variables, but as Constants.  For this, we use the Const statement in the Declarations Section, e.g.:

Option Explicit
Const PI = 3.1416
Const e = 2.718282

You cannot set a Constant equal to a formula, nor can you change it programmatically.

To make a constant available to the entire program (all modules), use Global Const instead of just Const.  This must go in the declarations section, before any subprograms.

5.     Comments

For the sake of your own later sanity, as well as that of others trying to understand your code, it is a good idea to say what you are doing!  Whenever the program finds a single quote (‘) anywhere on a line, it ignores it and the rest of that line.  This allows you to put in explanations –do this copiously!  In the Visual Basic Editor, by default. comments show up in green.

6.     Continuation Lines (Best Avoided)

Visual Basic has no practical limit to the length of a line of code.  Even so, do try not to make your lines so long that they become unwieldy. (Remember that the VB Editor does not wrap lines the way some text editors do!)

You may be able to split a line by breaking a complicated formula up into parts.

If you are creating a long string to use as a message, you can build it up in sections.

However, sometimes there is no convenient way of splitting a line.  In that case, you can continue a line of code on the next line by using the continuation character.  To do this you end the first line with a space followed by an underscore (_).  You can have as many continuation lines like this as you like.

Thus, the code

Message$ = “We are About to compare Workbook1 and Workbook2. Do you want this sheet (Yes) or all sheets (No)? Note: To compare selected cells only, Cancel and run Sub CompareSelection instead!"

Can be broken up with continuation lines like this:

Message$ = "We are About to compare Workbook1 and Workbook2." _
& " Do you want this sheet (Yes) or all sheets (No)?" _
& " Note: To compare selected cells only," _
& " Cancel and run Sub CompareSelection instead!"

We use the ampersand, “&”, concatenate (add together) strings, as mentioned above.

But, even better, would be not to use Continuation Lines at all.  Rather build up the message using separate statements.  The result will be more readable if you insert the line feed character, vbLf, too:

Msg$ = "We are About to compare Workbook1 and Workbook2."
Msg$ = Msg$ & vbLf & "Do you want this sheet (Yes) or all sheets (No)?"
Msg$ = Msg$ & vbLf & vbLf & "Note: To compare selected cells only,"
Msg$ = Msg$ & " Cancel and run Sub CompareSelection instead!" 

A benefit of not using Continuation Lines is that VB checks each line’s syntax.  If you have separate lines and one has an error, it will show up easily.  If you use Continuation Lines, the entire block is in effect one line.  An error anywhere in it will make the whole block show up in red.

7.     Simple Example

In a new blank Excel Workbook, press Alt+F11 to go to the Visual Basic Editor.

Double-click Sheet1 in the VBA Project to open the Code Window.

Type in the following code:

Sub Invert()  ' Invert a Number:
  Dim Numb#
  Numb# = InputBox("Input a Number to Invert")
  MsgBox Numb# & " Inverted = " & (1 / Numb#)
End Sub

Press F5 to run it.  Try it a few times with different values.

8.     Example with Error Trap

What happens if you use zero or text?

To avoid this, copy the Sub and change it as follows:

Sub Invert1() ' Invert Numeric Text, with primitive Error trap:
  Dim Text1$
  Text1$ = InputBox("Input a Number to Invert")
  If Val(Text1$) = 0 Then MsgBox "Can't divide by Zero!": Exit Sub
  MsgBox Text1$ & " Inverted = " & (1 / Val(Text1$))
End Sub

We will take this example further.  Now that we have looked at those basics, we are ready to look at program flow control.