Skip to content
Tutorials Center
My learning

Topics

Lesson 7 of 37 ยท Language Basics

Operators and Expressions in VBA

Learn VBA arithmetic, comparison, concatenation and logical operators, operator precedence, Mod and integer division, Like, Is and bitwise And/Or/Not.

An expression combines values, variables and operators into a new value. VBA has the usual operators plus a few surprises: \ for integer division, Mod for the remainder, & for joining text and word operators And, Or and Not.

Arithmetic operators

Operator Meaning Example Result
+ add 7 + 2 9
- subtract 7 - 2 5
* multiply 7 * 2 14
/ divide (always decimal) 7 / 2 3.5
\ integer division 7 \ 2 3
Mod remainder 7 Mod 2 1
^ power 2 ^ 10 1024
VB
Sub ArithmeticDemo()
    Debug.Print 7 / 2      ' 3.5
    Debug.Print 7 \ 2      ' 3
    Debug.Print 7 Mod 2    ' 1
    Debug.Print -7 Mod 2   ' -1 (sign follows the left operand)
    Debug.Print 2 ^ 3 ^ 2  ' 64 (left to right: (2^3)^2)
End Sub

Note that ^ is evaluated left to right, unlike in mathematics. Mod and \ round operands to whole numbers first, so 7.6 Mod 2 is 8 Mod 2, which is 0.

Dividing by zero raises run-time error 11. Check the divisor first.

Precedence

When you mix operators, VBA evaluates in this order (top first):

  1. Parentheses
  2. ^
  3. Unary minus (-x)
  4. * and /
  5. \
  6. Mod
  7. + and -
  8. &
  9. Comparisons (=, <>, <, >, <=, >=, Like, Is)
  10. Not, then And, then Or, then Xor, Eqv, Imp

Use parentheses whenever a reader could doubt the order. 1 + 2 * 3 is 7 but (1 + 2) * 3 is 9.

Comparison operators

=, <> (not equal), <, >, <=, >=. They return True or False. Note that = means both assignment and comparison, depending on context:

VB
Dim x As Long, ok As Boolean
x = 5               ' assignment
ok = (x = 5)        ' comparison, ok is True

Text comparisons are case sensitive by default ("a" = "A" is False). Put Option Compare Text at the top of a module to make comparisons ignore case, or compare with UCase/StrComp(a, b, vbTextCompare).

Like

Like matches patterns: ? is one character, * any run, # one digit, [abc] one of the listed characters, [!abc] none of them.

VB
Debug.Print "report2026" Like "report####"   ' True
Debug.Print "cat" Like "c?t"                 ' True
Debug.Print "invoice.xlsx" Like "*.xls*"     ' True

Is

Is compares object references, never values: If obj1 Is obj2 Then, and If obj Is Nothing Then tests for an unset object.

Concatenation

The & operator joins text and converts numbers for you:

VB
Dim s As String
s = "Total: " & 42 & " items"     ' Total: 42 items

+ also concatenates two strings but adds numbers if either side is numeric, which causes surprises ("3" + 4 is 7). Use & for text.

Logical operators

Operator True when
And both sides True
Or at least one side True
Not the value is False
Xor exactly one is True
VB
Dim age As Long, member As Boolean
age = 20: member = False
Debug.Print (age >= 18) And Not member     ' True
Debug.Print (age < 18) Or member           ' False

VBA does not short-circuit: in If a And b, both are always evaluated. So If x <> 0 And 10 / x > 1 still divides by zero. Use nested If statements instead (see If...Then...Else). There are also AndAlso-like patterns, but not in VBA.

And, Or, Not also work bitwise on numbers: 6 And 3 is 2, 5 Or 2 is 7. This is how option flags like vbYesNo + vbQuestion combine.

Assignment shortcuts

VBA has no +=. Write the whole thing: count = count + 1. Objects are assigned with Set (see Objects).

Stand-in: precedence and integer division

The JavaScript stand-in (not VBA) replays several VBA rules so you can try variations.

JS Stand-in: VBA operator rules

Press Run (or Ctrl + Enter) to see the console output here.

No output.

Common mistakes

  • Using / when you wanted a whole number; use \ or Int().
  • Using + to join text with numbers.
  • Expecting And to short-circuit.
  • Comparing floating-point values with =: 0.1 + 0.2 = 0.3 is False. Compare Abs(a - b) < 0.000001 instead, or use Currency for money.

Check your understanding

Quick check ยท 5 questions

of 5 answered You scored / 5 ยท perfect!

  1. 1. What is 17 Mod 5?

    17 = 3 * 5 + 2, so the remainder is 2.
  2. 2. What does 9 \ 2 return?

    The backslash is integer division and drops the fraction.
  3. 3. Which operator should you use to join text?

    The ampersand always concatenates and converts numbers to text.
  4. 4. Does VBA short-circuit And?

    VBA evaluates both operands, so guard risky expressions with nested If statements.
  5. 5. What does the pattern "#?*" with Like demand at the start of a string?

    matches exactly one digit.

Summary

  • / gives decimals, \ whole numbers, Mod the remainder, ^ powers (left to right).
  • & joins text; avoid + for that.
  • Like matches patterns, Is compares objects.
  • And/Or/Not do not short-circuit; use parentheses to be clear.

Next: Working with Text