assignment statement vb

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 .

The Assignment Statement

Format: [Let] <variable name> = <expression>

The assignment statement (also called the "Let statement", but this is a dated term) causes the value of the expression on the right side of the equal sign to be stored in the variable specified on the left side of the equal sign.

As indicated by the general format, the keyword Let is optional.  It is typically not used.  The following two statements have the exact same meaning:

An expression can be a constant, variable, or any valid combination of constants and/or variables connected with VB operators such as +, -, *, and /.

The assignment statement has the form variable = constant

(i.e., the <expression> is a constant)

If the variable name on the left of the equal sign is a string variable, then the constant must be a string constant enclosed in quotes.  The quotes are not stored.  Examples follow:

Assume that the following variables are declared:

The statement

      strCustName = "BOB SMITH"

would cause the characters BOB SMITH to be stored in the variable strCustName.

      strCustAddr = " 123 MAIN ST ."

would cause the characters 123 MAIN ST. to be stored in the variable strCustAddr.

If the variable name on the left of the equal sign is a numeric variable (Integer, Long, Single, Double, Currency), then the constant must be a valid numeric constant.  Numeric constants must not be enclosed in quotes.  They must consist only of the digits 0 through 9 and can optionally have a leading sign (- or +) and may have one decimal point.  Examples:

The following statements would cause the specified quantities to be stored in these variables:

If the variable name on the left of the equal sign is a Date variable, then the constant must be a valid date constant enclosed in pound signs (#). Assume that the following variable is declared:

                Dim dtmHireDate As Date

The following statement would cause the internal representation of November 29, 1999 to be stored in the variable dtmHireDate:

                dtmHireDate = #11/29/99#

Note: Whenever we assign a value to a variable name, the previous value stored in that variable is destroyed and replaced with the new value.  This is called a "destructive replacement" or "destructive write".  For example, if the previous value of the variable intI was 2, and then the statement intI = 6 was executed, the new value of intI would be 6 (the 6 would replace the 2).

The assignment statement has the form variable = variable

(i.e., the <expression> is a variable)

The contents of the variable on the right of the equal sign will be copied to the variable on the left of the equal sign, replacing the previous contents of the variable on the left.  The contents of the variable on the right will remain unchanged.

The examples below assume that the following variables have been declared:

The assignment statement has the form variable = expression

(i.e., the <expression> is an arithmetic expression)

If an arithmetic expression is on the right of the equal sign, the expression is evaluated and the result of the expression is stored in the variable on the left of the equal sign.

For example, given the two statements:

In the second statement above, VB will determine that the variable intI contains 2, add the constant 4 to it, and store the result (6) in intJ.

Given these two statements:

In mathematics, you could never have a statement like the second one above (intI = intI + 1), because in mathematics, the "="  indicates equality.  But in VB, the "=" does not indicate equality; rather, it means "is replaced by" - i.e., the expression on the right (intI + 1) is first evaluated and determined to be the value 3.  The 3 is then stored in the variable on the left (which happens to be intI).

Other assignment statement notes:

No expression on left of equal sign

There can never be an expression on the left of the equal sign.  For example,

            A + B = C + D

is invalid, it means nothing in VB.  By definition, the function of the assignment statement is to store a value in the variable specified on the left of the equal sign.

Assignment statements with mixed data types

In previous versions of BASIC and VB, you could only assign a string constant or another string variable to a string variable, and you could only assign a numeric constant, numeric variable, or numeric expression to a numeric variable.  Violation of this rule resulted in the generation of a Type Mismatch error.  In later versions of VB, this rule has been relaxed – basically, VB will convert one data type to another if it possibly can; if it can't perform a suitable conversion, the "Type Mismatch " error will still occur.  Generally, "mixed-mode" assignment statements should be avoided when possible; they are inefficient and may sometimes produce unexpected results.

Regarding the last statement, if the decimal portion of a Single or Double is exactly .5, VB always rounds to the nearest even number when converting to an Integer or Long. This is sometimes referred to as "bank rounding".

In the "mixed mode" assignment statements shown above, VB would perform the necessary conversions in these cases wherever it could. Such conversions are called implicit conversions .

In VB, you can also use a set of functions that explicitly convert (or "cast") one type of data to another.  The set of functions that enable you to do this all begin with the letter "C": CBool, CByte, CCur, CDate, CDbl, CDec,  CInt, CLng, CSng, CStr, and CVar. There are also two older functions, Val and Str, which enable you to perform conversions as well. These functions will be covered in a later topic.

Assigning Data to Arrays

Given the following definitions:

Dim aintCount(0 To 9)               As Integer

Dim asngSales(1 To 4, 1 To 5)       As Single

Dim asngResults(3, 1 To 12, 2 To 6) As Single

The following would be valid assignment statements:

      aintCount(4) = 36

      asngSales(2, 3) = 12543.22

      asngResults(0, 11, 5) = 4.567

Assigning Data to UDTs

      Public Type EmployeeName

          FirstName As String

          MidInit   As String

          LastName  As String

      End Type

      Public Type EmployeeRecord

          udtEmpName                   As EmployeeName

          dtmHireDate                  As Date

          sngHourlyRate                As Single

          dblQuarterlyEarnings(1 To 4) As Double

      Dim udtEmpRec           As EmployeeRecord

      Dim audtEmpRec(1 To 10) As EmployeeRecord

      udtEmpRec.sngHourlyRate = 28.75

      audtEmpRec(3).dtmHireDate = #1/15/2001#

      udtEmpRec.udtEmpName.MidInit = "B"

      audtEmpRec(4).dblQuarterlyEarnings(3) = 14950.00

Using With/End With

You can use a With/End With block to "factor out" a qualifying reference in a group of statements. For example, the following sets of statements are equivalent:

With statement blocks can be nested. The following sets of statements are equivalent:

Writing assignment statements

In VBA, there are two types of assignment, value assignment and object assignment. Here I will discuss value assignment. Value assignment means that a variable or the property of an object is given a value, for example strName = "Jean" or s = l * w . The characteristic of an assignment is that it has a source on the right-side, a destination on the left, and the '='-sign in between. For sure, this is the most common type of statement in any program. The following shows different ways to assign values.

• Value assignment

Using literals

Copy the value from another variable, obtaining the value from an object property, value returned from a function procedure.

Warning : not all assignments will be acceptable. What would be the result of Dim i As Integer: i = "a" ? Explicitly typing variables helps prevent such errors. If instead we would have coded Dim i: i = "a" , leaving the type out, you will stay in blissful ignorance until you try to use variable i in multiplication at which point runt-time error 'Type mismatch' appears.

A literal is value that is expressed as itself rather than as a variable's value or the result of an expression, such as the number 3 or the string "Hello". In the examples direct below I precede the assignment statement by a declaration statement to show the correct way to code literals depending on intended type. Note that I use the colon symbol ':' as line-continuation character, which allows me to write the two statements, declaration and statement on a single line, making the code more compact.

Generally you want to limit the number of variables, but there may be specific reasons why you may want to make copies. For example, in a loop you may want to detect if a value is different from the previous.

Although using a property value directly is the preferred approach, you may sometimes want to store the value in a variable if you don't want to retrieve the same value from a much used property repetitively.

Giving an object property a value

An object property, assuming it is defined as 'writable' can be assigned a value same way as a variable.

The most common use of a variable is to store the result of some function call, maybe a calculation or the content of a line in a file.

Image below shows context sensitive IntelliSense letting you select a VBA function -or any of the other ways of assigning a value discussed above- that will return a type Long value from a shortcut menu. This feature is made available from the Code VBA add-in .

• VB.Net Basic Tutorial
• VB.Net - Home
• VB.Net - Overview
• VB.Net - Environment Setup
• VB.Net - Program Structure
• VB.Net - Basic Syntax
• VB.Net - Data Types
• VB.Net - Variables
• VB.Net - Constants
• VB.Net - Modifiers

VB.Net - Statements

• VB.Net - Directives
• VB.Net - Operators
• VB.Net - Decision Making
• VB.Net - Loops
• VB.Net - Strings
• VB.Net - Date & Time
• VB.Net - Arrays
• VB.Net - Collections
• VB.Net - Functions
• VB.Net - Subs
• VB.Net - Classes & Objects
• VB.Net - Exception Handling
• VB.Net - File Handling
• VB.Net - Basic Controls
• VB.Net - Dialog Boxes
• VB.Net - Advanced Forms
• VB.Net - Event Handling
• VB.Net Advanced Tutorial
• VB.Net - Regular Expressions
• VB.Net - Database Access
• VB.Net - Excel Sheet
• VB.Net - Send Email
• VB.Net - XML Processing
• VB.Net - Web Programming
• VB.Net Useful Resources
• VB.Net - Quick Guide
• VB.Net - Useful Resources
• VB.Net - Discussion
• Selected Reading
• UPSC IAS Exams Notes
• Developer's Best Practices
• Questions and Answers
• Effective Resume Writing
• HR Interview Questions
• Computer Glossary

A statement is a complete instruction in Visual Basic programs. It may contain keywords, operators, variables, literal values, constants and expressions.

Statements could be categorized as −

Declaration statements − these are the statements where you name a variable, constant, or procedure, and can also specify a data type.

Executable statements − these are the statements, which initiate actions. These statements can call a method or function, loop or branch through blocks of code or assign values or expression to a variable or constant. In the last case, it is called an Assignment statement.

Declaration Statements

The declaration statements are used to name and define procedures, variables, properties, arrays, and constants. When you declare a programming element, you can also define its data type, access level, and scope.

The programming elements you may declare include variables, constants, enumerations, classes, structures, modules, interfaces, procedures, procedure parameters, function returns, external procedure references, operators, properties, events, and delegates.

Following are the declaration statements in VB.Net −

Executable Statements

An executable statement performs an action. Statements calling a procedure, branching to another place in the code, looping through several statements, or evaluating an expression are executable statements. An assignment statement is a special case of an executable statement.

The following example demonstrates a decision making statement −

When the above code is compiled and executed, it produces the following result −

To Continue Learning Please Login

This browser is no longer supported.

Upgrade to Microsoft Edge to take advantage of the latest features, security updates, and technical support.

Select...Case Statement (Visual Basic)

• 12 contributors

Runs one of several groups of statements, depending on the value of an expression.

If testexpression matches any Case expressionlist clause, the statements following that Case statement run up to the next Case , Case Else , or End Select statement. Control then passes to the statement following End Select . If testexpression matches an expressionlist clause in more than one Case clause, only the statements following the first match run.

The Case Else statement is used to introduce the elsestatements to run if no match is found between the testexpression and an expressionlist clause in any of the other Case statements. Although not required, it is a good idea to have a Case Else statement in your Select Case construction to handle unforeseen testexpression values. If no Case expressionlist clause matches testexpression and there is no Case Else statement, control passes to the statement following End Select .

You can use multiple expressions or ranges in each Case clause. For example, the following line is valid.

Case 1 To 4, 7 To 9, 11, 13, Is > maxNumber

The Is keyword used in the Case and Case Else statements is not the same as the Is Operator , which is used for object reference comparison.

You can specify ranges and multiple expressions for character strings. In the following example, Case matches any string that is exactly equal to "apples", has a value between "nuts" and "soup" in alphabetical order, or contains the exact same value as the current value of testItem .

Case "apples", "nuts" To "soup", testItem

The setting of Option Compare can affect string comparisons. Under Option Compare Text , the strings "Apples" and "apples" compare as equal, but under Option Compare Binary , they do not.

A Case statement with multiple clauses can exhibit behavior known as short-circuiting . Visual Basic evaluates the clauses from left to right, and if one produces a match with testexpression , the remaining clauses are not evaluated. Short-circuiting can improve performance, but it can produce unexpected results if you are expecting every expression in expressionlist to be evaluated. For more information on short-circuiting, see Boolean Expressions .

If the code within a Case or Case Else statement block does not need to run any more of the statements in the block, it can exit the block by using the Exit Select statement. This transfers control immediately to the statement following End Select .

Select Case constructions can be nested. Each nested Select Case construction must have a matching End Select statement and must be completely contained within a single Case or Case Else statement block of the outer Select Case construction within which it is nested.

The following example uses a Select Case construction to write a line corresponding to the value of the variable number . The second Case statement contains the value that matches the current value of number , so the statement that writes "Between 6 and 8, inclusive" runs.

• End Statement
• If...Then...Else Statement
• Option Compare Statement
• Exit Statement

Coming soon: Throughout 2024 we will be phasing out GitHub Issues as the feedback mechanism for content and replacing it with a new feedback system. For more information see: https://aka.ms/ContentUserFeedback .

Submit and view feedback for

Additional resources