Setting maximum and minimum values for x-axis as dates in Excel

3
votes

I have a graph that has dates on the x-axis and I'm trying to set maximum and minimum values for this axis using an Excel VBA. Below is my code which doesnt seem to work.Can anyone please help.

With ActiveSheet.ChartObjects(1).Chart.Axes(xlValue)
    .MinimumScale = ActiveSheet.Range("C33").Value
    .MaximumScale = ActiveSheet.Range("D54").Value
End With
2
excelvba
Your code actually sets bounds for y-axis. You can't set bounds for x-axis because they are taken from source data. – ttaaoossuuuu

2 Answers

2
votes

xlValue refers to the y-axis (or value axis). You're interested in adjust the x-axis values (or category axis) which require xlCategory. So use

With ActiveSheet.ChartObjects(1).Chart.Axes(xlCategory)
    .MinimumScale = ActiveSheet.Range("C33").Value
    .MaximumScale = ActiveSheet.Range("D54").Value
End With
0
votes

I created a chart for a bivariate normal distribution. X1 follows a normal distribution with mu1 and stdev1 and likewise for X2. X1 is along the X axis. I wanted the limits to be within 4 standard deviations of the mean. mywidth and myheight were assigned beforehand. The data start on row 2 since there are titles on row 1. The data for X1 are in the 1st column. n is the number of rows of data.

mysheetname = ActiveSheet.Name
Set mychart = Sheets(mysheetname).ChartObjects.Add(Left:=mywidth, Top:=myheight + 2, Width:=400, Height:=250)
mychart.Chart.ChartType = xlXYScatter
mychart.Chart.SeriesCollection.NewSeries
mychart.Chart.SeriesCollection(1).Values = Range(Cells(outputrow + 1, outputcol + 1), Cells(outputrow + n, outputcol + 1))
mychart.Chart.SeriesCollection(1).XValues = Range(Cells(outputrow + 1, outputcol), Cells(outputrow + n, outputcol))
mychart.Chart.HasLegend = False
mychart.Chart.Axes(xlValue).MinimumScale = mu2 - 4 * sigma2
mychart.Chart.Axes(xlValue).MaximumScale = mu2 + 4 * sigma2
mychart.Chart.Axes(xlCategory).MinimumScale = mu1 - 4 * sigma1
mychart.Chart.Axes(xlCategory).MaximumScale = mu1 + 4 * sigma1