Pages

How to find the name of an active chart using VBA

In an earlier lesson we looked at how to create a chart automatically. Read this post if you want to know how.

How to create a line chart automatically - Excel VBA


Today I’m going to teach you how to find the name of an active chart using VBA. When you create a chart either manually or automatically using VBA then it becomes active chart until you deselect it. Also if you select an existing chart in an excel sheet, then it also becomes active chart. Assume we have an active chart like this in our excel sheet.

Name of the sheet is “Sheet4”. Now let’s try to find the name of the chart using VBA. So you might think we can easily find the name of this active chart as follows.

Sub ActiveaChartName()

Debug.Print ActiveChart.Name

End Sub

But this doesn’t work. This is what we will get if we run above code.

We have an extra name addition to the chart name in the beginning. That extra name is the sheet name. So we need to do some additional work to extract the chart name. Make sure you have selected at least one chart when you run above code. Otherwise you will get below run-time error.

So now let’s see how we can extract only the chart name from the ActiveChart.Name. If we analyze the default chart names given for the charts when we create them, we can see that it has the following format.

Chart + ChartNumber

For example, Excel names the charts like this. Chart 1, Chart 2, Chart 3 etc. So we can use the split function to extract the chart name from the ActiveChart.Name easily.

Sub ActiveaChartName()

Dim SheetAndChartName As String
Dim ChartName As String
Dim WrdArray() As String

SheetAndChartName = ActiveChart.Name

WrdArray() = Split(SheetAndChartName)

ChartName = WrdArray(UBound(WrdArray) - 1) & " " & WrdArray(UBound(WrdArray))

Debug.Print ChartName

End Sub

If you want to learn more about split function, read this post.

Split Function

Above macro will print the chart name correctly in the immediate window as follows.


Note that this will only work if the user hasn’t altered the default chart name.