VBA Review Sheet Option Explicit- What needs to be done when you write option explicit? This command is placed in the declaration section of all modules and forms. This will flag misspellings before the program runs. If you omit Option Explicit a new variable will be created for every variation in spelling. Entered at the very top of the code window. Can insert it by going to tools- declare all variables Variable Declarations-What do declaration statements do- make the program easier to understand and prevents some common errors Variable Naming Conventions- Not acceptable names, appropriate names, wrong names- Variable names must use only alpha characters, numeric characters, or the underscore. Variable names must begin with an alpha character (not a numeric character) and can be up to 255 characters long. Visual Basic is not case sensitive. Variable Types and Valid Cases of these types- Single , double, decimal, integer, string- , variant Constants- A symbolic constant is a memory location whose contents cannot be changed while the program is running. [Public] Const constname [As datatype] = expression The word ?Public? and the ?As datatype? are optional in the declaration. Public is used to declare a global constant in the general declaration area of a code module. If the datatype is not given, Visual Basic will assign the Object datatype. This may lead to a datatype that is inefficient for the program. Public Const conMaxNumber As Integer = 200 Const conMaxArray As Integer = 30 Const conMessage As String = "Hello" Variable Initializations/Assignments- Variables may be given an initial value (This is called initializing the variable). variable = initialization sngFrequency = 4 Numeric variables are initialized to zero by default. String variables (text variables) have default values of "" (called a Null string or empty string). Click Button Events- Comments in VBA- an apostrophe before the statement VBA Error Types-Syntax errors, Logic Errors, Program Errors, Run Time Errors- A syntax error is an error in the structure or spelling of a statement. Run-time errors are not caught by the compiler initially and stop the program while it is running. These could be caused by a calculation such as "divide by zero" or if the operating system detects a memory access violation. Logic errors are also not caught by the compiler and are characterized by erroneous, unexpected, or unintentional errors resulting from a flaw in the program?s logic. It is important to run a set of values through the program, which have a known outcome. The output can be checked to verify that it is the expected result. It is best to use several different values to ensure that the output will be what is expected. No output: Usually caused by a missing output object or the program skips the print, text or caption property assignment instruction. A skip usually occurs because the instruction is within a block which is executed after a condition is met and when the program is executed that condition is not met. Unappealing or misaligned output: This is usually caused by the type of object selected or the spacing within the strings that are printed. Incorrect numerical results: This is caused by incorrect values assigned to the variables used in an expression, the incorrect use of an arithmetic expression, the omission of a statement, a rounding error, the use of an improper sequence of instructions, or improper formatting. A program error can be detected in several ways: Before a program is compiled, While the program is being compiled, While it is being run, After the program has been executed and the output is being examined, Errors detected by the compiler are referred to as compile-time errors. Errors that occur while the program is being run are called run-time errors. Arithmetic Operators and Precedence- More complex arithmetic expressions (and in general, all types of expressions) are evaluated according to precedence (level of priority) of operators. The precedence levels for the arithmetic operators are: Exponent, Multiplication, Division Higher Integer Division, Modulus , Addition, Subtraction Lower Parentheses and their role in Arithmetic Operations and when they are necessary and when they are redundant- Parentheses may be used to override the normal precedence. Expressions enclosed in parentheses have the highest precedence and are evaluated first: + 7) / (6 - 4) evaluates first as 5 + 7 = 12 and 6 - 4 = 2 yielding 12/2 for a result of 6. Parentheses cannot be used to indicate multiplication. Instead the multiplication operator must be used: (5 + 7) (3 + 5) is invalid (5 + 7) * (3 + 5) is the correct expression Formats and how certain formats change the way they look: Formatcurrency, Formatnumber, Formatpercentage- Formatnumber:rounding to two decimals, unless specified by the ordered pair, if specified round to that number of decimal places ie (4838.4566, 3) is 4838.457 Fornatcurrency: round to two decimals and place a money sign or change based on ordered pair, move decimal two to the right and place a percent sign and round to two decimals ie (.4566) is 45.66% Relational Operators: >, >=, <, <=, =, < > How you describe not equal to using relational operators: < > Logical Operators: AND, OR, NOT What are the conditions to have a ?true? statement with AND: both statements on the left or right of and have to be true What are the conditions to have a ?true? statement with OR: one statement on the left or right of or have to be true Types of if-then-end if statements How and /or used with If statement conditions How to create input boxes and Syntax How to create a name for input box How to create a message with input box How to create message boxes and syntax Definitions of Repetition Loops Pretest- A Pretest loop begins with a condition or expression which is evaluated. If it evaluates TRUE, the instruction(s) following the condition are executed. Posttest- A Posttest loop will run through the instruction(s) once and then evaluate a condition or expression. At that point, if it evaluates TRUE, the instruction(s) will be executed again. Counter- To run a set of instructions a certain number of times, a counter controlled loop is used. Nested Loops- Whenever a loop is placed within another loop, it is called a nested loop. For i = 1 To 5 For j = 1 To 4 Range(?B? & i) = Range(?B? & i).Value & i & " X " & j _ & " = " & i * j & " " Next j Next i Note in the above example that there are two loops. The first loop is controlled by the value of i and is called the outer loop. The second loop is controlled by the value of j and is called the inner loop. All statements in the inner loop are contained within the boundaries of the outer loop. A different variable is controlling the inner loop. For each single trip through the outer loop, the inner loop runs through the entire sequence. In the above example, each time the outer loop counter (i) increases by one, the inner loop which is controlled by the counter j executes completely. For i=2 to 8 Next i Conditions when Do While- Loops are executed and not executed- Do While loops are pretest loops. The condition value must be True for the loop to execute at least once. The condition must be modified within the instructions for the loop to end normally. Conditions when Do -Loop Until loops are executed- Unlike Do While loops which require the evaluation of the condition at the start of the repetition loop, the Do Until loop will execute the instruction(s) before the evaluation of the condition making them post-test loops. Conditions when For-Next I loops are executed.- Counting the number of times loops are executed How are upper and lower cases treated by VBA How to concatenate strings in VBA- Strings can be concatenated or put together into one string by using the & (ampersand). strName = strFirstName & " " & strLastName Range(?A1?) = "Customer Name: " & strName When concatenating a string the programmer may have to enter the spacing between the strings to create a readable output. Review all of your tutorials and homework assignments to get comfortable with the specifics
Want to see the other 3 page(s) in Notes that will help you for final?JOIN TODAY FOR FREE!