Visual Basic Scripting

The graphomate charts for Excel add-in supports Visual Basic scripting, making it possible to use the chart interactively. Use cases are the setting of new data, change the appearance, or apply a different scaling. In Visual Basic, properties need to be addressed by their technical names. These can be read out in the Basic Properties; when a property is selected, a small info box appears at the bottom, containing the technical name as well as the datatype of the property. 

Addressing Charts from VBA

To address a chart in a script, a connection with the add-in has to be established. This is done with the following code snippet.

Dim addIn As COMAddIn 
Dim automationObject As Object 
Set addIn = Application.COMAddIns("graphomate.charts") 
Set automationObject = addIn.Object

The variable automationObject is then used to address the individual charts.To read out or change the properties of a given chart, the following commands exist:

getProperty (String chart, String propertyName) 
setProperty (String chart, String propertyName, newValue)

Depending on the datatype of the property to be set, different formats have to be used:

Usually, the parameter newValue is a string, but in the case of int or float properties, it is possible to pass a number instead of a string. The following calls are equivalent:

Call automationObject.setProperty ("chart1", "pinWidth", 4)
Call automationObject.setProperty ("chart1", "pinWidth", "4") 

Call automationObject.setProperty ("chart1", "barWidth", 0.5)
Call automationObject.setProperty ("chart1", "barWidth", "0.5")

Examples

The following exempary script reads the chart type from one chart, saves it to a variable, and then sets it in another one:

Sub Schaltfläche1_Klicken()
    ' Verbindung zum Add-in herstellen
    Dim addIn As COMAddIn
    Dim automationObject As Object
    Set addIn = Application.COMAddIns("graphomate.charts")
    Set automationObject = addIn.Object
    ' Variable c mit dem Wert der Property "chartType" von chart1 setzen
    Dim c As String
    c = automationObject.getProperty("chart1", "chartType")
    ' ausgelesenen Chart-Typen auf chart2 setzen
    Call automationObject.setProperty("chart2", "chartType", c)
End Sub

To set data, the properties series1, series2 ... series10 can be used:

Call automationObject.setProperty("chart1", "series1", "$D$1:$F$21")

If the addressed cell range contains category labels, these will be interpreted by the add-in. Please note that the cell selection always relates to the worksheet which contains the element the script is run from. Thus, it is advisable to add the worksheet address to the selection:

Call automationObject.setProperty("chart1", "series1", "$D$1:$F$21|Worksheet1")

If the chart isn't refreshed correctly (e. g. after data updates from Analysis for Office), the chart can be refreshed via VBA. The functionality of this command is identical to that of the refresh button in the ribbon menu:

Call automationObject.refreshChart("chart1")

Addressing Charts from VBA with helper scripts

The way of addressing charts described above did lead to some issues in the past. It was not possible to create two charts with the same name neither in the same nor in different workbooks. Since Version 1.2.20 only charts in the same workbook have to be named differently. To still address a chart uniquely you have to state it's workbook because the add-in can't detect the workbook that called the script. Therefore we implemented a helper script that can be configured by clicking the Add VBA Module Button in the Ribbon. It inserts VBA helper scripts in the current workbook enabling you to only need the following helper functions:

Sub SetChartProperty(chartName As String, propertyName As String, value As String) … 
Function GetChartProperty(chartName As String, propertyName As String)…

The snippet that retrieves and uses the automationObject is implemented in these helper scripts. Their usage is described subsequently: 

Call SetChartProperty("Tabelle1---Chart 1", "rotated", "true") 
myVar = GetChartProperty("Tabelle1---Chart 1", "rotated")