Home / Excel / Whirlwind this week

Whirlwind this week

WhirlwindIt’s been busy the last week and a half so I haven’t done much posting. Really, haven’t done much of anything but work.

We did our training for the new report file and next Monday we start our trial run. The whole month of December will be testing, debugging, getting familiar, and finalizing process. Oh, it’s gonna be fun!

While my boss knew what was in the file, it was only in the last few days that she actually had time to get her hands on it, which I am glad. She finds things that need fixing only because of how she approaches things. One item I forgot was to apply protection on all of the files so folks couldn’t overwrite the formulas. With a least 45 files and 3 tabs requiring protection but still maintaining certain cells to be unlocked, I knew I had to create a macro to do the protection piece as well as do other actions. There was no way I was going to go through all of those actions and not forget to do something.

Thank goodness I did the macros (although we had a little bit of an issue that came out of the macro) because they really made a difference in getting things done. I started off with a macro recorder to get the codes down and then went in and cleaned them up for general use, including putting comments on what the codes did.

I’m definitely going to continue working on macros so that I get to the point where I can create them on the fly. They are making a difference.

About the issue I alluded to earlier: I had created a macro that would lock and hide the formulas except for a few selected cells. Then the 3 sheets would be protected. The problem was the size of the file nearly doubled from 46K kb to 82K kb! Not good. I investigated a little and found that doing the locking, hiding and protecting by hand kept the file size to 46K kb. So I must have done something in the macro to increase the size. What I ended up doing was to first create a macro to back off from the hiding, locking and protecting. That action brought the size back down to 46K kb. Then I reapplied the locking/hiding/protecting and the file stayed at 46K kb. I know I changed a certain area selection from I10:IV65000 to I10:CD1048576. That’s the only thing that I changed. Hmmmm.

Leave a Reply

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

Top