Speed up VBA - using class objects

[chris_culley]

Hi there,

I've read quite a few of the "How do I speed up my VBA" posts, and
they've contained a few useful tips that I'll be using... a more
specific question...

I'm working on a monte carlo simulator whose functionality is written
pretty much entirely in a single loooong method. I've knocked a couple
of hours of it's runngin time already, and am at the point of
clutching at straws to get rid of a few more minutes...

I heard from someone that splitting my code up and approaching it from
a more OO perspective, using excel user defined classes might speed it
up. Has anyone got an opinion on this?

Any other ideas, beyond disabling screen updating would be greatfully
recieved

Cheers

Chris

 

[Jim Thomlinson]

From my experience OO is not going to speed things up materially. It could
actually slow things down.

From the stand point of "One Big Procedure", oddly enough that will normally
be faster than a bunch of small procedures. Especialy if you are calling the
sub procedures in a loop. This is because each time the procedure is called
memory needs to be created on the stack and then destroyed when it is
finished. that does not take a significant amount of time but if you have it
in a loop that is called thousands of times it starts to add up.

The biggest things to speed up code are:
Use good coding practices such as declaring all of your variables (and not
as variants). Don't use New in a dim statement, ...
Turn off screenUpdating, set calculation to manual and disable events.
Each of these depends on what you are doing as to whether they will make any
difference at all.
Avoid using Select in your code. Using workbook, worksheet and range
objects you do not need to use selects 99.9% of the time.
There are a bunch of very marginal gains to be had by doing things like
declaring your variables as Long instead of Integer (you system actually
converts the int to a long and back again sincy you work in 32 bit) and
having your loops count down to 0 instead of up to some arbitrary number
(computers recognize zero faster than any other number so the comparison is
easier).
Finally you can convert your code to a VB6 bas module if you have a copy
of VB6 and then access the compiled code which will run faster than the
equivalent VBA code.

Note that some of those are more difficult than others and some show only
marginal increases in speed. If you want more specific help you will need to
post your code.

 

[Bob Phillips]

Classes will most likely slow things down, not speed them up. The value of
classes is rarely to get a performance boost.

Most savings will be achieved in the design, look at the overall objectives
and see if the approach can be modified to improve the performance, maybe
get Excel to do more, use Excel functionality from within VBA, etc.

--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[RB Smissaert]

and having your loops count down to 0 instead of up to some arbitrary

number

That is an interesting one, never thought of that.
Have you done some timings to see the difference?

RBS

 

[RB Smissaert]

A simple test doesn't show any difference:

Option Explicit
Private Declare Function timeGetTime Lib "winmm.dll" () As Long
Private lStartTime As Long

Sub StartSW()
lStartTime = timeGetTime()
End Sub

Sub StopSW(Optional ByRef strMessage As Variant = "")
MsgBox "Done in " & timeGetTime() - lStartTime & " msecs", , strMessage
End Sub

Sub test()

Dim i As Long
Dim n As Long
Dim arr(0 To 1000000) As Long

StartSW
For i = 0 To 1000000
n = arr(i)
Next i
StopSW "counting up"

StartSW
For i = 1000000 To 0 Step -1
n = arr(i)
Next i
StopSW "counting down to zero"

End Sub


RBS

 

[chris_culley]

Thanks guys - some useful replys... will investigate the bas module -
that sounds interesting..

Cheers

Chris