Easy Zoom – Chart Axis Scaling Using VBA

In certain models we need to be able to change the scale of the chart axes function of the result of a simulation. Excel charts do have auto-scaling as a default option but sometimes the scaling values we get are not what we need. Another reason against using auto-scaling is that during the time the model runs, the scale self-adjusts and it gives an ever changing, distorted view of the results. A fast and easy manual zoom option is preferred. This tutorial will handle that, namely it will create a VBA macro which can independently change chart axis scales between 1 to 10000 (in oscilloscope style: 1, 2, 5, 10, 20, 50, 100 …). Thanks, Jean-Marc for your VBA suggestions.


This article has 12 Comments

  1. Alex, I am not sure what you are talking about when you say “reading normal blogs can open your mind to solve the science problems”. I couldn’t find those blogs. I am not saying they don’t exist, I just have not found them. There are two approaches out there. There are people who take some books or courses from college and regurgitate that (see khan academy for instance) delivering the essentially same thing. People like this say “books are written from books”.The value in that is marginal unless one lives in a 3rd world country with no access to information. If it’s in th video form it might also be useful to a lot of kids nowadays who are TV educated and if they read more than few lines they fall asleep. Then there are people who produce something but don’t see the spirit. They created what they created by following a cookbook style approach and feel an urge to force others to follow the same methods rather than use their own judgement. This people will preach you the need of learning a new way of waxing a car every year and they see this as personal growth. What I also found, and I am getting this all the time, is people who call themselves various titles and who spend years “studying” some narrow topic to be able to solve some minor problems. A lot of this has to do with the current system including academia which what mostly teaches you is compliance and all this “revolutionary” software floating around. Now you can find lots of “techie blogs” which shows you how to apply the wax on the car the 7th way besides the 6 you know. What I am trying to do in this place is not teach people but poke them, maybe give them some ideas trying to make them think for themselves. And it might sound counterproductive but it’s the opposite: if you focus in one place you usually remain there. If you focus on ways of applying wax on cars all you are going to be is a wax polisher. You meet this everywhere around you, in science, in engineering, etc. Forget the old days, nowadays everyone is “specialized”, everyone is an “expert” and you meet all these experts with such a narrow expertise that when you integrate their “knowledge” you get very little. And you need a large team, large budget and large time line to do anything. You should focus on making the car and forgetting about the rules you learned. Free up your mind even if doing so it hurts. What many people like to say, break out of the box.

  2. Thanks, Alex! I haven’t heard about the choose function :-), not until today. It seems useful. However Excel is of a secondary role on this blog. This blog is for engineering and science. Excel is unspoiled, no need for programming and it does not solve things for you like other black boxes around. That’s why I use Excel. I worked for many companies and I’ve also kept in touch with school for many years and no matter where you go the stoy is one: people refuse to use their mind. They continuously read something or take classes, burry themselves in books, papers, syntax, expensive software, teams of collaborators and all the crap they take from the outside. But they are afraid to use their mind and that’s why there is this prevalent impotence wherever you go. Unfortunately in some posts I give the impression that I want to teach Excel. I don’t and I don’t want to know any more Excel myself. I know more that enough. It there is a good way to do something don’t learn any more syntax. Learn some different science if you wish. Pick something you never heard before or you barely heard before, play with it, think it over for few days and make o model. It’s time much better spent than learning 5 more ways to do the same thing with the same result and same speed as the 5 ways you already know. Stacking garbage in the memory is not good for anyone, yet people do it since it gives them an excuse to overflow their mind with ballast so they don’t use it the way it was intended to be used for, and that is thinking.

  3. I will keep the background white. The 3D effects are very strong.

  4. The best initial try I had was with black chart area, make the aqua lines brown (one colour darker than full red on the colour grid) and the red lines navy blue (above the royal blue in the grid). But it’s nowhere near as effective as the white background combination.

  5. Re stereoscopy (analglyphic stereoscopy) which is not relevant to scaling chart axes in case other readers are wondering where this thread’s going!

    In stereoscopy you need the white background because you need the line that’s the same colour as the lens on the “wrong eye” to disappear into the background and the same line to appear black or grey in the “correct eye”. This is the case for the red lines in the red eye (appear same as white does where the blue lines appear grey) and the cyan lines in the cyan eye (appear same as white when the red ones appear grey).
    To make a line disappear into a black background, but appear bright for red/cyan lens colours will take a bit of experimentation and I don’t think the colour choices are possible in Excel 2003… sounds like a small challenge.

  6. Thanks Don! I appreciate your input.
    By the way my original stereoscopy chart had a black background. Nothing would work with that. I experimented and some light colors work but white is the best. With white the cube comes out of the screen :-).

  7. Sorry all, it doesn’t make the code briefer, but it does extend the range for the scale factor to 1e-10 to 1e10 easily.

  8. I have an addition to make here that makes the code even briefer, although I have only returned the scale factor to a cell in this code, this technique is just as easy to apply to a chart using JeanMarc’s With… End With construct above.

    The technique involves reading the mantissa (1, 2 or 5) from an array and multiplying by 10 ^ exponent, where exponent is increased or decreased by 1 for every third click of the spin button. See my commented code below and feel free to use it folks.


    Private Sub Scaler_Change()
    ‘ *************************************************************
    ‘ * Written by Don Love 5/5/2011 for http://www.excelunusual.com *
    ‘ * Calculates a scale factor to apply based on a spin *
    ‘ * button named Scaler set up with a min value of -30 *
    ‘ * a max value of 30. The result is placed in cell C4 *
    ‘ * but you could use it for scaling a chart’s axes *
    ‘ * magnifying a time step, etc *
    ‘ * Please copy and modify as you wish *
    ‘ *************************************************************

    Dim base()
    base = Array(1, 2, 5)
    ‘ Calculate the scale factor and place it in cell C4
    ‘ Scale factor = [ mantissa portion of result ] * 10 ^ [ exponent of result ]
    Range(“C4”).Value = base((Scaler.Value + 30) Mod 3) * 10 ^ (Int(Scaler.Value / 3))

    End Sub

  9. Thanks Reuvain, that is correct! I upgraded the PDF. Georges

  10. George, I think the variable declaration before Scale_Y_Change() should be
    Dim aY as Integer
    instead of
    Dim bX as Integer

  11. Thanks Jean Mark! I will make a post including your code since this is important.
    I’ve been looking for something like this. I hate wasting space. George

  12. Hi George,
    Great post. I really like your site.
    Here’s a suggestion to make your “Scale_X_Change” and “Scale_Y_Change” subs simpler and get rid of all the “If…”

    First, create a variable :
    Dim arrScale as variant

    then in the 2 subs, replace all the IFs with this :

    Private Sub Scale_X_Change()
    arrScale = Array(1, 2, 5, 10, 20, 50, 100, 200, 500, 1000, 2000, 5000, 10000)
    aX = arrScale(Scale_X.Value – 1)
    With ActiveSheet.ChartObjects(“Chart 1″).Chart
    .Axes(xlCategory).MinimumScale = 0
    .Axes(xlCategory).MaximumScale = aX
    End With
    End Sub

    Continue your good work!

Leave a Reply

Your email address will not be published. Required fields are marked *