assignment operator vba

• Advertise With Us
• Excel Forum
• Commercial Services

Operators in Excel VBA

The signs and keywords we use to operate variable in VBA are called VBA Operators. For example, in the lines below lines =, +, >, & are operators.

There are five types of operators in any programming language, so in VBA 1. Assignment Operator 2. Arithmetic Operator 3. Comparison Operator 4. Logical Operator 5. Concatenation Operator

Let us take a look at each type of Operator in Excel VBA.

1. Assignment Operator (=)

This is the first operator you are going to use in any programming language. In VBA, it is used to assign values to variables. It is "=" (equals to).

We use this operator to assign values to variables in excel VBA. It is also used as a comparison operator in  VBA. We will talk about it later in this tutorial.

One simple example is

In the above example, we first use the assignment operator "=" to assign value to variable "a" and then use the "=" operator to assign value of "a" to value of Range("A1").

2. Arithmetic Operators

The arithmetic operators are the same operators that we are using since childhood to do simple calculations. In Excel VBA, these operators are used for doing calculations on variables and numbers.  They are:

(+) Arithmetic Addition:  This operator is used for adding two or more numbers or values of two or more variables. The lines below sum ups the values of the two variables and prints it on the Cell "A1".

A1 will have 22. This operator also works as a concatenation operator. If both, a and b will have the string values then the + operator will work as a concatenation operator. We will see how, later in the article.

(-) Arithmetic Subtraction:  This operator is used for subtracting one value from another value of  variables. The line below subtracts the value of a from b and prints it in the Cell "A1".

A1 on the sheet will have 2.

(*) Arithmetic multiplication:  This operator is used for multiplying or getting product of two or more numbers or values of two or more variables. The below lines multiplies the values of the two variables and prints it on the Cell "A1".

Cell A1 will have value 120.

(/) Arithmetic Division:  This operator is used dividing one value from another. The line below divides the value b by variable a and prints it on the Cell "A1".

Cell A1 will have value 1.2.

(Mod) Arithmetic Remainder Operator in VBA: While most PLs use the % (modulus) for getting the remainder, in VBA we use the keyword Mod.   This operator is used to get the reminder after dividing one value from another. The line below divides the value b by variable a and prints the reminder value in cell A1.

Cell A1 will have value 2.

(^ ) Arithmetic Exponential:  This operator is used to get the exponent of one value to another.  The lines below get us the value of 3 for exponential 4.

Cell A1 will have value 81 (3x3x3x3).

These operators in VBA follow the BODMAS rule. There are only 6 arithmetic operators in VBA. There are some operators that act as two types of operators like +. You will learn about them as you go through this tutorial.

3. Comparison Operators

When we want to compare two values in VBA, we use the comparison operators. The result of comparison operator is always Boolean. If the statement is true then the result in TRUE. If the statement is false then the value is False. These operators are frequently used in decision making in VBA . Let's see what they are:

(=) Equals:  Yes, the = (equals to) sign is also used as comparison operator in VBA. When we want to check if the two variables are equal or not then we use this comparison operator.

In the above example, we use the If statement and check if the values of a and b are equal. They are clearly not. Hence, the Else statement is printed.

You can check it by simply using the statement.

This will print False in the immediate window.

(<) Less Than:  This is used to check if the left value is less than right value or not.

In the above example, we check if the value of a is less than b. Since this is True, the if statement gets executed and Else doesn't.

This will print True in the immediate window.

(<=) Less Than or Equal to:  This is used to check if the left value is less than or equal to the right value or not.

In the above example, we check if the value of a is less than b. Since this is True, the If statement gets executed and Else doesn't.

(>) Greater Than:  This is used to check if the left value is greater than the right value or not.

In the above example, we check if the value of a is greater than b. Since this is False, the if statement won't get executed and Else does.

(>=) Greater Than:  This  is used to check if the left value is greater than or equal to the right value or not.

In the above example, we check if the value of a is greater than or equal to b. Since this is False, the if statement won't get executed and Else does.

(<>) Not Equal To:  This is used to check if the left value is not equal to the right value.

In the above example, we check if the value of a is not equal to b. Since this is True, the If statement will get executed and Else won't.

The above six operators are called comparison operators as we use them to compare two values or variables. You will be using them a lot in VBA to make decisions in VBA .

4. Logical Operators

Logical operators are used for doing logical operations on one or more variables. The result of such operations always results in TRUE or FALSE. The logical operators often (not always) are used to check more than one condition.

Below are the logical operators we use in VBA:

VBA AND Operator( And or *):  The VBA AND operator are used to ensure that the conditions on its left and right are True. If any of the conditions is False then the whole statement will result in False. We use the keyword And or the sign * (asterisk) for AND operations.

Check the below statement:

When we run the above snippet, the second if statement doesn't get executed. Because the first statement on the left is true, but the statement on the right is False. Hence the whole statement returns False.

In most of the languages, the symbol & is used as AND operator but not in VBA. In VBA you can use the multiplication operator * (asterisk) as AND operator to ensure that both conditions are true.

In the above example, you can use the below if statement to do the same.

You must use parenthesis to separate the conditional statements. Otherwise, the statements will follow the BODMAS rule and the result will inaccurate.

Q1: What will be the output of the below statement? Let me know in the comments section below:

When the value of a is 15 and b is 30. Write down in the comments section below.

VBA OR Operator (Or or +): The OR operator is used when we want to ensure that either the Left condition is TRUE or the Right condition is TRUE. If any of the two conditions is true then the result will be true. We use the OR keyword between two boolean statements. You can also use the + (plus) sign for OR operation. Just make sure that you use parenthesis properly to make statements clear while using + sign.

Examine the below code:

When we execute the above code, the first message is displayed and the second message is skipped. Because a is less than 15 and b is not. It only takes one condition to be True with the OR operator to get the output as true.

You write the statement as:

Q2: What will be the output of the below statement? Let me know in the comments section below:

When the value of a is 10 and b is 5. Write down in the comments section below.

VBA NOT Operator (Not): The Not operator is used to inverse the boolean value. In other words, the statement with Not operator only returns True if the statement before it is False . For example, if you use the Not keyword before a True Statement, it will result in False and vise-versa. In VBA, we use the Not keyword to check if something is not what True or False (?).

In the above code, statement  Not a = b  will return false. Initially, a=b is true but as we have used Not before it, the result is inversed and statement turns into False. In the above example, we have used the only statements before it. You can have as many statements as you want before Not operator. Just enclose them into parenthesis.

Q3: What will be the output of the below statement? Let me know in the comments section below:

5. Concatenation Operators ( & or +)

These operators are used to concatenate strings. The symbol & is used to concatenate texts. It is the recommended operator to concatenate strings. However, you can also use the + sign to concatenate.

The plus sign only concatenates two variables when both of them are strings. If any of the string is non-string the + sign will work as an addition operator.

See the below code:

In the above code, the first two lines will work perfectly fine.  The third line will run into an error because we are trying to add text with numbers. To concatenate numbers we always use the & (amp).

Multifunctional Operators in VBA

Through the above examples, you must have learned that there are many operators that work differently in different situations.

The + sign works as the addition operator while working with numbers. While working with boolean values, the plus sign works as Or operator in VBA. When used with string values the plus operator works as a concatenation operator. The lines below are valid:

The asterisk (*) sign works as a multiplication operator when operands are numeric. When the operands are boolean the asterisk works as And Operator.

The below lines of code work perfectly fine.

So yeah guys, this all about the operators in VBA. I hope it was helpful. If you have any doubts regarding the operators in VBA, ask in the comment section below.

Related Articles:

The If ElseIf Statement in VBA |In If ElseIf statement, the next condition is only checked when the previous condition falls. When a condition is matched, the code in that block is executed and the control exits the If block.

Excel VBA Variable Scope  | VBA too has scope specifiers. These scope specifiers can be used to set the visibility/scope of a variable in Excel VBA.

VBA Select Case Statement  | The Select Case Statements are useful when you have too many conditions to check. They are excellent replacements of multiple If ElseIf statements.

Using Loop in VBA in Microsoft Excel  | The loops in VBA enable us to do a similar task over and over without repetition of code. There are 3 types of loops in Excel VBA.

ByRef and ByVal Arguments  |When an argument is passed as a ByRef argument to a different sub or function, the reference of the actual variable is sent. Any changes made into the copy of the variable, will reflect in original argument.

Display A Message On The Excel VBA Status Bar  The status bar in excel can be used as a code monitor. When your VBA code is lengthy and you do several tasks using VBA, you often disable the screen update so that you don’t see that screen flickering

Turn Off Warning Messages Using VBA In Microsoft Excel 2016 |This code not only disables VBA alerts but also increases the time efficiency of the code. Let’s see how.

Popular Articles:

50 Excel Shortcuts to Increase Your Productivity  | Get faster at your task. These 50 shortcuts will make you work even faster on Excel.

The VLOOKUP Function in Excel  | This is one of the most used and popular functions of excel that is used to lookup value from different ranges and sheets.

COUNTIF in Excel 2016  | Count values with conditions using this amazing function. You don't need to filter your data to count specific value. Countif function is essential to prepare your dashboard.

How to Use SUMIF Function in Excel  | This is another dashboard essential function. This helps you sum up values on specific conditions.

Leave a Reply Cancel reply

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

To avoid automated spam,Please enter the value * 1 × nine =

Related Excel Tips

Delete all Comments in Microsoft Excel...

Private Profile Strings using Words System.Pr...

Eliminate screen flashing while running a mac...

Display a record with a double click using VB...

Check if a VBProject is protected using VBA i...

• Basic Excel
• Excel 365 Functions
• Excel Business Templates and Dashboards
• Excel Dashboards
• Excel Date and Time
• Excel Errors
• Excel Functions
• Excel Functions List
• Excel General
• Excel Macros and VBA
• Excel Spanish
• Excel Text, Editing and Format
• Excel Tips and Tricks

The applications/code on this site are distributed as is and without warranties or liability. In no event shall the owner of the copyrights, or the authors of the applications/code be liable for any loss of profit, any problems or any damage resulting from the use or evaluation of the applications/code.

• Excel Editing
• Excel Format

Get latest updates from exceltip in your mail.

Google serves cookies to analyse traffic to this site. Information about your use of our site is shared with Google for that purpose

Privacy Overview

Strictly necessary cookies.

This website uses cookies so that we can provide you with the best user experience possible. Cookie information is stored in your browser and performs functions such as recognising you when you return to our website and helping our team to understand which sections of the website you find most interesting and useful.

You can adjust all of your cookie settings by navigating the tabs on the left hand side.

Strictly Necessary Cookie should be enabled at all times so that we can save your preferences for cookie settings.

If you disable this cookie, we will not be able to save your preferences. This means that every time you visit this website you will need to enable or disable cookies again.

VBA Assignment Statements And Operators

3 minute read

An assignment statement is a VBA statement that assigns the result of an expression to a variable or an object.

In a book I read Excel’s Help system defines the term expression as:

“Combination of keywords, operators, variables, and constants that yields a string, number, or object. An expression can be used to perform a calculation, manipulate characters, or test data.”

Much of your work in VBA involves developing (and debugging) expressions.

If you know how to create simple formulas in Excel, you’ll have no trouble creating expressions.

With a formula, Excel displays the result in a cell.

A VBA expression, on the other hand, can be assigned to a variable.

For understanding purpose, I used Excel as an example. Please don’t get confused with it.

In the assignment statement examples that follow, the expressions are to the right of the equal sign:

Expressions can be as complex as you need them to be; use the line continuation character (a space followed by an underscore) to make lengthy expressions easier to read.

As you can see in the VBA uses the equal sign as its assignment operator .

You’re probably accustomed to using an equal sign as a mathematical symbol for equality.

Therefore, an assignment statement like the following may cause you to raise your eyebrows:

How can the variable x be equal to itself plus 1?

Answer: It can’t.

In this case, the assignment statement is increasing the value of x by 1 .

Just remember that an assignment uses the equal sign as an operator , not a symbol of equality.

Smooth Operators

Operators play a major role in VBA. Besides the assignment operator i.e. equal sign (discussed in the previous topic), VBA provides several other operators.

Below table lists these operators.

The term concatenation is programmer speak for “join together”.

Thus, if you concatenate strings, you are combining strings to make a new and improved string.

VBA also provides a full set of logical operators. Below table, shows some of logical operators.

The precedence order for operators in VBA is exactly the same as in Excel formulas .

Exponentiation has the highest precedence. multiplication and division come next, followed by addition and subtraction .

You can use parentheses to change the natural precedence order, making whatever’s operation in parentheses come before any operator.

Take a look at this code:

When this code is executed, what’s the value of z ?

If you answered 13 , you get a gold star that proves you understand the concept of operator precedence.

If you answered 16 , read this: The multiplication operation (5 * y) is performed first, and that result is added to x .

If you answered something other than 13 or 16 , I have no comment.

By the way, I can never remember how operator precedence works, so I tend to use parentheses even when they aren’t required.

For example, in real life I would write that last assignment statement like this:

Don’t be shy about using parentheses even if they aren’t required — especially if doing so makes your code easier to understand. VBA doesn’t care if you use extra parentheses .

Next post will be about VBA Arrays .

Breaking down the A-to-Zs of VBA to turbocharge your productivity.

Vba operators and precedence.

• by Sola Bode
• in VBA Coding Constructs
• February 25, 2021

In this article:

Vba operators.

VBA operators are special tokens that instruct the compiler to “ operate ” on values or program identifiers. They are also referred to as program symbols or code elements. Also, the program identifiers they operate on (e.g., addition) must be capable of holding values.

You may have heard of the term operand . These are the values (i.e., literals ) or identifiers (e.g., variables and constants) that operators act on. Based on their number of operands, operators are either unary or binary.

Unary VBA operators are operators that only act on a single operand at a time. Binary VBA operators are those that only act on two operands at a time.

VBA supports the following categories of operators:

• Assignment Operator ;
• Member Access Operators ;
• Concatenation Operators ;
• Arithmetic Operators ;
• Comparison Operators ;
• Logical Operators .

This article gives a birds eye view of each of these VBA operator categories.

VBA Operator Precedence

Often, an operation involves more than one operator, sometimes of different categories. So, it is worthwhile paying special attention to the order of precedence of the VBA operators. Otherwise, VBA’s default operator precedence applies.

The default precedence is as follows: arithmetic first, then concatenation, followed by comparison, then logical, and finally assignment operators.

Moreover, there is a pre-set precedence for operators within each category. How do you override this default order and remove any ambiguity? Easy, set the preferred precedence with open and close parentheses, ( ).

Assignment VBA Operator

VBA supports a single assignment operator, =. It stores the value of the operand on its right in the operand on its left. Afterward, both operands hold the same value.

Moreover, the operand on the right can comprise several expressions. Note that expressions are combinations of operands and operators.

Meanwhile, the identifier on the left must be a value-holding program identifier.

In any case, both operands must be of the same data type (otherwise, they can’t hold the same values).

So, the assignment operator writes the value of the right-side expression to the memory address of the left-side program identifier .

Check out this detailed article on the assignment operator . It features sample code and vital points on using the assignment operator.

Member Access VBA Operators

Member access operators ease the referencing (i.e., accessing) of class or object’s members (i.e., properties, methods, or events ). There are two such VBA operators, the dot (.) and bang or exclamation point (!) operators, as described in the table below.

As shown in the table, both member access operators have the same precedence. So, when they both appear together in an expression, evaluation proceeds in the order of appearance from left to right.

Besides, enclosing a part of an expression in parentheses prioritizes its evaluation ahead of other parts.

Check out this detailed article on member access operators . It features sample code and vital points on using the member access operators.

Concatenation VBA Operators

Concatenation operators join two String operands together. The result is a single expression with the operand after the operator appended to the operand before it.

The table below summarizes the different VBA concatenation operators.

As shown in the table, both concatenation operators have the same precedence. So, when they both appear together in an expression, evaluation proceeds in the order of appearance from left to right.

Also, enclosing a part of an expression in parentheses causes it to be evaluated ahead of other parts. Moreover, note that the arithmetic plus (+) operator has precedence over concatenation plus (+) .

Check out this detailed article on concatenation operators . It features sample code and vital points on using the concatenation operators.

Arithmetic VBA Operators

Arithmetic operators perform simple mathematic operations. Their operations include addition, subtraction, division, multiplication, exponentiation and modulus. The table below summarizes the different VBA arithmetic operators.

When several arithmetic operators with the same precedence appear together in an expression, evaluation proceeds in the order of appearance from left to right.

Also, enclosing a part of an expression in parentheses causes it to be evaluated ahead of other parts.

Check out this detailed article on arithmetic operators . It features sample code and vital points on using the arithmetic operators.

Comparison VBA Operators

Comparison operators perform relational, object equality, and string match operations on a pair of operands. The table below summarizes the various comparison operators supported by VBA.

As shown in the table, comparison operators all have equal precedence. So, when several of them appear together in an expression, their evaluation proceeds in the order of appearance from left to right.

Check out this detailed article on comparison operators . It features sample code and vital points on using the comparison operators.

Logical VBA Operators

Logical operators perform logical negation, conjunction, disjunction, exclusion, equivalence, and implication operations. They do so on either one operand ( unary operation ) or a pair of operands ( binary operation ).

The table below summarizes the various logical operators supported by VBA.

As shown in the table, all VBA logical operators have different precedence. So, when several of them appear in an expression, evaluation proceeds in the order of appearance from left to right.

Check out this detailed article on logical operators . It features sample code and vital points on using the logical operators.

Subscribe Now

To stay updated on our freshest Office & VBA articles and freebies.

Article Tags:

• Operators , VBA Programming
• Fresh Off The Stove

VBA Statements: The Building Blocks of VBA Programs

The While – Wend Statement | VBA Iteration Statements

The Do – Loop Until Statement | VBA Iteration Statements

The Do – Loop While Statement | VBA Iteration Statements

The Do Until – Loop Statement | VBA Iteration Statements

• Trending Articles
• The Peoples' Favorites

VBA Editor Customization: Menus, Toolbars and Toolbox

Getting Started with the Watch Window

Getting Started with the Locals Window

VBA Program Identifiers: Data Type, Scope, and Lifetime

Learn Excel VBA? Top Reasons Why You Should

Related articles.

VBA programs are nothing but a sequence of VBA statements. They are the cornerstone of VBA programs. Here, we explore these vital elements of VBA coding.

VBA programs often include repetitive execution paths. The While – Wend statement enables such loops. Here, we explore this crucial code construct in-depth.

VBA programs often include repetitive execution paths. The Do – Loop Until statement enables such loops. Here, we explore this vital code construct in-depth.

Join our list

Subscribe to our mailing list and get interesting stuff and updates to your email inbox.

Thank you for subscribing.

Something went wrong.

We respect your privacy and take protecting it seriously

VBA Operators

Operators perform actions on values and return a result. Operations work much like functions as they take input values, do an operation, and return an output value. There are arithmetic operators, string concatenation operators, comparison operators, logical operators, and bitwise operators. Expressions are created by joining values or expressions using operators.

Operator Precedence

Operator Precedence is the order in which operators are evaluated in an expression.

Precedence Order

• Parentheses: ()
• Exponentiation: ^
• Unary Identity and Negation: +, -
• Multiplication and Floating-Point Division: *, /
• Integer Division: \
• Remainder Division: Mod
• Addition and Subtraction: +, -
• String Concatenation: &, +
• All Comparison Operators: =, <>, <, <=, >, >=, Like, Is, TypeOf...Is

Associativity

Operators of equal precedence will be interpreted from left to right.

Parentheses

Expressions inside parentheses are evaluated first. Parentheses are used to override precedence order.

Arithmetic Operators

Arithmetic Operators are used to perform mathematical operations on numbers.

Concatenation Operators

Concatenation Operators are used to join strings together. The & and + string concatenation operators both join strings together however the & operator will convert any non-string argument to a string whereas the + operator will not. If the + operator is used to combine a string with a non-string an error will occur. The CStr function can be used to explicitly convert non-string values to strings.

Comparison Operators

Comparison Operators are used to compare values and return True or False.

When comparing strings using comparison operators, the comparison method used is determined by the Option Compare statement. If no Option Compare statement is used, strings are compared using their underlying Binary representations by default (Same as using Option Compare Binary). The StrComp function provides finer control over string comparison by allowing the compare method to be specified.

The Like operator can be used to compare strings using Wildcard characters to match text patterns.

The Is keyword can be used to check if two object variables refer to the same object in memory. When objects are compared using Is, the data stored in the object's fields are not compared. A function would need to be created that compares each member of an object, given the object's type.

TypeOf...Is can be used to check an object's type. This should be used when an object is declared using the generic Object type and a certain action requires the object to be of a certain type.

Logical Operators

Logical Operators are used to perform logical operations. Logical operations result in True or False. Logical operators can be used to join together conditional statements into more complex logical expressions. In VBA, there are no short-circuit logical operators. This means that all conditions will be evaluated in a logical expression without regard to the outcome of a previous condition. For example, when using a logical And, both conditions need to be True for the expression to return True. Thus, if the first condition is False, the expression could skip evaluating the next condition and immediately return False. In VBA however, evaluation will continue even if the result could already be determined.

Note: There are no short-circuit logical operators in VBA.

True if both conditions are true

True if at least one condition is true

True if one and only one condition is true

True if False, False if True

True if conditions are logically equivelent, otherwise False

If Condition1 is True, Condition2 must be True. If Condition1 is False, Condition2 can be True or False.

If I am a fish, I breathe water. If I am not a fish, I may breathe water or not.

Bitwise Operators

Bitwise Operators are used to logically compare the bits of binary representations of numbers and return a number representing the result of the bitwise comparison. The number of bits differs depending on the integral data type being compared and every bit is considered when doing bitwise comparisons. For example, when comparing an 8-bit integer to an 16-bit integer, the 8-bit integer will be treated like a 16-bit integer with the first 8 bits set to 0's so that all 16 bits can be compared.

Bitwise And reads integers as Binary and compares each bit using And logic.

Bitwise Or reads integers as Binary and compares each bit using Or logic.

Bitwise Xor reads integers as Binary and compares each bit using Xor logic.

Bitwise Not reads an integer as Binary and reverses each bit.

Bitwise Eqv reads an integer as Binary and compares each bit using Eqv logic.

Bitwise Imp reads an integer as Binary and compares each bit using Imp logic.

Assigment Operator

The assignment operator is used to assign values to variables. The left side of the assignment operator must be a variable and the right side of the assignment operator must be an expression that evaluates to a value that can be assigned to a variable.

AddressOf Operator

The AddressOf operator is used to pass the address of a procedure to an API function which requires a function pointer as a parameter.

There a few operator-like symbols in VBA which perform specific functions.

120+ Professional Project Management Templates!

Save Up to 85% LIMITED TIME OFFER

Operators and operands in excel vba.

Share This Post

VBA Projects

Full Access with Source Code

Designed and Developed by PNRao

Full Access with VBA Source Code

Well Commented Codes Lines

Creative and Professional Design

120+ PROFESSIONAL

Project Management Templates

120+ pm templates includes:.

50+ Excel Templates

50+ PowerPoint Templates

25+ Word Templates

Effortlessly Manage Your Projects

Seamlessly manage your projects with our powerful & multi-purpose templates for project management.

We we are doing anything with one are more values is called an operation or task. To perform any operation we required minimum one symbol and one Variable or Value. The symbol is called an Operator and the Variable or Value is called an Operand.In this tutorial we will learn more about Operators and Operands in Excel VBA.

• » The Assignment Operator
• » The Line Continuation and Other Operator
• » Arithmetic Operation
• » Logical Operators

Operators and Operands in Excel VBA – The Assignment Operator:

If it is a numeric variable the computer will initiate 0 by default, if it is a string it will initiate an empty space. You can initiate your own value to variable by using assignment operator ‘ = ‘

The Line Continuation Operator: _

You can use space + underscore to split a single statement into more than one line

The Double Quotes: “”

You can use “” to delimit a group of characters and symbols.

The Colon Operator: :

You can use : to put the two statements in a sing line.

String Concatenation: &

It is used to concatenate tow strings.

Carriage Return-Line Feed: vbCrLf

It will create new line. If you want to display a message to the user in two line instead of on single line, you can use this.

Operators and Operands in Excel VBA – Arithmetic Operators

Operators and Operands in Excel VBA – Logical Operators

A Powerful & Multi-purpose Templates for project management. Now seamlessly manage your projects, tasks, meetings, presentations, teams, customers, stakeholders and time. This page describes all the amazing new features and options that come with our premium templates.

All-in-One Pack 120+ Project Management Templates

Essential pack 50+ pm templates, excel pack 50+ excel pm templates, powerpoint pack 50+ excel pm templates, ms word pack 25+ word pm templates, ultimate project management template, ultimate resource management template, project portfolio management templates, related posts.

VBA ActiveSheet – Excel Active Sheet Object

Excel VBA ColorIndex

Excel VBA Copy Range to Another Sheet with Formatting

Hi, Really appreciate your hard work. Found a tiny typo. MagBox intRank

Thanks. Changwon.

Hi Changwon, Thanks for finding a type, corrected now!

Thanks-PNRao!

Hi dears, I appreciate you. Its a good job. Thanks for your great work. I am an accountant. I want to learn excel VBA as a beginner. how can I be a good programmer ? Thank you in advance!!!

Leave A Comment Cancel reply

Save my name, email, and website in this browser for the next time I comment.

Effectively Manage Your Projects and  Resources

ANALYSISTABS.COM provides free and premium project management tools, templates and dashboards for effectively managing the projects and analyzing the data.

We’re a crew of professionals expertise in Excel VBA, Business Analysis, Project Management. We’re Sharing our map to Project success with innovative tools, templates, tutorials and tips.

Project Management

Download Free Excel 2007, 2010, 2013, 2016, 2019 + Microsoft Office 365 Templates, Dashboards, Tools for Project Management, Data Mining, Analysis, Visualization and VBA Automation. Learn Project Management and VBA for Microsoft Excel, Word, PowerPoint, Access, Outlook to develop applications for retail, insurance, banking, finance, telecom, healthcare domains.

© 2023 Analysistabs | Sitemap | Your Privacy | Terms

An assignment statement is a statement containing the assignment operator "=" that assigns a literal value, the value of a variable or the value of an expression to a variable or object. The syntax of an assignment statement is VariableName = Expression where VariableName is the name of a variable that has been declared, and Expression is either a literal, a variable, or an expression which can be evaluated to a value. The same syntax applies in the case of an object rather than a variable. Examples of assignment statements (assuming that the variables have been declared): NumberMoles = 0.327 'setting the number of moles to 0.327 x = x + 1 'incrementing the value of x by one x = (y + 2) / (z - 3) 'assign x a value based on the values of y and z FileOpen = True 'setting a boolean variable to True Range("BirthYear").Value = 1988 'assigning a property value of an object VBA uses the equal sign as the assignment operator. The above statement, x = x + 1, means that to take the value that is stored in variable x, add 1 to it, and assigned the resulting value to x. As a result the value originally stored in x is incremented by 1. If you interpret the "=" as the equal sign as in mathematics, you will have a great conceptual difficulty, since there is nothing whose value is equal to 1 greater than itself (i.e., the "equation" would have no solution!).

Introduction An operation is an action performed on one or more values either to modify one value or to produce a new value by combining existing values. Therefore, an operation is performed using at least one symbol and one value. The symbol used in an operation is called an operator. A variable or a value involved in an operation is called an operand. A unary operator is an operator that performs its operation on only one operand. An operator is referred to as binary if it operates on two operands. Dimensioning a Variable When interacting with Microsoft Excel, you will be asked to provide a value. Sometimes, you will be presented with a value to view or change. Besides the values you use in a spreadsheet, in the previous lesson, we learned that we could also declare variables in code and assign values to them. In the previous lesson, we saw that we could use the operator to declare a variable. Here is an example: End Sub After declaring a variable like this, we saw that we could then use it as we saw fit. The Assignment Operator We mentioned that you could declare a variable but not specify the type of value that would be stored in the memory area reserved for it. When you have declared a variable, the computer reserves space in the memory and gives an initial value to the variable. If the variable is number based, the computer gives its memory an intial value of 0. If the variable is string based, the computer fills its memory with an empty space, also referred to as an empty string. Initializing a variable consists of giving it a value as soon as the variable has been declared. To initialize a variable, you use the assignment operator which is "=". You type the name of the variable, followed by =, and followed by the desired value. The value depends on the type of variable. If the variable is integral based, give it an appropriate natural number. Here is an example: If the variable is made to hold a decimal number, initialize it with a number that can fit in its type of variable. Here is an example: If the variable is for a string, you can initialize it with an empty string or put the value inside of double-quotes. The Line Continuation Operator: _ If you plan to write a long piece of code, to make it easier to read, you may need to divide it in various lines. To do this, you can use the line continuation operator represented by a white space followed by an underscore. To create a line continuation, put an empty space, then type the underscore, and continue your code in the next line. Here is an example: The Parentheses: () Parentheses are used in various circumstances. The parentheses in an operation help to create sections in an operation. This regularly occurs when more than one operators are used in an operation. Consider the following operation: The result of this operation depends on whether you want to add 8 to 3 then multiply the result by 5 or you want to multiply 3 by 5 and then add the result to 8. Parentheses allow you to specify which operation should be performed first in a multi-operator operation. In our example, if you want to add 8 to 3 first and use the result to multiply it by 5, you would write (8 + 3) * 5. This would produce 55. On the other hand, if you want to multiply 3 by 5 first then add the result to 8, you would write 8 + (3 * 5). This would produce 23. As you can see, results are different when parentheses are used on an operation that involves various operators. This concept is based on a theory called operator precedence. This theory manages which operation would execute before which one; but parentheses allow you to completely control the sequence of these operations. The Comma , The comma is used to separate variables used in a group. For example, a comma can be used to delimit the names of variables that are declared on the same line. Here is an example: The Double Quotes: "" A double-quote is used to delimit a group of characters and symbols. To specify this delimitation, the double-quote is always used in combination with another double-quote, as in "". What ever is inside the double-quotes is the thing that need to be delimited. The value inside the double-quotes is called a string. Here is an example: The Colon Operator : Most of the time, to make various statements easier to read, you write each on its own line. Here are examples: The Visual Basic language allows you to write as many statements as necessary on the same line. When doing this, the statements must be separated by a colon. Here is an example: String Concatenation: & The & operator is used to append two strings or expressions. This is considered as concatenating them. For example, it could allow you to concatenate a first name and a last name, producing a full name. The general syntax of the concatenation operator is: Carriage Return-Line Feed Arithmetic Operators Positive Unary Operator: + -∞   -6 -5 -4 -3 -2 -1   1 2 3 4 5 6   +∞    0 -∞   -6 -5 -4 -3 -2 -1   1 2 3 4 5 6   +∞ A value on the right side of 0 is considered positive. To express that a number is positive, you can write a + sign on its left. Examples are +4, +228, +90335. In this case the + symbol is called a unary operator because it acts on only one operand. The positive unary operator, when used, must be positioned on the left side of its operand, never on the right side. As a mathematical convention, when a value is positive, you don't need to express it with the + operator. Just writing the number without any symbol signifies that the number is positive. Therefore, the numbers +4, +228, and +90335 can be, and are better, expressed as 4, 228, 90335. Because the value does not display a sign, it is referred as unsigned . The Negative Operator - As you can see on the above ruler, in order to express any number on the left side of 0, it must be appended with a sign, namely the - symbol. Examples are -12, -448, -32706. A value accompanied by - is referred to as negative. The - sign must be typed on the left side of the number it is used to negate. Remember that if a number does not have a sign, it is considered positive. Therefore, whenever a number is negative, it MUST have a - sign. In the same way, if you want to change a value from positive to negative, you can just add a - sign to its left. Addition + The addition is performed with the + sign. It is used to add one value to another. Here is an example: Besides arithmetic operations, the + symbol can also be used to concatenate strings, that is, to add one string to another. This is done by appending one string at the end of another. Here is an example: Multiplication * The multiplication operation allows you to add a number to itself a certain number of times set by another number. The multiplication operation is performed using the * sign. Here is an example: Subtraction - The subtraction operation is performed using the - sign. This operation produces the difference of two or more numbers. It could also be used to display a number as a negative value. To subtract 28 from 65, you express this with 65-28. The subtraction can also be used to subtract the values of two values. Integer Division \ Dividing an item means cutting it in pieces or fractions of a set value. Therefore, the division is used to get the fraction of one number in terms of another. The Visual Basic language provides two types of operations for the division. If you want the result of the operation to be a natural number, called an integer, use the backlash operator "\" as the divisor. The formula to use is: This operation can be performed on two types of valid numbers, with or without decimal parts. After the operation, the result would be a natural number. Decimal Division / The second type of division results in a decimal number. It is performed with the forward slash "/". Its formula is: After the operation is performed, the result is a decimal number. Exponentiation ^ Exponentiation is the ability to raise a number to the power of another number. This operation is performed using the ^ operator (Shift + 6). It uses the following formula: In Microsoft Visual Basic, this formula is written as: and means the same thing. Either or both y and x can be values, variables, or expressions, but they must carry valid values that can be evaluated. When the operation is performed, the value of y is raised to the power of x. Remainder: Mod The division operation gives a result of a number with or without decimal values, which is fine in some circumstances. Sometimes you will want to get the value remaining after a division renders a natural result. The remainder operation is performed with keyword Mod . Its formula is: The result of the operation can be used as you see fit or you can display it in a control or be involved in another operation or expression. Introduction From our introduction to variables, you may remember that the computer stores its data in memory using small locations that look like boxes and each box contains a bit of information. Because a bit can be represented only either as 1 or 0, we can say that each box contains 1 or 0. Bit manipulation consists of changing the value (1 or 0, or 0 or 1) in a box. As we will see in the next few operations, it is not just about changing a value. It can involve reversing a value or kind of "moving" a box from its current position to the next position. The operations on bits are performed on 1s and 0s only. This means that any number in decimal or hexadecimal format involved in a bit operation must be converted to binary first. You will almost never perform some of the operations we are going to review. You will hardly perform some other operations. There is only one operation you will perform sometimes: the OR operation. "Reversing" a Bit Remember that, at any time, a box (or chunk) in memory contains either 1 or 0: 0 1 Bit reversal consists of reversing the value of a bit. If the box contains 1, you can reverse it to 0. If it contains 0, you can reverse it to 1. To support this operation, the Visual Basic language provides the Not Operator. As an example, consider the number 286. The decimal number 286 converted to binary is 100011110. You can reverse each bit as follows: 286 1 0 0 0 1 1 1 1 0 Not 286 0 1 1 1 0 0 0 0 1 Bitwise Conjunction Bitwise conjunction consists of adding the content of one box (a bit) to the content of another box (a bit). To support the bitwise conjunction operation, the Visual Basic language provides the And operator. To perform the bit addition on two numbers, remember that they must be converted to binary first. Then: Bit0 0 Bit1 0 Bit0 And Bit1 0 Bit0 1 Bit1 0 Bit0 And Bit1 0 Bit0 0 Bit1 1 Bit0 And Bit1 0 Bit0 1 Bit1 1 Bit0 And Bit1 1 As an example, consider the number 286 bit-added to 475. The decimal number 286 converted to binary is 100011110. The decimal number 4075 converted to binary is 111111101011. Based on the above 4 points, we can add these two numbers as follows: 286 0 0 0 1 0 0 0 1 1 1 1 0 4075 1 1 1 1 1 1 1 0 1 0 1 1 286 And 4075 0 0 0 1 0 0 0 0 1 0 1 0 Therefore, 286 And 4075 produces 100001010 which is equivalent to:   Bit8 Bit7 Bit6 Bit5 Bit4 Bit3 Bit2 Bit1 Bit0   256 128 64 32 16 8 4 2 1 286 And 4075 1 0 0 0 0 1 0 1 0   256 0 0 0 0 8 0 2 0 This means that 286 And 4075 = 256 + 16 + 2 = 266 This can also be programmatically calculated as follows: Bitwise Disjunction Bitwise disjunction consists of disjoining one a bit from another bit. To support this operation, the Visual Basic language provides the Or operator. To perform a bitwise conjunction on two numbers, remember that they must be converted to binary first. Then: Bit0 0 Bit1 0 Bit0 Or Bit1 0 Bit0 1 Bit1 0 Bit0 Or Bit1 1 Bit0 0 Bit1 1 Bit0 Or Bit1 1 Bit0 1 Bit1 1 Bit0 Or Bit1 1 As an example, consider the number 305 bit-disjoined to 2853. The decimal number 305 converted to binary is 100110001. The decimal number 2853 converted to binary is 101100100101. Based on the above 4 points, we can disjoin these two numbers as follows: 305 0 0 0 1 0 0 1 1 0 0 0 1 2853 1 0 1 1 0 0 1 0 0 1 0 1 305 Or 2853 1 0 1 1 0 0 1 1 0 1 0 1 Therefore, 305 Or 2853 produces 101100110101 which is equivalent to:   Bit11 Bit10 Bit9 Bit8 Bit7 Bit6 Bit5 Bit4 Bit3 Bit2 Bit1 Bit0   2048 1024 512 256 128 64 32 16 8 4 2 1 305 Or 2853 1 0 1 1 0 0 1 1 0 1 0 1   2048 0 512 256 0 0 32 16 0 4 0 1 This means that 286 And 4075 = 2048 + 512 + 256 + 32 + 16 + 4 + 1 = 2869 Bitwise Exclusion Bitwise exclusion consists of adding two bits with the following rules. To support bitwise exclusion, the Visual Basic language provides an operator named Xor : Bit0 0 1 Bit1 0 1 Bit0 Xor Bit1 0 0 Bit0 0 1 Bit1 1 0 Bit0 Xor Bit1 1 1 As an example, consider the number 618 bit-excluded from 2548. The decimal number 618 converted to binary is 1001101010. The decimal number 2548 converted to binary is 100111110100. Based on the above 2 points, we can bit-exclude these two numbers as follows: 618 0 0 1 0 0 1 1 0 1 0 1 0 2548 1 0 0 1 1 1 1 1 0 1 0 0 618 Xor 2548 1 0 1 1 1 0 0 1 1 1 1 0 Therefore, 305 Or 2853 produces 101110011110 which is equivalent to:   Bit11 Bit10 Bit9 Bit8 Bit7 Bit6 Bit5 Bit4 Bit3 Bit2 Bit1 Bit0   2048 1024 512 256 128 64 32 16 8 4 2 1 618 Xor 2548 1 0 1 1 1 0 0 1 1 1 1 0   2048 0 512 256 128 0 0 16 8 4 2 0 This means that 286 And 4075 = 2048 + 512 + 256 + 128 + 16 + 8 + 4 + 2 = 2974 More results... Difference Between Equal ‘=’ and ‘:=’ Colon Equal in VBA Rich asked a great question about the difference between the use of the equal sign = and the colon equal sign := in VBA. Often times you will see a combination of the two characters with the color before the equal sign. := = Equal Sign Sets a Property, Object, or Variable in VBA Typically we use the equal sign to set a property of an object, or to write a value to a variable for temporary storage. The following line of code will set the Value property of Cell A1 to the word hello. We can also use the equal sign to set the value of a variable. The following line will set the value of the variable lRow equal to 10. The equal sign can also be used in If statements as a comparison operator to evaluate a condition. := The Colon Equal Sign Sets a Value of a Parameter for a Property or Method The colon equal sign := is used to set the value of an parameter (argument)  for a property or method. We will use the Worksheets.Add method as an example. The Worksheets.Add method has four optional parameters. When using this method we reference the parameter name, followed by the := colon equal sign, then the parameter value. The following line of code will add a worksheet after the active sheet. Notice the use of the := colon equal sign symbols. This tells VBA that we are setting the parameter to a value or object. We don't have to list out all the optional parameters of the method either. The following line of code will add 5 worksheets after the active sheet. Notice that I did not need to reference the other optional parameters or leave commas between the parameters. We are able to do this because we are referencing the parameter name before the parameter value. So to sum it up, the colon equal combination := is used to set the value of a parameter (argument) for a property or method. The equal sign is use to set the value of a property, object, variable, or as a comparison operator. I hope that helps. Please leave a comment below with questions or suggestions. Thanks! You may also like New Microsoft Office Theme 2023 – How to Migrate or Revert Back Table of Contents Macros How to Search Data Validation Drop-down Lists in Excel Go To Source Cell of XLOOKUP Formula 11 comments, cancel reply. Your email address will not be published. Required fields are marked * thanks – really good explanation I need to go back to where I saw it and look closer at what was happening Awesome! Thanks Rich! thanks – I got a good explanation and satisfactory answer of my query Thank you Jon. Good enough explanation for me. I’m just starting to practice VBA :-). Great explanation. VBA beginner. I’m not sure the place you’re getting your information, but great topic. I must spend a while studying much more or figuring out more. Thanks for fantastic info I was looking for this information for my mission. For me it was helpful, too. MsgBox “Thank you, Jon” 🙂 Great explanation! Thanks, you explained this very well. Thanks. It is surprising how hard it is to find this information in the VBA reference manual. Most languages would have an assignment operator and be done with it but not VBA. No sir, if there is a more obscure way to do things VBA will find it. Thank you!!!!!!!!! Excel Shortcuts List Learn over 270 Excel keyboard & mouse shortcuts for Windows & Mac. Join Our Weekly Newsletter The Excel Pro Tips Newsletter is packed with tips & techniques to help you master Excel. AutoMacro: Ultimate VBA Add-in Read all reviews Return to VBA Code Examples VBA Logical Operators – OR, AND, XOR, NOT, IS, & LIKE Mel Jenkins Reviewed by Steve Rynearson In this Article Using the And Logical Operator Using the or logical operator, using the not logical operator, using the xor logical operator, is operator, like operator. VBA allows you to use the logical operators And , Or , Not , Xor  to compare values. The operators are considered “Boolean”, which means they return True or False as a result. If you want to learn how to compare strings, click here: VBA Compare Strings – StrComp If you want to learn how to use comparison operators, click here: VBA Comparison Operators – Not Equal to & More The And logical operator compares two or more conditions. If all the conditions are true, the operator will return True. If at least one of the conditions is not true, the operator will return False. Here is an example: In this example, we want to check if both intA and intB are equal to 5. If this is true, the value of Boolean blnResult will be True, otherwise, it will be False. First, we set values of intA and intB to 5: After that, we use the And operator in the If statement to check if the values are equal to 5: As both variables are equal to 5, the blnResult returns True: Image 1. Using the And logical operator in VBA The Or logical operator compares two or more conditions. If at least one of the conditions is true, it will return True. If none of the conditions are true, the operator will return False. Here is the code for the example: In this example, we want to check if both intA is equal to 5. or intB is equal to 10. If any of these conditions is true, the value of Boolean blnResult will be True, otherwise, it will be False. First, we set the value of intA to 5 and intB to 10: After that, we use the Or operator in the If statement to check if any of the values is equal to 5: As intA value is 5, the blnResult returns True: Image 2. Using the Or logical operator in VBA The Not logical operator checks one or more conditions. If the conditions are true, the operator returns False. Otherwise, it returns True. Here is the code for the example: In this example, we want to check if the value of intA is not equal to 6. If intA is different than 6, the value of Boolean blnResult will be True, otherwise, it will be False. First, we set the value of intA to 5: After that, we use the Not operator in the If statement to check if the value of intA is different than 6: As intA  value is 5, the blnResult returns True: Image 3. Using the Not logical operator in VBA The Xor logical operator compares two or more conditions. If exactly one of the conditions is true, it will return True. If none of the conditions are true, or more than one are true, it will return False. Here is the code for the example: In this example, we want to check if exactly one of the values (intA or IntB) are equal to 5. If only one condition is true, the value of Boolean blnResult will be True, otherwise, it will be False. As intA value is 5 and intB is 10, the blnResult returns True: Image 4. Using the Xor logical operator in VBA The Is Operator tests if two object variables store the same object. Let’s look at an example. Here we will assign two worksheets to worksheet objects rng1 and rng2, testing if the two worksheet objects store the same worksheet: Of course the worksheet objects are not the same, so “Different WSs” is returned. The Like Operator can compare two strings for inexact matches. This example will test if a string starts with “Mr.” VBA Coding Made Easy VBA Code Examples Add-in Easily access all of the code examples found on our site. Simply navigate to the menu, click, and the code will be inserted directly into your module. .xlam add-in. (No installation required!) Free Download AutoMacro: VBA Add-in with Hundreds of Ready-To-Use VBA Code Examples & much more! Visual Basic Operators In Visual Basic, Operator is a programming element that specifies what operation needs to perform on operands or variables . For example, an addition (+) operator in Visual Basic is used to perform the sum operation on operands. Visual Basic Operator Types In Visual Basic different types of operators are available; those are Arithmetic Operators Assignment Operators Logical/Bitwise Operators Comparison Operators Concatenation Operators Now, we will learn each operator in a detailed manner with examples in the Visual Basic programming language. Visual Basic Arithmetic Operators In Visual Basic, Arithmetic Operators are useful for performing basic arithmetic calculations like addition, subtraction, division, etc., based on our requirements. The following table lists the different arithmetic operators available in Visual Basic. Operator Description Example (a = 6, b = 3) + It will add two operands. a + b = 9 - It will subtract two operands. a - b = 3 * It will multiply two operands. a * b = 18 / It divides two numbers and returns a floating-point result. a / b = 2 \ It divides two numbers and returns an integer result. a \ b = 2 Mod It divides two numbers and returns only the remainder. a Mod b = 0 ^ It raises a number to the power of another number. a ^ b = 216 Visual Basic Assignment Operators In Visual Basic, Assignment Operators are useful to assign a new value to the operand. The following table lists the different assignment operators available in Visual Basic. Operator Description Example = It will assign a value to a variable or property. a = 10 += It will add left and right operands and assign a result to the left operand. a += 10 equals to a = a + 10 -= It will subtract left and right operands and assign a result to the left operand. a -= 10 equals to a = a - 10 *= It will multiply left and right operands and assign a result to the left operand. a *= 10 equals to a = a * 10 /= It will divide left and right operands and assign the floating-point result to the left operand. a /= 10 equals to a = a / 10 \= It will divide left and right operands and assign the integer result to the left operand. a \= 10 equals to a = a \ 10 ^= It will raise the value of a variable to the power of expression and assign the result back to the variable. a ^= 10 equals to a = a ^ 10 &= It will concatenate a String expression to a String variable and assign the result to the variable. a &= "World" equals to a = a & "World" >>= It will move the left operand bit values to the right based on the number of positions specified by the second operand. a >>= 2 equals to a = a >> 2 <<= It will move the left operand bit values to the left based on the number of positions specified by the second operand. a <<= 2 equals to a = a << 2 Visual Basic Logical / Bitwise Operators In Visual Basic, Logical / Bitwise Operators are useful to perform the logical operation between two operands like AND, OR, etc., based on our requirements. The Logical / Bitwise Operators will always work with Boolean expressions ( true or false ) and return Boolean values. The following table lists the different types of logical/bitwise operators available in Visual Basic. Operator Description Example (a = True, b = False) And It will return true if both operands are non zero. a And b = False Or It will return true if any one operand becomes a non zero. a Or b = True Not It will return the reverse of a logical state that means if both operands are non zero, it will return false. Not(a And b) = True Xor It will return true if any one of expression1 and expression2 evaluates to true. a Xor b = True AndAlso It will perform the short-circuiting logical operation and return true if both operands evaluate to true. a AndAlso b = False OrElse It will perform the short-circuiting logical operation and return true if any operand evaluates to true. a OrElse b = True IsFalse It will determine whether an expression is False.   IsTrue It will determine whether an expression is True.   Visual Basic Comparison Operators In Visual Basic, Comparison Operators are useful to determine whether the defined two operands are equal, greater than or less than, etc., based on our requirements. The following table lists the different comparison operators available in Visual Basic. Operator Description Example (a = 10, b = 5) < It will return true if the right operand is greater than the left operand. a < b = False <= It will return true if the right operand is greater than or equal to the left operand. a <= b = False > It will return true if the left operand is greater than the right operand. a > b = True >= It will return true if the left operand is greater than or equal to the right operand. a >= b = True = It will return true if both operands are equal. a = b = False <> It will return true if both operands are not equal. a <> b = True Is It will return true if two object references refer to the same object.   IsNot It will return true if two object references refer to different objects.   Visual Basic Concatenation Operators In Visual Basic, Concatenation Operators are useful to concatenate defined operands based on our requirements. The following table lists the different types of concatenation operators available in Visual Basic. Operator Description Example (a = Hello, b = World) & It will concatenate given two expressions. a & b = HelloWorld + It is useful to add two numbers or concatenate two string expressions. a + b = HelloWorld Table of Contents Operators in Visual Basic with Examples Arithmetic Operators in Visual Basic Assignment Operators in Visual Basic Logical/Bitwise Operators in Visual Basic Comparison & Concatenation Operators in Visual Basic Stack Overflow for Teams Where developers & technologists share private knowledge with coworkers Advertising & Talent Reach devs & technologists worldwide about your product, service or employer brand OverflowAI GenAI features for Teams OverflowAPI Train & fine-tune LLMs Labs The future of collective knowledge sharing About the company Visit the blog Collectives™ on Stack Overflow Find centralized, trusted content and collaborate around the technologies you use most. Q&A for work Connect and share knowledge within a single location that is structured and easy to search. Get early access and see previews of new features. Dynamic operator in VBA. How can I? How can I create a dynamic operator using VBA? Obviously this fails but can anyone make it work? operator-keyword I don't understand your suggestion. Can you elaborate –  Johnson Jason Commented Sep 3, 2015 at 20:32 1 Not possible. You can't define new operators in VBA. –  John Coleman Commented Sep 3, 2015 at 20:33 You can write a public function that returns true or false based on what is passed into it. –  Scott Craner Commented Sep 3, 2015 at 20:34 John :-( Not Goooooood. @Scott, that's not a bad idea. –  Johnson Jason Commented Sep 3, 2015 at 20:34 4 Answers 4 Use basic string concatenation methods to build a simple math formula bringing the operator(s) in as a string character. Once the formula is constructed from string parts, it can be resolved with Application Evaluate . You're thinking it wrong: the VBA language grammar doesn't work like that, operators are well defined, and when this line gets tokenized: Seeing the If keyword it will expect this: ...and then bump into a op B and throw a fit, because a is an identifier, op is an identifier, and B is another identifier - there's no logical operator there, and that can't be evaluated as a [BoolExp] - hence the compile error. But you know this already. swt = True op = IIf(swt = True, "<", ">") IIf works similarly: IIf([BoolExp], [ValueStmt], [ValueStmt]) - here swt being assigned to the Boolean literal True , it constitutes a Boolean expression all by itself . Thus, the assignment for op can be simplified to this: Now that's prettier, but op is still a String variable, and that just won't work. Short of making Excel do the work with Application.Evaluate , the only way is to branch in VBA code that works in all Office hosts is, well, to branch : The edge case where a = b also needs to be handled, of course. Use the Evaluate() method. As I said in the comments and others have mentioned as well, VBA syntax doesn't allow you to define new infix operators. You never said what you wanted to do this for, but I couldn't help but think that you had a custom sort in mind where a comparison operator is passed to a sort sub. Think about how such things are handled in C (which also doesn't allow you to define new infix operators). The standard library has an implementation of qsort which needs to be passed a pointer to a comparison function. You can do something similar in VBA. VBA lacks function pointers -- but Application.Run (which is in some ways more flexible than Application.Evaluate ) allows you to use the name of the function almost as if it is a pointer. For example, say you write a compare function that takes to doubles and either returns the larger of them or the smaller of them, depending on the state of a global parameter, like thus: To illustrate how Application.Run allows you to pass a custom compare function, I wrote a function which takes the name of a compare function as well as an array, and returns the element of the array which is largest according to the sort order determined by the compare function. The compare function can be rather arbitrary, e.g. it could return the tree of greatest height in an array of trees: The following sub illustrates how this is called: Your Answer Reminder: Answers generated by artificial intelligence tools are not allowed on Stack Overflow. Learn more Sign up or log in Post as a guest. Required, but never shown By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy . Not the answer you're looking for? Browse other questions tagged vba variables dynamic operator-keyword or ask your own question . The Overflow Blog LLMs evolve quickly. Their underlying architecture, not so much. From PHP to JavaScript to Kubernetes: how one backend engineer evolved over time Featured on Meta We've made changes to our Terms of Service & Privacy Policy - July 2024 Bringing clarity to status tag usage on meta sites Feedback requested: How do you use tag hover descriptions for curating and do... What does a new user need in a homepage experience on Stack Overflow? Hot Network Questions Polyline to polygon Solve an equation perturbatively Is it possible to do physics without mathematics? If physics can be reduced to mathematics (and thus to logic), does this mean that (physical) causation is ultimately reducible to implication? Reduce String Length With Thread Safety & Concurrency Why are volumes of revolution typically taught in Calculus 2 and not Calculus 3? What's the proper way to shut down after a kernel panic? Inconsistent “unzip -l … | grep -q …” results with pipefail Minimum time required for door opening after arrival at gate Do we need to be translating the Hebrew form and meaning of the word hate in Romans 9:13? Canceling factors in a ratio of factorials A simplified Blackjack C++ OOP console game Can light become a satellite of a black hole? High CPU usage by process with obfuscated name on Linux server – Potential attack? R Squared Causal Inference How do I safely remove a mystery cast iron pipe in my basement? Trying to find an old book (fantasy or scifi?) in which the protagonist and their romantic partner live in opposite directions in time Electric skateboard: helmet replacement Submitting a paper as a nonacademic practitioner in a field What's the origin of the colloquial "peachy", "simply peachy", and "just peachy"? Is it OK to make an "offshape" bid if you can handle any likely response? How does current in the cable shield affect the signal in the wires within Which cards use −5 V and −12 V in IBM PC compatible systems? How do you hide an investigation of alien ruins on the moon during Apollo 11? This browser is no longer supported. Upgrade to Microsoft Edge to take advantage of the latest features, security updates, and technical support. -= Operator (Visual Basic) 12 contributors Subtracts the value of an expression from the value of a variable or property and assigns the result to the variable or property. variableorproperty Required. Any numeric variable or property. expression Required. Any numeric expression. The element on the left side of the -= operator can be a simple scalar variable, a property, or an element of an array. The variable or property cannot be ReadOnly . The -= operator first subtracts the value of the expression (on the right-hand side of the operator) from the value of the variable or property (on the left-hand side of the operator). The operator then assigns the result of that operation to the variable or property. Overloading The - Operator (Visual Basic) can be overloaded , which means that a class or structure can redefine its behavior when an operand has the type of that class or structure. Overloading the - operator affects the behavior of the -= operator. If your code uses -= on a class or structure that overloads - , be sure you understand its redefined behavior. For more information, see Operator Procedures . The following example uses the -= operator to subtract one Integer variable from another and assign the result to the latter variable. - Operator (Visual Basic) Assignment Operators Arithmetic Operators Operator Precedence in Visual Basic Operators Listed by Functionality Additional resources 348 COMMENTS Assignment Operators The following are the assignment operators defined in Visual Basic. = Operator ^= Operator *= Operator /= Operator \= Operator += Operator-= Operator <<= Operator >>= Operator &= Operator. See also. Operator Precedence in Visual Basic; Operators Listed by Functionality; Statements Writing assignment statements (VBA) Writing assignment statements. Assignment statements assign a value or expression to a variable or constant. Assignment statements always include an equal sign ( = ). The following example assigns the return value of the InputBox function to the variable. Dim yourName As String. Operators in Excel VBA It is also used as a comparison operator in VBA. We will talk about it later in this tutorial. One simple example is. sub test() a=10 Range("A1").value=a end sub In the above example, we first use the assignment operator "=" to assign value to variable "a" and then use the "=" operator to assign value of "a" to value of Range("A1"). 2. Assignment Operator in VBA Besides the assignment operator, there are several expression statements that also perform assignments. These include the Let, Set, Get, Put, Input #, Line Input #, Print #, and Write # statements. 5. The VBA assignment operator, a symbol that tells the compiler to store the value of the operand on its right in the operand on its left, is ... VBA Assignment Statements And Operators An assignment statement is a VBA statement that assigns the result of an expression to a variable or an object. In a book I read Excel's Help system defines the term expression as: "Combination of keywords, operators, variables, and constants that yields a string, number, or object. An expression can be used to perform a calculation ... = Operator The element on the left side of the equal sign ( =) can be a simple scalar variable, a property, or an element of an array. The variable or property cannot be ReadOnly. The = operator assigns the value on its right to the variable or property on its left. The = operator is also used as a comparison operator. For details, see Comparison Operators. VBA Operators and Precedence Otherwise, VBA's default operator precedence applies. The default precedence is as follows: arithmetic first, then concatenation, followed by comparison, then logical, and finally assignment operators. Moreover, there is a pre-set precedence for operators within each category. VBA Operators VBA Operators. Operators perform actions on values and return a result. Operations work much like functions as they take input values, do an operation, and return an output value. ... The left side of the assignment operator must be a variable and the right side of the assignment operator must be an expression that evaluates to a value that can ... Operators and Operands in Excel VBA Operators and Operands in Excel VBA - The Assignment Operator: If it is a numeric variable the computer will initiate 0 by default, if it is a string it will initiate an empty space. You can initiate your own value to variable by using assignment operator ' = '. Example: Dim intRank As Integer. intRank = 500. Using Assignment Statements An assignment statement is a statement containing the assignment operator "=" that assigns a literal value, the value of a variable or the value of an expression to a variable or object. ... VBA uses the equal sign as the assignment operator. The above statement, x = x + 1, means that to take the value that is stored in variable x, add 1 to it ... VBA Operators in Excel Follow the below steps to understand this operator. Step 1: Open the module in VBA, write the sub-procedure, and define two variables of Integer data type. Then, assign a random numeric value to them, as shown below. Step 2: Now, we will use Equals to the VBA operator to see whether the variables are equal. Microsoft Excel VBA Microsoft Excel VBA - Lesson 04: VBA Operators and Operands. VBA Operators and Operands. VBA Operators. Introduction. An operation is an action performed on one or more values either to modify one value or to produce a new value by combining existing values. Therefore, an operation is performed using at least one symbol and one value. Difference Between Equal '=' and ':=' Colon Equal in VBA The colon equal sign := is used to set the value of an parameter (argument) for a property or method. We will use the Worksheets.Add method as an example. The Worksheets.Add method has four optional parameters. Worksheets.Add ([Before], [After], [Count], [Type]) When using this method we reference the parameter name, followed by the := colon ... vb6 No, VBA doesn't have the right-associativity for assignment operator. However, VBA does have the right-associativity or chaining operations for other logical operators. Consider the following expression: alpha = 3. bravo = 5. charlie = 4. alpha = bravo < charlie. MsgBox(alpha) 'would display false. alpha = 3. Appendix D: Summary of VBA Operators Assignment Operators (there is only one assignment operator in VBA): = Assignment e.g. x = y where x is a variable or a writable property and y can be any numeric, logical, string literal, constant, or expression. x and y must be with the same data type. Miscellaneous Operators: & String Concatenation VBA Logical Operators Image 4. Using the Xor logical operator in VBA. Is Operator. The Is Operator tests if two object variables store the same object. Let's look at an example. Here we will assign two worksheets to worksheet objects rng1 and rng2, testing if the two worksheet objects store the same worksheet: &= Operator Overloading the & operator affects the behavior of the &= operator. If your code uses &= on a class or structure that overloads &, be sure you understand its redefined behavior. For more information, see Operator Procedures. Example. The following example uses the &= operator to concatenate two String variables and assign the result to the ... Visual Basic Operators Visual Basic Operator Types. In Visual Basic different types of operators are available; those are. Arithmetic Operators. Assignment Operators. Logical/Bitwise Operators. Comparison Operators. Concatenation Operators. Now, we will learn each operator in a detailed manner with examples in the Visual Basic programming language. += Operator This assignment operator implicitly performs widening but not narrowing conversions if the compilation environment enforces strict semantics. For more information on these conversions, see Widening and Narrowing Conversions.For more information on strict and permissive semantics, see Option Strict Statement.. If permissive semantics are allowed, the += operator implicitly performs a variety of ... Dynamic operator in VBA. How can I? Thus, the assignment for op can be simplified to this: op = IIf(swt, "<", ">") Now that's prettier, but op is still a String variable, and that just won't work. Short of making Excel do the work with Application.Evaluate, the only way is to branch in VBA code that works in all Office hosts is, well, to branch: If swt Then. -= Operator The - Operator (Visual Basic) can be overloaded, which means that a class or structure can redefine its behavior when an operand has the type of that class or structure. Overloading the - operator affects the behavior of the -= operator. If your code uses -= on a class or structure that overloads -, be sure you understand its redefined behavior. assignment essay homework paper phd review speech thesis