I added a few worksheets to my workbook, now all the macros in that
workbook run atleast 20 times slower. I have my older version saved and
it works fine, so I made another copy and tried moving this one sheet in
and slow macros again. This sheet in question consists of about 6
columns completely filled with formulas containing the indirect
function. It's serves the purpose of displaying the status of my
current jobs as well as a button for the user to list them
alphabetically. Maybe I went about the creation of that sheet wrong. I
don't understand why it would slow each and every macro down on all the
other pages. Is it possible the sheet could be calculating these
indirect references during every macro, I don't understand why it would
do that, but it's the only logical thing. There must be some kind of
code to put in the sheet module to disable the sheet from calculating
until I select the sheet. Or do I need to make a macro that enters
these indirect references into the cells when the sheet is selected,
then clears them upon exit of the sheet? Beats the you know what outa
me.


--
famdamly
------------------------------------------------------------------------
famdamly's Profile: http://www.excelforum.com/member.php...o&userid=29382
View this thread: http://www.excelforum.com/showthread...hreadid=521643

Hi famdamly,

If worksheet calculation is the cause of the slow macro execution you
can speed things up by:
1. Detecting the calculation mode early in the macro code using...

Dim CalcMode
CalcMode = Application.Calculation

2. Then switching to manual calculation mode using...

Application.Calculation = xlCalculationManual


3. Reverting to the initial calculation mode at the end of your macro
code using...

Application.Calculation = CalcMode


Ken Johnson

To add to Ken's comments - if at any point your macro depends on the
worksheet being calculated, and you set calculation to manual, you'll have
problems. However, you can choose to re-calc a specific sheet or the entire
workbook at any point during the macro that you need to.
See this from the VBA help file

Calculate Method
Calculates all open workbooks, a specific worksheet in a workbook, or a
specified range of cells on a worksheet, as shown in the following table.

To calculate Follow this example
All open workbooks - Application.Calculate (or just Calculate)
A specific worksheet - Worksheets(1).Calculate
A specified range - Worksheets(1).Rows(2).Calculate


"famdamly" wrote:


I added a few worksheets to my workbook, now all the macros in that
workbook run atleast 20 times slower. I have my older version saved and
it works fine, so I made another copy and tried moving this one sheet in
and slow macros again. This sheet in question consists of about 6
columns completely filled with formulas containing the indirect
function. It's serves the purpose of displaying the status of my
current jobs as well as a button for the user to list them
alphabetically. Maybe I went about the creation of that sheet wrong. I
don't understand why it would slow each and every macro down on all the
other pages. Is it possible the sheet could be calculating these
indirect references during every macro, I don't understand why it would
do that, but it's the only logical thing. There must be some kind of
code to put in the sheet module to disable the sheet from calculating
until I select the sheet. Or do I need to make a macro that enters
these indirect references into the cells when the sheet is selected,
then clears them upon exit of the sheet? Beats the you know what outa
me.


--
famdamly
------------------------------------------------------------------------
famdamly's Profile: http://www.excelforum.com/member.php...o&userid=29382
View this thread: http://www.excelforum.com/showthread...hreadid=521643

Thanks to you both for your responses.

This raises a new question, is it a pretty common technique to turn
off calculation during macros.?I already disable screen updating on
most of my macros.

It just donned on me that I might want to write a macro that first
disables then at the end enables these things. Then sandwich my new
macro in the middle. Can I record a macro in the middle of a macro,
like if my cursor is in a code module between the disable/enable can I
just start recording from there, or would I just have to copy and paste
to accomplish that would probably be just as easy anyway. I'm just
starting to get an understanding of vba. I haven't done any programming
since basic in the eighties, it really seemed greek at first.


--
famdamly
------------------------------------------------------------------------
famdamly's Profile: http://www.excelforum.com/member.php...o&userid=29382
View this thread: http://www.excelforum.com/showthread...hreadid=521643

Hi famdamly,
Did our suggestion speed things up?
It is a common technique, but as Duke said you need to be sure that its
not going to interfere with the macro's function, which will be the
case if the macro changes cell values then later uses calculated cell
values that depend on the changed cells.
I don't think you would be able to set things up so that a macro could
be recorded while a macro is running. Sounds a little like (with
emphasis on little) writing code that writes code, which is possible
using the Visual Basic Integrated Design Environment or VBIDE. I know
very little about its use other than it is used to speed up code
writing. Maybe some day I'll drag it out of the too hard basket and
give it a go.
John Green's Excel 2000 VBA Programmer's Reference has a chapter
"Programming the VBE" if you are interested. Plenty of Greek there!:-)

Ken Johnson

I've been playing around with Frontpage today, that's what I'm building
my web site with. I know pretty lame huh.lol

I will give your tips a try tommorrow after work, it's late.

You must have misunderstood my question about starting the macro
recorder while in break mode I guess. I hope that clarifies it better.

I get the feeling there is a wysiwyg spreadsheet program around the
corner that would make creating applications alot easier. It seems due,
what year is it anyway?:)


--
famdamly
------------------------------------------------------------------------
famdamly's Profile: http://www.excelforum.com/member.php...o&userid=29382
View this thread: http://www.excelforum.com/showthread...hreadid=521643