EIN 4905ESI 6912 Decision Support Systems Excel

1
Spreadsheet-Based Decision Support Systems
Chapter 13 More on Objects
Prof. Name
name_at_email.com Position
[phone] University Name
2
Overview

• 13.1 Introduction
• 13.2 Objects and their properties and methods
• 13.3 The With construct
• 13.4 Referencing in VBA
• 13.5 Formulas in VBA
• 13.6 Summary

3
Introduction

• Various properties and methods for commonly
  manipulated objects.
• The With construct.
• Cell referencing and naming in VBA.
• Excel formulas and functions in VBA.

4
Objects and their Properties and Methods

• Workbooks and worksheets
• Ranges
• Charts
• Drawing objects
• Application

5
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

6
Figures 13.1 and 13.2

• For example, we want to take some values from a
  workbook called CH13 Workbook1 and transfer
  them into a chart or another form of analysis in
  a different workbook, CH13 Workbook2.

7
Figure 13.5

• We want to activate the appropriate workbooks and
  then copy the data.

8
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.

9
Figure 13.8

• We can hide all worksheets but the welcome sheet
  when the application is opened.

10
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

11
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,

12
Figure 13.10

• Let us create a solid, red range of cells on the
  Welcome sheet.
• Range(A1F12).Interior.ColorIndex 3
• Range(A1F12).Interior.Color vbRed

13
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 xlInsideHorizontal, xlEdgeBottom,
  
• The BordersAround method has several possible
  arguments
• LineSytle xlDashed, xlSolid,
• Weight xlThick, xlThin,
• Color VB Constant, RGB Function

14
Figures 13.19 and 13.20

• Let us combine these properties, sub properties,
  and methods to format borders for several ranges
  of cells.

15
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

16
Figures 13.21 and 13.22

• Let us enter some different values into a
  spreadsheet.

17
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

18
Figures 13.24 and 13.25

• Let us format the font of the values we just
  created by modifying the code.

19
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.

20
Figures 13.28, 13.29, and 13.30

• Let us apply some of the clearing methods to the
  table we created in Examples 4 and 5.

21
Conditional Formatting

• Also associated with formatting the Range object
  is the FormatConditions object, which places
  conditional formatting on a specified range of
  cells.
• There are three main methods and several
  properties for this object.
• Add method
• Modify method
• Delete method

22
Figure 13.31

• We place a conditional format on a range of cells
  so that any cell with a value less than 10
  becomes red.
• Range("C1C10").FormatConditions.Add
  TypexlCellValue, OperatorxlLess, Formula110
• Range("C1C10").FormatConditions(1).Interior.Color
  vbRed

23
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.

24
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

25
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

26
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

27
Figures 13.32 and 13.33

• The Graph() procedure creates a chart from a
  table of data.

28
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

29
Drawing Objects

• Drawing objects, or shapes, can be useful to help
  the user visualize a problem scenario or
  suggested solution.
• You can use the Drawing Toolbar to help you draw
  a variety of shapes on the spreadsheet.
• These objects can also be created and/or
  formatted in VBA code. There are four main
  objects we use to create/format shapes in VBA.
• ActiveSheet
• Shape
• Selection
• ShapeRange

30
Creating Drawing Objects

• There is one main method used to create a drawing
  object.
• AddShape arguments Type, Left/Top,
  Width/Height 
• The Type argument can be equal to any
  MsoAutoShapeType 
• Left/Top sets the position of the shape in
  respect to the upper-left hand corner of the
  spreadsheet
• Width/Height sets the width and height of the
  shape
• This method is used with the Shapes object which
  is used with the Worksheets object (or
  ActiveSheet object).

31
Naming Drawing Objects

• You can name drawing objects which can greatly
  help in modifying them using VBA code.
• To name a drawing object, we use the Name
  property.
• First select an object in Excel to see what the
  default name is.
• This is necessary so that you can select the
  appropriate shape first before naming it.
• Then we will use the Select method for the Shapes
  object and then the Selection object to use the
  Name property.

32
Figures 13.37 and 13.38

• We can select and name a circle object.

33
Formatting Drawing Objects

• We can use several different VBA properties to
  format drawing objects these are a few.
• Fill
• ForeColor
• SchemeColor
• Line
• Weight
• EndArrowheadStyle, EndArrowheadWeight,
  EndArrowheadLength
• BeginArrowheadStyle, BeginArrowheadWeight,
  BeginArrowheadLength
• These properties are used with the ShapeRange
  object which is used with the Selection object.

34
Figures 13.39 and 13.40

• Suppose we have have created and named several
  circles and lines to make a network (of nodes and
  arcs). Let us format these drawing objects.

35
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

36
The Application Object (contd)

• 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.

37
Figures 13.44, 13.45, and 13.46

• Compare the results with and without the property
  CutCopyMode False.

38
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.

39
Figures 13.47 and 13.48

• Let us now format the two tables in our example.

40
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(A1C8).Interior.Color
vbRed Range(A1C8).Font.Bold
True Range(A1C8).Font.Name
Arial Range(A1C8).Borders(xlEdgeBottom).LineS
tyle xlDash
With Range(A1C8) .Interior.Color
vbRed .Font.Bold True .Font.Name
Arial .Borders(xlEdgeBottom). LineStyle
xlDash End With
41
Referencing in VBA

• Referencing ranges and cells
• Naming ranges
• Naming other Excel objects

42
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

43
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 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 xth position above
  (if pos., below if neg.) and yth position to the
  right (if pos., left if neg.) of the named range.

44
Figures 13.58 and 13.59

• Given data in an airline survey, we want to
  highlight certain columns of data using both the
  Offset and Cells properties comparatively.

45
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.

46
Figures 13.62 and 13.63

• We will now modify some more formatting of this
  table.

47
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.

48
Figures 13.64 and 13.65

• We may use these properties on our table in
  preparation for future entries.

49
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.

50
Figures 13.66 and 13.67

• We will copy and paste our table using the End
  property.

51
Naming Ranges

• The most common way to assign object names is by
  using the Name property.
• When you name a range in Excel using Insert gt
  Name gt Define, the name appears in the name
  window whenever the corresponding range is
  selected. The same occurs after naming a range in
  VBA

52
Figures 13.70 and 13.71

• Let us name a range and format one of its cells.

53
Formulas in VBA

• Using ranges and cells
• Using the Application object

54
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

55
Figures 13.76 and 13.77

• We will calculate some sums and averages using
  both the Formula and FormulaR1C1 properties.
• We also use the AutoFill method to copy and paste
  formulas.

56
Formulas with Application Object

• The Application 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

57
Figures 13.79 and 13.80

• Let us use these sub properties of the
  WorksheetFunction property to make some more
  calculations.

58
Summary

• We study several methods and properties for the
  following objects
• Workbooks and Worksheets
• Ranges
• Charts
• Drawing Objects
• Application
• The With construct can help reduce code when
  modifying several properties of one object.
• There are several properties of the Range object
  to reference ranges and cells.
• Formulas can be created in VBA by using
  properties of the Range object and the
  Application object.

59
Additional Links

• (place links here)