Question: Display number of series on a chart in an excel cell

%3CLINGO-SUB%20id%3D%22lingo-sub-1289924%22%20slang%3D%22en-US%22%3EQuestion%3A%20Display%20number%20of%20series%20on%20a%20chart%20in%20an%20excel%20cell%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1289924%22%20slang%3D%22en-US%22%3E%3CP%3EI%20have%20a%20excel%20chart%20which%20may%20contain%200-255%20series.%26nbsp%3B%20Since%20the%20chart%20legend%20can't%20hold%20all%20the%20names%2C%20I%20would%20like%20a%20formula%20that%20would%20just%20put%20the%20total%20number%20of%20series%20in%20the%20object%20in%20an%20excel%20cell%20on%20the%20same%20worksheet.%3C%2FP%3E%3CP%3EI%20put%20this%20macro%20in%20to%20do%20this%2C%20but%20it%20does%20not%20auto-update%20when%20I%20delete%20series.%26nbsp%3B%20Is%20there%20a%20way%20to%20put%20the%20macro%20code%20into%20the%20cell%20formula%20directly%3F%3C%2FP%3E%3CP%3EOr%20is%20there%20a%20way%20to%20put%20update%20code%20into%20a%20chart%20change%20event%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CFONT%3EFunction%20DisplaySeriesCount(strSheetName%20As%20String%2C%20strChartName%20As%20String)%20As%20Integer%3CBR%20%2F%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%20Dim%20iAnswer%20As%20Integer%3CBR%20%2F%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%20iAnswer%20%3D%20Sheets(strSheetName).ChartObjects(strChartName).Chart.SeriesCollection.Count%3CBR%20%2F%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%20DisplaySeriesCount%20%3D%20iAnswer%3CBR%20%2F%3EEnd%20Function%3CBR%20%2F%3E%3C%2FFONT%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1289924%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3ECharting%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1612573%22%20slang%3D%22en-US%22%3ERe%3A%20Question%3A%20Display%20number%20of%20series%20on%20a%20chart%20in%20an%20excel%20cell%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1612573%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F609194%22%20target%3D%22_blank%22%3E%40OldStoneFace63%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EYour%20UDF%20is%20a%20non-volatile%20type%20try%20to%20recalculate%20it%3C%2FP%3E%3CP%3E%3CSTRONG%3EActiveSheet.Calculate%3C%2FSTRONG%3E%3C%2FP%3E%3CP%3Eor%3C%2FP%3E%3CP%3E%3CSTRONG%3ERange.Calculate%3C%2FSTRONG%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EMore%20info%20about%20realculations%20can%20be%20found%20below%3A%3C%2FP%3E%3CP%3E%3CSTRONG%3E%3CSPAN%3E%3CA%20href%3D%22https%3A%2F%2Fdocs.microsoft.com%2Fen-us%2Foffice%2Fclient-developer%2Fexcel%2Fexcel-recalculation%22%20target%3D%22_blank%22%20rel%3D%22noopener%20noreferrer%20noopener%20noreferrer%22%3Ehttps%3A%2F%2Fdocs.microsoft.com%2Fen-us%2Foffice%2Fclient-developer%2Fexcel%2Fexcel-recalculation%3C%2FA%3E%3C%2FSPAN%3E%3C%2FSTRONG%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E
Highlighted
Visitor

I have a excel chart which may contain 0-255 series.  Since the chart legend can't hold all the names, I would like a formula that would just put the total number of series in the object in an excel cell on the same worksheet.

I put this macro in to do this, but it does not auto-update when I delete series.  Is there a way to put the macro code into the cell formula directly?

Or is there a way to put update code into a chart change event?

 

Function DisplaySeriesCount(strSheetName As String, strChartName As String) As Integer
    Dim iAnswer As Integer
    iAnswer = Sheets(strSheetName).ChartObjects(strChartName).Chart.SeriesCollection.Count
    DisplaySeriesCount = iAnswer
End Function

1 Reply
Highlighted

@OldStoneFace63 

Your UDF is a non-volatile type try to recalculate it

ActiveSheet.Calculate

or

Range.Calculate

 

More info about realculations can be found below:

https://docs.microsoft.com/en-us/office/client-developer/excel/excel-recalculation