Category: vba

VBA – Operators ”; Previous Next An Operator can be defined using a simple expression – 4 &plus; 5 is equal to 9. Here, 4 and 5 are called operands and &plus; is called operator. VBA supports following types of operators − Arithmetic Operators Comparison Operators Logical (or Relational) Operators Concatenation Operators The Arithmatic Operators Following arithmetic operators are supported by VBA. Assume variable A holds 5 and variable B holds 10, then − Show Examples Operator Description Example &plus; Adds the two operands A &plus; B will give 15 – Subtracts the second operand from the first A – B will give -5 &ast; Multiplies both the operands A &ast; B will give 50 / Divides the numerator by the denominator B / A will give 2 % Modulus operator and the remainder after an integer division B % A will give 0 ^ Exponentiation operator B ^ A will give 100000 The Comparison Operators There are following comparison operators supported by VBA. Assume variable A holds 10 and variable B holds 20, then − Show Examples Operator Description Example = Checks if the value of the two operands are equal or not. If yes, then the condition is true. (A = B) is False. <> Checks if the value of the two operands are equal or not. If the values are not equal, then the condition is true. (A <> B) is True. > Checks if the value of the left operand is greater than the value of the right operand. If yes, then the condition is true. (A > B) is False. < Checks if the value of the left operand is less than the value of the right operand. If yes, then the condition is true. (A < B) is True. >= Checks if the value of the left operand is greater than or equal to the value of the right operand. If yes, then the condition is true. (A >= B) is False. <= Checks if the value of the left operand is less than or equal to the value of the right operand. If yes, then the condition is true. (A <= B) is True. The Logical Operators Following logical operators are supported by VBA. Assume variable A holds 10 and variable B holds 0, then − Show Examples Operator Description Example AND Called Logical AND operator. If both the conditions are True, then the Expression is true. a<>0 AND b<>0 is False. OR Called Logical OR Operator. If any of the two conditions are True, then the condition is true. a<>0 OR b<>0 is true. NOT Called Logical NOT Operator. Used to reverse the logical state of its operand. If a condition is true, then Logical NOT operator will make false. NOT(a<>0 OR b<>0) is false. XOR Called Logical Exclusion. It is the combination of NOT and OR Operator. If one, and only one, of the expressions evaluates to be True, the result is True. (a<>0 XOR b<>0) is true. The Concatenation Operators Following Concatenation operators are supported by VBA. Assume variable A holds 5 and variable B holds 10 then − Show Examples Operator Description Example &plus; Adds two Values as Variable. Values are Numeric A &plus; B will give 15 & Concatenates two Values A & B will give 510 Assume variable A = “Microsoft” and variable B = “VBScript”, then − Operator Description Example &plus; Concatenates two Values A &plus; B will give MicrosoftVBScript & Concatenates two Values A & B will give MicrosoftVBScript Note − Concatenation Operators can be used for both numbers and strings. The output depends on the context, if the variables hold numeric value or string value. Print Page Previous Next Advertisements ”;

VBA – Variables ”; Previous Next Variable is a named memory location used to hold a value that can be changed during the script execution. Following are the basic rules for naming a variable. You must use a letter as the first character. You can”t use a space, period (.), exclamation mark (!), or the characters &commat;, &, &dollar;, # in the name. Name can”t exceed 255 characters in length. You cannot use Visual Basic reserved keywords as variable name. Syntax In VBA, you need to declare the variables before using them. Dim <<variable_name>> As <<variable_type>> Data Types There are many VBA data types, which can be divided into two main categories, namely numeric and non-numeric data types. Numeric Data Types Following table displays the numeric data types and the allowed range of values. Type Range of Values Byte 0 to 255 Integer -32,768 to 32,767 Long -2,147,483,648 to 2,147,483,648 Single -3.402823E+38 to -1.401298E-45 for negative values 1.401298E-45 to 3.402823E+38 for positive values. Double -1.79769313486232e+308 to -4.94065645841247E-324 for negative values 4.94065645841247E-324 to 1.79769313486232e+308 for positive values. Currency -922,337,203,685,477.5808 to 922,337,203,685,477.5807 Decimal &plus;/- 79,228,162,514,264,337,593,543,950,335 if no decimal is use &plus;/- 7.9228162514264337593543950335 (28 decimal places). Non-Numeric Data Types Following table displays the non-numeric data types and the allowed range of values. Type Range of Values String (fixed length) 1 to 65,400 characters String (variable length) 0 to 2 billion characters Date January 1, 100 to December 31, 9999 Boolean True or False Object Any embedded object Variant (numeric) Any value as large as double Variant (text) Same as variable-length string Example Let us create a button and name it as ”Variables_demo” to demonstrate the use of variables. Private Sub say_helloworld_Click() Dim password As String password = “Admin#1” Dim num As Integer num = 1234 Dim BirthDay As Date BirthDay = DateValue(“30 / 10 / 2020”) MsgBox “Passowrd is ” & password & Chr(10) & “Value of num is ” & num & Chr(10) & “Value of Birthday is ” & BirthDay End Sub Output Upon executing the script, the output will be as shown in the following screenshot. Print Page Previous Next Advertisements ”;

VBA – Loops ”; Previous Next There may be a situation when you need to execute a block of code several number of times. In general, statements are executed sequentially: The first statement in a function is executed first, followed by the second, and so on. Programming languages provide various control structures that allow for more complicated execution paths. A loop statement allows us to execute a statement or group of statements multiple times. Following is the general form of a loop statement in VBA. VBA provides the following types of loops to handle looping requirements. Click the following links to check their detail. Sr.No. Loop Type & Description 1 for loop Executes a sequence of statements multiple times and abbreviates the code that manages the loop variable. 2 for ..each loop This is executed if there is at least one element in the group and reiterated for each element in a group. 3 while..wend loop This tests the condition before executing the loop body. 4 do..while loops The do..While statements will be executed as long as the condition is True.(i.e.,) The Loop should be repeated till the condition is False. 5 do..until loops The do..Until statements will be executed as long as the condition is False.(i.e.,) The Loop should be repeated till the condition is True. Loop Control Statements Loop control statements change execution from its normal sequence. When execution leaves a scope, all the remaining statements in the loop are NOT executed. VBA supports the following control statements. Click the following links to check their detail. S.No. Control Statement & Description 1 Exit For statement Terminates the For loop statement and transfers the execution to the statement immediately following the loop 2 Exit Do statement Terminates the Do While statement and transfers the execution to the statement immediately following the loop Print Page Previous Next Advertisements ”;

VBA – Macro Comments ”; Previous Next Comments are used to document the program logic and the user information with which other programmers can seamlessly work on the same code in future. It includes information such as developed by, modified by, and can also include incorporated logic. Comments are ignored by the interpreter while execution. Comments in VBA are denoted by two methods. Any statement that starts with a Single Quote (”) is treated as comment. Following is an example. ” This Script is invoked after successful login ” Written by : TutorialsPoint ” Return Value : True / False Any statement that starts with the keyword “REM”. Following is an example. REM This Script is written to Validate the Entered Input REM Modified by : Tutorials point/user2 Print Page Previous Next Advertisements ”;

VBA – Error Handling ”; Previous Next There are three types of errors in programming: (a) Syntax Errors, (b) Runtime Errors, and (c) Logical Errors. Syntax errors 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. Function ErrorHanlding_Demo() dim x,y x = “Tutorialspoint” y = Ucase(x End Function Runtime errors Runtime errors, also called exceptions, occur during execution, after interpretation. For example, the following line causes a runtime error because here the syntax is correct but at runtime it is trying to call fnmultiply, which is a non-existing function. Function ErrorHanlding_Demo1() Dim x,y x = 10 y = 20 z = fnadd(x,y) a = fnmultiply(x,y) End Function Function fnadd(x,y) fnadd = x + y End Function 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. Err.Raise 6 ” Raise an overflow error. MsgBox “Error # ” & CStr(Err.Number) & ” ” & Err.Description Err.Clear ” Clear the error. Error Handling VBA enables an error-handling routine and can also be used to disable an error-handling routine. Without an On Error statement, any run-time error that occurs is fatal: an error message is displayed, and the execution stops abruptly. On Error { GoTo [ line | 0 | -1 ] | Resume Next } Sr.No. Keyword & Description 1 GoTo line Enables the error-handling routine that starts at the line specified in the required line argument. The specified line must be in the same procedure as the On Error statement, or a compile-time error will occur. 2 GoTo 0 Disables the enabled error handler in the current procedure and resets it to Nothing. 3 GoTo -1 Disables the enabled exception in the current procedure and resets it to Nothing. 4 Resume Next Specifies that when a run-time error occurs, the control goes to the statement immediately following the statement where the error occurred, and the execution continues from that point. Example Public Sub OnErrorDemo() On Error GoTo ErrorHandler ” Enable error-handling routine. Dim x, y, z As Integer x = 50 y = 0 z = x / y ” Divide by ZERO Error Raises ErrorHandler: ” Error-handling routine. Select Case Err.Number ” Evaluate error number. Case 10 ” Divide by zero error MsgBox (“You attempted to divide by zero!”) Case Else MsgBox “UNKNOWN ERROR – Error# ” & Err.Number & ” : ” & Err.Description End Select Resume Next End Sub Print Page Previous Next Advertisements ”;

VBA – Programming Charts ”; Previous Next Using VBA, you can generate charts based on certain criteria. Let us take a look at it using an example. Step 1 − Enter the data against which the graph has to be generated. Step 2 − Create 3 buttons – one to generate a bar graph, another to generate a pie chart, and another to generate a column chart. Step 3 − Develop a Macro to generate each one of these type of charts. ” Procedure to Generate Pie Chart Private Sub fn_generate_pie_graph_Click() Dim cht As ChartObject For Each cht In Worksheets(1).ChartObjects cht.Chart.Type = xlPie Next cht End Sub ” Procedure to Generate Bar Graph Private Sub fn_Generate_Bar_Graph_Click() Dim cht As ChartObject For Each cht In Worksheets(1).ChartObjects cht.Chart.Type = xlBar Next cht End Sub ” Procedure to Generate Column Graph Private Sub fn_generate_column_graph_Click() Dim cht As ChartObject For Each cht In Worksheets(1).ChartObjects cht.Chart.Type = xlColumn Next cht End Sub Step 4 − Upon clicking the corresponding button, the chart is created. In the following output, click on generate Pie Chart button. Print Page Previous Next Advertisements ”;