VBA FOR EXCEL
All rights reserved. No part of this book may be reproduced or transmitted, in any form or by any means, without permission in writing from TeamLease EdTech. Any person who does any unauthorized act in relation to this book may be liable to criminal prosecution and civil claims for damages. This book is meant for educational and learning purposes. The author/authors of the book has/have taken all reasonable care to ensure that the contents of the book do not violate any existing copyright or other intellectual property rights of any person in any manner whatsoever. In the event, the author/authors has/ have been unable to track any source and if any copyright has been inadvertently infringed, please notify the publisher in writing for corrective action.
CONTENTS UNIT - 1: Introduction To Excel VBA......................................................................................1 UNIT - 2: Getting started with VBA .........................................................................................5 UNIT - 3: Data Types & Constants .........................................................................................15 UNIT - 4: Arrays......................................................................................................................21 UNIT - 5: Looping with VBA .................................................................................................28 UNIT – 6: Operators ................................................................................................................36 UNIT - 7: Decisions................................................................................................................49 UNIT – 8: Strings....................................................................................................................58 UNIT – 9: Functions ...............................................................................................................79 UNIT – 10: Sub Procedure ......................................................................................................76 UNIT – 11: Events ..................................................................................................................87 UNIT – 12: Error Handling.....................................................................................................93
1 UNIT - 1: INTRODUCTION TO EXCEL VBA Structure 1.0 Learning Objectives 1.1 Introduction 1.2 What Can You Do with VBA? 1.3 Advantages and disadvantages of using VBA 1.4 Summary 1.5 Glossary 1.6 References 1.0 Learning Objectives After studying this unit, you will be able to: • Get a conceptual overview of VBA • Find out what you can do with VBA • Discover the advantages and disadvantages of using VBA 1.1 Introduction If you already know how to use Microsoft Excel but there’re a few things you can’t do, it is time to learn the strongest functionality it has, Visual Basic for Applications (VBA). Visual Basic for Applications is a programming language incorporated in Microsoft Excel, Access, PowerPoint and even Word, which let you do all things you already know about them and much more. For example, you want that every time you open a specific Microsoft Word file it writes automatically the current date two lines below where you left last time. Or maybe you want a whole spreadsheet of Excel without formulas on it and still apply them as if they were there. How would you do that? All these things and much more are done with Visual Basic for Applications for Microsoft Office. VBA, which stands for Visual Basic for Applications, is a programming language developed by Microsoft -you know, the company that tries to get you to buy a new version of Windows every few years. Excel, along with the other members of Microsoft Office, includes the VBA
2 language (at no extra charge). In a nutshell, VBA is the tool that people like you and me use to develop programs that control Excel. Imagine an intelligent robot that knows all about Excel. This robot can read instructions, and it can also operate Excel very fast and accurately. When you want the robot to do something in Excel, you write up a set of robot instructions by using special codes. Then you tell the robot to follow your instructions, while you sit back and drink a glass of lemonade. That’s kind of what VBA is all about - a code language for robots. Note, however, that Excel does not come with a robot or lemonade. It helps techies to build customized applications and solutions to enhance the capabilities of those applications. The advantage of this facility is that you need not have visual basic installed on our PC, however, installing Office will implicitly help in achieving the purpose. You can use VBA in all office versions, right from MS-Office 97 to MS-Office 2016 and also with any of the latest versions available. Among VBA, Excel VBA is the most popular. The advantage of using VBA is that you can build very powerful tools in MS Excel using linear programming. Application of VBA You might wonder why to use VBA in Excel as MS-Excel itself provides loads of inbuilt functions. MS-Excel provides only basic inbuilt functions which might not be sufficient to perform complex calculations. Under such circumstances, VBA becomes the most obvious solution. For example, it is very hard to calculate the monthly repayment of a loan using Excel's built-in formulas. Rather, it is easy to program a VBA for such a calculation. 1.2 What can you do with VBA? You’re probably aware that people use Excel for thousands of different tasks. Here are just a few examples: ▪ Analyzing scientific data ▪ Budgeting and forecasting ▪ Creating invoices and other forms ▪ Developing charts from data ▪ Keeping lists of things such as customers’ names, students’ grades, or holiday gift ideas (a nice fruitcake would be lovely)
3 Every reader has in common is the need to automate some aspect of Excel. That, dear reader, is what VBA is all about. For example, you might create a VBA program to import some numbers and then format and print your month-end sales report. After developing and testing the program, you can execute the macro with a single command, causing Excel to automatically perform many time-consuming procedures. Rather than struggle through a tedious sequence of commands, you can click a button and then hop on over to Facebook and kill some time while your macro does the work. 1.3 Advantages and disadvantages of VBA In this section, we will be looking at the advantages and disadvantages of VBA. VBA advantages • Excel instructions are automated by VBA. • Excel always executes the task in exactly the same way. • Excel performs the task much faster than you can do it manually • Excel always performs the task without errors • Excel simplifies time-consuming tasks VBA disadvantages Let’s understand the disadvantages of VBA: • VBA programs must have their own copies of Excel • VBA is a moving target. 1.4 Summary ▪ Plan to distribute your Excel/VBA files to other users, it’s vitally important that you understand which version of Excel they use. ▪ People using an older version won’t be able to take advantage of features introduced in later versions. ▪ The advantage of using VBA is that you can build very powerful tools in MS Excel using linear programming.
4 1.5 Glossary ▪ Modules: Modules is the area where the code is written. This is a new Workbook, hence there aren't any Modules. ▪ Procedure: Procedures are a group of statements executed as a whole, which instructs Excel how to perform a specific task. ▪ Function: A function is a group of reusable code, which can be called anywhere in your program. ▪ Sub-Procedures-Sub-procedures work similar to functions. While sub procedures do not Return a value, functions may or may not return a value. 1.6 References • https://www.tutorialspoint.com/vba/vba_overview.html • https://www.tutorialspoint.com/vba/vba_tutorial.pdf • https://www.excel-easy.com/vba.html • http://lbabout.iis.p.lodz.pdf
5 UNIT - 2: GETTING STARTED WITH VBA Structure 2.0 Learning Objectives 2.1 Introduction 2.2 Opening the visual Basic Editor (VBE) 2.3 Declaring Variables 2.4 Adding A New Object Library Reference 2.5 Getting Started with the Excel Object model 2.6 Summary 2.7 Learning Activity 2.8 Glossary 2.9 References 2.0 Learning Objectives After studying this unit, you will be able to: • Open the Visual Basic Editor (VBE) • Add new New Object Library Reference • Start with The Excel Object Model 2.1 Introduction Microsoft Excel includes a comprehensive macro programming language called VBA. This programming language provides you with at least three additional resources: 1. Automatically drive Excel from code using Macros. For the most part, anything that the user can do by manipulating Excel from the user interface can be done by writing code in Excel VBA. 2. Create new, custom worksheet functions. 3. Interact Excel with other applications such as Microsoft Word, PowerPoint, Internet Explorer, Notepad, etc. VBA stands for Visual Basic for Applications. It is a custom version of the venerable Visual Basic programming language that has powered Microsoft Excel's macros since the mid-1990s
6 2.2: Opening the visual basic editor (VBE) Step 1: Open a Workbook Step 2 Option A: Press Alt + F11 This is the standard shortcut to open the VBE. Step 2 Option B: Developer Tab --> View Code First, the Developer Tab must be added to the ribbon. Go to File -> Options -> Customize Ribbon, then check the box for developer.
7 Then, go to the developer tab and click \"View Code\" or \"Visual Basic\" Step 2 Option C: View tab > Macros > Click Edit button to open an Existing Macro All three of these options will open the Visual Basic Editor (VBE):
8 2.3 Declaring Variables To explicitly declare variables in VBA, use the Dim statement, followed by the variable name and type. If a variable is used without being declared, or if no type is specified, it will be assigned the type Variant. Use the Option Explicit statement on first line of a module to force all variables to be declared before usage Always using Option Explicit is highly recommended because it helps prevent typo/spelling errors and ensures variables/objects will stay their intended type.
9 2.4 Adding a New Object Library Reference The procedure describes how to add an Object library reference, and afterwards how to declare new variables with reference to the new library class objects. The example below shows how to add the PowerPoint library to the existing VB Project. As can be seen, currently the PowerPoint Object library is not available. Step 1: Select Menu Tools --> References
10 Step 2: Select the Reference you want to add. This example we scroll down to find “Microsoft PowerPoint 14.0 Object Library”, and then press “OK”. Create code: Hello World 1. Open the Visual Basic Editor (Opening the Visual Basic Editor) 2. Click Insert --> Module to add a new Module:
11 3. Copy and Paste the following code in the new module: To obtain 4. Click on the green “play” arrow (or press F5) in the Visual Basic toolbar to run the program:
12 6. Done, you should see the following window: 2.5 Getting Started with the Excel Object Model 1. Open the Visual Basic Editor (VBE) 2. Click View --> Immediate Window to open the Immediate Window (or ctrl + G): 3. You should see the following Immediate Window at the bottom on VBE:
13 This window allows you to directly test some VBA code. So let's start, type in this console: VBE has intellisense and then it should open a tooltip as in the following figure: Select. Count in the list or directly type. Cout to obtain: 4. Then press Enter. The expression is evaluated and it should return 1. This indicates the number of Worksheet currently present in the workbook. The question mark (?) is an alias for Debug.Print. Worksheets is an Object and Count is a Method. Excel has several Object (Workbook, Worksheet, Range, Chart 4. Then press Enter The expression is evaluated and it should return 1. This indicates the number of Worksheet currently present in the workbook. 5. Now let's try another expression, type (without the? character):
14 2.6 Summary ▪ VBA in Excel to start the macros, enable 'Developer' menu in Excel 20XX. ▪ VBA in Excel as MS-Excel itself provides loads of inbuilt functions. ▪ VBA advantage is that you can build very powerful tools in MS Excel using linear programming. 2.7 Learning Activity 1. Record Your First Macro and Run It 2. How to enable the developer tab 2.8 Glossary ▪ property page: A grouping of properties presented as a tabbed page of a property sheet. ▪ drop source: The selected text or object that is dragged in a drag-and-drop operation. ▪ keyboard state: A return value that identifies which keys are pressed and whether the keyboard modifiers SHIFT, CTRL, and ALT are pressed. ▪ Target: An object onto which the user drops the object being dragged. 2.9 References • https://www.tutorialspoint.com/vba/vba_excel_macros.htm • https://www.listendata.com/2014/05/excel-macro-beginner-tutorial-make- your.html#developertab • https://www.excel-easy.com/vba.html
15 UNIT - 3: DATA TYPES & CONSTANTS Structure 3.0 Learning Objectives 3.1 Introduction 3.2 VBA Data types 3.3 Constant in VBA 3.4 Summary 3.5 Learning Activity 3.6 Glossary 3.7 References 3.0 Learning Objectives After studying this unit, you will be able to: • Explain VBA Data Types • Demonstrate Constant in VBA 3.1 Introduction Variables are used in almost all computer program and VBA is no different. It’s a good practice to declare a variable at the beginning of the procedure. It is not necessary, but it helps to identify the nature of the content (text, data, numbers, etc.) A data type is the characteristic of a variable that determines what kind of data it can hold. Data types include those in the following table as well as user-defined types and specific types of objects. Constant is a named memory location used to hold a value that cannot be changed during the script execution. If a user tries to change a Constant value, the script execution ends up with an error. Constants are declared the same way the variables are declared. 3.2 VBA Data Types Computer cannot differentiate between the numbers (1,2, 3.) and strings (a, b, c,). To make this differentiation, we use Data Types. VBA data types can be segregated into two types 1. Numeric data type 2. Non-Numeric Data Types
16 Below is the table that shows all the available data types you can use in VBA Excel. Numeric data type Non-Numeric Data Types In VBA, if the data type is not specified, it will automatically declare the variable as a Variant. Let see an example, on how to declare variables in VBA. In this example, we will declare three types of variables string, joining date and currency.
17 Step 1) insert the commandButton1 in our Excel sheet. Step 2) In next step, right-click on the button and select View code. It will open the code window as shown below. Step 3) In this step, • Save your file by clicking on save button • Then click on Excel icon in the same window to return the Excel sheet. • You can see the design mode is “on” highlighted in green
18 Step 4) Turn off design mode, before clicking on command button Step 5) After turning off the design mode, you will click on commandButton1. It will show the following variable as an output for the range we declared in code. • Name • Joining Date • Income in curreny 3.3 Constant in VBA Constant is like a variable, but you cannot modify it. To declare VBA constants, you can use keyword Const. There are two types of constant, • Built-in or intrinsic provided by the application. • Symbolic or user defined
19 You can either specify the scope as private by default or public. The syntax for VBA Variable, To declare a variable in VBA, type Dim followed by a name: Before we execute the variables, we have to record a macro in Excel. To record, a macro does the following – Step 1): Record the Macro 1 Step 2): Stop Macro 1 Step 3): Open the Macro editor, enter the code for variable in the Macro1 Step 4): Execute the code for Macro 1 For example, for VBA Variable Run this code and See the Output is coming. 3.4 Summary ▪ Variables are specific values that are stored in a computer memory or storage system. ▪ Variable use \"Dim\" keyword in syntax to declare explicitly ▪ VBA data types can be segregated into two types o Numeric Data Types or Non-Numeric Data Types ▪ Constant is like a variable, but you cannot modify it. To declare a constant in VBA you use keyword const. 3.5 Learning Activity 1. Highlight Negative Numbers 2. Highlight Cells with Comments
20 3.6 Glossary • Character code: A number that represents a particular character in a set, such as the ANSI character set. • Code pane: A pane contained in a code window that is used for entering and editing code. A code window can contain one or more code panes. • Constant: A named item that retains a constant value throughout the execution of a program • Data type: The characteristic of a variable that determines what kind of data it can hold. • Declaration: Nonexecutable code that names a constant, variable, or procedure, and specifies its characteristics, such as data type. 3.7 References • https://www.guru99.com/vba-data-types-variables-constant.html • https://www.automateexcel.com/vba-code-examples/ • https://www.javatpoint.com/vba-variables • https://www.educba.com/vba-dim/ • https://excelchamps.com/blog/useful-macro-codes-for-vba-newcomers/
21 UNIT - 4: ARRAYS Structure 4.0 Learning Objectives 4.1 Introduction 4.2 Advantages of arrays 4.3 Types of arrays 4.4 How to use Array in Excel VBA? 4.5 Testing our application 4.6 Summary 4.7 Learning Activity 4.8 Glossary 4.9 References 4.0 Learning Objectives After studying this unit, you will be able to: • Declare array • Assign Values to an array • Identify the types of array 4.1 Introduction We know very well that a variable is a container to store a value. Sometimes, developers are in a position to hold more than one value in a single variable at a time. When a series of values are stored in a single variable, then it is known as an array variable. You can declare an array to work with a set of values of the same data type. An array is a single variable with many compartments to store values, while a typical variable has only one storage compartment in which it can store only one value. Refer to the array as a whole when you want to refer to all the values it holds, or you can refer to its individual elements. For example, to store daily expenses for each day of the year, you can declare one array variable with 365 elements, rather than declaring 365 variables. Each element in an array contains one value. The following statement declares the array variable with 365 elements. By default, an array is indexed beginning with zero, so the upper bound of the array is 364 rather than 365.
22 By using an array, you can refer to the related values by the same name. You can use an index or subscript to tell them apart. The individual values are referred as the elements of the Excel VBA array. They are contiguous from index 0 through the highest index value. 4.2 Advantages of Arrays The following are some of the benefits offered by VBA array function • Group logically related data together –for storing information single array variable that has separate locations for student categories. • Arrays make eay to write maintainable code. • Better performance – once an array has been defined, it is faster to retrieve, sort, and modify data. 4.3 Types of Arrays in VBA VBA supports two types of arrays namely; • Static – These types of arrays have a fixed pre-determined number of elements that can be stored. One cannot change the size of the data type of a Static Array. These are useful when you want to work with known entities such as the number of days in a week, gender, etc. For Example: Dim ArrayMonth (12) As String • Dynamic – These types of arrays do not have a fixed pre-determined number of elements that can be stored. These are useful when working with entities that you cannot predetermine the number. For Example: Dim ArrayMonth () As Variant Syntax to declare arrays The syntax for declaring static arrays is as follows: Here, Action Code 1. It declares an array variable called arrayName with a size of Dim arrayName (n) n and datatype. Size refers to the number of elements that the datatype array can store. Dynamic arrays The syntax for declaring DYNAMIC arrays is as follows:
23 Here, Action Code 1. It declares an array variable Dim arrayName () datatype called arrayName without specifying the number of ReDim arrayName(4) elements 2. It specifies the array size after the array has been defined. Array Dimensions An array can be one dimension, two dimensions or multidimensional. • One dimension: In this dimension, the array uses only one index. For example, a number of people of each age. • Two dimensions: In this dimension, the array uses two indexes. For example, a number of students in each class. It requires number of classes and student number in each class • Multi-dimension: In this dimension, the array uses more than two indexes. For example, temperatures during the daytime. (30, 40, 20). 4.4 How to Use Array in Excel VBA We will create a simple application. This application populates an Excel sheet with data from an array variable. In this VBA Array example, we are going to do following things. • Create a new Microsoft Excel workbook and save it as Excel Macro-Enabled Workbook (*.xlsm) • Add a command button to the workbook • Set the name and caption properties of the command button • Programming the VBA that populates the Excel sheet Let do this exercise step by step, Step 1 – Create a new workbook 1. Open Microsoft Excel
24 2. Save the new workbook as VBA Arrays.xlsm Step 2 – Add a command button 1. Add a command button to the sheet 2. Set the name property to cmdLoadBeverages 3. Set the caption property to Load Beverages Your GUI should now be as follows Step 3 – Save the file 1. Click on save as button 2. Choose Excel Macro-Enabled Workbook (*.xlsm) as shown in the image below Step 4 – Write the code We will now write the code for our application 1. Right click on Load Beverages button and select view code 2. Add the following code to the click event of cmdLoadBeverages
25 Here, Code Action Dim Drinks(1 To 4) As String It declares an array variable called Drinks. The first array index is 1 and the last array index is 4. Drinks(1) = “Pepsi” Assigns the value Pepsi to the first array element. The other similar code does the same for the other elements in the array Sheet1.Cells(1, 1).Value = “My Favorite Writes the value My Favorite Beverages in cell Beverages.” address A1. Sheet1 makes reference to the sheet, and Cells(1,1) makes reference to row number 1 and column 1 (B) Sheet1.Cells(2, 1).Value = Drinks(1) Writes the value of the array element with index 1 to row number two of column 1
26 4.5 Testing Our Application Select the developer tab and ensure that the Design mode button is “off.” The indicator is, it will have a white background and not a coloured (greenish) background Click on Load Beverages button You will get the following results 4.6 Summary ▪ An array is a variable capable of storing more than one value ▪ Excel VBA supports static and dynamic arrays ▪ Arrays make it easy to write maintainable code compared to declaring a lot of variables for data that is logically related. ▪ Arrays are an efficient way of storing a list of items of the same type. ▪ Array item can be acessed directly using the number of the location which is known as the subscript or index.
27 4.7 Learning Activity 1. Copy an entire worksheet of 20 columns x 1000 rows to a 2-dimensional array with a single statement. 2. How to read from Range to the VBA Array? 4.8 Glossary • Drop source: The selected text or object that is dragged in a drag-and-drop operation. • grid block: The space between two adjacent grid points. • keyboard state: A return value that identifies which keys are pressed and whether the keyboard modifiers SHIFT, CTRL, and ALT are pressed. 4.9 References • https://www.guru99.com/vba-arrays.html • https://www.excel-easy.com/vba/array.html • https://www.automateexcel.com/vba/arrays/ • https://excelmacromastery.com/excel-vba-array/
28 UNIT - 5: LOOPING WITH VBA Structure 5.0 Learning Objectives 5.1 Introduction 5.2 Do until 5.3 Do While 5.4 For Loops 5.5 Summary 5.6 Learning Activity 5.7 Glossary 5.8 References 5.0 Learning Objectives After studying this unit, you will be able to: • Get a conceptual overview of Do until loop. • Find out what you can do with Do while loop • Overview of for Loops 5.1 Introduction Loops are one of the most powerful programming tools in VBA, and they allow users to repeat the same code block multiple times until a specific point is attained or a given condition is met. Once the condition is fulfilled, the program executes the next section of the code. Program looping is the repetition of a block of code a specified number of times. The number of times the block of code is repeated may be well defined or based on a conditional statement. All computer languages contain looping structures because these structures are excellent at solving problems that would otherwise require repetitive code. Imagine a program whose function it is to search for a specific name in a column of data with one hundred entries. A program with one hundred If/Then statements testing the value of each cell for the required name will solve the problem. The program would be technically easy to create, but cumbersome to type the repetitive code and it would look awful. Fortunately, we have looping code structures to help us.
29 The Loop command allows users to go through a set of values in a statement or groups of statements and analyze each value individually. Rather than repeat a block of code multiple times, it enables users to write a few lines of code and achieve a significant output without the need to write code for each of the repetitive tasks. Types of Loops in Excel There are various types of VBA loops in Excel that help users automate tasks that would otherwise require long lines of code. The following are the main types of loops in Excel: • Do Until Loop • Do While Loop • For Loop 5.2 Do Until Loop The Do Until loop continues repeating until the condition is met or the specified condition evaluates to TRUE. The Do Until statements will continue to be executed as long as the condition is false. The criteria are specified immediately after the “Do Until” statement, and it ends with the loop statement. The syntax for Do Until loop can take the following two options: Option 1: Do [Until Condition] [Code block to execute] Loop Option 2: Do [Code block to execute] Loop [Until Condition]
30 The primary difference between options 1 and 2 above is when the Until Condition is checked. In the first option, the Until Condition is checked first before the code blocks are executed. On the other hand, in the second option, the code block is executed first, and the Until Condition is checked. In the first option, if the Until Condition is True, the code block will not be executed, whereas, in the second option, the code block will be executed at least once since the Until Condition is checked after the code has been executed. Example of Do Until Loop In this example, we use Do Until... loop to check the condition at the starting of the loop. The statement inside the loop is executed only if the condition is false. And it exists out of the loop when then the condition is True. After executing the code, you will get the output: 5.3 Do While Loop The Do While Loop allows users to continuously execute a section of code while the condition is met or evaluated to TRUE. The syntax for a Do While Loop is as follows:
31 Do while [Condition Statement] [Code block to execute] Loop Alternatively, the syntax for the Do While Loop can be as follows: Do While [Code block to execute] Loop [Condition statement] The main difference between the two syntax options is when the condition statement is checked. In option one, the condition statement is checked before the start of the loop. Once the statement is checked, the program moves to execute the code written below the Do While statement to help control the number of loops. The code to be looped will only be executed if the condition statement has not been satisfied. If the condition statement is satisfied, the code will stop looping. In the second option, the code block will be executed first, while the condition statement will be checked next. If the condition is not met, the code block will run at least once as the condition statement is checked. It is opposite to Option 1 syntax, where the code block below the condition statement will not be executed even once if the condition is satisfied. Example The below example uses Do While... loop to check the condition at the start of the loop. The statements inside the loop will be executed, only when the condition becomes True. After executing the code, you will get the following output.
32 Example 2 In this example, we use Do...while loop to check the condition at the end of the loop. The statements inside the loop should execute at least once, even if the condition is False. After executing the code, you will get the following output. 5.4 For Loop The For loop is a control structure that allows the user to write a loop that will be executed a specific number of times. In this case, the user specifies the number of times they want the loop to run. For loop is classified into Next Loop and For Each Loop. The syntax for a For Loop is written as follows: For counter = start To end [Step value] [Code block to execute] Next [Counter]
33 Step: ▪ The counter variable is i, which is required to be declared. ▪ The Start_value of the counter is 1, and its End_value of the counter is 10, both are the numeric values. ▪ The keyword \"Step\" is optional, and it is a numeric value by which the counter is incremented each time when the loop is run. ▪ The next statement is increment the counter by the step value and returns to the for the statement, which repeats the code if the counter value does not exceed the \"End_value\" of 10. ▪ If the counter is equal to the End_value, the loop will be continued, or it will be stopped only when the End_value exceeds. Nested For Next loops You also can use the nested for loop to get the more complex automation done in excel. There would be a \"For Next\" loop within a \"For Next\" loop. For example: Suppose you want to protect all the worksheets in all open workbooks in your system. 5.5 Summary ▪ A loop allows users to repeat the same task multiple times without having to write code for each of the tasks.
34 ▪ The main types of loops in VBA include Do Until Loop, Do While Loop, and For Loop. ▪ The type of loop determines the beginning and ending statement of a block of code. 5.6 Learning Activity 1. Create an automation that increments an integer variable from 0 to 10, and displays a message every time it is incremented. 2. Exemplify how to use a While activity, let’s create an automation that increments an integer variable from 10 to 100, and writes all the numbers to a Microsoft Word document. 5.7 Glossary • Call: We can use “Call” to run one subprocedure from another. • Code Window: The VBA Code Window is where we write, display. or edit code. Quickly access the Code Window by selecting F7. • Do.. While: A commonly used form of looping code that allows a section of code to run more than once. “Do While” loops continue while a condition remains true. • Do…Until: A commonly used form of looping code that allows a section of code to run more than once. “Do Until” loops continue until the condition is true. 5.8 References • https://docs.uipath.com/studio/docs/the-while-activity • https://corporatefinanceinstitute.com/resources/excel/study/vba-loop/#gf_355 • https://www.javatpoint.com/vba-for-loop • https://sites.ualberta.ca/~jbb/files/Chapter01_VBA.pdf
35 UNIT – 6: OPERATORS Structure 6.0 Learning Objectives 6.1 Introduction 6.2 Assignment Operator 6.3 Arithmetic Operator 6.4 Comparison Operator 6.5 Logical Operator 6.6 Concatenation Operator 6.7 Summary 6.8 Learning Activity 6.9 Glossary 6.10 References 6.0 Learning Objectives After studying this unit, you will able to understand • Explain different types of operators. • Illustrate the use of opeators. 6.1 Introduction 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
36 4. Logical Operator 5. Concatenation Operator Let us take a look at each type of Operator in Excel VBA. 6.2 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 the symbol \"=\" (equals to). We use this operator to assign values to variables in excel VBA. It is also used as a comparison operator in VBA 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\"). 6.3 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 form b and prints it in the Cell \"A1\".
37 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.
38 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 +. 6.4 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 operato 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.
39 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. You can check it by simply using the statement. 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. You can check it by simply using the statement. This will print True in the immediate window. (>) Greater Than: This is used to check if the left value is greater than the right value or not.
40 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. You can check it by simply using the statement. This will print False in the immediate window. (>=) 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. You can check it by simply using the statement. This will print False in the immediate window. (<>) Not Equal To: This is used to check if the left value is not equal to the right value.
41 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. You can check it by simply using the statement. This will print True in the immediate window. The above six operators are called comparison operators as we use them to compare two values or variables. 6.5 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:
42 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. 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. Example: Assume variable A holds 20 and variable B holds 0, then a<>0 OR b<>0 is true.
43 Output: OR LOGICAL Operator Result is: True
44 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 (?). Example Assume variable A holds 20 and variable B holds 0, then NOT(a<>0 OR b<>0) is false. Output: NOT LOGICAL Operator Result is: False
45 VBA XOR (Logical XOR Operator): It is the combination of NOT and OR Operator. If one, and only one, of the expressions, evaluate to be True, the result is True. Example: Assume variable A holds 20 and variable B holds 0, then (a<>0 XOR b<>0) is true.
46 Output: XOR LOGICAL Operator Result is: True 6.6 Concatenation Operator To concatenate operator mostly used in Excel is “&” for numbers as well as strings. But for strings, we can also use “+” operator to concatenate two or more strings. The syntax to concatenate using formula is: cell_number1 & cell_number2; without space in between cell_number1 & \" \" & cell_number2; with space in between cell_number1 & \"Any_Symbol\" & cell_number2; with any symbol in between
47 cell_number(s) & \"string text”; concatenate cell values and strings \"string_text\" & cell_number(s); concatenate cell values and strings Concatenate Two Numbers: Example 1: Take any two numbers as input and concatenate into a single number. The code to concatenate two numbers in Excel is: Run the above code in VBA and the output will be shown in the message box. Output: The result after concatenating two numbers are: 1050
Search
