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;
- Arithmetic Operators: These operators are used to perform arithmetic operations such as adding, subtracting, dividing or multiplying numbers.
- String Operators: these are used to manipulate string data.
- Comparison Operators: These are operators that are used to compare values. Comparison operators include equal to, less than, greater than and not equal to.
- Logical Operators: These are used when working with more than one condition.
In this tutorial, you will learn-
As we see in previous tutorial, make sure your ribbon will have "Developer" icon as shown here.
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).
After that, click on Save button.
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.
|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,
- Click on the developer tab
- Click on Insert Drop down button
You will get the following drop down panel
If you hover the mouse over control, the name of the control will appear as shown below
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
We will now set the caption of the button to something more descriptive
- Right click on the equal button that we have just created
- Select properties menu
- You will get the following properties window
- 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.
These are used to perform arithmetic operations. The following table shows the arithmetic operators in VBA
|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|
Add a button to the Excel sheet as we show earlier, and then follow the following points
- 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
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
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
Click on Add operator
You will get the following results
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.
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.
Concatenate: This operator is used to concatenate strings together
"John " & "Doe"
MsgBox "John " & "Doe", vbOKOnly, "Concatenate Operator"
Executing the above code produces the following result
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.
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
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
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
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
Less than or equal to:
If x <= z Then
Returns true if x is less than or equal to z, else it returns false
Greater than or equal to:
If x >= Then
Returns true if x is greater than z, else it returns false
Equal Comparison Operator
If 2 = 1 Then MsgBox "True", vbOKOnly, "Equal Operator" Else MsgBox "False", vbOKOnly, "Equal Operator" End If
- "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
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.
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
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
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
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
The following table shows the properties that you need to change and the values that you need to update too.
AND Operator (0 = 0)
OR Operator (1 = 1) Or (5 = 0)
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
- "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
- "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
- "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
- 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