PDA

View Full Version : How to determine that category axis is related to time?



oleval
02-08-2008, 04:03 AM
Hi All,

I'd like to have a code to determine whether the category axis is related to a series of dates. I tried some example but my code failed to produce any results:

If .CategoryType = xlTimeScale Then .TickLabels.NumberFormat = "M/YY"

Thank you very much for your help!

Alexander

JonPeltier
02-10-2008, 10:23 AM
If Excel has detected dates and automatically applied the time-scale axis, .CategoryType = xlAutomatic.

I use a formula to tell me the type of axis I have:

Public Function GetAxisType(axAxis As Axis) As XlCategoryType
Dim vTest As Variant

Select Case axAxis.Type
Case xlValue
GetAxisType = xlAutomaticScale
Exit Function
Case xlSeriesAxis
GetAxisType = xlCategoryScale
Exit Function
End Select

On Error Resume Next
vTest = axAxis.MaximumScale
If Err.Number <> 0 Then
GetAxisType = xlCategoryScale
Exit Function
End If
On Error GoTo 0

On Error Resume Next
vTest = axAxis.TickLabelSpacing
If Err.Number <> 0 Then
GetAxisType = xlAutomaticScale
Exit Function
End If
On Error GoTo 0

GetAxisType = xlTimeScale

End Function


There is no XlCategoryType enumeration for value type axis, but the way the function works, I return xlAutomatic only for value type axes.

Use the function like this:

If GetAxisType(.Axes(xlPrimary)) = xlTimeScale Then .Axes(xlPrimary).TickLabels.NumberFormat = "M/YY"

oleval
02-11-2008, 04:23 AM
Thank you!!! It works very well indeed.

Regards,
Alexander