can somebody pls. look @ this idea & tell me if it's possible?

[Susan]

thanks in advance..........

i have several large workbooks. a couple have very large &
complicated macros associated with them, and therefore take a long
time to open & close.

can i.........
have a much smaller macro in the data workbook, that when a button to
"run" a macro is clicked, will simply open up a dummy workbook & then
run the code from there????? at the end of the session, when the data
workbook is saved & closed, the dummy workbook would also close.
almost all of the macro coding would be stored in the dummy workbook.

i'm willing to try to figure it out myself, but if it's not possible
then i'd like to save myself massive headaches.

(i'm assuming that the userforms would have to be in the data
workbook, but i could direct all the coding from it to the dummy
workbook - if it's possible.
thanks!
susan

 

[Guest]

Why would you want to run the code in a Dummy workbook? What advantage do you
expect to get doing it that way? You could create an addin and run the code
in the addin...

 

[Guest]

Hi Susan,
The UserForm is not acually a member of Workbooks. If you open the Object
Browser and type UserForm in the search box you will see it is member of
MSForms or VBA>Global>UserForm. So you can call them from a second workbook
or "dummy" as long as the workbook they reside in is open. The slow open and
close is not necessarily because your code and data reside in the same
workbook, but could be related to whether you have a lot of formulas that
calculate each time you open or close. In simpler terms, it is the content
design rather than the content itself that affects the opening and closing
times.

 

[Guest]

Hi,

I have to agree with these guys, an Add-In would be preferable to a dummy
workbook, and then your code would be available to all workbooks you use.
However, if you only require that code for one specific workbook then you
might as well keep it there. If anything, having to open two workbooks would
make things slower, although closing might be slightly faster as only one
workbook would need to be saved.

If the workbook is taking a ridiculous amount of time to open then I'd look
at trying to tidy the data, or reducing the code if possible. One thing that
can cause lengthy delays is the calculation of formulae when you open a
workbook. Say, for instance, you have a formula in one column and it spans
thousands of rows, but it doesn't need recalculating once you have the
results, then you can copy the column and use paste special to paste the
values over the same area. This is something I often do to keep calculations
to a minimum.

Sean.

 

[Susan]

thanks all you guys for your replys.
sorry i didn't notice there WERE replies!
i am thinking about trying an add-in.
susan

 

[Susan]

JLGWhiz -
ahhh. so it's not the macros that are slowing it down, it's the seven
large worksheets with lots of calculations that are slowing it down.
thanks for explaining it that way.

susan

 

[Susan]

sean - thanks for your ideas, too....... not sure how to implement
them because this is an on-going project workbook that i can't paste
values in most of the places (although already have in some of the
places).
yes, i only require the code for that one specific workbook........
i'm going to export the macros first & see if that changes the opening
time.

susan