Excel formatting takes too long virus/corrupt files???

[griselda]

Hi
I have an excel form/file which I open from an Excel Macro.
The excel macro does some formatting etc to the form/file while it is being opened.
Everything has been running fine for over a year.

However now for some unknown reason, there is some huge waiting time on some cells when I try to format them (eg changing to bold/unbold, right alignment, etc)
This only happens to some of the cells, not on all.

Here is an example:
QN.Range("C" & the_row + 1 & ":L" & the_row + 1).Font.Bold = False

This takes some 25 seconds to do !

The funny thing is that if I open the Excel form/file directly (not via the macro), and I try to format the cells, everything works perfectly.

So it all points to the macro which opens the excel form/file....might be doing something to the cells while opening the form...but what ?
What could I do via code to make the formatting take 25 seconds to process !!!????

Note there are no formulas on any of the cells, they are "free text" cells.

When macro is being used on other computers, everything runs fine...this only happens on my laptop (which happens to be the development machine!)

To me it looks more like a corrupt file, virus ????
Amazingly the macro I run is huge (hundreds of routines) and there are not problems anywhere on the code (apart from the formatting I mentioned)
So I don't think the macro file would be corrupted, because I don't see problems anywhere else on the macro/code.

Has anyone had this problem before and, most importantly, how can I fix it ?

Look forward to your comments

 

[griselda]

further more, note that I have just discovered that, if I am connected to local network (at work), everything runs fine....
It is when running offline that I get the problem !!!
Weird

 

[xenou]

Connected as in "wireless" connection?

 

[griselda]

Hi, yes, at home I have wireless on, although the Excel application is running locally in my laptop (no connection needed to office server)

In the office I am connected via wire.

 

[xenou]

I have macros that sometimes take 1 seconds, sometimes take 30 seconds (usually, the former). I've always attributed the unusual slow runs to networking, or to the O/S using up resources greedily - who knows, really? Typically, I'd take care to do as much housekeeping as possible (defrag, check disk for errors, run cleanup, whatever you can do - make sure you have enough memory too - personally I think 512MB just doesn't cut it anymore, so if that's all you have, or less, I'd add a memory card). erik.van.geit has a post with REBUILD in the title that suggests a plan for creating a clean file when development is complete - in essence, move things to clean workbook, avoiding copying entire sheets and so on. With a move to 2007 it may help to "rebuild" in 2007 what you have in 2003 - there's always the possibility of gum in the works somewhere. Perhaps even just that one module can be deleted and re-inserted, pasting in the code and re-compiling it. It's rather obscure what's happening.

Rebuild your workbook

 

[griselda]

Hi Xenou and thanks for your input.

All my macros run very fast o my laptop (I ave 4Gb RAM) and the application I am having problems with has hundreds of routines and thousands of code line...all works fast and perfect....(except the problem I mentioned)
Usually any routines run within 1-3 seconds max, and does the same in some 140 PC's using the application.
My laptop is quite new and powerful so I have no major performance problems with it.

Surely I do defrag often etc, and I have run the application on its own (no other applications are opened) to see if the problem is there, but to no avail.
I restart the laptop a couple of times...but the problem is there.
Then for no reason, everything works fine again..until the next time.
Very weird.
I was hoping to hear that there is some bug/virus or something which causes this problem....

By the way, thank you for the link to rebuild, most appreciated.

 

[xenou]

Hmmm. I can't think of a thing then - sounds like everything's right on, except this one little problem. It doesn't sound like a known issue that I've heard of. Sometimes it is the formatting that does slow you down surprisingly much, though. I'd try workarounds (basically, the "if at first you don't succeed try try again" approach to programming success).

With Formatting, I've often used a "template" from a hidden sheet to copy the formatting - not sure if its of interest to you. It does have the advantage that no matter how much or how complicated your formatting needs to be, the code all boils down to a simply copy formatting command. Also, if you want to change it, you just change it on the template and there's no code to write. It does, however, mean that my workbooks have hidden (or "very hidden") sheets to hold the formatted ranges to be copied from. I picked this up in a thread years ago where it was described as a "mirror" sheet (but if you're formatting a blank sheet/workbook from scratch, on the fly, its not as helpful). Another useful trick is using styles to encapsulate formatting that can be applied all at a single go.

Just some stupid ideas off the top of my head - I feel your pain. At least its not affecting your users.

ξ

 

[griselda]

thanks for your ideas.

I wish I could do what you mention (have the formatting done on the template itself) however depending on the user's input, the formatting changes.
The templates have around 200 rows, which users add data to it and, based on their selections, the code applies one format or another.
Also there is some "stripping/formatting" done by code as well, which occurs when the user saves the template...and this formatting/stripping is done based on user's input.
So unfortunately using a standard template will not work in this case.

Like I said, all users (140 of them) are ok, I am the only one with the problem...

Lucky me.

Anyway, I'll see if I can get a new laptop....it's a good excuse...

thanks again for your input