Display problems during and after complex macro

I've got a long complex macro. It cycles through 30+ sheets, moves a bunch of data around, activates and sets properties on 300+ charts so they don't move or size, and activates and updates data range for 300+ named chartobjects. It makes use of private subs and private functions. I shut off screen updates at the beginning, and turn it on at the end.

2 Problems w/ questions:

- After it's done, when I switch tabs (worksheets), the charts appear to be giant sized (some still activated) until I zoom out and click around in some open cells. How do I refresh, reset, deactivate, etc. or whatever I need to do so this doesn't happen?

- While it's running, I still see scroll bars moving on right side, so something is happening despite having screen updates off. Why? How can I stop that from happen? Or should I not care?

Thanks much,
Frank

Originally Posted by AlphaFrog

I suspect in your code you have several lines that either .Select or .Activate sheets, charts, ranges...etc. Those are often recorded in a macro, but usually can be eliminated e.g.; instead of something like...

Please Login or Register  to view this content.

You could use...

Please Login or Register  to view this content.

So go through your code and eliminate all .Select and .Activate commands.

I've been able to do that with just about everything except chart objects. What I need to do is cycle through all 300 charts (they are named) and change the "Chart Data Range". The only way I've been able to get it to work is to use this code:

Please Login or Register  to view this content.

Tabname is the worksheet name (31 of them). ChartName is the name of the charts on each sheet - there's ten on each sheet. StartRow, StartCol, EndRow, EndCol are row and column numbers to define range where chart object looks for the data.

Originally Posted by AlphaFrog

Please Login or Register  to view this content.

You ROCK! That worked perfectly and shaved 10 seconds off the execution. I don't use the "With" very much, and I'm unsure of syntax, so every example helps. I have one more subroutine. This one sets properties on each chart. I'd like to see if you wouldn't mind helping do this one with "With" as well.

Please Login or Register  to view this content.

Thanks, Frank

Originally Posted by AlphaFrog

Please Login or Register  to view this content.

I get a runtime error 445
Object doesn't support this action.

Debugger hits on this line:

Please Login or Register  to view this content.

From what I've read, I have to refer to each chartobject individually. If I understand this right (correct me please), ChartObjects is a collection, and it doesn't support that property?

One more thing. I have a shape on a sheet called "START" .... button to allow user to launch the VBA routine. Not sure if that affects anything, but thought I'd let you know to be sure.

Ok... using your stuff as an example, I found some code that seems to work. At least it doesn't crash the compiler. Give this a look and let me know if there's a more elegant way to do it.

Please Login or Register  to view this content.

Originally Posted by AlphaFrog

Well done.

The With statement is most useful if you need to reference the With-object (myChart) multiple times. In this case, you only reference myChart once. So the With statement is extraneous. You could replace the With code block with this one line.

myChart.Placement = xlFreeFloating

Thank you for the help. I'm really inexperienced using that. Information on using "With" in code seems spotty on the web. Not finding many sites that explain the "why" the syntax is the way it is. Using the recorder, you get "With" then a few properties changed, then it ends, then followed by another.

Is there a limit to the number of properties that can be changed inside a "With" statement or is the structure recorded just an artifact of that process?

I hate to trouble you more, but would you mind taking a moment to teach me a little of the "Why" behind how your code works?

In the code below...

(1) What is the purpose of the "." in front of Cells inside the .Range? I mean the obvious answer is "so it works", but I guess I'm looking to understand when they're needed and when not?

(2) Could any of the methods that are members of "Chart" be set the same way? Can more than one be set? If so, would the syntax be similar but just more lines after the With Sheets?

Please Login or Register  to view this content.

Originally Posted by AlphaFrog

There is no limit to the number of properties as long as they are members of the With-object.

You may be overthinking the With code block. I understand it's new to you, but it's fairly simple. Think of it as a temporary variable. Any property or member within the code block that starts with a period refers to the With-obejct. It's a shorthand way of not having to repeatedly type the object every time you want to reference it.

So going back to the first code where I used it

Please Login or Register  to view this content.

That code is exactly the same as this...

Please Login or Register  to view this content.

The With code block is just a more concise way to write the code. That's it.

It doesn't speed up the code. The speed improvement to your original code was eliminating the Select and Activate commands by fully qualifying each reference with a worksheet.

I hope that helps.

Yeah, I'm over thinking it. That helps. Thanks for taking the time to explain. Really appreciate the help with this.