sm session-6 examples
TRANSCRIPT
-
SM Session-6&7Examples
OnRanges
1 of 20
-
3Ranges are the most often used objectsIts properties and methods could be grouped into the following categories Format
Color Border Font
Values Clearing Copy and PasteSpecial
Range: Color FormatTo change the color of any range of cells, use the Interior property, with a few sub properties
ColorIndex; value = numerical color index3 = red5 = blue6 = yellow4 = green
Color; value = VB Constant or RGB FunctionvbRed, vbBlue, vbYellow, vbGreen( 255, 0, 0) = red( 0, 0, 255) = blue( 255, 255, 0) = yellow( 0, 255, 0) = green
Pattern, value = XL ConstantxlSolid, xlChecker,
Search for PatternColorIndex, Color Constants, RGB Function in VBA Help
2 of 20
-
5Ex.: Creating a solid, red range of cells on the Welcome sheetRange(A1:F12).Interior.ColorIndex = 3
Range(A1:F12).Interior.Color = vbRed
6
Range: Border FormatOne main property and One main method
Borders property BordersAround method
The Borders property has several sub properties LineStyle;
value = xlDashed, xlSolid, Weight;
value = xlThick, xlThin, Color;
value = VB Constant, RGB Function XL Constants
xlInsideHorizontal, xlEdgeBottom,
The BordersAround method has several possible arguments LineStyle:=
xlDashed, xlSolid, Weight:=
xlThick, xlThin, Color:=
VB Constant, RGB Function
3 of 20
-
7Ex.: To format a table on the Input sheetName the sub procedure Borders()
Activate the Inputworksheet
Specify the range where the table is to be located, followed by the Borders property
If we type another period after the Borders property, we see a list of its sub properties
Ex.: To format a table on the Input sheet (contd.)Make the LineStyle of the first row of the range dashed instead of solid
Also modify the color of the second and third rows borders by using the Color sub property
4 of 20
-
9Ex.: To format a table on the Input sheet (contd.)Borders can also take predefined xl values to convey which set of borders should be modified
10
Ex.: To format a table on the Input sheet (contd.)Specify a new range and set the Borders property to xlInsideHorizontal so that each row of the range has a line above and below it
Set the weight of this border to xlThick
5 of 20
-
11
Use BorderAround method to set the line style and weight
VBE provides a list of XL Constants for this arguments values
Ex.: To format a table on the Input sheet (contd.)
12
Ex.: To format a table on the Input sheet (contd.)Combine these properties, sub properties, and methods to format borders for several ranges of cells
6 of 20
-
13
Values are assigned to Ranges in VBA using the Value propertyThe value of a range or cell can be Text string Numerical value Basic Formula Variable value
14
Ex.: Enter some values into a spreadsheet
7 of 20
-
15
Range: Font FormatThe Font property is used, with sub properties Bold;
value = True or False
Size;value = number
Color; value = VB Constant, RGB Function
ColorIndex; value = number
FontStyle; value = style
16
Ex.: Format the font of the values created by modifying the code
8 of 20
-
17
Range: Three Clearing MethodsClear = clears everything
ClearContents = clears values or formulas only
ClearFormats = clears formats only
18
Ex.: Apply these clearing methods to the previously created table
9 of 20
-
19
Range: Conditional FormattingThe FormatConditions object places conditional formatting on a specified range of cells Three main methods with several properties
Add method Modify method Delete method
20
Ex.: Conditional formatting a RangeA cell with a value >10 becomes red:
Range(C1:C10).FormatConditions.Add Type:=xlCellValue, Operator:=xlGreater, Formula1:=10
Range(C1:C10).FormatConditions(1).Interior.Color = vbRed
A blank cell becomes yellow:Range(C1:C10).FormatConditions.Add Type:= xlBlanksConditionRange(C1:C10).FormatConditions(2).Interior.Color = vbYellow
10 of 20
-
OnReferencing and Names
22
Referencing and Names in VBAReferencing ranges and cells
Naming ranges
11 of 20
-
23
Referencing and Names in VBAThe most common way to name an object in VBA is with the Name property
Several ways to reference ranges and cells using VBA Offset Cells Rows Columns EntireRow EntireColumn End
24
Offset vs CellsThe Offset property:
Considers the named range to be in the 0th row and 0thcolumn
It then offsets the range selection by a certain row count to above (if pos., below if neg.) and column count to the right (if pos., left if neg.) of this named range
The Cells property: Considers the named range to be in the 1st row and 1st
column It then finds the cell in the x-th position above (if pos., below
if neg.) and y-th position to the right (if pos., left if neg.) of the named range
12 of 20
-
25
Ex.: In the given airline survey data compare highlighting certain columns using Offset and Cells properties
26
Columns and Rows, reference columns and rows in a named rangeBoth take a numerical index value to find the numbered
column within the named range consider the first column or row in the range to be
indexed as 1
13 of 20
-
27
Ex.: Modifying some table formatting
28
EntireColumn and EntireRow, are used to modify every column or row in the named range for the length of the column or row of the entire worksheetEntireColumn property affects every column in the named range and
EntireRow property affects every row in the named range
for their entire respective length
14 of 20
-
29
30
End is very useful in finding the end of row or column of any range of dataEnd property can take four values: xlDown and xlUp
for columns
xlToRight and xlToLeft for rows
Do not name an entire data range to use this property, just one cell in the data range is enough
15 of 20
-
31
Ex.: Copy and paste using End property
32
Most common way to assign object names is by using the Name propertyNaming a range and formatting one of its cells
16 of 20
-
OnFormulas in VBA
(using Range and Application Object)
34
Formulas with the Range ObjectTwo main properties Formula;
value = reference by column letter and row number
FormulaR1C1; value = reference by R1C1 Notation
A cell or an entire range of cells, or array, can be used with these properties
One more method that can be used with the Range object concerning formulas AutoFill;
arguments = Destination, Type
17 of 20
-
35
Ex. Calculate sums and averages using both the Formula and FormulaR1C1 propertiesSince relative referencing is used, the formula will automatically modify for each relative row of data
36
Ex.: Using R1C1 Notation to calculate the average age of passengers
18 of 20
-
37
Ex. Using the AutoFill method to copy and paste formulas
38
Formulas with Application ObjectThe Application object uses the WorksheetFunctionproperty to set a function for a cell or range of cells
The WorksheetFunction property has several sub properties for almost all of the Excel functions
Max Min Average Sum Count VLookup
19 of 20
-
39
Ex.: Using these sub properties of the WorksheetFunction property
Thanks
20 of 20