Excel VBA - Referring to Ranges and Using 'If'
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>
For bodily your VBA queries: www.theSpreadsheetCompany.co.uk <\p>
