VBA Operators

An operator is a program element that is used either as an expression or a statement. Operators are used to manipulate data. For example, "+" symbol is an operator that is used to sum up numeric data. In addition to that, operators are also used to compare data. For example, greater than symbol ">" is used to compare two numbers.

VBA has four types of operators namely;

  1. Arithmetic Operators: These operators are used to perform arithmetic operations such as adding, subtracting, dividing or multiplying numbers.
  2. String Operators: these are used to manipulate string data.
  3. Comparison Operators: These are operators that are used to compare values. Comparison operators include equal to, less than, greater than and not equal to.
  4. Logical Operators: These are used when working with more than one condition.

In this tutorial, you will learn-

Configure Microsoft Excel

As we see in previous tutorial, make sure your ribbon will have "Developer" icon as shown here.

VBA Operators

Now, rename sheet1 to "VBA Operator" as shown in screen-shot below. Save the workbook in an Excel Macro-Enabled Workbook (*.xlsm) file extension. (see the image below).

VBA Operators

After that, click on Save button.

Creating VBA GUI controls in Excel

GUI is the acronym for Graphical User Interface. The GUI is the part of the program that the user interacts with. A GUI is made up of controls. The following table shows some of the most commonly used GUI controls in VBA.

S/N Control Description
1 Command Used to execute code
2 Combo Box Used to present a drop down list to the users
3 CheckBox Used for true or false values. Multiple check boxes can have true value at the same time.
4 List Box Used to present a simple list to the users
5 Text Box Used to accept user input
6 Scroll Bar Used for to provide a scrollbar for navigation purposes.
7 Spin Button Used to increment or decrease numeric values by clicking on up or down arrows that appear in the spin button
8 Option Button Used for true or false values. Only one option button can have a true value at a time.
9 Label Used to display static text to the user
10 Image Used to display images
11 Toggle Button Button that has pressed and un-pressed states.

In the GUI control,

  1. Click on the developer tab
  2. Click on Insert Drop down button

You will get the following drop down panel

VBA Operators

If you hover the mouse over control, the name of the control will appear as shown below

VBA Operators

Adding GUI controls to a spreadsheet

We will now add a command button to our workbook, for that we have to

  • Click on Insert drop down
  • Click on Command Button (ActiveX Control) as shown in the image above
  • Drag and draw the button on the sheet

VBA Operators

Setting GUI control properties

We will now set the caption of the button to something more descriptive

VBA Operators

  1. Right click on the equal button that we have just created
  2. Select properties menu
  3. You will get the following properties window

VBA Operators

  • Set the name property to btnButton. The name property is used to identify the control in the code window. btn is the prefix for the button.
  • Set the Caption property to Click Me. The text in the caption property is what the users will see on the button.

Close the window when you are done.

You will get the following results.

VBA Operators

VBA Arithmetic Operators

These are used to perform arithmetic operations. The following table shows the arithmetic operators in VBA

S/N Operator Description Example Output
1 + Addition: This operator is used to add up numbers 2 + 2 4
2 - Subtraction: This operator is used to subtract numbers 5 - 3 2
3 * Multiplication: This operator is used to multiply numbers 3 * 2 6
4 / Division: This operator is used to divide numbers 9 / 3 3
5 ^ Exponentiation: This operator is used to raise a number to the power of another number 2^3 8
6 mod Modulus Operator: Divides a number and returns the reminder 10 mod 3 1

VBA Arithmetic Operators Example

Add a button to the Excel sheet as we show earlier, and then follow the following points

VBA Operators

  • Change the name property to btnAdd
  • Change the caption property to Add Operator
  • Right click on the button
  • Select view code
  • You will get the following code window

VBA Operators

Enter the following code in between Private Sub btnAdd_Click() and End Sub

  • Dim x As Integer, z As Integer
    • x = 2
    • z = 3
  • MsgBox x + z, vbOKOnly, "Addition Operator"
  • Click on save button
  • Close the code editor window

Let's now execute our code

On the ribbon bar, look for the button Design Mode

VBA Operators

If the button is in active state (green background colour), then it's in design mode. You cannot execute code in this state. If it is not in the active state (white background color), then it allows you to run the code.

Click on Design Mode button

The button should now appear as follows

VBA Operators

Click on Add operator

You will get the following results

VBA Operators

Arithmetic Operators Tutorial exercise

The best way to learn is by practicing. Follow the above steps to create buttons for subtraction, division, multiplication and exponentiation.

Write the code for the buttons and test them to see if the code executes.

VBA String Operators

String data is used to hold data that is made up of numbers, characters, and symbols. "Jul-2015" is an example of a string data. It is made up of

  • Characters (Jul)
  • Symbol (-)
  • Numbers (2015)

String operators are used to manipulate string data. For example, you can concatenate the value of July-2015 from the first 3 letters of the month and the year like "Jul-2015".

The following table shows the concatenation string operator.

S/N

Operator

Description

Example

Output

1

&

Concatenate: This operator is used to concatenate strings together

"John " & "Doe"

John Doe

Example Source Code

VBA Operators

MsgBox "John " & "Doe", vbOKOnly, "Concatenate Operator"

Executing the above code produces the following result

VBA Operators

VBA Comparison operators

The following table shows VBA Comparison Operators.

Comparison operators are used to compare values for validation purposes. Let's say you are developing a simple point of sale application. In this application, you want to validate the values entered before you post. In such cases, you can use comparison operators. This operator will check against the negative numbers or to ensure that the amount paid does not exceed the billed amount. Comparison operators come in handy in such situations.

S/N

Operator

Description

Example

Output

1

=

Equal: checks if two values are equal. It is also used as an assignment operator

If x = z Then

Returns true if they are equal, else it returns false

2

<

Less than: This operator is used to subtract numbers

If x < z Then

Returns true if x is less than z, else it returns false

3

>

Greater than: This operator is used to multiply numbers

If x > z Then

Returns true if x is greater than z, else it returns false

4

<>

Not equal to: This operator is used to divide numbers

If x <> z Then

Returns true if they are not equal, else it returns false

5

<=

Less than or equal to:

If x <= z Then

Returns true if x is less than or equal to z, else it returns false

6

>=

Greater than or equal to:

If x >= Then

Returns true if x is greater than z, else it returns false

Example source code

Equal Comparison Operator

    If 2 = 1 Then
            MsgBox "True", vbOKOnly, "Equal Operator"
        Else
            MsgBox "False", vbOKOnly, "Equal Operator"
    End If

HERE,

  • "If 2 = 1 Then… Else… End If" uses the if statement to evaluate the condition "2 = 1"
  • "MsgBox…" Is a built-in function that displays a message box.
    • The first parameter "True" or "False" is what will be displayed in the message box. In our example, 2 is not equal to 1, therefore, it will show "false" in the msg box.
    • The second parameter "vbOKOnly" is the button that is displayed in the message box
    • The third parameter "Equal Operator" is the title of the message box.

Executing the above code gives the following results

VBA Operators

Logical Operators

Let's say you want to process a customer order. For that, you want to first check to see if the ordered product exists or not. If it does, you also want to check if the quantity on hand is enough. Logical operators come in handy in such cases. Logical operators are used to evaluate more than one condition.

S/N

Operator

Description

Example

Output

1

AND

AND: This is used to combine more than one condition. If all the conditions are true, AND evaluates to true. If any of the condition is false, AND evaluates to false

If true = true AND false = true THEN

false

2

OR

OR: This is used to combine more than one condition. If any of the conditions evaluate to true, OR returns true. If all of them are false, OR returns false

If true = true OR true = false THEN

true

3

NOT

NOT: This one works like an inverse function. If the condition is true, it returns false, and if a condition is false, it returns true.

If NOT (true) Then

false

Example Source Code

For the sake of simplicity, we will be comparing hard coded numbers.

Add ActiveX buttons to the sheet from the "Insert option."

Set the properties as shown in the image below

VBA Operators

The following table shows the properties that you need to change and the values that you need to update too.

S/N

Control

Property

Value

1

CommandButton1

Name

btnAND

Caption

AND Operator (0 = 0)

2

CommandButton2

Name

btnOR

Caption

OR Operator (1 = 1) Or (5 = 0)

3

CommandButton3

Name

btnNOT

Caption

NOT Operator Not (0 = )

Add the following code to btnAND_Click

Private Sub btnAND_Click()
    If (1 = 1) And (0 = 0) Then
            MsgBox "AND evaluated to TRUE", vbOKOnly, "AND operator"
        Else
            MsgBox "AND evaluated to FALSE", vbOKOnly, "AND operator"
    End If
End Sub

HERE,

  • "If (1 = 1) And (0 = 0) Then" the if statement uses the AND logical operator to combine two conditions (1 = 1) And (0 = 0). If both conditions are true, the code above 'Else' keyword is executed. If both conditions are not true, the code below 'Else' keyword is executed.

Add the following code to btnOR_Click

Private Sub btnOR_Click()
    If (1 = 1) Or (5 = 0) Then
            MsgBox "OR evaluated to TRUE", vbOKOnly, "OR operator"
        Else
            MsgBox "OR evaluated to FALSE", vbOKOnly, "OR operator"
    End If
End Sub

HERE,

  • "If (1 = 1) Or (5 = 0) Then" the if statement uses the OR logical operator to combine two conditions (1 = 1) And (5 = 0). If any of the conditions is true, the code above Else keyword is executed. If both conditions are false, the code below Else keyword is executed.

Add the following code to btnNOT_Click

Private Sub btnNOT_Click()
    If Not (0 = 0) Then
            MsgBox "NOT evaluated to TRUE", vbOKOnly, "NOT operator"
        Else
            MsgBox "NOT evaluated to FALSE", vbOKOnly, "NOT operator"
    End If
End Sub

HERE,

  • "If Not (0 = 0) Then" the if statement uses the NOT logical operator to negate the result of the condition. If the conditions is true, the code below 'Else' keyword is executed. If the condition is true, the code above Else keyword is executed.

Download Excel containing above code

Download the above Excel Code

Summary:

  • An operator is a program element that is used either as an expression or a statement.
  • VBA has four types of operators
    • Arithmetic operators
    • String operators
    • Comparison operators
    • Logical operators