## Automating chart scale limits – update

I wrote about this function 2 1/2 years ago, and promptly forgot about it, but a few days ago brian provided an answer to a question that was raised shortly after it was first posted, concerning how to apply the function to secondary chart axes.

I have updated the spreadsheet to incorporate brian’s code (slightly modified), and the new version (including full open source code) can be downloaded from: SetScale.xls

I have also added an example of the technique for plotting a function entered as text on the spreadsheet.  The procedure is:

• Name cells containing the lower and upper limits for the X range: “xstart” and “xend” respectively.
• Create a range “nsteps” with the value 1000 (or however many steps you would like in your graph)
• Create a name “x” that will contain a rnge of x values, between the specified limits by entering: =xstart+xrange/(nsteps-1)*(ROW(OFFSET(Sheet1!\$A\$1,0,0,nsteps,1))-1) in the name “refers to” box (see picture below).
• Create names “Y1vals” and “Y2vals” with the formulas: =EVALUATE(Sheet1!\$B\$17&”+x*0″) and =EVALUATE(Sheet1!\$B\$18&”+x*0″) (adjusting the cell references to the location of your function(s).
• Create an XY (scatter) graph and set the data ranges to =SetScale.xls!x for the X range for both series and =SetScale.xls!Y1vals and =SetScale.xls!Y2vals for the two Y series

This entry was posted in Charts, Excel, UDFs, VBA and tagged , , , , . Bookmark the permalink.

### 13 Responses to Automating chart scale limits – update

1. jp says:

Doug

When I try and open the file, I get a message
“A formula in this worksheet contains one or more invalid references ….”

Any idea why this might be the case?
Cheers
James

Like

• dougaj4 says:

Probably I uploaded the wrong file, I’ll check now.

Like

• dougaj4 says:

I have uploaded the latest version, but the previous version was working OK on my machine, so that may not fix it.

Please let me know.

Note that the ChangeChartAxisScale function only works on Excel 2007 and later (I should have mentioned that)

Like

• jp says:

Doug

Thanks, I am using xl2007.
Don’t worry, I can probably get the gist of it and try and recreate.

Cheers
James

Like

• dougaj4 says:

I’m not getting any warning in Excel 2010. I have uploaded the same file, but saved as .xlsm.
http://www.interactiveds.com.au/software/SetScale.xlsm
Could you let me know if that works.

Like

• dougaj4 says:

I have also just uploaded another one with the names for the Y values amended:
=EVALUATE(Sheet1!\$B\$17&”+Sheet1!x*0″)
=EVALUATE(Sheet1!\$B\$18&”+Sheet1!x*0″)

http://www.interactiveds.com.au/software/SetScale2.xlsm

Like

• jp says:

Sorry Doug … same problem … and with file saved as XLSM … James

Like

• dougaj4 says:

James – I have just downloaded both files from the net and tried them on a different computer running XL 2007 and Windows Vista and had no problem with either of them.

Have you got the latest service pack for Office?

Like

• jp says:

Doug

Thanks, I have automatic updates, but have also downloaded latest SP.
I am still on XP …
Please don’t worry, I can still understand the post without the detailed workbook.

Cheers
James

Like

2. metroxx says:

Hello, I have the same proble 🙂

Like

This site uses Akismet to reduce spam. Learn how your comment data is processed.