Microsoft Office Tutorials and References
In Depth Information
Changing the Data Used in a Chart
gFile = Dir(DirName & “\*.*”)
Do While gFile <> “”
If Right(gFile, 3) <> “png” Then Kill DirName & “\” & gFile
gFile = Dir
Loop
‘ Show the exported graphics
Shell “explorer.exe “ & DirName, vbNormalFocus
End Sub
The procedure starts by saving the active workbook. Then it saves the workbook as an HTML file,
closes the file, and re-opens the original workbook. Next, it deletes the HTML file because we’re
just interested in the folder that it creates (that’s where the images are). The code then loops
through the folder and deletes everything except the PNG files. Finally, it uses the Shell
function to display the folder.
This example is available on the companion CD-ROM. The filename is export all
graphics.xlsm .
Changing the Data Used in a Chart
The examples so far in this chapter have used the SourceData property to specify the complete
data range for a chart. In many cases, you’ll want to adjust the data used by a particular chart
series. To do so, access the Values property of the Series object. The Series object also has
an XValues property that stores the category axis values.
The Values property corresponds to the third argument of the SERIES formula, and the
XValues property corresponds to the second argument of the SERIES formula. See the
sidebar, “Understanding a chart’s SERIES formula.”
Understanding a chart’s SERIES formula
The data used in each series in a chart is determined by its SERIES formula. When you select a
data series in a chart, the SERIES formula appears in the formula bar. This is not a real formula:
In other words, you can’t use it in a cell, and you can’t use worksheet functions within the SERIES
formula. You can, however, edit the arguments in the SERIES formula.
A SERIES formula has the following syntax:
=SERIES(series_name, category_labels, values, order, sizes)
 
Search JabSto ::




Custom Search