Skip this Video
Download Presentation
Chapter 13: More on Objects

Loading in 2 Seconds...

play fullscreen
1 / 59

Chapter 13: More on Objects - PowerPoint PPT Presentation

  • Uploaded on

Spreadsheet-Based Decision Support Systems. Chapter 13: More on Objects. Prof. Name [email protected] Position (123) 456-7890 University Name. Overview. 13.1 Introduction 13.2 Objects and their properties and methods

I am the owner, or an agent authorized to act on behalf of the owner, of the copyrighted work described.
Download Presentation

PowerPoint Slideshow about 'Chapter 13: More on Objects' - preston

An Image/Link below is provided (as is) to download presentation

Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author.While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server.

- - - - - - - - - - - - - - - - - - - - - - - - - - E N D - - - - - - - - - - - - - - - - - - - - - - - - - -
Presentation Transcript

Spreadsheet-Based Decision Support Systems

Chapter 13: More on Objects

Prof. Name [email protected]

Position (123) 456-7890

University Name

  • 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
  • Various properties and methods for commonly manipulated objects.
  • The With construct.
  • Cell referencing and naming in VBA.
  • Excel formulas and functions in VBA.
objects and their properties and methods
Objects and their Properties and Methods
  • Workbooks and worksheets
  • Ranges
  • Charts
  • Drawing objects
  • Application
workbooks and worksheets
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
figures 13 1 and 13 2
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.”
figure 13 5
Figure 13.5
  • We want to activate the appropriate workbooks and then copy the data.
workbooks and worksheets cont
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.
figure 13 8
Figure 13.8
  • We can hide all worksheets but the welcome sheet when the application is opened.
  • 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
range color format
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, …
figure 13 10
Figure 13.10
  • Let us create a solid, red range of cells on the “Welcome” sheet.

Range(“A1:F12”).Interior.ColorIndex = 3

Range(“A1:F12”).Interior.Color = vbRed

border format
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
figures 13 19 and 13 20
Figures 13.19 and 13.20
  • Let us combine these properties, sub properties, and methods to format borders for several ranges of cells.
  • 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
figures 13 21 and 13 22
Figures 13.21 and 13.22
  • Let us enter some different values into a spreadsheet.
font format
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”
figures 13 24 and 13 25
Figures 13.24 and 13.25
  • Let us format the font of the values we just created by modifying the code.
  • 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.
figures 13 28 13 29 and 13 30
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.
conditional formatting
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
figure 13 31
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("C1:C10").FormatConditions.Add Type:=xlCellValue, Operator:=xlLess, Formula1:=10

Range("C1:C10").FormatConditions(1).Interior.Color = vbRed

  • 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.
two chart objects
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
active chart
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
using active chart
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
figures 13 32 and 13 33
Figures 13.32 and 13.33
  • The Graph() procedure creates a chart from a table of data.
further chart modification
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
drawing objects
Drawing Objects
  • Drawing objects, or shapes, can be useful to help the user visualize a problem scenario or suggested solution.
  • You can use the DrawingToolbar 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
creating drawing objects
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).
naming drawing objects
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.
figures 13 37 and 13 38
Figures 13.37 and 13.38
  • We can select and name a circle object.
formatting drawing objects
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.
figures 13 39 and 13 40
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.
the application object
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
the application object cont d
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.
figures 13 44 13 45 and 13 46
Figures 13.44, 13.45, and 13.46
  • Compare the results with and without the property CutCopyMode = False.
the wait method
The Wait Method
  • We will use the Wait method frequently when performing a Simulation in Excel.
  • Waitpauses 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.
figures 13 47 and 13 48
Figures 13.47 and 13.48
  • Let us now format the two tables in our example.
the with construct
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 = vbRed

Range(“A1:C8”).Font.Bold = True

Range(“A1:C8”).Font.Name = “Arial”

Range(“A1:C8”).Borders(xlEdgeBottom).LineStyle = xlDash

With Range(“A1:C8”)

.Interior.Color = vbRed

.Font.Bold = True

.Font.Name = “Arial”


LineStyle = xlDash

End With

referencing in vba
Referencing in VBA
  • Referencing ranges and cells
  • Naming ranges
  • Naming other Excel objects
referencing and names in vba
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
offset vs cells
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.
figures 13 58 and 13 59
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.
columns and rows
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.
figures 13 62 and 13 63
Figures 13.62 and 13.63
  • We will now modify some more formatting of this table.
entirecolumn and entirerow
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.
figures 13 64 and 13 65
Figures 13.64 and 13.65
  • We may use these properties on our table in preparation for future entries.
  • Endis 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.
figures 13 66 and 13 67
Figures 13.66 and 13.67
  • We will copy and paste our table using the End property.
naming ranges
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 > Name > Define, the name appears in the name window whenever the corresponding range is selected. The same occurs after naming a range in VBA
figures 13 70 and 13 71
Figures 13.70 and 13.71
  • Let us name a range and format one of its cells.
formulas in vba
Formulas in VBA
  • Using ranges and cells
  • Using the Application object
formulas with the range object
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
figures 13 76 and 13 77
Figures 13.76 and 13.77
  • We will calculate some sums and averages using both the FormulaandFormulaR1C1properties.
  • We also use the AutoFillmethod to copy and paste formulas.
formulas with application object
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
figures 13 79 and 13 80
Figures 13.79 and 13.80
  • Let us use these sub properties of the WorksheetFunction property to make some more calculations.
  • 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.
additional links
Additional Links
  • (place links here)