zooming a chart sub-region with a mouse, log plot scale

[Tony Gozdz]

Two challenging questions:

Is there an add-on/hack that would allow one to draw with a mouse a rectangular
box on a chart and have it replotted (like other serious scientific graphing
programs can do) for more detailed inspection? I'm not talking here about
zooming the entire chart, but zooming in on a small region of the chart and having
it redrawn with the rescaled axes and labels, albeit the same axis titles.
Sometimes I'd like to use a very large or very small aspect ratio of the rectangle
being redrawn, and it's really inconvenient to have to do it repeatedly by
clicking on the axis and manually adjust the range... I'm not a programmer by
any stretch of imagination, but years ago *I* did such a thing in True Basic. Am
I asking for too much, or for something that will have already been implemented in
XL2002 (credits to J. Walkenbusch)? <VBG>

Also, has anybody figured out how to plot logarithmic-scale plots with a full
range less than one full decade, which is an idiotic and unexplainable limitation
imposed by the MSFT programmers? One half-assed solution is to plot log(x) on a
linear narow scale, but then it is not what I'm really after...

Tony

[Bill Koran]

I have VBA code that does the first. I keep hoping to have time to make it
work generally for all types of charts, including pre-existing charts, but I
just can't make the time. The code is at least a couple years old, and
embarrassing for me now, but it works very well for the charts it was
designed for.

Although all that is necessary is to change the scale, I did this using an
AutoFilter on the data. I forget why, but I think it was to make it easier
to know the start and end of the data. I'm sure I'd have a better method
now, but like I said, it works. It was designed for Time-series data, using
scatter charts, but could easily be adapted to be more generalized.

The way it works is that the user clicks the rectangle button (like on the
Drawing toolbar), then draws the rectangle. Then the user executes the
macro to zoom the chart. Successive zooms work fine, too, and drawing the
rectangle off either end of the chart, or both sides, unzooms to the
beginning, end, or to all data.

The code is not generalized at all--it has hard-coded sheet names and
ranges, and only works as-is for certain chart/data types, but if you'd like
the as-is code to work from, send me an e-mail and I'll send you the code.

Bill

"Tony Gozdz" <t_g...@yahoo.com> wrote in message
news:82c101c0be44$80875420$a5e62ecf@tkmsftngxa07...

[Patrick Choi]

Bill,

May I have a copy of your code as well if you don't mind? I found it very
interesting and useable in extending the flexibility of presenting a chart.

Rgds,
Patrick Choi

"Bill Koran" <bko...@aol.com> 撰寫於郵件
news:ONuDFftvAHA.1996@tkmsftngp04...

[KLiC]

You could also check out Stephen Bullen's excellent site at
http://www.bmsltd.co.uk/Excel/Default.htm. There are a number of charting
examples, one of which (FunChrt7) allows a user to zoom in and out of a
chart using scroll bars. It isn't exactly what was asked for but it is
pretty neat and no VBA required either.

Keith

"Tony Gozdz" <t_g...@yahoo.com> wrote in message
news:82c101c0be44$80875420$a5e62ecf@tkmsftngxa07...

[Tony Gozdz]

Keith,

thanks for the suggestion; I've already looked at the page and exmaple 7, but
haven't checked it yet, since it seemed to be a bit different than what I wanted.
I'll give it a try today.

Tony

[Tony Gozdz]

Keith, I looked at FunChrt7, it would be just great for my purpose, even
better than the original box-zoom request--if I could figure out how to change the
maximum number of points. In my case, the number of points is variable and can
easily go into thousands, so it must be a dynamic definition, or at least a large
number (something like 32000). Can somebody, perhaps Stephen, offer a suggestion?

Tony

[Tushar Mehta]

Since Stephen Bullen's example is 'Zoom/Scroll a Chart,' it had to have
a zoom capability. And it does. The cell ZoomVal provides the zoom
capability. This is analogous to your example of 'number of points is
variable.'

Create a name such as ChtX,
which is set to =OFFSET(Sheet1!$A$4,ScrollVal,0,ZoomVal,1)

A4 is the title of the data that you want to plot. The real data starts
in the next cell down, i.e., A5.

Define ScrollVal and ZoomVal as names for specific cells. Now, when you
change the content of ZoomVal the number of points in the chart will
change. Change the content of ScrollVal and the starting point of the
chart will change.

--
Regards,

Tushar Mehta
www.tushar-mehta.com
--
In <038301c0c10c$dd0ed900$9ee62ecf@tkmsftngxa05>, Tony Gozdz
<t_g...@yahoo.com> wrote
> Keith, I looked at FunChrt7, it would be just great for my purpose, even =
>
> better than the original box-zoom request--if I could figure out how to =
> change the=20
> maximum number of points. In my case, the number of points is variable =
> and can=20
> easily go into thousands, so it must be a dynamic definition, or at =
> least a large=20
> number (something like 32000). Can somebody, perhaps Stephen, offer a =
> suggestion?
>
> Tony
>

[Tony Gozdz]

Tushar, true, but the example will not handle data files longer than 1096 points,
because that's the (hard-coded?) upper limit of the scroll bars. My question was,
how to extend the scroll bar range to, say, 32,000.

I also found that while the range and zoom function defines ***which data points
*** are plotted, the axis ranges are defined by Excel's Autorange function, which
simply doesn't work well with my data.

All in all, at least in my case, selecting the chart axis ranges with a
mouse-defined rectangle would overcome both problems...

Tony

[Tushar Mehta]

Right click the scroll bar (or zoom bar) and select Format Control...
In the dialog box, set the maximum value to 30000, the largest allowed
value.

The more important point is not focusing on the scroll bar, but the
underlying name, i.e., the ChtX name. You can use its defintion to
develop your own solution.

As far as the min./max. values of the axis go, the only solution I know
is an event driven macro which would adjust the values. There was a
recent discussion on the subject, which you might find through Google.
Also, if you are using XL2000, check out the free add-in, AutoChart, on
my web site.

--
Regards,

Tushar Mehta
www.tushar-mehta.com
--
In <44f401c0c11c$4621a8f0$b1e62ecf@tkmsftngxa04>, Tony Gozdz
<t_g...@yahoo.com> wrote
> Tushar, true, but the example will not handle data files longer than =
> 1096 points,=20
> because that's the (hard-coded?) upper limit of the scroll bars. My =
> question was, =20

> how to extend the scroll bar range to, say, 32,000.
>

> I also found that while the range and zoom function defines ***which =
> data points=20
> *** are plotted, the axis ranges are defined by Excel's Autorange =
> function, which=20

> simply doesn't work well with my data.
>

> All in all, at least in my case, selecting the chart axis ranges with a=20

> mouse-defined rectangle would overcome both problems...
>
> Tony
>
> -----Original Message-----

> Since Stephen Bullen's example is 'Zoom/Scroll a Chart,' it had to have=20
> a zoom capability. And it does. The cell ZoomVal provides the zoom=20
> capability. This is analogous to your example of 'number of points is=20
> variable.'
>

[Tony Gozdz]

Tushar,

thanks a lot for your very helpful suggestions. I'll check your site.

Tony

[Bill Koran]

I sent you a copy via e-mail. I hope you find it useful or at least
interesting.

Bill

"Patrick Choi" <hkch...@netvigator.com> wrote in message
news:9ar8vi$9l...@imsp212.netvigator.com...

> Bill,
>
> May I have a copy of your code as well if you don't mind? I found it very
> interesting and useable in extending the flexibility of presenting a
chart.
>
> Rgds,
> Patrick Choi
>

> "Bill Koran" <bko...@aol.com> źśźgŠóślĽó

[Bill Koran]

I sent you a copy via e-mail. I hope you find it useful or at least

interesting. It will surely need modification to fit your needs, but you
can see what it does.
Bill

"Tony Gozdz" <t_g...@yahoo.com> wrote in message

news:44f401c0c11c$4621a8f0$b1e62ecf@tkmsftngxa04...

[Tony Gozdz]

Bill, thank you for sending your chart zooming example. This is almost exactly
what I'm after, and if I were a VB programmer, I would already be at home. <VBG>
After simplifying the workbook to just one worksheet and one chart, the zooming
function still works great, but I will have to defer to people-in-the-know to
adapt it to my application. At least it's clear it's doable.

Just one quick question: How can I zoom out without having to set the axis range
to AutoRange? Undo doesn't work.

Now, I wonder if somebody would take a look at the zoom code and made it into a
general XL add-on? It's such a useful functionality that I bet a lot of people
would immediately grab it.

Thanks again for sharing your work.

Tony

[Bill Koran]

Tony,

I'm glad you liked it. For unzooming, just draw a larger rectangle that
goes outside the plot area. If you go past the y-axis, it will unzoom to
the beginning of your data. If you go past the right side of plot area
(secondary y-axis), it will unzoom to the end of your data. Draw the
rectangle big enough to go outside the plot area both directions, and you
show all of the data again. The code supports sequential zooming, too, as I
suppose you noticed.

I've always hoped to make this a general add-in myself, and I got pretty
close a year ago or so, but I've just been too busy to focus on it. I may
have time to do it for work in the next couple months.

The code is really pretty simple. If you have a clear situation you need it
for, it should be pretty easy to adapt. Try the macro recorder to select
the appropriate reference cells, and you may be able to figure out what you
need to do.

Failing that, and somebody else doing this, you might drop me a note in a
month or 2 to see if I've done anything more.

Bill

"Tony Gozdz" <t_g...@yahoo.com> wrote in message

news:93dd01c0c1c3$d238dfa0$a5e62ecf@tkmsftngxa07...

[Tushar Mehta]

Hi Bill,

This looks like a useful idea. Would you mind zipping a copy this way?

--
Regards,

Tushar Mehta
www.tushar-mehta.com
--
In <uWOFnyWwAHA.1824@tkmsftngp02>, Bill Koran <bko...@aol.com> wrote

[Desart Eric]

Hi Bill

There is yet another interested person.
Should I could get a copy to?
Thanks in advance.

--
Eric Desart
Tel: +32-(0)3/281.14.97
Fax: +32-(0)3/272.41.50
Email: eric....@belgacom.net

"Tushar Mehta" <ng_p...@bigfoot.com> schreef in bericht
news:MPG.153d4451b...@msnews.microsoft.com...

[KLiC]

Hi Bill,

Looks like you've started something here - please could I have a copy too.

TIA,
Keith

[David Lee]

Me too please (if you're not too fed up with all the requests!)

Thanks

David Lee
dlee_m...@hotmail.com

KLiC <kl...@blueyonder.co.uk> wrote in article
<Oc7erzlwAHA.1824@tkmsftngp02>...

[Bill Koran]

I'll get a copy to everybody that asked, but it might be a couple
days...beyond buried with work right now...

Bill

"David Lee" <dlee_m...@hotmail.com> wrote in message
news:01c0c260$94dcef50$d00a5092@DAVID_LEE...

[qzyo...@gmail.com]

Hi Bill Koran,

I am interested in the chart zooming example. I am working on a project currently and it is related with this chart zooming category. Would you mind if you share a copy to me as well?

My email is qzyo...@yahoo.com

Thank you very much.