Their If Function. In VBA Is One of the Most Frequently used of all statements. The If Function Checks If The Specified Condition Is Being Met or Not. The If Function in VBA Works in a Slightly Different Manner from How It Works in Excel. In Excel, The If Function Does Two Things: IT Checks Whether The Given Condition Evaluates to True or False and then Returns a Value to Use Based on the Evaluation i.e If ( cONDITION, RETURN_VALUE_IF_CONDITION_TRUE, RETURN_VALUE_IF_CONDITION_FALSE). HOWEVER, THE IF FUNCTION IN VBA DOES ONLY THE FIRST PART, I.E. Check If The Condition Evaluates To True or False. The Steps to Take in Either Scenario AS Specified Using The Additional Then Statement.
Using a simple if function in vba
HERE'S AN EXAMPLE OF THE If Function.. This Piece of VBA Code IS SIMPLY CHECKING WHETHER THE CONDITION SPECIFIED (I.E. 1\u003e 4) EVALUATES TO TRUE OR FALSE. In This Case, We Have Only Specified The Steps to Be Completed When The Condition Evaluates To True. In This Case, The Result Will Be a Being Displayed on The Screen. If The Function Were to Evaluate to False, The VBA Code Will Do Nothing.
1 | Sub if_function () |
Their Then. Statement is Essentially A Directive Indicating That The Steps Immediately Following It Are To Be Executed If The Condition Just Before IF Evaluate To True.
The If Function Typically Ends with An End If Declaration Which Lets The Application Know That It Is The Last Line of The If Function. Given Below Is a Slightly Shorter Form of the If Function. Note That The End If Statement Has Been Eliminated in this and Three Lines of Code Has Been Merged Into One.
Sub if_function_short () |
HOWEVER THIS FORM SHOULD BE USED ONLY WHEN ARE NO ELSE. Or. ELSE if. Statements are needed. Let's Look At What They Are and How They Help Enhance The If Function.
Using A If Function With Else in VBA
In The Example Given Above, We Saw That The If Function Would BE Able to Carry Out Steps Only If The Condition Evaluated to True. But What If We Wanted to Specify The Steps to Be Carried Out When Either of the Conditions Were Met. One Way to Achieve Branching Out of An If Function Is To Use The Else Statement. Simply Put, The Else Statement Causes The Application to Execute The Steps Mentioned in The Else Block When The Condition Specified in The If Clause Evaluates to False.
In The Condition In The If Function Evaluates to False, The Steps Mentioned In The ELSE Block Are Executed With The Result That The Message "1 IS LESS THAN 4" GETS Displayed on The Screen. Multiple Steps Can Be Added to the Block to Have Them All Execute One After Another.
1 | SUB if_elseif_function () |
Using A If Function With Elseif and Else: In VBA
The Else Statement May Be Supported by Elseif Statements. THEY ARE TYPICALLY USED TO CHECK FOR SPECIFIC CONDITIONS AND IF All Of Them Evaluate to False, The Steps Specified in the Else Condition Will Finally Get Executed. The If Statement Will Check for a Given Condition. If That Condition Turns Out to Be False, The Condition Specified in The First Elseif Statement Will Be Checked. If That Also Turns Out to Be False, The Condition Specified in the Second Elseif Statement Will Be Checked, and So On and So Forth. If All Conditions Specified in The If and Elseif Statements Turn Out to Be False, by Default The Steps Mentioned Under the Final Else: Block Will Get Executed. Please Remember That If There Are Multiple Elseif Statements, The First One That Evaluates to True Will Get Executed and OnCe Completed, The Code Execution Will Move to the End If Statement. Even If there are Multiple Elseif Conditions That Evaluate to True, Only The First One That Evaluates To True Will Be Executed.
1 |
|
In The Example Shown Below, The If Condition IS Checked First. SINCE IT EVALUATES TO FALSE, THE FIRST ELSEIF CONDITION IS EVALUATED, FOLLOWED by The Second. SINCE NONE OF THEM EVALUATE TO TRUE, THE STEPS MENTIONED IN THE ELSE: CONDITION ARE EXECUTED.
1 | Sub if_elseif_else_function () |
Quick Tip - How To Make If States Work Faster
Both The Pieces of Code Appear Similar. The Two Elseif Conditions in the Second Function Have Been Swapped. Which One Of The Following Pieces of VBA Code Do You Think Executes Faster?:
End if
NEXT I.
MSGBOX TIMER - T
End Sub.
The Answer Is That The Second One Executes Much Faster Than The First. Why? BECAUSE The Second One Needs to Application to Go Through Lines of Code Before It Finds A Condition That Evaluates to True. REMEMBER THAT THE FIRST ELSEIF CONDITION THAT IF FOUND TRUE GETS EXECUTED AND NONE OF THE OTHER CONDITIONS ARE EVALUATED, EVEN IF THEY WERE TO ALSO EVALUATE TO TRUE. In The First Piece of the VBA Code, The Elseif Function On Line 6 Evaluates To True While in The Second, Line 5 Meets The Criteria. In Essence, The More Likely The Condition IS to Get Evaluated to True, The Earlier It Should Be Placed in The Vba Code, All Else Being The Same.
Control structures allow you to manage a sequence of program execution. Without control operators, all program operators will be performed from left to right and top down. However, it is sometimes necessary to repeatedly perform some set of instructions automatically, or solve the task differently depending on the value of variables or parameters specified by the user during execution. For this serve as management and cycles.
VBA supports the following decisions design designs:
IF. . . Then. . . ELSE.
6.1 Construction IF. . . Then.
Construction IF. . . Then applies when you need to execute one or group of operators depending on some condition. The syntax of this design allows you to set it in one row or in several rows of the program:
IF Condition Then Expression IF Condition Then Expression END IF
Usually, the condition is a simple comparison, but it can be any expression with the calculated value. This value is interpreted as false (false), if it is zero, and any non-zero is considered as true (truth). If the condition is true, then all the expressions standing after the key word of then are performed. To conventional execution of one operator, you can use both the syntax for one line and the syntax for several rows (block design).
The following two operators are equivalent:
If Aidate.< Now Then anyDate = Now If anyDate < Now Then anyDate = Now End If
Note that the syntax of the IF operator. . . Then for one line does not use the END IF statement. To perform the sequence of statements if the condition is true, the IF block design should be used. . . Then. . . End if.
If Aidate.< Now Then anyDate = Now Timer.Enabled = False " Запретить таймер. End If
If the condition is false, then the operators after the key word are not performed, and the control is transmitted to the next string (or the string after the END IF operator in block design).
6.2 IF design. . . Then. . . ELSE.
defines several blocks of operators, one of which will be performed depending on the condition:
IF Condition1 Then Expression1 ELSEIF Condition2 Then Expression2. . . ELSE Expression-N End If
When executed, the condition1 is first checked. If it is false, VBA checks the following condition2, etc., until it finds the true condition. Having found it, VBA performs the appropriate block of operators and then transmits the control of the instruction following the END IF statement. In this design, you can enable the ELSE operator block, which VBA performs if none of the conditions is performed.
Construction IF. . . Then. . . ELSEIF is actually just a special case of an IF design. . . Then. . . ELSE. Note that in this design there may be any number of ELSEIF blocks, or even a single one. The ELSE unit can be included regardless of the presence or, on the contrary, the absence of ELSEIF blocks.
Sub example1 () Dim A As Single, B As Single, X As Single Dim Z AS Double Call Read ("A1", a) Call Read ("B1", b) Let X \u003d CSNG (InputBox ("Enter X", "Entering Data", 0)) if x<= a Then z = Sin(x) ElseIf x >\u003d B THEN Z \u003d Tan (X) Else: z \u003d cos (x) End if Call Out ("C1", Z) End Sub
Note that you can add any number of ELSELF blocks to the IF design. . . Then. However, the number of ELSELF blocks can become so large that the IF design. . . Then will become very cumbersome and uncomfortable. In a similar situation, another design of the decision is to be applied - Select Case.
6.3 SELECT CASE design
The SELECT CASE design is an IF alternative. . . Then. . . ELSE If a block consists of a large set of operators. The SELECT CASE design provides an opportunity similar to the possibility of an IF design. . . Then. . . ELSE, but unlike her it makes the code more readable in the presence of several choices.
The Select Case design works with a single expression, which is calculated once at the entrance to this design. The VBA then compares the result obtained with the values \u200b\u200bspecified in the CASE design operators. If the coincidence is found, the operator block is performed associated with the CASE operator:
SELECT CASE VERSEMENT_MENUE]]. . . ] END SELECT
Each list of expressions is a list of one or more values. If in one list more than one value, they are separated by commas. Each operator block contains several operators or a single one. If it turns out that the calculated value of the expression value corresponds to the values \u200b\u200bfrom several CASE statements, the operator block is performed associated with the first Case operator from all the compliance found. VBA performs a block of operators associated with the CASE ELSE statement (note that it is optional) if no compliance of the values \u200b\u200bof the expression and values \u200b\u200bof all lists of CASE operators are not found.
Consider an example of calculating the function
Sub Example () Const Pi2 \u003d 1.57 Dim X As Single Dim z AS Double Let X \u003d CSNG (InputBox ("Enter X", "Data Entering", 0)) Select Case x Case -pi2 z \u003d sin (x) Case 0 z \u003d cos (x) Case Pi2 Z \u003d Tan (x) Case ELSE MSGBox "Invalid source data!" EXIT SUB END SELECT CALL OUT ("D1", Z) END SUB
Note that the SELECT Case design calculates the expression only once at the entrance to it, and in the structure if. . . Then. . . ELSE are calculated by various expressions for each ELSELF operator. IF design. . . Then. . . ELSE can be replaced with the SELECT CASE design only if the IF statement and each ELSELF statement calculate the same expression.
The procedures and functions that VBA performs in a linear order - VBA starts execution of code from the first operator after the announcement line of the procedure (functions) and continues to perform each operator line until the END Sub statement (End Function) is reached . Such a linear algorithm can be submitted to the following flowchart:
However, there are quite often situations when it is necessary that the procedures or functions perform various actions under different conditions. Such a situation illustrate the following flowcharts:
Operators performing the role of the program branching on the basis of any condition are called conditional transition operators.
The simplest conditional transition operators are operators. If..then; If..then..else. The first operator allows you to choose the only branch of the procedure (left block diagram), while the second makes it possible to choose from two alternative branches of the procedure code (right block diagram) based on an assessment of whether the condition is equal to True or False.
Syntax if..Then.
Option 1:
If Condition. Then. Statements
Option 2:
If Condition. Then.
Statements
End if
Condition. - logical expression;
Statements
The first option requires the writing of the operator in one line, while in the statements section you can specify several operators, separating them with a colon.
The logic of the work is as follows: VBA first estimates the logical expression presented in the Condition section; If this logical expression is true, the statement (operators) of the statements section is performed; The VBA then continues the execution of the code of the next line if..then (option 1) or for the keywords END if (option 2). If the logical expression is FALSE, then the statements section operators are skipped and the code next for this section is performed.
Below is the listing of the elementary use of the first version of writing if..Then:
And now - the second option:
Please note that the statements section statements are written using the retirement (tab). This is an important point. You should immediately teach yourself to such design of the program code - it increases its readability and greatly facilitates the search for errors.
Syntax if..then..else
Option 1:
If Condition. Then. Statements ELSE. ElseStatements.
Option 2:
If Condition. Then.
Statements
ELSE.
ElseStatements.
End if
Condition. - logical expression;
Statements, ElseStatements - One, several or not a single VBA operator.
By analogy with IF..Then - the first option requires the writing of the operator to one line, while in the statements and elsestatements sections you can specify several operators, separating them with a colon.
The second option allows you to specify several operators in different lines.
The logic of the work is as follows: VBA first estimates the logical expression presented in the Condition section; If this logical expression is true, the statement (operators) of the statements section is performed; The VBA then continues the execution of the code of the next line if..then (option 1) or for the keywords END if (option 2). If the logical expression is false, then the operators of the ElseStatements section are performed.
Basic Language Operators VBA
Comments (0)
3.1. Rules for recording operators
When recording operators, you must follow the following rules:
Each new operator is written from a new line.
To record multiple operators on the same line, they are separated by a colon (:).
If the operator does not fit in one line, then you must install a space and an underscore (_) sign at the end of the string, and then continue the non-compliant part on the next line.
3.2. Application operator
The assignment statement is used if some variable must be assigned a new value. It has the following syntax:
Name_name= EXPRESSION
First, the expression on the right side is calculated, and then the result is assigned to the variable in the left side.
for example. Record with the assignment operator the following mathematical expression:
On VVA, this expression can be written in the form of the following operator:
Y \u003d a ^ (1/3) + (a ^ 2 + exp (-b) / (sin (a) ^ 2-log (b))
3 .3. I / O statements
3 .3.1. Operator and MSGBOX function
Operator MSGBOX. Excludes information output in the dialog box and sets the button standby clicking the user button.
It has the following syntax:
MSGBOX. Message [, buttons] [, Title]
Arguments:
Message - Mandatory argument that specifies the displayed information message in the window. May consist of several text strings united by a sign & . Use in this argument CHR (13) leads to a transition to a new string in the output of information.
Buttons - The value of this argument determines the categories of buttons appearing in the window. From the value of the argument, the dependent buttons also, if any icon appears in the window. If it is not specified, which buttons must be displayed in the message window, the default value corresponds to the OK button. In tab. 3.1 Possible combinations of buttons and icons in the message window.
Title - Specifies the window header.
Function MSGBOX. Returns the value of the Integer type, indicating which button is pressed in the dialog box.
Table 3.1. Permissible values \u200b\u200bof the variable button
|
Display |
Argument |
|
Button OK. |
|
|
OK buttons and cancellation |
|
|
Buttons and no |
|
|
Buttons yes, no and cancel |
|
|
Stop buttons, repeat and ignore |
VBabortretryignore. |
|
Buttons repeat and cancel. |
|
|
Information sign |
|
|
Question mark |
|
|
Exclamation mark |
for example. Display the current date.
MSGBOX "Today on the calendar" & Date, "Attention"
As a result, the following window will be displayed (Fig. 3.1).
After clicking on the OK button, the message window closes, and the execution of the program will resume from the operator directly behind the MSGBox call.
3.3.2. Function InputBox.
Function InputBox.inscribe the values \u200b\u200bof variables using the input window and has the following syntax:
Name_name = INPUTBOX (Message [, Title] )
Arguments:
Message - Mandatory argument. Specifies the information message in the window, usually explaining the meaning of the insertion value
Title - Specifies the window header.
for example, Enter the value of the N variable from the keyboard, handling the default value is 10.
To do this, you can use the following operator:
N \u003d InputBox ("Enter N", "Enter the source data", 10)
As a result, the following window will be displayed for entering the value of the variable N (Fig. 3.2).
If the default value is suitable for the user, after clicking the OK button, the input window closes, the variable n is assigned the value 10 and executing the program will resume from the operator directly behind the call InputBox..
If the default value is not suitable for the user, before clicking on the OK button, you must enter the desired value of the variable N.
3 .four. Conditional operator if.
To implement a branching computing process in VBA, an operator is used If ... Then ... Else, which represents the simplest form of testing conditions. It has the following syntax:
IfCONDITION Then.Operator_1 E.lSE.Operator_2.
Operator_1 Performed if CONDITIONtrue, otherwise executed Operator_2.. In this case, the IF ... Then ... ELSE is written in one line.
CONDITION - This is a logical expression. The result of an expression always has a Boolean type. The expression may be simple and complex. When writing simple conditions, all possible operations of the relationship indicated in Table can be used. 3.2.
Table3 .2. Logic relations
|
Operation |
Name |
Expression |
Result |
|
True, if as well as in |
|||
|
True, if not equal to |
|||
|
True, if and, more in |
|||
|
True, if less than in |
|||
|
More or equal |
True, if more or equal to |
||
|
Less or equal |
True, if less than or equal to |
Sophisticated conditions are formed from ordinary by applying logical operations and round brackets. List of logical operations is given in Table. 3.3.
Table3 .3. Logical operations
|
Name |
Expression |
Result |
|||
|
Logical |
|||||
|
Logical I. |
|||||
|
Logical or |
The conditional operator is permissible to use the operator block instead of any of the operators. In this case, the conditional operator has the form:
IfCONDITION Then.
Block references_1
Block references_2.
End I.f.
Several conditions can be checked in the conditional statement. In this case, the conditional operator has the form:
IfCondition_1 Then.
Block references_1
ElseifCondition_2.Then.
Block references_2.
ELSE.
End.If
Example 1.. Write part of the program for the algorithm in Fig. 3.3.
Example 2. Write part of the program for the algorithm in Fig. 3.4.
3.5. Select Case Selection Operator
SELECT CASE Operator is convenient to use when depending on the value of a certain expression having a final set of permissible values, it is necessary to perform different steps. It also applies to conditional operators, but has a different look:
Select Case. Checked
Case. Values_1
Operators_1
Case. Values_ 2
Operators_ 2
. . .
Case. Values_n.
Operators_N.
[ Case.ELSE.
Otherwise_ Operators]
End Select.
Checked May have any scalar type, except for real. Values Consist from an arbitrary number of values \u200b\u200bor ranges separated from each other commas.
A type Values must coincide with the type Checked.
First calculates Checked. If its value coincides with one of the values Values_i., then run Operators_i. End Select.. If its value does not match any of the values Values_i., then run Otherwise and control is transmitted to the operator standing after End Select.
For example. Write part of the program for the algorithm in Fig. 3.5, defining the value of the variable S, depending on the value of the variable N.
3.6. Cycle operators
To implement a cyclic computing process, i.e., the multiple performing one or more operators is the operator of the cycle For ... Nextwhich has the following syntax:
ForCounter \u003d Nach_New T.o.Con_ Step.STEP
Block referencers
Block referencers
NextCOUNTER
Cycle for ... NEXT Returns Variable Values COUNTERwhich is a cycle parameter from the initial to the end value with the specified change step. This ensures the execution of a block of cycle body operators with each new meter value. If a Step.STEPthere is no design, then by default it is believed that the step is 1. By operator EXIT FOR.you can exit the cycle operator before COUNTERreaches the last value. *
For busting objects from a group of similar objects, for example, cells from the range or array elements, it is convenient to use the cycle operator For ...Each ... Next.
For Each.Element IN.Group
BLOCK_ Operators
Block referencers
NextElement
In VBA, other cycle operators are used to organize cycles with an unknown date of repetitions:
cycles with precondition - Do.While … Loop.,
Do.Until … Loop.;
cycles with postcondition - Do. … Loop.While,
Do. … Loop.Until.
Below is the syntax of these cycle operators:
"Cycle with preconditionDo. While … Loop.
Do whileCONDITION
Block referencers
Block referencers
"Cycle with preconditionDo. Until … Loop.
Do.UntilCONDITION
Block referencers
Block referencers
"Cycle with postconditionDo. … Loop While
BLOCK_ Operators
Block referencers
Loop WhileCONDITION
"Cycle with postconditionDo. … Loop Until.
BLOCK_ Operators
Block referencers
Loop Until.CONDITION
Operator Do.W.hile ... Loop.provides multiple repetition of the operator unit until CONDITION complied with the operator
Other news
3.5.1 Conditional and unconditional transition operators. Operator If ... THEN ... ELSE.
Checking the conditions in VBA, IF ... Then ... ELSE, nested structures if
Conditional transition operators - Some of the most important and frequently used elements in programming languages. The general principle of their work is simple: Compliance is checked with some conditions (the truth or falsity of any expressions) and, depending on this, the program is sent to one or another branch. In VBA, there are two conditional transition operators: if ... then ... else and select case.
Operator IF ... Then.... ELSE is the most popular programmers. Full of its syntax looks like this:
IF condition Then.
Teams1
Wherein:
- Condition - The expression that is verified for truth. If it is true, commands are running1, if falsely - commands2;
- Conditions - Additional conditions that can also be checked. In the event that they are executed (the expression of the Conditions is true), commands are executed.
IF ... THEN ... ELSE applies:
- when you need to check for compliance with one condition and in case of conformity to make some action:
If Ntemperature.< 10 Then
MSGBOX "Dress Jacket"
- when you need to do that, as in the previous example, and in case of inconsistencies, perform another action:
If Ntemperature.< 10 Then
MSGBOX "Dress Jacket"
MSGBox "Dress on Windbreaker"
- when you need to check for compliance with multiple conditions (pay attention to the use of logical operators):
If (Ntemperature.< 10) And (bRain = True) Then
Msgbox "dress a jacket and take an umbrella"
- when in case the first check returned FALSE, you need to check for many more conditions (in this case it is convenient to use ELSEIF):
If (BIGOINCAR \u003d TRUE) THEN
MSGBox "Dress for Machine"
Elseif Ntemperature.< 10 Then
MSGBOX "Dress Jacket"
Msgbox "can go in a shirt"
In this example, since BIGOINCAR is a variable type of Boolean and by itself takes True or False values, the first line may look like this:
IF Bigoincar Then ...
Some remarks on the use of if ... Then ... Else:
- the pin keyword must be in the same line with IF and the condition. If you take it to the next line, an error message will be issued;
- if you place a command that you want to perform with the truth of the verified condition, on the same line with IF and THEN, then end if you can not write:
If Ntemperature.< 10 Then MsgBox "Одеть куртку"
- if you use multiple commands or ELSE / ELSEIF commands, the end if at the end you need to write anything - otherwise there will be a syntactic error.
- for an IF ... THEN expression, it is strongly recommended to use indents to highlight block blocks. Otherwise, read the code will be difficult.
- iF ... Then can be inserted into each other:
IF MyVar \u003d 5 Then
MSGBOX "MyVar \u003d 5"
If MyVar \u003d 10 Then
MSGBOX "MyVar \u003d 10"
