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