48 6.7 Summary ▪ In VBA Operators are the symbols in any computer language or mathematical calculation which is used to compute or compare some given expression. ▪ In VBA Assignment, Arithmatic, comparison, logical, Concatentation operator to compute the expressions ▪ In VBA, we mostly use the comparison operators in most of the line of our code. 6.8 Learning Activity 1. Test how the equals to operator work. 2. Test the less than operator 6.9 Glossary • add-in: A customized tool that adds capabilities to the Visual Basic development environment. • Application: A collection of code and visual elements that work together as a single program. • ASCII Character Set: American Standard Code for Information Interchange (ASCII) 7- bit character set used to represent letters and symbols found on a standard U.S. keyboard • Boolean expression: An expression that evaluates to either True or False. 6.10 REFERENCES • https://www.geeksforgeeks.org/excel-vba-logical-operators/ • /www.exceltip.com/general-topics-in-vba/operators-in-excel-vba.html • https://www.educba.com/vba-operators/ • https://data-flair.training/blogs/vba-comparison-operators/
49 UNIT - 7: DECISION Structure 7.0 Learning Objectives 7.1 Introduction 7.2 If Statement 7.3 If Else Statement 7.4 If Elseif - Else statement 7.5 Nested If Statement 7.6 Switch Statement. 7.7 Summary 7.8 Glossary 7.9 References 7.0 Learning Objectives After studying this unit, you will be able to: • Demonstrate the conceptual overview of Decision • Illustrate types of decision-making statements • Find out what you can do with decision statements 7.1 Introduction Decision making allows the programmers to control the execution flow of a script or one of its sections. The execution is governed by one or more conditional statements. Following is the general form of a typical decision-making structure found in most of the programming languages.
50 7.2 If Statement An If statement consists of a Boolean expression followed by one or more statements. If the condition is said to be True, the statements under If condition(s) are executed. If the condition is said to be False, the statements after the If loop are executed. Syntax Following is the syntax of an If statement in VBScript. Flow Diagram
51 Example- Find the biggest between the two numbers of an Excel with the help of a function. When the above code is executed, it produces the following result. Output 7.3 If-Else Statement An If statement consists of a Boolean expression followed by one or more statements. If the condition is said to be True, the statements under If condition(s) are executed. If the condition is said to be False, the statements under Else Part is executed.
52 Flow Diagram Example Fnd the biggest between the two numbers of an Excel with the help of a function. When the above code is executed, it produces the following result. Output 7.4 If Elseif - Else Statement An If statement followed by one or more ElseIf statements that consists of boolean expressions and then followed by a default else statement, which executes when all the condition becomes false.
53 Syntax Following is the syntax of an If Elseif - Else statement in VBScript. Flow Diagram
54 Example Find the biggest between the two numbers of an Excel with the help of a function. When the above code is executed, it produces the following result. Output 7.5 Nested If Statement An If or ElseIf statement inside another If or ElseIf statement(s). The inner If statements are executed based on the outermost If statements. This enables VBScript to handle complex conditions with ease. Syntax Following is the syntax of an Nested If statement in VBScript.
55 Example Find the type of a positive number with the help of a function. When the above code is executed, it produces the following result. Output 7.6 Switch Statement When a user wants to execute a group of statements depending upon a value of an Expression, then Switch Case is used. Each value is called a Case, and the variable is being switched ON based on each case. Case Else statement is executed if the test expression doesn't match any of the Case specified by the user. Case Else is an optional statement within Select Case, however, it is a good programming practice to always have a Case Else statement. Syntax Following is the syntax of a Switch statement in VBScript.
56 Example Find the type of integer with the help of a function. When the above code is executed, it produces the following result. Output
57 7.7 Summary ▪ Decision making allows the programmers to control the execution flow of a script or one of its sections. ▪ An if statement consists of a Boolean expression followed by one or more statements ▪ An if else statement consists of a Boolean expression followed by one or more statements. ▪ An if statement followed by one or more ElseIf statements, that consists of Boolean expressions and then followed by an optional else statement, ▪ A switch statement allows a variable to be tested for equality against a list of values. 7.8 Learning Activity 1. Let us find the biggest between the two numbers of an Excel with the help of a function. 2. Find the type of integer with the help of a function. 7.9 Glossary • Expression: A combination of keywords, operators, variables, and constants that yields a string, number, or object. • Form: A window or dialog box. Forms are containers for controls. • In process: Running in the same address space as an application. • line number: Used to identify a single line of code. A line number can be any combination of digits that is unique within the module where it is used. 7.10 References • https://www.techonthenet.com/excel/formulas/if_then.php • https://trumpexcel.com/if-then-else-vba/ • https://analysistabs.com/excel-vba/conditional-statements/ • https://excelmacromastery.com/vba-if/
58 UNIT – 8: STRINGS Structure 8.0 Learning Objectives 8.1 Introduction 8.2 String function 8.3 Summary 8.4 Glossary 8.5 References 8.0 Learning Objectives After studying this unit, you will be able to clarify the use of string functions 8.1 Introduction In this chapter, you'll find the most important functions to manipulate strings in VBA such as concatenation, add or remove extra spaces, replace strings or part of strings, find part of strings on the left or right side or in the mid. We end with searching of a specified substring with InStr, InStrRev, StrReverse. Strings are a sequence of characters, which can consist of either alphabets, numbers, special characters, or all of them. A variable is said to be a string if it is enclosed within double quotes \" \". Syntax 8.1 String Functions There are predefined VBA String functions, which help the developers to work with the strings very effectively. Following are String methods that are supported in VBA. Instr- The InStr Function returns the first occurrence of one string within another string. The search happens from the left to the right.
59 Syntax Parameter Description • Start − An optional parameter. Specifies the starting position for the search. The search begins at the first position from the left to the right. • String1 − A required parameter. String to be searched. • String2 − A required parameter. String against which String1 is searched. • Compare − An optional parameter. Specifies the string comparison to be used. It can take the following mentioned values. ▪ 0 = vbBinaryCompare - Performs Binary Comparison (Default) ▪ 1 = vbTextCompare - Performs Text Comparison Example Add a button and add the following function. When you execute the above function, it produces the following output. Output:
60 InString Reverse- The InStrRev function returns the first occurrence of one string within another string. The Search happens from the right to the left. Syntax Parameter Description • String1 − A required parameter. String to be searched. • String2 − A required parameter. String against which String1 is searched. • Start − An optional parameter. Specifies the starting position for the search. The search begins at the first position from the right to the left. • Compare − An optional parameter. Specifies the string comparison to be used. It can take the following mentioned values. ▪ 0 = vbBinaryCompare - Performs Binary Comparison (Default) ▪ 1 = vbTextCompare - Performs Text Comparison Example Add a button and place the following function. Upon executing the above script, it produces the following result. Output:
61 Lcase-The LCase function returns the string after converting the entered string into lower case letters. Syntax Example Add a button and place the following function inside the same. Upon executing the above script, it produces the following output. Output: UCase- The UCase function returns the string after converting the entered string into UPPER case letters. Syntax Example Add a button and place the following function inside the same.
62 Upon executing the above script, it produces the following output. Output Left- The Left function returns a specified number of characters from the left side of the given input string. Syntax Parameter Description • String − A required parameter. Input String from which the specified number of characters to be returned from the left side. • Length − A required parameter. An Integer, which specifies the number of characters to be returned. Example Add a button and add the following function.
63 When you execute the above function, it produces the following output. Output: Right- The Right function returns a specified number of characters from the right side of the given input string. Syntax Parameter Description • String − A required parameter. Input String from which the specified number of characters to be returned from the right side. • Length − A required parameter. An Integer, which Specifies the number of characters to be returned. Example Add a button and add the following function.
64 When you execute the above function, it produces the following output. Output: Mid- The Mid Function returns a specified number of characters from a given input string Syntax Parameter Description • String − A required parameter. Input String from which the specified number of characters to be returned. • Start − A required parameter. An Integer, which specifies the starting position of the string. • Length − An optional parameter. An Integer, which specifies the number of characters to be returned. Example Add a button and add the following function. When you execute the above function, it produces the following output.
65 Output: Ltrim- The Ltrim function removes the blank spaces from the left side of the string. Syntax Example Add a button and add the following function. When you execute the function, it produces the following output. Output: Rtrim- The Rtrim function removes the blank spaces from the right side of the string. Syntax Example Add a button and add the following function. When you execute the above function, it produces the following output.
66 Trim- The Trim function removes both the leading and the trailing blank spaces of the given input string. Syntax Example Add a button and add the following function. When you execute the above function, it produces the following output. Ouput: Len- The Len function returns the length of the given input string including the blank spaces. Syntax Example Add a button and add the following function. When you execute the above function, it produces the following output. Output:
67 Replace- The Replace function replaces a specified part of a string with a specific string, a specified number of times. Syntax Parameter Description • String − A required parameter. The Input String which is to be searched for replacing. • Find − A required parameter. The part of the string that will be replaced. • Replacewith − A required parameter. The replacement string, which would be replaced against the find parameter. • Start − An optional parameter. Specifies the start position from where the string has to be searched and replaced. Default value is 1. • Count − An optional parameter. Specifies the number of times the replacement has to be performed. • Compare − An optional paramete pecifies the comparison method to be used. Default value is 0. ▪ 0 = vbBinaryCompare - Performs a binary comparison ▪ 1 = vbTextCompare - Performs a Textual comparison Example
68 When you execute the above function, it produces the following output. Output: Space- The Space function fills a string with a specific number of spaces. Syntax Parameter Description • Number − A required parameter. The number of spaces that we want to add to the given string. Example When you execute the above function, it produces the following output. strComp- The StrComp function returns an integer value after comparing the two given strings. It can return any of the three values -1, 0, or 1 based on the input strings to be compared. • If String 1 < String 2, then StrComp returns -1 • If String 1 = String 2, then StrComp returns 0 • If String 1 > String 2, then StrComp returns Syntax
69 Parameter Description • String1 − A required parameter. The first string expression. • String2 − A required parameter. The second string expression. • Compare − An optional parameter. Specifies the string comparison to be used. It can take the following values. ▪ 0 = vbBinaryCompare - Performs Binary Comparison(Default) ▪ 1 = vbTextCompare - Performs Text Comparison Example Add a button and add the following function When you execute the above function, it produces the following output. Output: String Function The String function fills a string with the specified character for specified number of times. Syntax Parameter Description • Number − A required parameter. An integer value, which would be repeated for a specified number of times against the character parameter. • Character − A required parameter. Character value, which has to be repeated for a specified number of times. Example Add a button and add the following function.
70 When you execute the above function, it produces the following output. Output: StrReverse- The StrReverse function reverses the specified string. Syntax Example Add a button and add the following function. 8.3 Summary ▪ Strings are a sequence of characters Consist of either alphabets, numbers, special characters. ▪ In a String in variables is said to be string If it is enclosed within double quotes \" \". ▪ String Functions are helping the developers to work with the strings very effectively. 8.4 Learning Activity • Create an Empty Spaced String • Create reverse string
71 8.5 Glossary • MsgBox: This VBA function displays a pop-up message box. Message boxes are typically used to provide information and guidance. • User Defined Functions: VBA in Excel allows us to create custom functions. • Mid Function: returns a specified number of characters from a given input string. • Strings: Strings are a sequence of characters, which can consist of either alphabets, numbers, special characters. 8.6 References • http://www.functionx.com/vbaexcel/topics/strings.htm • https://excelmacromastery.com/vba-string-functions/ • https://www.techonthenet.com/excel/formulas/instr.php • http://excelerator.solutions/2017/10/02/working-strings-excel-vba/
72 UNIT – 9: FUNCTIONS Structure 9.0 Learning Objectives 9.1 Introduction 9.2 Calling a Function 9.3 User defined function 9.4 Summary 9.5 Glossary 9.6 References 9.0 Learning Objectives After studying this unit, you will be able to describe the purpose of function in VBA 9.1 Introduction A function is a piece of code that performs a specific task and returns a result. Functions are mostly used to carry out repetitive tasks such as formatting data for output, performing calculations, etc. Suppose you are developing a program that calculates interest on a loan. You can create a function that accepts the loan amount and the payback period. The function can then use the loan amount and payback period to calculate the interest and return the value. VBA Syntax for declaring Function Here in the syntax, Action Code • Here the keyword “Function” is used to declare a function named “myFunction” and start the body of “Private Function the function. myFunction(…)”
73 Code Action • The keyword ‘Private’ is used to specify the scope of the function “ByVal arg1 As Integer, It declares two parameters of integer data type named ByVal arg2 As Integer” ‘arg1’ and ‘arg2.’ myFunction = arg1 + arg2 evaluates the expression arg1 + arg2 and assigns the result to the name of the function. “End Function” “End Sub” is used to end the body of the function 9.3 Calling a Function Function demonstrated with Example: Functions are very similar to the subroutine. The major difference between a subroutine and a function is that the function returns a value when it is called. While a subroutine does not return a value, when it is called. Let’s say you want to add two numbers. You can create a function that accepts two numbers and returns the sum of the numbers. 1. Create the user interface 2. Add the function 3. Write code for the command button 4. Test the code Set the following properties of CommanButton1 to the following. S/N Control Property Value 1 CommandButton1 Name btnAddNumbers 2 ……………….. Caption Add Numbers Function
74 Your interface should now appear as follows 9.4 User Defined Function Syntax Add a button and add the following function. Example Add the following function which returns the area. Note that a value/ values can be returned with the function name itself.
75 9.5 Summary ▪ Function can return multiple values separated by a comma as an array assigned to the function name itself. ▪ Function in VBA is defined by using by using the Function keyword, followed by a unique function name ▪ Function can be invoking by calling the function using the function name 9.6 Learning Activity 1. Call a function using arguments position. 2. Call a function using explicit arguments. 9.7 Glossary • Properties: Properties are attributes of objects. Each object has a set of properties associated with it, such as its numeric value, its font style, or color. • User Defined Functions: To create a user-defined function, the code must start with “Function” and end with “End Function”. • WorksheetFunction: To use Excel functions in VBA code, the function must be preceded by “Application.WorksheetFunction” • Arguments: VBA procedures can be passed data via arguments 9.8 References • https://trumpexcel.com/user-defined-function-vba/ • https://www.guru99.com/vba-function.html • https://excelchamps.com/vba/user-defined-function/ • https://www.excelfunctions.net/vba-functions-and-subroutines.htm
76 UNIT – 10: SUB PROCEDURE Structure 10.0 Learning Objectives 10.1 Introduction 10.2 Calling Procedures 10.3 Summary 10.4 Glossary 10.5 References 10.0 Learning Objectives After studying this unit, you will be able to explain the use of sub procedure in VBA Excel. 10.1 Introduction Sub Procedures are similar to functions, however there are a few differences. • Sub procedures do not Return a value while functions may or may not return a value. • Sub procedures can be called without a call keyword. • Sub procedures are always enclosed within Sub and End Sub statements. Sub procedure are one of the major cornerstones of VBA. A Sub procedure does things. They perform actions such as formatting a table or creating a pivot table. The majority of procedures written are Sub procedures. All macros are Sub procedures. A sub procedure begins with a Sub statement and ends with an End Sub statement. The procedure name is always followed by parentheses. Example
77 10.3 Calling Procedures To invoke a Procedure somewhere in the script, you can make a call from a function. We will not be able to use the same way as that of a function as sub procedure WILL NOT return a value. Now you will be able to call the function only but not the sub procedure as shown in the following screenshot. The area is calculated and shown only in the Message box. The result cell displays ZERO as the area value is NOT returned from the function. In short, you cannot make a direct call to a sub procedure from the excel worksheet.
78 Types of Sub Procedures in VBA A sub procedure can take two forms, i.e., private and public. The modifiers “private” and “public” allows users to use the subs differently. The private sub procedure can only be used in the current module. The public sub allows users to use the procedure in all modules present in the workbook. 10.4 Summary ▪ Sub procedure, is a piece of code that is used to perform a specific task mentioned in the code but does not return any kind of value. ▪ Sub procedure a task but does not return a value. ▪ Sub procedure cannot be used directly in spreadsheets as formulas. ▪ Subs can be recalled from anywhere in the program and in multiple types. 10.6 Learning Activity 1. Create a sub-procedure to find the area of a circle. 2. Create Sub-procedure to find the diameter of a circle. 10.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. • Locals Window: This is the name given to code that allows for a section of code to run more than once. • Module: A standard module contains code that can be shared among all modules in a project. 10.8 References • https://www.softwaretestinghelp.com/vba-functions-and-sub- procedures/#What_Is_A_Sub-Procedure • https://powerspreadsheets.com/vba-sub-procedures/ • https://www.automateexcel.com/vba/sub-procedure/ • https://analysistabs.com/excel-vba/sub-procedures/
79 UNIT – 11: EVENTS Structure 11.0 Learning Objectives 11.1 Introduction 11.2 Worksheet Events 11.3 Workbook Events 11.4 Summary 11.5 Learning Activity 11.6 Glossary 11.7 References 11.0 Learning Objectives After studying this unit, you will be able to illustrate the use of events with its types. 11.1 Introduction An event is an action that can trigger the execution of the specified macro. For example, when you open a new workbook, it’s an event. When you insert a new worksheet, it’s an event. When you double-click on a cell, it’s an event. There are many such events in VBA, and you can create codes for these events. This means that as soon as an event occurs, and if you have specified a code for that event, that code would instantly be executed. There are different objects in Excel – such as Excel itself (to which we often refer to as the application), workbooks, worksheets, charts, etc. Each of these objects can have various events associated with it. For example: • If you create a new workbook, it’s an application-level event. • If you add a new worksheet, it’s a workbook level event. • If you change the value in a cell in a sheet, it’s a worksheet level event. Kinds of event VBA, an event-driven programming can be triggered when you change a cell or range of cell values manually. Change event may make things easier, but you can very quickly end a page full of formatting. There are two kinds of events. • Worksheet Events • Workbook Events
80 11.2 Worksheet Events Worksheet events are triggered when there is a change in the worksheet. It is created by performing a right-click on the sheet tab and choosing 'view code', and later pasting the code. The user can select each one of those worksheets and choose \"WorkSheet\" from the drop down to get the list of all supported Worksheet events. Following are the supported worksheet events that can be added by the user. Example Let us say, we just need to display a message before double click. Output Upon double-clicking on any cell, the message box is displayed to the user as shown in the following screenshot
81 11.3 Workbook Events Workbook events are triggered when there is a change in the workbook on the whole. We can add the code for workbook events by selecting the 'ThisWorkbook' and selecting 'workbook' from the dropdown as shown in the following screenshot. Immediately Workbook_open sub procedure is displayed to the user as seen in the following screenshot. Following are the supported Workbook events that can be added by the user. Example Let us say, we just need to display a message to the user that a new sheet is created successfully, whenever a new sheet is created.
82 Output Upon creating a new excel sheet, a message is displayed to the user as shown in the following screenshot. 11.4 Summary • A workbook event is defined as an action that triggers the execution of a specific macro in Excel. • VBA automatically executes an event once a user specifies the code for an event that has already occurred. • An example of a VBA worksheet event is Open, which is triggered as soon as a Workbook is activated. 11.5 Learning Activity 1. Range (A1:D10) to show a message whenever a cell is changed in range 2. Highlight the active row and column of the selected cell for table. 11.6 Glossary • Event-An event is a condition or the result of an activity at a specific point in a program. • Application object: Returns the currently active invoice definition object (Definition), or a NULL object if no invoice definition is active. • Application: A collection of code and visual elements that work together as a single program.
83 • Automation object: An object that is exposed to other applications or programming tools through Automation interfaces. 11.7 References • https://www.excel-easy.com/vba/events.html • https://www.automateexcel.com/vba/events/ • https://www.excel-pratique.com/en/vba/workbook_events • http://www.cpearson.com/excel/Events.aspx
84 UNIT – 12: ERROR HANDLING Structure 12.0 Learning Objectives 12.1 Introduction 12.2 On Error statements 12.3 Summary 12.4 Learning Activity 12.5 Glossary 12.6 References 12.0 Learning Objectives After studying this unit, you will be able to: • Learn different types of errors and how to resolve them. • Explain On Error statements that are useful to catch the error and fix them accordingly. 12.1 Introduction Error is basically a mistake in the code you have written and that mistake or error does not make your code work as expected and thereby gives you an unexpected output. There are three types of errors in programming: a) Syntax Errors, b) Runtime Errors c) Logical Errors. Syntax Errors We all know that Syntax is an integral part of any programming language. VBA checks for any syntax errors each time when you hit enter and displays a dialog showing the expected syntax. Syntax errors, also called as parsing errors, occur at the interpretation time for VBScript. For example, the following line causes a syntax error because it is missing a closing parenthesis.
85 Logical Errors Logical errors can be the most difficult type of errors to track down. These errors are not the result of a syntax or runtime error. Instead, they occur when you make a mistake in the logic that drives your script and you do not get the result you expected. You cannot catch those errors, because it depends on your business requirement what type of logic you want to put in your program. For example, dividing a number by zero or a script that is written which enters into infinite loop. Err Object Assume if we have a runtime error, then the execution stops by displaying the error message. As a developer, if we want to capture the error, then Error Object is used. Example In the following example, Err.Number gives the error number and Err.Description gives the error description. 12.2 On Error Statements On Error Statements activate the error handling in VBA. Using this statement, you can instruct VBA to perform certain actions when the code encounters an error. Given below are the On Error Statements: 1. On Error Resume Next 2. On Error GoTo 0 3. On Error Goto Line On Error Resume Next This command will instruct the compiler to ignore any error in the code and execute the next step. You should be very cautious while using this, as you might get into serious trouble by ignoring certain Errors.
86 If no error handling is set in your code, On Error GoTo 0 is the default error handler. In this mode, any runtime errors will launch the typical VBA error message, allowing you to either end the code or enter debug mode, identifying the source. While writing code, this method is the simplest and most useful, but it should always be avoided for code that is distributed to end users, as this method is very unsightly and difficult for end users to understand. On Error Resume Next On Error Resume Next will cause VBA to ignore any errors that are thrown at runtime for all lines following the error call until the error handler has been changed. In very specific instances, this line can be useful, but it should be avoided outside of these cases. For example, when launching a separate program from an Excel Macro, the On Error Resume Next call can be useful if you are unsure whether or not the program is already open: Had we not used the On Error Resume Next call and the Powerpoint application was not already open, the GetObject method would throw an error. Thus, On Error Resume Next was necessary to avoid creating two instances of the application. On Error GoTo <line> This method of error handling is recommended for all code that is distributed to other users. This allows the programmer to control exactly how VBA handles an error by sending the code to the specified line. The tag can be filled with any string (including numeric strings), and will send the code to the corresponding string that is followed by a colon. Multiple error handling
87 blocks can be used by making different calls of On Error GoTo <line> . The subroutine below demonstrates the syntax of an On Error GoTo <line> call. If you exit your method with your error handling code, ensure that you clean up: • Undo anything that is partially completed • Close files • Reset screen updating • Reset calculation mode • Reset events • Reset mouse pointer • Call unload method on instances of objects, that persist after the End Sub • Reset status bar
88 12.3 Summary ▪ Error handling prevents end users from seeing VBA runtime errors ▪ Error handling helps the developer easily diagnose and correct errors. ▪ Error handling enables an error-handling routine 12.4 Learning Activity 1. Create code to handle type mismatch error and a Divide by 0 error in a different way using 2 different Error handlers. 2. write a function using On Error Resume Next. 12.5 Glossary • On Error Statements: On error statements bypass Excel’s built-in error handling procedures and allow us to create our own error handling procedures. • Option Explicit: With an “Option Explicit” statement, code cannot be run that contains any undeclared variables. Excel will generate an error if it encounters a misspelled variable. • Subscript Out of Range Error: This error is often caused by referencing a nonexistent object. • Object Browser: The Object Browser enables us to see a list of all the different objects with their methods and properties. The browser can be accessed by hitting F2 or by selecting it from the “View” pull-down menu. 12.6 References • https://riptutorial.com/excel-vba/example/5411/error-handling • https://www.softwaretestinghelp.com/error-handling-in-vba/ • https://www.myonlinetraininghub.com/error-handling-in-vba • http://www.cpearson.com/excel/errorhandling.htm
Search
