• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Title should read Dynamically adjust X axis time scale with vba

D

dnessim

Member
I guess the VBA forum would be a better place to ask this one?


Hi,

First, Is it true that a regular line chart cant scale the Y axis in hours? so we should use a scater plot?

Here is my real question,

My hospital takes temp and humidity readings every 30 mins for 11 Operating rooms. This data is saved to a big Excel file.

I have a procedure that creates a new workbook with worksheets and charts for each room. From a user form my user can enter a start date to end date .

So each worksheet has a chart on it , the X axis has the time/date and the Y axis has the Humidity reading.

If the user selects 1 day of data , then my “major unit” scale would be 1/24 = .0416666

So it takes the 48 data points and plots 24 of them 1 hour apart. This works great.

If my user selects a 30 day range then too many points are plotted on the date axis, the correct number would be 1 so that it plots 30 points 1 day apart.

What I want to be able to do is come up with the math that would let it be adjusted dynamically in my program,

Anybody know how I could do this?

I am also assuming that the only chart that would work for this is a xlXYScatterLinesNoMarkers

Here is a snippet of the code If I always wanted it set for 1 day (plot every hour) then

Myval = 1/24 or .0416666


'Create a chart on each new worksheet

Set hchart = Worksheets(roomname).Shapes.AddChart.Chart


hchart.SetSourceData Source:=HRange

hchart.ChartType = xlXYScatterLinesNoMarkers

hchart.SetElement (msoElementLegendBottom)

hchart.Axes(xlCategory).Select

hchart.Axes(xlCategory).MinimumScale = MinScaleValue

hchart.Axes(xlCategory).MaximumScale = MaxScaleValue

'displays dates in the x axis by hour increments, not exact

hchart.Axes(xlCategory).MajorUnit = myval

'Hchart.Axes(xlCategory).MinorUnit = .02

hchart.Axes(xlValue).MinimumScale = 10

hchart.Axes(xlValue).MaximumScale = 70


Thanks,

Dave
 
Hi Dave ,


I hope someone answers your question ; in case you don't get one , please upload a specimen workbook.


Narayan
 
You must log in or register to reply here.
Back
Top