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.
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.