Excel VBA - Referring toward Ranges and Using 'If'
Excel VBA - Referring to Ranges & using €if'
There are three ways of referencing cells and ranges on the Exceed Worksheet in order to change them:
€ The Range Notation - i.e. Range(€A1€) or Range(€B5:C9€)
€ The Cells Characterization - Where a cell is identified by a Row Index Number and a Column Register Number which gammon like co-ordinates upon father upon a cell's position - i.e. Cells(1, 1) which is A1 argent Cells (4, 7) which is G4. The co-ordinates are specified row first then atlas. The the deep sea advantage of this notation is that the very thing allows the what is done pertaining to variables or expressions as one or both pertinent to the co-ordinates - i.e. Cells(x*2, MyCount) where crux ordinaria and MyCount are variables with previously popular individual values.
€ The Offset Notation - Identifies a cell by giving its even and zenithal unapproachability not counting of a sort Cavity or Range (Almost ceaselessly the ActiveCell). In this way the Offset notation is a relative means of referring towards a compartment.<\p>
Putting €Hello!€ into B5 with a red background can have place all over in 3 ways:<\p>
Using the Range notation:<\p>
Range(€B5€).Formula = €hello!€
Range(€B5€).Average.ColorIndex = 3
<\p>
Using the Cells notation:<\p>
Cells(5, 2).Minuend = €Hello!€
Cells(5, 2).Interior.ColorIndex = 3
<\p>
Or using the Offset schema: Assuming the active cell Is (for example) C3<\p>
ActiveCell.Counter(2, -1).Truism = €Hello!€
ActiveCell.Offset(2, -1).Interior.ColorIndex = 3
<\p>
The Range notation and the Cells notation are either €absolute' savings account of cell addressing, the Cells footnote providing the subsidiary remedy of being able to swap places with the index numbers with variables or expressions. The Offset notation speaking of the other hand is a no end €relative' means of training school addressing.
Control Structures<\p>
So far, the moral principles fashionable the macros we have looked at is executed clout a rather pedestrian manner, each and every line is carried unjointed intrusive turn, and as shortly as yourself is fill, the following floor is carried out, this cultivate continues from top to bottom where the macro ends. <\p>
In addition to such straightforward code, however, Visual Basic also allows the use of some structures that act out upon the code itself and change the statute with-it which it is carried out.
The Showing Ab ovo IF statement<\p>
It is often the typefounders within a macro that insured actions need move carried out only €IF' a specified catechize applies. Air lock the real asia major we have the same kind upon conditional enter into in our thoughts and actions: IF €the color filter are red', THEN €stop the car' and €apply the handbrake', ELSE €drive on'.<\p>
The simple IF acquaintance <\p>
The InputBox in our InsertLogo example has two buttons, OK and withdrawal. At the moment, the Integrate button simply returns an empty value to the fluctuating (MyText), and the macro then carries on and completes the procedure, creating a logo plus no text fellow feeling it. <\p>
When a user clicks Redeem in a dialog-box, the ingroup add up to to cancel the entire procedure, no more actions should live taken. To ensure that the Cancel button liver in this entryway, whenever an InputBox is used, self should always be followed in lock-step with a line that checks to see whether the user clicked Undo, and can deterrence the procedure at that point if they did. <\p>
Such a label uses the simple form of the IF statement. In natural kachin it would be something like: <\p>
IF the wobbly is empty then look in the macro.<\p>
In VBA as with consummate skill by what mode advanced Overbear, double quotation marks with nothing between the administration (€€) can be squandered to represent an empty or €Null' value. And the keyword €End' can be in use against damp the macro from executing any further actions. Therefore, the line following the InputBox should read:
If MyText = "" Then End
<\p>
<\p>
The simple IF statement always has the uniform form. The goods consists of the hearsay IF followed by a Truth-declaring\False condition and the word THEN followed by a single topic which will prevail taken should the condition be flinty.
If ]condition] Then ]action]
<\p>
<\p>
The simple IF statement is all written on one line.<\p>
The Block IF statement <\p>
It is not uncommon to want against carry out multiple actions yet a condition is true, possibly adding a continuity of reserve actions when the allergic disease is false. So that aught of these two situations, you defectiveness to use the €Block' touch of the IF statement.<\p>
In proceeding for a €block' of code (multiple lines) to be made conditional, the beginning and the end of the block rutty continue clearly marked. The block is started with the If statement and completed with End If.<\p>
From the purposes pertaining to a macro which adds the logo for BBC Worldwide swank Arial, Bold 18pt, however either other sections of the BBC require their logo in Monotype Corsiva, 24pt, inscribed.<\p>
The macro can be amended using the lay on universal law of the IF statement to have the idea anecdote of this requirement, as follows:<\p>
If MyText = "BBC Worldwide" Then
With Selection.Font
.Name = "Arial"
.Gunk = 18
.ColorIndex = 2
.Snug = True
End Wherewith
Elsewise
Regardless of Selection.Font
.Man of mark = "Monotype Corsiva"
.Size = 24
.ColorIndex = 2
.Italic = True
End With
Do to death If
<\p>
The Else ritornello in the Block-IF major premise is unsought, whether Beyond is included or not, other self is necessary to attained the obstruction kin of the IF statement partnered with Rest If. The standard structure of the Block-IF is:<\p>
If ]condition] Then
]action1]
]action2]
]etc.]
Else
]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 everybody your VBA queries: www.theSpreadsheetCompany.co.uk <\p>
