CODEWITHSUNDEEP
excelvba
Ber
Ber

Reputation: 53

VBA: How to know if chart is in separate sheet?

How can I access the format of the underlying data of a series in VBA? Something like this:

If ActiveChart.SeriesCollection(1).UnderlyingXValues.NumberFormat = "m/d/yy" Then
    ActiveChart.Axes(xlValue).TickLabels.NumberFormat = "mmm yyyy"
Else If ActiveChart.SeriesCollection(1).UnderlyingXValues.NumberFormat = "0" Then
    ActiveChart.Axes(xlValue).TickLabels.NumberFormat = "0.0"
End If

Is there something that works like UnderlyingXValues in the example above?

Upvotes: 0

Views: 67

Answers (1)

BigBen
BigBen

Reputation: 50162

You can parse the .Formula of the SeriesCollection, like this:

Sub Test()
    Dim seriesFormula As String
    seriesFormula = ActiveChart.SeriesCollection(1).Formula

    Dim seriesAddress As String
    seriesAddress = Split(seriesFormula, ",")(2)

    Dim seriesRng As Range
    Set seriesRng = Range(seriesAddress)

    Select Case seriesRng.NumberFormat
        Case "m/d/yy"
            ActiveChart.Axes(xlValue).TickLabels.NumberFormat = "mmm yyyy"
        Case "0"
            ActiveChart.Axes(xlValue).TickLabels.NumberFormat = "0.0"
    End Select
End Sub

As already pointed out, the number formatting of the source range must be consistent for this to work.

Upvotes: 2

Related Questions