VBA code If statement to check if chart x-axis is formatted as Date

[gmcgough600]

Hi, looking for some help writing some VBA code.

I need to write an If statement to check if the x-axis of a chart is formatted as a date (doesn't matter if its dd-mmm-yy or dd/mm/yyyy, mm/dd/yyyy etc) then run some other code. Here's what I've got so far:

If worksheet_charts = 1 Then
    Dim sht As Worksheet
    Dim CurrentSheet As Worksheet
    Dim cht As ChartObject
    For Each sht In ActiveWorkbook.Worksheets
        For Each cht In sht.ChartObjects
        'Edit the x-axis
        With cht
            [B]'[help needed here] If 'chart has date x-axis' Then[/B]
                .Chart.Axes(xlCategory).MaximumScale = chart_end_date
                .Chart.Axes(xlCategory).MinimumScale = chart_start_date
            Else if
                'Do nothing
            End if
        End With
        Next cht
    Next sht
End If

Hope someone can help, thanks!

 

[RoryA]

Do you want to know if it's actually a Date axis, or just whether it's got a date format applied to the axis labels?

 

[gmcgough600]

Do you want to know if it's actually a Date axis, or just whether it's got a date format applied to the axis labels?

Hi RoryA, I suppose the most important thing is that a date (or time) format has been applied. But if it's possible to check that the data is actually a date this might be beneficial if this can be done?

Thanks

 

[RoryA]

The most reliable way I know to do that is to test the axis.majorunitscale property. If that causes an error, then the axis is not a date axis.

 

[gmcgough600]

Thanks RoryA, what's the easiest way to check this property? Probably very simple but I gave this a try and got stuck:

        With cht
            With .Chart.Axes(xlCategory)
                If .MajorUnitScale = xlDays Then

I'm getting error "Run-time error '-2147467259 (80004005)':

This property is not used by value axes."

The chart I'm testing on has dates on the x-axis.

 

[RoryA]

As I mentioned, it causes an error for a non-date axis. Simple method is to add a separate function like:

Function IsDateAxis(ax as Axis) as Boolean
dim typ as Long
typ = -1
on error resume next
typ = ax.majorunitscale
on error goto 0
isdateaxis = (typ > -1)
End Function

then your code just becomes:

If IsDateAxis(.Chart.Axes(xlCategory)) Then

 

[gmcgough600]

As I mentioned, it causes an error for a non-date axis. Simple method is to add a separate function like:

Function IsDateAxis(ax as Axis) as Boolean
dim typ as Long
typ = -1
on error resume next
typ = ax.majorunitscale
on error goto 0
isdateaxis = (typ > -1)
End Function

then your code just becomes:

If IsDateAxis(.Chart.Axes(xlCategory)) Then

Thanks RoryA, but I've tried your code and it seems to be giving an error for a date axis, so now nothing happens for all graphs. Hovering over ax.MajorUnitScale in the function it says: "ax.MajorUnitScale = <This property is not used by value axes.>"

My chart for testing the code is just a simple chart with dates on the x-axis, see attached.

 

[RoryA]

The function has an error handler so unless you have set your vb editor to break on all errors you should not be seeing an error

 

[gmcgough600]

The function has an error handler so unless you have set your vb editor to break on all errors you should not be seeing an error

Hi RoryA, I've got it set to "Break On Unhandled Errors".

 

[RoryA]

I just noticed you said value axis - why are you checking those for dates?