Are objects slow in VBA?

  • Thread starter Thread starter Charles
  • Start date Start date
C

Charles

Hello

I just have a general question: in your opinion, are objects in VBA
slower than using directly arrays and subs? I mean you necessarily
have a few more declarations to do, etc in the object. But that aside,
does the management of objects by VBA significantly reduces the speed
of execution of the code?

I have made a try, and I am a bit disappointed by the exec time. Could
be my fault, but just asking in case...

thanks!
Charles
 
In my experience object ore not distinctly slower than regular procedural
programming. As you say they do come with a bit more overhead but not enough
to cause great concern...
 
It depends what you mean by object. If you mean manipulating a range one
cell at a time, yes, this might be obviously slower than manipulating the
entire range at once, or than getting the values into VBA, doing
calculations, and dumping the results back into the sheet.

- Jon
 
Sorry, I didn't mean Excel object. I understood the Excel-VBA link is
extremely slow. I was thinking to VBA-defined objects (using "class").
I left everything on option explicit, "long" used for integers. I
think that I must simply do an unecessary number of loops.

Also, I am building a model aimed to do monte carlo simulations. So I
was looking at how long it took to run 10,000 times the model (which
implies about 50 arrays of dim ~40*1, and some loops to populate/
aggregate them). I guess that with that sort of dimensions, I can't
expect it to take less than a minute.

Charles
 
I've always felt that working within VBA was *not* the rate limiting part of
most of my applications, even the intensive data manipulation ones. But 10k
simulations requiring that many arrays of that size should be expected to
take more than a couple seconds.

- Jon
 
Back
Top