Excel VBA II.pdf

SPREADSHEET-BASED DECISION SUPPORT SYSTEMS Excel VBA II  Overview  Objects and their properties and methods  Workbooks and Worksheets  Ranges  Charts  Application  With Construct  Referencing in VBA  Formulas in VBA Excel VBA...

SPREADSHEET-BASED DECISION SUPPORT SYSTEMS Excel VBA II  Overview  Objects and their properties and methods  Workbooks and Worksheets  Ranges  Charts  Application  With Construct  Referencing in VBA  Formulas in VBA Excel VBA II 2  Objects  Application: Represents the entire Microsoft Excel application.  Workbooks and Worksheets: A collection of all the Workbook objects that are currently open in the Microsoft Excel application, the Worksheet object is a member of the Worksheets collection  Range: Represents a cell, a row, a column, a selection of cells containing one or more contiguous blocks of cells, or a 3D range.  Chart: Represents a chart in a workbook. Excel VBA II 3  Workbooks and Worksheets  Workbooks and Worksheets will not be manipulated too often.  There is one important method that both use  Activate; argument = (none)  There is one important property that Worksheets often use  Visible; value = True or False Excel VBA II 4  Workbooks and Worksheets (cont)  We will commonly make Worksheets visible and hidden as we navigate the user through our worksheets.  In all of our case studies we also hide every Worksheet except for the “Welcome” Worksheet when the Workbook is opened.  This is accomplished in a sub procedure which uses the Open event procedure of the Workbook object. Excel VBA II 5  Example 1  If we only want the “Welcome” Worksheet to be visible when we initially open this Workbook, we have to set the Visible property to True for the “Welcome” Worksheet and False for all others using the Workbook_Open event procedure. Sub Workbook_Open() Worksheets("Welcome").Visible = True Worksheets(“Example 3").Visible = False Worksheets(“Example 4, 5, 6, 7").Visible = False Worksheets(“Example 8").Visible = False Worksheets(“Example 9").Visible = False Worksheets(“Example 10").Visible = False Worksheets(“Example 11").Visible = False End Sub Excel VBA II 6  Ranges  Ranges will probably be the objects we use the most in VBA.  There are several properties and methods we will learn for Ranges; we will group them into the following categories.  Color format  Border format  Values  Font format  Clearing  Copy and PasteSpecial Excel VBA II 7  Range: Color Format  To change the color of any range of cells, use the Interior property; there are a few sub properties we can then use  ColorIndex; value = numerical color index  3 = red  5 = blue  6 = yellow  4 = green  Color; value = VB Constant or RGB Function  vbRed, vbBlue, vbYellow, vbGreen  ( 255, 0, 0) = red  ( 0, 0, 255) = blue  ( 255, 255, 0) = yellow  ( 0, 255, 0) = green  Pattern, value = XL Constant  xlSolid, xlChecker, … Excel VBA II 8  Example 2  Let us create a solid, red range of cells on the “Welcome” sheet. Sub Color() Worksheets("Welcome").Activate 'Range("A1:K40").Interior.ColorIndex = 3 'Range("A1:K40").Interior.Color = RGB(255, 0, 0) Range("A1:K40").Interior.Color = vbRed Range(“A1:K40”).Interior.Pattern = xlPatternChecker End Sub Excel VBA II 9  Example 2 (cont)  Here we change the color of some cells using the Interior property. Excel VBA II 10  Range: Border Format  There is one main property and one main method we will use to format range borders  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 = xlDiagonalDown, xlDiagonalUp, xlEdgeBottom, xlEdgeLeft, xlEdgeRight, xlEdgeTop, xlInsideHorizontal, or xlInsideVertical.  The BordersAround method has several possible arguments  LineSytle:= xlDashed, xlSolid, …  Weight:= xlThick, xlThin, …  Color:= VB Constant, RGB Function Excel VBA II 11  Example 3  Let us combine these properties, sub properties, and methods to format borders for several ranges of cells. Sub Borders() Worksheets(“Example 3").Activate Range("B3:D10").Borders.Weight = xlThick Range("B3:D3").Borders.LineStyle = xlDash Range("B4:D5").Borders.Color = vbYellow Range("B12:E14").Borders(xlInsideHorizontal).Weight = xlThick Range("B15:C17").BorderAround LineStyle:=xlSolid,Weight:=xlThick, Color:=RGB(0, 0, 255) End Sub Excel VBA II 12  Example 3 (cont)  The macro should produce the following on the “Example 3” Worksheet. Excel VBA II 13  Range: Values  Values are assigned to Ranges in VBA with the Value property.  The value of a range or cell can be  Text string  Numerical value  Basic Formula  Reference  Variable value Excel VBA II 14  Example 4  Let us enter some different values into a spreadsheet.  We will create a table labeled “Example 4” with the entries.  “Value Sum” = 350  “Value Average” = 80 Sub ValueFont() Worksheets("Example 4, 5, 6, 7").Activate Range("A2").Value = "Example 4" Range("A3").Value = "Value Sum" Range("B3").Value = 350 Range("A4").Value = "Value Average" Range("B4").Value = 80 End Sub Excel VBA II 15  Example 4 (cont)  The resulting table should look like this. Excel VBA II 16  Range: Font Format  The Font property is used to format fonts of ranges.  There are several sub properties to use with the Font property.  Bold; value = True or False  Size; value = number  Color; value = VB Constant, RGB Function  ColorIndex; value = number  FontStyle; value = “style” Excel VBA II 17  Example 5  Let us format the font of the values we created in Example 4 by modifying the code. Sub ValueFont() Worksheets("Example 4, 5, 6, 7").Activate Range("A2").Value = "Example 4" Range("A3").Value = "Value Sum" Range("B3").Value = 350 Range("A4").Value = "Value Average" Range("B4").Value = 80 Range("A2").Font.Bold = True Range("A2").Font.Size = 14 Range("B3:B4").Font.Color = vbRed Range("B3:B4").Font.FontStyle = “Bold Italic” Range("B3:B4").Font.Name = “Times Roman” End Sub Excel VBA II 18  Example 5 (cont)  Our table is now formatted as follows. Excel VBA II 19  Range: Clearing  There are three common methods used to clear a range of cells.  Clear = clears everything  ClearContents = clears values or formulas only  ClearFormats = clears formats only  It is important to know which method is most appropriate for your worksheet. Excel VBA II 20  Example 6  Let us apply some of the clearing methods to the table we created in Examples 4 and 5.  First we will clear the formatting done to the table values and change the table title. Sub ClearCells() Worksheets("Example 4, 5, 6, 7").Activate Range("B3:B4").ClearFormats Range("A2").ClearContents Range("A2").Value = "Results" End Sub Excel VBA II 21  Example 6 (cont)  The resulting table is like this. Excel VBA II 22  Example 6 (cont)  However, if we would use the Clear method instead of the ClearContents method to change the table title, the result would be different. Sub ClearCells() Worksheets("Example 4, 5, 6, 7").Activate Range("B3:B4").ClearFormats 'Range("A2").ClearContents 'Range("A2").Value = "Results" Range("A2").Clear Range("A2").Value = "Results" End Sub Excel VBA II 23  Example 6 (cont)  Notice the lack of formatting in the table title. Excel VBA II 24  Range: Copy and PasteSpecial  These two methods are used to copy and paste values, formats, etc from one range to another.  Copy  PasteSpecial  The Copy method does not require any arguments, however the PasteSpecial method has four arguments which set what exactly should be pasted from the copied selection.  xlPasteAll, xlPasteFormats, xlPasteFormulas, xlPasteValues  xlPasteSpecialOperationsNone, …  Skip blanks  Transpose Excel VBA II 25  Example 7  Let us copy and past some values and formats. Sub CopyPaste() Worksheets("Example 4, 5, 6, 7").Activate Range("A2").Copy Range("A6").PasteSpecial xlPasteFormats Range("A6").Value = "Table 2" Range("B3:B4").Copy Range("A7").PasteSpecial xlPasteValues Range("B3:B4").Copy Range("B7").PasteSpecial xlPasteAll Range("A1").Select Application.CutCopyMode = False End Sub Excel VBA II 26  Example 7 (cont)  The second table is pasted below the initial one and looks like this. Excel VBA II 27  The Application Object  The Application object is useful for some common functions as well as some other features for running VBA code.  There are two main properties we will use for this object.  ScreenUpdating; value = True, False  CutCopyMode; value = True, False  There is also one main method we will use.  Wait; arguments = Now, TimeValue Excel VBA II 28  The Application Object (cont’d)  The ScreenUpdating property helps the code run more efficiently since the Excel screen does not need to be updated after every action in the code.  The CutCopyMode property prevents a flashing box from remaining around the range which has been copied after a macro has been run.  These are both useful for example, when copying and pasting large data. Excel VBA II 29  Example 10  Suppose we are creating a weekly diet based on three possible meal plans.  Since we are doing several copy and paste actions, we will use both of these properties. Excel VBA II 30 Example 10 (cont) Sub Applications() Worksheets("Example 10").Activate Application.ScreenUpdating = False Range("A2:A5").Copy Range("A9").PasteSpecial Range("D9").PasteSpecial Range("G9").PasteSpecial Range("C2:C5").Copy Range("B9").PasteSpecial Range("E9").PasteSpecial Range("E2:E5").Copy Range("C9").PasteSpecial Range("F9").PasteSpecial Range("A1").Select Application.CutCopyMode = False Application.ScreenUpdating = True End Sub Excel VBA II 31  Example 10 (cont)  After running this macro, you can notice how quickly it runs and that no flashing box is shown afterwards. Excel VBA II 32  The Wait Method  We will use the Wait method frequently when performing a Simulation in Excel.  Wait pauses the macro while it is being run until a specified time is reached.  The Now argument calculates the current time and the TimeValue argument gives an integer-valued time amount to add to the current time.  The macro will play again once Now plus TimeValue time is reached. Excel VBA II 33  Example 10 (cont)  Let us know format the two tables in our example.  We will make the top table yellow, pause the macro, and then make the second table blue.  We add the following lines of code. Range("A2:E5").Interior.Color = vbYellow Application.Wait (Now + TimeValue("0:00:03")) Range("A9:G12").Interior.Color = vbBlue Excel VBA II 34  Charts  Charts have many parameters which can be modified using VBA code.  The four main parts of the chart to manipulate are  Chart Type  Source Data  Chart Options  Chart Location  The formatting of the chart can also be changed with VBA. Excel VBA II 35  Two Chart Objects  When modifying charts with VBA, we will use two different chart objects  Charts  ActiveChart  The methods we will use with the Chart object are  Add; arguments = Before, After, Count  Copy  Delete  Select Excel VBA II 36  Example 8  We have a small table of data that we would like to graph as a bar graph.  We will use the Add method of the Chart object to create a chart in VBA. Sub Graph() Worksheets("Example 8").Activate Charts.Add End Sub Excel VBA II 37  Active Chart  The ActiveChart object will be used to set all other chart parameters and formatting.  Some main chart properties are  ChartType; value = XL Constants  HasLegend; value = True, False  HasTitle; value = True, False  ChartTitle; value = text name  Some main methods are  SetSourceData; arguments = Source, PlotBy  Location; arguments = Where, Name Excel VBA II 38  Using Active Chart  Before using the ActiveChart object, we will need to Select or Add a particular Chart object.  When creating a new chart, we must set the four main parts of the chart using the following  Chart Type: ChartType  Source Data: SetSourceData  Chart Options: HasLegend, HasTitle, etc  Chart Location: Location Excel VBA II 39  Example 8 (cont)  We will now set the four main parts of our new chart. Sub Graph() Worksheets("Example 8").Activate Charts.Add ActiveChart.ChartType = xl3DColumnClustered ActiveChart.SetSourceData Source:=Sheets("Example 8").Range("A3:B6"), PlotBy:=xlColumns ActiveChart.HasLegend = False ActiveChart.HasTitle = True ActiveChart.ChartTitle.Text = "Annual Report" ActiveChart.Location Where:=xlLocationAsObject, Name:="Example 8" End Sub Excel VBA II 40  Example 8 (cont)  At this point, if we run the Graph() macro, we would see the following result. Excel VBA II 41  Further Chart Modification  We can modify formatting with extra properties such as  SeriesCollection  Add, Extend, HasDataLabels, Interior, ColorIndex  We can modify some parameters with other methods such as  ApplyCustomType; arguments = ChartType Excel VBA II 42 Example 8 (cont)  We can change our chart to a regular bar graph and change the color of our data with the following extra lines of code. ActiveChart.ApplyCustomType ChartType:=xlColumnClustered ActiveChart.SeriesCollection(1).Interior.ColorIndex = 37 Excel VBA II 43  Example 8 (cont)  Our final chart looks like this. Excel VBA II 44  Quick Review  Some important properties and methods of the following objects.  Workbooks and Worksheets  Activate, Visible  Ranges  Borders, Font, Value  Charts  ChartType, SetSourceData, Location  Application  ScreenUpdating, CutCopyMode, Wait Excel VBA II 45  The With Construct  The With construct is basically used to set several properties of one object in an enclosed statement.  For example, compare these two sets of code. Range(“A1:C8”).Interior.Color = With Range(“A1:C8”) vbRed.Interior.Color = vbRed Range(“A1:C8”).Font.Bold = True.Font.Bold = True Range(“A1:C8”).Font.Name =.Font.Name = “Arial” “Arial”.Borders(xlEdgeBottom Range(“A1:C8”).Borders(xlEdge ). LineStyle = xlDash Bottom).LineStyle = xlDash End With Excel VBA II 46  Referencing and Names in VBA  As we have seen, the most common way to name an object in VBA is with the Name property.  There are several ways to reference ranges and cells using VBA.  Offset  Cells  Rows  Columns  EntireRow  EntireColumn  End Excel VBA II 47  Offset vs Cells  The Offset property  considers the named range to be in the 0th row and 0th column.  It then offsets the range selection by a certain row count to below if pos., above if neg. and column count to the left if neg., right if pos. of this named range.  The Cells property  considers the named range to be in the 1 st row and 1st column.  It then finds the cell in the xth position above if neg., below if pos. and yth position to the right if pos., left if neg. of the named range. Excel VBA II 48  Example 11  Given data in an airline survey, we want to highlight certain columns of data using both the Offset and Cells properties comparatively. Sub Referencing() Worksheets("Example 11").Activate Range("A3").Offset(1, 10).Interior.Color = vbYellow Range("A3").Cells(1, 10).Interior.Color = vbRed Range("B3").Offset(2, 5).Interior.Color = vbYellow Range("B3").Cells(2, 5).Interior.Color = vbRed Range(Range("A3").Offset(1, 10), Range("A3").Offset(6, 10)).Interior.ColorIndex = 6 End Sub Excel VBA II 49  Example 11 (cont)  Notice the change in the table Excel VBA II 50  Columns and Rows  Columns and Rows, reference columns and rows in our named range, respectively.  Both properties take a numerical index value to find the numbered column within the named range.  Both consider the first column or row in the range to be indexed as 1. Excel VBA II 51  Example 11 (cont)  We will now modify some more formatting of this table using these two properties by adding the following lines of code. Range("B3:K9").Columns(3).Interior.ColorIndex = 5 Range("B3:K9").Rows(2).Font.Color = vbRed Excel VBA II 52  EntireColumn and EntireRow  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 worksheet.  The EntireColumn property will affect every column in the named range and the EntireRow property will affect every row in the named range for their entire respective length. Excel VBA II 53  Example 11 (cont’d)  We may use these properties on our table in preparation for future entries.  We add the following lines of code. Range("B3:K9").EntireColumn.HorizontalAlignment = xlLeft Range("B4:K5").EntireRow.Borders(xlInsideHorizontal).Weight =3 Excel VBA II 54  End  End is a very useful property as it can help you find the end of row or column of any range of data.  The End property can take four values:  xlDown and xlUp for columns  xlToRight and xlToLeft for rows  You do not need to name an entire data range to use this property, just one cell in the data range is fine. Excel VBA II 55  Example 11 (cont)  We try to add the information of a new passenger into the current data table. Range("A3").End(xlToRight).Offset(0, 1) = "New Passenger" Excel VBA II 56  Referring to Named Ranges  We can use some of these referencing properties to refer to named ranges.  For example  Cells works with named ranges, but not Offset  However the Offset property can be used to name a range Excel VBA II 57  Example 11 (cont)  Let us name a range and format one of its cells. Sub Naming() Worksheets("Example 11").Activate Range(Range("A3"), Range("A3").End(xlToRight)).Name = "Passengers" Range("Passengers").Cells(1, 2).BorderAround Weight:=xlThick End Sub Excel VBA II 58  Formulas in VBA  As we will see, many simple calculations can be made using variables and programming structures.  However, it is still important to know how to use all of the functions and formulas available in Excel with VBA code.  There are two main ways to use Excel-defined formulas in VBA  The Range object  The Application object Excel VBA II 59  Formulas with Application Object  The Application (i.e. Excel) object uses the WorksheetFunction property 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 Excel VBA II 60  Example 11 (cont)  Let us use these sub properties of the WorksheetFunction property to make some calculations.  We add the following lines of code. Sub FormulasOriginal() Worksheets("Example 11").Activate Range(Range("A3"), Range("A3").End(xlDown)).Copy Range("A12").PasteSpecial Range(“B12").Value = Application.WorksheetFunction.Max(Range("B6:K6")) Range(“B13").Value = Application.WorksheetFunction.Min(Range("B7:K7")) Range(“B14").Value = Application.WorksheetFunction.Average(Range("B8:K8")) End Sub Excel VBA II 61  Example 11 (cont)  Notice that with the Application object, the result of the calculation will be placed in the cell or range; this is in contrast to using the Range object which will place a function in the cell or range. Excel VBA II 62  Formulas with the Range Object  Two main properties can be used with the Range object  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.  There is also one method we can use with the Range object concerning formulas.  AutoFill; arguments = Destination, Type Excel VBA II 63  Example 11 (cont)  We will calculate some sums and averages using both the Formula and FormulaR1C1 properties.  We also use the AutoFill method. Sub Formulas() Worksheets("Example 11").Activate Range("L6:L9").Formula = "=SUM(B6:K6)" Range("L4").FormulaR1C1 = "=AVERAGE(RC[-10]:RC[-1])" Range("A11").FormulaR1C1 = "=AVERAGE(R[-7]C:R[-7]C)" Range("M6").FormulaR1C1 = "=AVERAGE(RC[-11]:RC[-2])" Range("M6").AutoFill Destination:=Range("M6:M9"), Type:=xlFillDefault End Sub Excel VBA II 64  Example 11 (cont)  We can check the results of our macro. Excel VBA II 65  Quick Review  The With Construct  Referencing and Names in VBA  Offset and Cells  Columns and Rows  EntireColumn and EntireRow  End  Formulas in VBA  Range Object  Formula, FormulaR1C1, AutoFill  Application Object  Worksheet Function: Max, Min, Average Excel VBA II 66