PDA

View Full Version : Impact on Performance of VBA based applications with VBE open!



agarwaldvk
09-03-2008, 04:44 PM
Hi Everybody

I am led to believe that there is a significant adverse impact on the performance of the VBA program if the VBE is open.

I have various VBA based Excel applications that run for reasonably long period of time say anything between 1 and 8 hours, hence performance improvement is very significant. If something as simple as keeping the VBE closed can provide performance improvement to any degree - I would certainly love that!

Does anyone have any suggestions as to whether or not the VBE should be closed (when a VBA based application is running) or is it only ones personal preference to have the VBE open or closed when running VBA programs and wouldn't have any performance improvement one way or the other?

Thanks in advance.


Best regards


Deepak Agarwal

mdmackillop
09-04-2008, 12:29 AM
In my experience it appears to slow things down as it shows VBE modules in other workbooks as they are opened. This must use up some resources. I can't see any good reason to have the VBE open in any case.

Why not add a simple timer function to test this out.

Bob Phillips
09-04-2008, 12:44 AM
In 2007 there is a definite advantage to having the VBE open.

If you open a macro enabled workbook without the VBE open , and it is not signed or in a trusted location, macros are not (yet) enabled, but you get an options button in the formula bar. If you have the VBE open, you get the old familiar dialog.

Why MS ever introduced this naff change is beyond me (but unfortunately, I say that about a lot of 2007, they have tinkered just for the sake of it in so many cases, fixing things that ain't broke!).

agarwaldvk
09-04-2008, 03:17 PM
Hi Xld

In 2007 I am aware that there is that problem that you identified, that's ok. But even there if all the right check boxes have been ticked, then if you have a workbook that has a vba based application that needs to be run on the click of a button, then do you think it would have an impact on the processing time.

I am not attaching any dataset to this because this is independent of any dataset - this query is irrespective of the dataset - that it is working on!

Of course, as mdmackillop suggested I can certainly run the timer and see but then I thought may be somebody would have done this beforehand. Anyow!


Best regards


Deepak

Bob Phillips
09-04-2008, 03:52 PM
I personally have not come across this as a general view in any way.