Automatically set min & max scale for y-axis

[tms_]

Hi,

I am writing a code, which would automatically set min and max scale for y-axis.

I have a chart, which adds new given values automatically (this is carried out using OFFSET and COUNTA-functions: the named ranges expand when adding new values, and the chart uses these dynamical areas.)

However, min and max scale of the y-axis don't change automatically (as I would like them to change.)

I'm quite a beginner with visual basic, but I finally managed to come out with this:

(This code is located in Modules: Module 3 - is this the right place?)

Please Login or Register  to view this content.

The code above is called by the next code (for example):

(The next code is located in Microsoft excel objects: Thisworkbook - Is this the right place?)

Please Login or Register  to view this content.

The previous example updates the scaling of the chart called "nameofthechart", and the difference between the maximum value (minimum value) and the y-maximum (y-minimum) scale is 10 percent.

Is this a practical way to carry this out? Would you prefer some other way to do this?

The code seems to work properly when there is just one data series in the chart. But when there is for example two data series (xy scatter), it gives an error message when adding values just to the other series (so that the series contain different number of records)

Debugger highlights the row

Please Login or Register  to view this content.

How could this be prevented?

Thank you!

[Andy Pope]

I can not duplicate your problem.

Are all the values for each series valid? no empty cells or #N/A's

Can you post example workbook?

[tms_]

Thank you Andy!

I found out what was causing the problem. I had defined the names incorrectly... (there wasn't an equal number of rows in every column in these cases that caused the problems)

By the way, how does the code seem like? Do you think it is proper, logical etc.?

[Andy Pope]

code looks fine.
Does it do what you want?

[tms_]

Yes, it seems to work.

I would also like to give the user a chance to let the excel adjust the max & min values automatically.

Would you know, how this could be carried out?

[Andy Pope]

You could add code to check the Min and or MAX arguments in your function.
Allow the user to specify TRUE for automatic. Add code to the function to check whether the parameters contain valid numeric values.
If not check for content being TRUE and then set the scale properties to automatic.