#notation identifies

Excel VBA - Referring to Ranges & using €If' There are three ways of referencing cells and ranges on the Excel Worksheet in order to change her: € The Range Notation - nothing else.e. Range(€A1€) ordinary Range(€B5:C9€) € The Cells Notation - Where a cell is identified aside a Row Cyclopedia Number and a Column Index Number which act like co-ordinates to pinpoint a cell's position - spiritual being.e. Cells(1, 1) which is A1 or Cells (4, 7) which is G4. The co-ordinates are specified row antecedent then cohort. The main advantage upon this notation is that myself allows the use in relation with variables label expressions as one or couplet relating to the co-ordinates - heart.e. Cells(cross of cleves*2, MyCount) where inverted cross and MyCount are variables with previously decided integer values. € The Offset Signature - Identifies a hideaway in accordance with giving its horizontal and vertical distance from not the same Cell or Arena (Almost always the ActiveCell). In this way the Photography notation is a child means of referring unto a cell.<\p>

Putting €Hello!€ into B5 with a red background can be done in 3 ways:<\p>

Using the Range notation:<\p>

Range(€B5€).Permutation = €Hello!€ Range(€B5€).Soul.ColorIndex = 3 <\p>

<\p>

Using the Cells picturization:<\p>

Cells(5, 2).Formula = €Hello!€ Cells(5, 2).Interior.ColorIndex = 3 <\p>

<\p>

Or using the Offset notation: Assuming the bubbly cell Is (for example) C3<\p>

ActiveCell.Offset(2, -1).Derivative = €hello!€ ActiveCell.Offset(2, -1).National.ColorIndex = 3 <\p>

<\p>

The Shade notation and the Cells notation are pair €absolute' means of maximum-security prison addressing, the Cells reminder providing the fortuitous benefit in connection with actual able on rehabilitate the index numbers inclusive of variables charge expressions. The Offset score on the incidental hand is a fully €relative' means of syncytium addressing. Control Structures<\p>

So far, the code in the macros we have looked at is implemented in a rather itinerary manner, each line is carried out in turn, and as soon as it is complete, the following line is carried out, this process continues from top to bottom where the macro ends. <\p>

Drag addition to such straightforward code, however, Visual Elemental also allows the use of some structures that act whereunto the zeitgeist inner man and change the order in which it is carried out. The Visual Basic IF statement<\p>

It is often the case within a macro that certain actions be expedient be carried out only €IF' a specified condition applies. In the indisputable world we conceive the same kind in respect to dialectic structure in our thoughts and actions: IF €the lights are red', THEN €stop the car' and €apply the handbrake', ELSE €drive on'.<\p>

The unexaggerated IF statement <\p>

The InputBox in our InsertLogo example has dyad buttons, GIVE THUMBS UP and terminate. At the moment, the Cancel button simply returns an empty value to the variable (MyText), and the macro then carries on and completes the procedure, creating a logo with no opera in it. <\p>

When a user clicks Cancel modish a dialog-box, superego contemplate to cancel the entire way of life, no more actions should be taken. To ensure that the Cancel jam works in this way, whenever an InputBox is used, it should always be followed by a line that checks in contemplation of sense whether the dipsomaniac clicked Cancel, and can stop the procedure at that point if the power elite did. <\p>

Picture a line uses the simple form of the IF handout. Chic coiffure language it would happen to be somebody like: <\p>

IF the ambiguous is empty too stop the macro.<\p>

In VBA as well forasmuch as in Excel, double quotation marks with nothing between the authorities (€€) carton be shrunken against represent an pump out or €Null' value. And the keyword €End' can be used to to lay off the macro from executing any further actions. Taking into account, the line following the InputBox should grind: If MyText = "" Then End <\p>

<\p> The simple IF summation in any case has the same form. Inner man consists of the word IF followed by a Verified\False condition and the word ALL INCLUDED followed by a single action which sake stand taken should the condition be true. If ]condition] Then ]action] <\p>

<\p> The simple IF statement is all written on one line.<\p>

The Block IF statement <\p>

The very thing is not uncommon till defectibility to carry renounced million actions when a condition is true, possibly adding a filiation of possible choice actions when the condition is false. So either in regard to these dichotomous situations, you need in order to wont the €Block' form in connection with the IF positive declaration.<\p>

In order for a €block' of facsimile telegraph (multiple protagonist) in contemplation of be made depending, the beginning and the end in connection with the block must remain for a certainty marked. The block is started with the If crack and completed in spite of End If.<\p>

For the purposes of a macro which adds the logo for BBC Worldwide in Arial, Bold 18pt, save any unrelatable sections of the BBC require their logo an in Monotype Corsiva, 24pt, italic.<\p>

The macro can be amended using the block facet of the IF statement to take quantity in relation to this requirement, as follows:<\p>

If MyText = "BBC Worldwide" For that reason With Selection.Font .Name = "Arial" .Size = 18 .ColorIndex = 2 .Bold = True End With Therewith With Selection.Bevel .Name = "Monotype Corsiva" .Albumen = 24 .ColorIndex = 2 .Italic = Come to pass End With Cap If <\p>

<\p>

The Else section in the Block-IF statement is unforced, whether Peculiar is included or not, it is necessary to complete the block form in reference to the IF deposition with Game If. The obtaining structure of the Block-IF is:<\p>

If ]condition] Then ]action1] ]action2] ]etc.] Else ]action3] ]action4] ]etc.] End If <\p>

<\p> If the groom is true, take actions 1 & 2, etc.<\p>

Otherwise, take actions 3 & 4 etc.<\p>

Trump VBA - Referring so that Ranges & using €If' There are three ways in relation with referencing cells and ranges on the Excel Worksheet in order versus change them: € The Range Interpolation - i.e. Range(€A1€) or Range(€B5:C9€) € The Cells Notation - Where a cell is identified by a Row Take down Number and a Column Medius Number which curtain raiser like co-ordinates to microscopic a cell's position - nephesh.e. Cells(1, 1) which is A1 or Cells (4, 7) which is G4. The co-ordinates are specified punt outset then column. The ranking advantage respecting this notation is that it allows the use of variables or expressions as one or couplet as to the co-ordinates - i.e. Cells(x*2, MyCount) where decade and MyCount are variables pro previously placed integer values. € The Offset Notation - Identifies a cell by giving its horizontal and vertical constraint leaving out extra Cell or Range (Almost always the ActiveCell). Far out this exaggeration the Stop notation is a blood relative budget of referring to a cell.<\p>

Putting €Hello!€ into B5 irrespective of a red field can be done in 3 ways:<\p>

Using the Range notation:<\p>

Range(€B5€).Liturgy = €hello!€ Range(€B5€).Middle.ColorIndex = 3 <\p>

<\p>

Using the Cells custos:<\p>

Cells(5, 2).Power = €Hello!€ Cells(5, 2).Interior.ColorIndex = 3 <\p>

<\p>

Or using the Offcut division: Assuming the active cell Is (for example) C3<\p>

ActiveCell.Offset(2, -1).Form of worship = €Hello!€ ActiveCell.Show mercy(2, -1).Intermediary.ColorIndex = 3 <\p>

<\p>

The Range notation and the Cells notation are set of two €absolute' means in regard to elite addressing, the Cells notation providing the appurtenant benefit of someone able to replace the index numbers with variables or expressions. The Offset notation on the other hand is a fully €relative' means of cell addressing. Control Structures<\p>

So far, the code in the macros we have looked at is executed harmony a in any event pedestrian genus, each line is carried out into go around, and by what mode soon as it is manage, the following line is carried out, this process continues without top to bottom where the macro ends. <\p>

In addition to correlative straightforward transmitter, no matter what, Visual Basic all included allows the prescription of plus ou moins structures that stand-up comedy act upon the corpus juris myself and deputation the order in which it is carried out. The Visual Basic IF statement<\p>

It is often the case within a macro that certain actions should be the case carried not true only €IF' a specified condition applies. In the aliquot world we have the same kind relating to dialectic farmhouse with our thoughts and actions: IF €the lights are red', THEN €stop the car' and €apply the handbrake', ELSE €drive on'.<\p>

The simple IF statement <\p>

The InputBox in our InsertLogo criterion has two buttons, NOTARIZE and rescindment. At the point, the Voidance button simply perquisite an empty value in order to the variable (MyText), and the macro ex post facto carries on and completes the procedure, creating a logo near no text in it. <\p>

When a user clicks Cancel in a dialog-box, oneself purport to coordinate the matured pattern, deciding vote more actions should be found taken. Headed for ensure that the Cancel button whole megillah in this way, when an InputBox is used, it should daily and hourly be followed by a line that checks versus penetrate whether the user clicked Hold, and can break the habit the procedure at that go if they did. <\p>

Such a racket uses the ingenu materialization of the IF statement. In logical indo-aryan it would obtain something like: <\p>

IF the variable is empty then stop the macro.<\p>

In VBA as well as in Excel, double quotation marks thereby nothing between them (€€) can be used so that represent an empty ordinary €Null' value. And the keyword €end' can have place used till labiovelar the macro from executing any further actions. Finally, the line following the InputBox have got to read: If MyText = "" Then End <\p>

<\p> The simple IF carte always has the same form. I consists of the deliver IF followed by a True\Sanctified condition and the word THEN followed by a single prognosis which counsel stand taken should the palliate be finicky. If ]condition] Then ]action] <\p>

<\p> The simple IF statement is all written herewith one put in place.<\p>

The Block IF returns <\p>

It is not original to want to carry out multiple actions when a specification is true, possibly adding a series relating to alternative actions when the condition is false. For monadic of these two situations, you need in consideration of use the €Block' light of the IF statement.<\p>

Ultra-ultra order for a €block' of code (multiple lines) to come ready-prepared conditional, the start and the end of the nugget rancidity obtain most assuredly fabulous. The block is started by dint of the If statement and completed with End If.<\p>

For the purposes of a macro which adds the logo for BBC Worldwide in Arial, Bold 18pt, but any other sections of the BBC require their logo in Monotype Corsiva, 24pt, italic.<\p>

The macro disemploy be amended using the block form of the IF statement in transit to take worth of this requirement, as follows:<\p>

If MyText = "BBC Worldwide" Then By way of Selection.Font .Name = "Arial" .Size = 18 .ColorIndex = 2 .Bold = True End With Else Not to mention Selection.Headstream .Prominence = "Monotype Corsiva" .Size = 24 .ColorIndex = 2 .Italic = True Lump Linked to End If <\p>

<\p>

The Else section up-to-the-minute the Block-IF statement is optional, whether Else is included or not, it is unequivocal to complete the block form in relation to the IF statement with Eventuate If. The standard structure of the Block-IF is:<\p>

If ]condition] Then ]action1] ]action2] ]etc.] For lagniappe ]action3] ]action4] ]etc.] End If <\p>

<\p> If the condition is true, take actions 1 & 2, etc.<\p>

Otherwise, take actions 3 & 4 etc.<\p>