Thread: Excel 2007 - axis control in VBA (or else)

Hi.
I'm designing some dynamic charts.

1st problem is that in VBA whatever parent I chose, Charts child is always empty: count 0.
I have about 10 charts in the workbook, and the count is always 0.
Why that may be?

2nd problem: I used named ranges to make some charts dynamic: essentially I can zoom horizontally and pan to examine data more accurately with scrollbars.
All of this without VBA (scrollbars put data on some cells, this data is used to scale the offsets of the named ranges which then get plotted).
Now sometimes Excel get confused and instead of plotting just the data I tell it to, it plots a lot of white space, eighter before, after, or both before and after the real data.
This means that sometimes my zoom and pan may lead to an effective data range from 567 to 682, I get this data plotted in the center and about 200 points equivalent white space before and another 200 points equivalente white space after the data.
As if the X-axis were set to 367 - 882. But the Primary Axis is on auto mode for everything!
This nullifies the purpose of the zoom, so I thought of using the same variables I use for offsetting the range, to manually impose X-axis max and min.

But since I can't get no charts in VBA, I can't manually set the axis.
Otherwise I would set the MinX to E15 and MaxX to E14+E15.
Anyway the dynamic scaling is done like this

=OFFSET(data!$A$2;Vars!$E$15;0;Vars!$E$14)

Vars!E15 contains

=ROUND(E11/100 * ($E$5-E14);0)

Vars!E14 contains

=ROUND(E5*(E7/10)/100;0)

Vars!E11 contains

=E8/10

Vars!E10 contains

=100-E7/10

Vars!E7 contains the raw zoom value from the zoom-scroller from 10 to 1000
Vars!E8 contains the raw pan value from the pan-scroller from 0 to 1000
Vars!E5 contains

=COUNTA(data!$A:$A)

I hope this is clear.

Edit: attached you'll find a copy of my idea. It does the exact same thing I said in this post.