Operators in VBA Excel

Types of operators in Excel VBA

In our scripts we will use both arithmetic expressions and logical expressions. For this purpose, we will be able to use operators in Excel VBA, we can put them into 3 groups:

  • arithmetic
  • logical
  • comparison

VBA operators: arithmetic

Arithmetic operators serve as the name suggests the implementation of operations in math. When creating a procedure containing mathematical operations, it may also be necessary to use parentheses. We write them in the standard way: “()”.

OperatorNameExample
+AdditionDim DblResult As Double DblResult = 9 + 7   
MsgBox DblResult
SubtractionDim DblResult As Double DblResult = 9 – 7   
MsgBox DblResult
*MultiplicationDim DblResult As Double DblResult = 9 * 7   
MsgBox DblResult
/DivisionDim DblResult As Double DblResult = 9 / 7   
MsgBox DblResult
\Division without restDim DblResult As Double DblResult = 9 \ 7   
MsgBox DblResult
^exponentiationDim DblResult As Double DblResult = 9 ^ 7   
MsgBox DblResult
modModulo (the rest from sharing)Dim DblResult As Double DblResult = 9 mod 7   
MsgBox DblResult
+ and &Adding stringsDim DblResult As Double DblResult = “string_1” & “string_2”   
MsgBox DblResult

As you can see, the “+” operator can also be used to connect strings to each other. For the same purpose, you can also use the operator: “&”. The example is described below.

'OfficeInside.Org
Sub SumOperators()
    MsgBox "string_1 " & "string_2"
    MsgBox "string_1 " + "string_2"
End Sub

If you want to use the minority and majority operators in relation to letters, the procedure will return true or false depending on which place the given letter is in the alphabet.

'OfficeInside.Org 
Sub LogOperators()
    MsgBox "A" < "B" 'returns True 
    MsgBox "A" = "B" 'returns False
End Sub
Types of operators in Excel VBA
Types of operators in Excel VBA

VBA operators: comparison

Comparison operators are used to compare selected data. Their description and application example are below. Using the following construction, the result will be True or False each time.

OperatorNameExample
=equalMsgBox 100 = 200
<>unevenMsgBox 100 <> 200
> =Bigger thanMsgBox 100 >= 200
<=Smaller thanMsgBox 100 <= 200
>BiggerMsgBox 100 > 200
<SmallerMsgBox 100 < 200

VBA operators: logical

The basis for a good script is correctly logic. We will use logical operators to save it.

OperatorNameExample
andConjunctionMsgBox (1 = 1) And (2 <> 3) ‘will return True
OrAlternativeMsgBox (1 = 1) Or (2 = 3) ‘will return True
xorAn alternative with one choice. Returns false only if more than one condition is trueMsgBox (1 = 1) Xor (2 <> 3) ‘will return False
notNegationMsgBox Not (0 = 1) ‘will return true

Tasks (You can save the result in a comment)

  1. Record any action using all the arithmetic operators at the same time. The operation should give in result 100.

More examples of Excel VBA basics can be found in the Excel VBA Course section and  Excel VBA How To section. If you have any questions about this chapter, you can post them on the forum without logging in.

Leave a comment

Your email address will not be published. Required fields are marked *

%d bloggers like this: