I want to use programatic adjusting of a chart in Excel 2016. I found a list of properties belonging to the Axis class here -> Excel class Axis VBA.
I can set the CategoryType to xlTimeScale programmatically using VBA, but I get a runtime error when I try to set the MajorUnitScale.
How can I control these properties from VBA?
Here is the code I am using:
Public Sub Fix_chart()
Dim ws As Worksheet
Dim ch As ChartObject
Dim xA As Object
Dim yA As Object
Set ws = Sheets("Sheet1")
Set ch = ws.ChartObjects(1)
ch.Chart.HasTitle = True
ch.Chart.ChartTitle.Text = "Temperatur"
Set xA = ch.Chart.Axes(xlCategory)
Set yA = ch.Chart.Axes(xlValue)
yA.MinimumScale = 15
yA.MaximumScale = 35
xA.MinimumScale = Hour(Now()) / 24
xA.MaximumScale = (Hour(Now()) + 2) / 24
xA.CategoryType = xlTimeScale
xA.MajorUnitScale = 1 / 24 'corresponding to one hour, gives runtime error "Property not used by value axes"
End Sub
I can set the CategoryType to xlTimeScale programmatically using VBA, but I get a runtime error when I try to set the MajorUnitScale.
How can I control these properties from VBA?
Here is the code I am using:
Public Sub Fix_chart()
Dim ws As Worksheet
Dim ch As ChartObject
Dim xA As Object
Dim yA As Object
Set ws = Sheets("Sheet1")
Set ch = ws.ChartObjects(1)
ch.Chart.HasTitle = True
ch.Chart.ChartTitle.Text = "Temperatur"
Set xA = ch.Chart.Axes(xlCategory)
Set yA = ch.Chart.Axes(xlValue)
yA.MinimumScale = 15
yA.MaximumScale = 35
xA.MinimumScale = Hour(Now()) / 24
xA.MaximumScale = (Hour(Now()) + 2) / 24
xA.CategoryType = xlTimeScale
xA.MajorUnitScale = 1 / 24 'corresponding to one hour, gives runtime error "Property not used by value axes"
End Sub