Initializing the Chart Sheet

Vertex42 The Excel Nexus

Professional Excel Templates

Get Instant Access

The public sub procedure Main() is triggered from the form button on the chart sheet and contains calls to the initialization procedures for the chart sheet, then scans the chart for score sequences. Screen updating is initially turned off otherwise Excel will update the screen as images are added or removed from the chart. Screen updating is turned back on so that the user can see the chart before it is scanned for score sequences. Note that the ChartTitle object is used to display help messages to the user telling them how to play the game. The ChartTitle object is accessed via the ChartTitle property of the Chart object, which in turn is returned from the Sheets property of the Application object. I added the title to the bottom of the chart when initially formatting it.

Public Sub Main()

Dim msg As ChartTitle

Set msg = Sheets("Alienated").ChartTitle

'Call initialization procedures.

Application.ScreenUpdating = False




Application.ScreenUpdating = True Delay l

'Scan the chart, remove and score consecutive images, 'then update the chart with new images...repeat.


'Update messages and initialize chart for player 'selection of two images.

msg.Text = "Select two adjacent aliens to swap. " & _ "Two single clicks will select a single alien."

End Sub

The InitData() sub procedure is called from Main() and serves to reset the score, outputs an informational message, and fills the image maps range in the ImageMap worksheet with random integer values between 1 and 7. I named the range B2:K11 ImageMap when formatting the ImageMap worksheet.

Private Sub InitData()

Dim msg As ChartTitle, score As AxisTitle Dim wsAlien As Chart, wsMap As Worksheet Dim c As Range

'Initialize Alienated chart sheet.

Set wsAlien = Sheets("Alienated")

Set wsMap = Worksheets("ImageMap")

Set msg = wsAlien.ChartTitle

Set score = wsAlien.Axes(xlCategory).AxisTitle score.Text = "0"

filePath = ActiveWorkbook.Path & "\AlienImages\alien" msg.Text = "Please wait while board is initialized."

'Initialize data on the Hidden worksheet.

Randomize With wsMap

For Each c In .Range("ImageMap") c.Value = Int(Rnd * 7) + 1

Next End With End Sub

The AddSeries() sub procedure is also called from Main() and its purpose is to add the data to the chart. Since the data remains static, I can add it programmatically using variant arrays. You can add a data series to a chart via the SeriesCollection object that is returned using the SeriesCollection property of the Chart object. I first delete any existing series before adding ten new series in a For/Next loop. I set all three variables (x, y, and point size) for each series within the loop. Since each data series requires the same set of x-values and marker sizes, I can use variant arrays (xArray and ptSize) with the XValues and BubbleSizes properties of the SeriesCollection object to set the x-axis and marker size values. Values for they-axis variable are constant for a given set of x-values and are set using the Values property of the SeriesCollection object.

Prior to setting the data values for each series, I set the BubbleScale property of a ChartGroup object. A ChartGroup object represents all the data series charted with the same format (line, bar, bubble, and so on). In this example, all ten series are charted with the same format (bubble) so the ChartGroups property with an index value of 1 returns all ten series as a ChartGroup object. The BubbleScale property only applies to bubble charts and sets a scale factor for the bubbles on the chart. I have to set this property because the images I created are too large to fit in a reasonably sized chart; thus, I scaled them down to 35 percent of their original size.

Private Sub AddSeries() Dim I As Integer Dim chAlien As Chart

Dim xArray As Variant, ptSize As Variant

On Error GoTo ErrorHandler xArray = Array(0, 1, 2, 3, 4, 5, 6, 7, 8, 9) ptSize = Array(1, 1, 1, 1, 1, 1, 1, 1, 1, 1)

'Add 10 data series to the bubble chart.

Set chAlien = Sheets("Alienated") chAlien.ChartGroups(1).BubbleScale = 35 With chAlien

If .SeriesCollection.Count > 0 Then

For I = .SeriesCollection.Count To 1 Step -1

.SeriesCollection(I).Delete Next I End If

.SeriesCollection.NewSeries .SeriesCollection(I).XValues = xArray .SeriesCollection(I).Values = Array(10 - I, 10 10 - I, 10 - I, 10 - I, 10 - I, 10 .SeriesCollection(I).BubbleSizes = ptSize Next I End With Exit Sub ErrorHandler:

MsgBox Err.Description, vbCritical, "Error" End End Sub

At this point in the program, the image map in the ImageMap worksheet has been randomly filled with numbers and the chart has been initialized by resetting the score to zero and adding ten new series of data. All that remains is to fill the chart markers with the images of the aliens. This is accomplished in the InitSeriesImages() sub procedure. In this procedure, nested For/Each loops iterate through each Points collection object associated with the Series object for the chart. Recall that there are ten data series in the chart; therefore, the SeriesCollection object contains ten Series objects. Furthermore, each Series object contains a Points collection containing ten Point objects making for a grand total of 100 data points. The nested For/Each loops effectively iterate through each Point object in the chart and use the UserPicture() method of the ChartFillFormat object to load an image of an alien into the data marker. The ChartFillFormat object is returned by the Fill property of the Point object. The specific image is selected using the value of the cell in the ImageMap worksheet mapped to the specific Point object in the chart (recall how the file names for the alien images were named, see Figure 9.14). If the image map does not contain a value, then the ColorIndex property of the Interior object associated with the Point object is set to xlNone. This effectively removes an image from a data marker and leaves the marker without a background color so it cannot be seen. This is included in the InitSeriesImages() procedure because this procedure will be called again when sequential images need to be removed from the chart.

Private Sub InitSeriesImages() Dim chAlien As Chart

Dim chSeries As Series, chPoint As Point

Dim imagelndex As Integer

Dim wsMap As Worksheet

Dim I As Integer, J As Integer

On Error GoTo InitSeriesError

'Use inital image map to fill data points in chart with images.

Set chAlien = Sheets("Alienated") Set wsMap = Worksheets("ImageMap") I = 1: J = 1 With chAlien

For Each chSeries In .SeriesCollection For Each chPoint In chSeries.Points imagelndex = wsMap.Range("ImageMap").Cells(I, J).Value If imageIndex <> 0 Then chPoint.Fill.UserPicture PictureFile:=filePath & _ imageIndex & ".png"

Else chPoint.Interior.ColorIndex = xlNone 'Erase image End If


I = I + 1 'Increment row index J = 1 'Reset column index

Next End With Exit Sub


MsgBox "An error was encountered while loading images into the chart. " & vbCrLf & Err.Description, vbOKOnly, "Chart Initialization Error: " & Err.Number

End End Sub

Was this article helpful?

0 0
Biorhythm Awareness

Biorhythm Awareness

Who else wants to take advantage of biorhythm awareness to avoid premature death, escape life threatening diseases, eliminate most of your life altering mistakes and banish catastrophic events from your life.

Get My Free Ebook

Post a comment