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
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.