Graph

The object model of the graph document is a hierarchical design where the primary interface is the layout object.

This object is returned from the Content function of the Graph class, and contains interfaces to customize the graph in many ways.

The layout object contains an Axes property that returns the collection of axes from the graph. This collection enables you to edit any given axis, such as adding labels or changing its scaling.

Next, the layout object also contains a Plots property that returns the plots within the graph. This collection enables you to customize all the properties of any given plot. For example, you can add and edit fit lines (through the Fits collection of the plot), change the display of point labels, alter the color of a histogram's bars, etc.

The following diagram offers a general idea of this hierarchy:
Note: All graph customizations documented here can be recorded from the Graph Options dialog. When a graph is displayed, select the Format tab on the Ribbon bar. In the Tools group, click Graph Options to display the Graph Options dialog. Check the Record macro option, and after clicking OK, a macro will be created with all your customizations.

Customizing 2D Graphs

To customize graphs programmatically, you must use the “layout” of the graph to access most of its functionality. For a 2D graph, create a Layout2D object, set it to the content of the graph, and then use that object to customize the graph. For example, to customize a 2D graph (e.g., a histogram) you would do the following:

  1. Set a 2D graph as the active graph and enter the following into a new macro: 
    Option Base 1
Option Explicit
Sub Main
    Dim graphLayout As Layout2D
    Set graphLayout = ActiveGraph.Content
End Sub
  2. Use the graphLayout variable—which is pointing to the 2D graph's content—to access the first plot. Most graphs will usually have just one plot, so for brevity we will make this assumption. When you access the plot, you will be able to find most of the features that you would normally find on the Graph Options dialog interactively.
For example, let us say that we need to turn on point labels that display the Y axis value. To accomplish this, simply add the following lines: 
graphLayout.Plots(1).DisplayPointLabels = True
graphLayout.Plots(1).DisplayPointLabelsX = True
Next, we want to set the format of these point labels to US Dollars, so we will add this line: 
graphLayout.Plots(1).LabelsFormat = "$#,##0;($#,##0)"
As another example, say that we want to remove the legend. Again, we will find this in the plot of the graph's layout. Add the following code to do this: 
graphLayout.Plots(1).LegendType = scgPlotLegendOff

Along with the plot of a graph, the axes are also an important aspect of the graph that is commonly customized. To access this area, use the Axes property of the graphLayout object.

For example, to change the scaling of the Y axis, we need to set the scaling to manual and then set our new range. To do this, enter the following: 
graphLayout.Axes(scgLeftY).StepMode = scgManualStep
graphLayout.Axes(scgLeftY).SetManualRange(1,10)

Note that unlike plots (that were indexed by a numeric value), axes must be indexed more explicitly. That is to say, you must pass in an AxisType value to specify which axis you want to access.

Some more examples of using the Axes property could be turning on minor tick marks on the X axis: 
graphLayout.Axes(scgX).DisplayMinorTickMark = True
…or changing the X axis's font: 
graphLayout.Axes(scgX).Font.Face.FaceName = "MS UI Gothic"
Our final code for the 2D graph customization example will look like this: 
Option Base 1
Option Explicit
Sub Main
    Dim graphLayout As Layout2D
    Set graphLayout = ActiveGraph.Content
    graphLayout.Plots(1).DisplayPointLabels = True
    graphLayout.Axes(scgLeftY).StepMode = scgManualStep
    graphLayout.Axes(scgLeftY).SetManualRange(1,10)
    graphLayout.Axes(scgX).DisplayMinorTickMark = True
    graphLayout.Axes(scgX).Font.Face.FaceName = _
        "MS UI Gothic"
End Sub

Customizing 3D Graphs

Customizing 3D graphs is very similar to customizing 2D graphs. The main difference is that rather than using a Layout2D object, you will need to use a different layout object meant for the specific graph. For example, a 3D histogram will require a Layout3DHistograms object, while a Ternary plot will require a LayoutTernary object. The following table details these relations:

Graph Type Layout Type Required
All 2D graphs Layout2D
3D Histogram Layout3DHistograms
3D Ternary Plot LayoutTernary
3D Line Plot Layout3DLines
3D Scatterplot Layout3DScatterplot
3D Matrix Plot LayoutMatrix
Icon Plot LayoutIcons

Call a Graph object's TypeContent property to deduce which layout object it uses.

Customizing 3D graphs is comparable to 2D graphs in that it relies on accessing the plot and axes of the graph's layout. For example, to customize a 3D scatterplot, you would do the following:

  1. Create a 3D scatterplot and enter the following into a new macro: 
    Option Base 1
Option Explicit
Sub Main
    Dim graphLayout As Layout3DScatterplot
    Set graphLayout = ActiveGraph.Content
End Sub
  2. Remembering from 2D graphs that all the plot features are available in the Plots property of the layout object, we will now add a fit line to the graph by adding this line: 
    graphLayout.Plots(1).Fits.Add
    Now a linear fit (the default for a 3D scatterplot) will be added to our graph. Note that if we wanted a different type of fit (e.g., Quadratic), then do this: 
    graphLayout.Plots(1).Fits.Add.FitType = scgFit3DQuadratic
  3. It is important to note that most of the customization features available to 2D graphs are also available to 3D graphs. For example, all the customizations that we made in the previous 2D graph example could be used in our 3D graph macro. Take this code from the 2D graph example and paste it into our current 3D macro: 
    graphLayout.Plots(1).DisplayPointLabels = True
graphLayout.Axes(scgLeftY).StepMode = scgManualStep
graphLayout.Axes(scgLeftY).SetManualRange(1,10)
graphLayout.Axes(scgX).DisplayMinorTickMark = True
graphLayout.Axes(scgX).Font.Face.FaceName = "MS UI Gothic"

    After running the macro, note that all the changes made to the 2D graph (in the previous example) were now made to the 3D graph.

  4. Along with most of the features of 2D graphs, the Plots property of a 3D graph's layout also has its own specific functionality. For a 3D scatterplot, it is possible to add customize spike lines to the points. Add the following lines to your macro: 
    graphLayout.Plots(1).DisplaySpike3D = True
graphLayout.Plots(1).Spike3D.Type.Value = scgSolid
graphLayout.Plots(1).Spike3D.ForegroundColor = RGB(0,255,0)

    This will add spike lines between the points and the X axis, set them to solid lines, and finally set their color to neon green.

  5. Editing the axes of a 3D graph is basically the same as a 2D graph. For example, to set the step size of the X axis, add these lines: 
    graphLayout.Axes(scgX).StepMode = scgManualStep
graphLayout.Axes(scgX).StepSize = 2
    Another example could be to add a custom label to the X axis. Add these lines to change the value “2” to appear as “two pounds”: 
    Dim custScaleValue As CustomScaleValue
Set custScaleValue = graphLayout.Axes(scgX).CustomScaleValues.Add()
custScaleValue.Value = 2
custScaleValue.Label = "two pounds"

In the above example, we first create a CustomScaleValue object to point to the custom label when we create it. Next, we call the Add function from the CustomScaleValues property of the X axis, which in turn returns a CustomScaleValue object. Finally, we set our custom label to replace the value “2” with “two pounds” by using the CustomScaleValue object that was returned from the Add function.

Our final 3D scatterplot customization macro will look like this: 
Option Base 1
Option Explicit
Sub Main
    Dim graphLayout As Layout3DScatterplot
    Set graphLayout = ActiveGraph.Content
    graphLayout.Plots(1).Fits.Add.FitType = _
        scgFit3DQuadratic
    graphLayout.Plots(1).DisplayPointLabels = True
    graphLayout.Axes(scgLeftY).StepMode = scgManualStep
    graphLayout.Axes(scgLeftY).SetManualRange(1,10)
    graphLayout.Axes(scgX).DisplayMinorTickMark = True
    graphLayout.Axes(scgX).Font.Face.FaceName = _
        "MS UI Gothic"
    graphLayout.Plots(1).DisplaySpike3D = True
    graphLayout.Plots(1).Spike3D.Type.Value = scgSolid
    graphLayout.Plots(1).Spike3D.ForegroundColor = _
        RGB(0,255,0)
    graphLayout.Axes(scgX).StepMode = scgManualStep
    graphLayout.Axes(scgX).StepSize = 2
    Dim custScaleValue As CustomScaleValue
    Set custScaleValue = _
        graphLayout.Axes(scgX).CustomScaleValues.Add()
    custScaleValue.Value = 2
    custScaleValue.Label = "two pounds"
End Sub