1 / 11

Chapter 8

Chapter 8 . Working with Other Excel Objects. Collections and Specific Members of Collections. Two main ideas: (1) specifying a member of a collection; (2) specifying a hierarchy in the object model. Workbooks collection is the collection of all open workbooks.

joel-hewitt
Download Presentation

Chapter 8

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. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Chapter 8 Working with Other Excel Objects

  2. Collections and Specific Members of Collections • Two main ideas: (1) specifying a member of a collection; (2) specifying a hierarchy in the object model. • Workbooks collection is the collection of all open workbooks. • Any member of this collection (a workbook) can be specified with its name. • Worksheets collection allows a worksheet to be referenced as Worksheet(“Analysis”). • Chart collection allows a chart to be referenced as Charts(“Sales”). • Note: If you want to reference any particular member of a collection, you must write out the plural collection name and then follow it in parentheses with the name of the member in double quotes. • Hierarchy for the above collections: (1) Workbooks collection consists of individual workbooks; (2) A particular workbook contains a Worksheets collection and a Charts collection • If a particular worksheet belongs to the active workbook, you can refer to it as Worksheets(“Analysis”) or ActiveWorkbook.Worksheets(“Analysis”)

  3. Collections and Specific Members of Collections • Sometimes you must spell out the workbook: Workbooks(“Research”).Worksheets(“Anaysis”) • The same can be said for the Chart sheet: • Charts(“Sales”) • ActiveWorkbook.Charts(“Sales”) • Workbooks(“Customers.xls”).Charts(“Sales”) • The Worksheets collection is one step down the hierarchy from the Workbooks collection. • Range objects are one step farther down the hierarchy from the Workbooks collection. • If you want to reference the range A3:C10 in the active sheet: (1) Range(“A3:C10”) • (2) ActiveSheet.Range(“A3:C10”) • Declaring the range explicitly for a particular sheet: • Workbooks(“Customers.xls”).Worksheet(“Data”).Range(“A3:C10”) • You can always read this reference from right to left • The most commonly used property is Count: • ActiveWorkbook.Worksheets.Count • The most commonly used method is Add which allows you a add a new member to the collection.

  4. Opening, Closing, and Saving a Workbook • Sub Workbooks1() • ' This sub shows how to open or close a workbook. They are done differently. • ' To open a workbook, use the Open method of the Workbooks collection, • ' followed by the name of the workbook file. To close a workbook, use • ' the Close method of that workbook. • ' • ' The following line assumes there is a file called Text.xls in the • ' C:\MyDocuments folder. If you want to run this (without an error message), • ' make sure there is such a file. • Workbooks.Open Filename:="C:\My Documents\Test.xls" • ' Count the worksheets in this file and display this in a message box. • MsgBox "There are " & ActiveWorkbook.Worksheets.Count & " worksheets in " _ • & "the " & ActiveWorkbook.Name & " file." • ' Close the workbook. • Workbooks("Test.xls").Close • End Sub • Sub Workbooks2() • ' This sub shows how to save an open workbook. It mimics the familiar Save • ' and SaveAs menu items. • With ActiveWorkbook • ' This saves the active workbook under the same name - no questions asked. • .Save • ' The SaveAs method requires as arguments information you would normally fill • ' out in the SaveAs dialog box. • .SaveAs Filename:="C:\My Documents\NewWorkbook", _ • FileFormat:=xlWorkbookNormal • ' Check the name of the active workbook now. • MsgBox "The name of the active workbook is " & .Name • End With • End Sub

  5. Locating the Path of a Workbook • Sub Workbooks3() • ' This sub assumes a file named Customer.xls exists in the same folder as • ' the file containing this code. Otherwise, an error message will be displayed. • Workbooks.Open ThisWorkbook.Path & "\Customer.xls" • MsgBox "The Customer.xls file is now open.", vbInformation • Workbooks("Customer.xls").Close • MsgBox "The Customer.xls file is now closed.", vbInformation • End Sub • Sub Workbooks4() • ' This sub shows some properties you can obtain from an open workbook. • With ActiveWorkbook • ' Display the file's name. • MsgBox "The active workbook is named " & .Name • ' Check the file format (.xls, .csv, .xla, and many others). Actually, this • ' will display an obscure number, such as -4143 for .xls. You have to search • ' online help to decipher the number! • MsgBox "The file format is " & .FileFormat • ' Check whether the file is password protected (True or False). • MsgBox "Is the file password protected? " & .HasPassword • ' Check whether the file is an add-in, with an .xla extension (True or False). • MsgBox "Is the file an add-in? " & .IsAddin • ' Check the file's path. • MsgBox "The path to the file is " & .Path • ' Check whether the file is ReadOnly (True or False). • MsgBox "Is the file read only? " & .ReadOnly • ' Check whether the file has been saved since the last changed (True or False). • MsgBox "Has the file been changed since the last save? " & .Saved • End With • End Sub

  6. Examples of Worksheets in VBA using Examples 8.5 and 8.6 • Sub Worksheets1() • Dim ws As Worksheet • ' Go through each state (however many there are) and display info for that state. • For Each ws In ActiveWorkbook.Worksheets • With ws • If .Name <> "AllStates" Then • MsgBox "The headquarters of " & .Name & " is " _ • & .Range("B1") & ", there are " & .Range("B2") _ • & " branch " & "offices, and sales in 1999 were " & _ • Format(.Range("B3"), "$#,##0") & ".", _ • vbInformation, .Name & " info" • End If • End With • Next • End Sub • Sub Worksheets2() • ' This sub just lists all of the states and their headquarters from the state sheets. • ' It uses the built-in constant vbCrLf to format the message box nicely. • Dim ws As Worksheet, Msg As String • Msg = "The states and their headquarters listed in this workbook are:" • For Each ws In ActiveWorkbook.Worksheets • If ws.Name <> "AllStates" Then _ • Msg = Msg & vbCrLf & ws.Name & ": " & ws.Range("B1") • Next • MsgBox Msg, vbInformation, "State info" • End Sub

  7. Adding a New Worksheet • Sub Worksheets3() • ' This sub asks the user for a new state and its information, then creates a new • ' sheet for the new state. • Dim IsNew As Boolean, NewState As String, HQ As String, NBranches As Integer, _ • Sales99 As Currency, ws As Worksheet • ' Keep asking for a new state until the user provides one that is really new. • Do • NewState = InputBox("Enter a new state.", "New state") • IsNew = True • For Each ws In ActiveWorkbook.Worksheets • If NewState = ws.Name Then • MsgBox "This state already has a worksheet. Enter another state.", _ • vbExclamation, "Duplicate state" • IsNew = False • Exit For • End If • Next • Loop Until IsNew • ' Get the required information for the new state. • HQ = InputBox("Enter the headquarters of " & NewState, "Headquarters") • NBranches = InputBox("Enter the number branch offices in " & NewState, _ • "Branch offices") • Sales99 = InputBox("Enter sales in 1999 in " & NewState, "1999 Sales") • ' Add the name of the new state to the list in the AllStates sheet. • Worksheets("AllStates").Range("A1").End(xlDown).Offset(1, 0) = NewState • ' Copy the Indiana sheet (or it could be any other state's sheet) to obtain a new • ' sheet, which becomes the active sheet. Then change its name and information. • Worksheets("Indiana").Copy after:=Worksheets(Worksheets.Count) • With ActiveSheet • .Name = NewState • .Range("B1") = HQ • .Range("B2") = NBranches • .Range("B3") = Sales99 • End With • End Sub

  8. Sorting a Worksheet • Sub Worksheets4() • ' This sub puts the state sheets (not including the AllStates sheet) in alphabetical • ' order. It first sorts the states in the AllStates sheet, then uses this order. • Dim Sht1 As String, Sht2 As String, cell As Range • With Worksheets("AllStates") • .Range("A1").Sort Key1:=.Range("A1"), Order1:=xlAscending, Header:=xlYes • With .Range("A1") • Range(.Offset(1, 0), .End(xlDown)).Name = "States" • End With • End With • Sht1 = "AllStates" • For Each cell In Range("States") • Sht2 = cell.Value • Worksheets(Sht2).Move after:=Worksheets(Sht1) • Sht1 = Sht2 • Next • MsgBox "State sheets are now in alphabetical order." • End Sub • Notes: (1) .End(xlDown) will go the bottom of the current list; (2) .Offset(1,0) is used to go down one more row; (3) This line makes a copy of the Indiana sheet: Worksheets(“Indiana”).Copy after”=Worksheets(Worksheets.Count), it will place it after the Worksheets.Count; (4) use of Sort, Move, and After; (5) notice how the For Each loop cycles through the sheets to be sorted and moved.

  9. Displaying Properties of the Chart • Sub Charts1() • ' This sub illustrates some of the properties of a chart. The chart already • ' exists (was built with the Chart Wizard) on the Sales sheet. • With Worksheets("Sales").ChartObjects(1) • MsgBox "The next four messages indicate the position of the chart." • MsgBox "Left property: " & .Left • MsgBox "Top property: " & .Top • MsgBox "Height property: " & .Height • MsgBox "Width property: " & .Width • MsgBox "The next few messages indicate some properties of the chart." • With .Chart • MsgBox "Chart name: " & .Name • MsgBox "Chart type: " & .ChartType • MsgBox "HasLegend property: " & .HasLegend • MsgBox "HasTitle property: " & .HasTitle • MsgBox "Title: " & .ChartTitle.Text • MsgBox "Number of series plotted: " & .SeriesCollection.Count • MsgBox "Some properties of the horizontal axis (there are many!):" • With .Axes(xlCategory) • MsgBox "Format of tick labels: " & .TickLabels.NumberFormat • MsgBox "Title: " & .AxisTitle.Caption • MsgBox "Font size of title: " & .AxisTitle.Font.Size • End With • MsgBox "Some properties of the vertical axis:" • With .Axes(xlValue) • MsgBox "Title: " & .AxisTitle.Caption • MsgBox "Font size of title: " & .AxisTitle.Font.Size • MsgBox "Minimum scale: " & .MinimumScale • MsgBox "Maximum scale: " & .MaximumScale • End With • End With • End With • End Sub

  10. Changing Properties of a Chart • ' This sub allows you to change the product columns (two of them) that are charted. • Dim Prod1 As Integer, Prod2 As Integer • MsgBox "You can choose any two of the products to plot versus time." • Prod1 = InputBox("Enter the index of the first product to plot (1 to 7)") • Prod2 = InputBox("Enter the index of the second product to plot (1 to 7, not " _ • & Prod1 & ")") • ' Note that the columns of data already have the range names Product1, Product2, etc. • With Worksheets("Sales").ChartObjects(1).Chart • With .SeriesCollection(1) • ' The Values property indicates the range of the data being plotted. The XValues • ' property indicates the values on the X-axis (in this case, the months). The Name • ' property is the name of the series (which is shown in the legend). This name is • ' found in row 1, right above the first cell in the corresponding Product range. • .Values = Range("Product" & Prod1) • .XValues = Range("Month") • .Name = Range("Product" & Prod1).Cells(1).Offset(-1, 0) • End With • With .SeriesCollection(2) • .Values = Range("Product" & Prod2) • .Name = Range("Product" & Prod2).Cells(1).Offset(-1, 0) • End With • End With • End Sub

  11. More Properties and Methods of Charts • Sub Charts3() • ' This sub shows some other things you can do to "fine tune" charts. I • ' learned the coding mostly from recording. • Dim Color1 As Integer, Color2 As Integer • ' Use this next statement so that the random colors chosen later on will be • ' different from run to run. • Randomize • Worksheets("Sales").ChartObjects(1).Activate • With ActiveChart • With .PlotArea • MsgBox "The plot area will be changed from gray to blank." • .ClearFormats • MsgBox "It will now be restored to light gray." • .Interior.ColorIndex = 15 • End With • With .Axes(xlValue) • MsgBox "The horizontal grid lines will be deleted." • .HasMajorGridlines = False • MsgBox "They will now be restored." • .HasMajorGridlines = True • End With • MsgBox "The two series will now change to some random colors." • ' Generate two random colors (that aren't the same). • Color1 = Int(Rnd * 40) + 1 • Do • Color2 = Int(Rnd * 40) + 1 • Loop Until Color2 <> Color1 • With .SeriesCollection(1) • .Border.ColorIndex = Color1 • .MarkerBackgroundColorIndex = Color1 • .MarkerForegroundColorIndex = Color1 • End With • With .SeriesCollection(2) • .Border.ColorIndex = Color2 • .MarkerBackgroundColorIndex = Color2 • .MarkerForegroundColorIndex = Color2 • End With • .Deselect • End With • End Sub

More Related