It's the "without looking at the code" part that makes it difficult !!!
There are LOTS of ways to speed up loops, but everything
doesn't always apply!
first of all, what version of Excel are you using?
Excel2007 has a way of dropping to a lower priority after several seconds,
thus causing long running macros to run even longer!
To elaborate on what Ayush mentioned:
"When you're looking for something, why is it always in the last place you
look?"
because once you find it, you stop looking!
In Loops, sometimes we forget to stop looking once we find it.
Something else.
Let's say you're loading an array with data.
Then, you're using several loops to look through the array.
This can be very cpu-intensive.
In true "databases", it is possible to create an index.
then, your search uses the index, the index returns the record number
(or numbers) that matches your search criteria.
what i have done is created an "index" using the Dictionary Object.
(or several Objects)
I have a spreadsheet of 91,400 rows and 74 columns.
the combination the first two columns constitute the unique "Record"
(they are Requisition # and line item#)
see this:
Sub timetest()
Dim tStart, tStop, Dict_Data, R, nRows, TGORitem, stat
tStart = Now()
Set Dict_Data = CreateObject("Scripting.Dictionary")
stat = Dict_Data.RemoveAll
nRows =
Application.WorksheetFunction.CountA(Sheets("data").Range("A1:a100000"))
For R = 2 To nRows
TGORitem = Sheets("Data").Cells(R, 1).Value & "." &
Sheets("Data").Cells(R, 2).Value
If (Dict_Data.exists(TGORitem)) Then
Dict_Data.Item(TGORitem) = Dict_Data.Item(TGORitem) & "," & R
Else
Dict_Data.Add TGORitem, R
End If
Next R
tStop = Now()
MsgBox DateDiff("s", tStart, tStop) & Chr(13) & tStart & Chr(13) & tStop
End Sub
It takes approximately 3 seconds to load the dictionary.
but if I wanted to find the record(s) for Requision: 3000819624.10
it doesn't matter if it is the first record, or in this case record #90162
it finds it instantaneously.
And I can have multiple "custom" indexes.
for instance: Dict_Create could be the date a requisition was created.
The value of the index would be a comma-separated list of row numbers.
I could instead make the index the year-month it was created, or even the
Quarter
or simply the year.
As you can imagine, putting a =VLookup formula in 90,000 rows would cause the
file to calculate VERY slowly.
But instead, I can create a dictionary index and do the VLookup quickly.
if you'd like to send me your code, I would be glad to look it over and see if
I see
any potentials for improvement!!!
Paul
________________________________
From: scubagirl <jill.brya...@gmail.com>
To: MS EXCEL AND VBA MACROS <excel-macros@googlegroups.com>
Sent: Thu, March 18, 2010 10:00:34 PM
Subject: $$Excel-Macros$$ macro efficiency
I have a fairly large VBA project for work - it runs a Monte Carlo
simulation on some data that is grabbed from MS Project. It takes
longer than I'd like to run - about 4 minutes for 1000 iterations, not
including writing out to Excel. I have done everything I can think of
to make it efficient: using For Each loops, declaring variables by
type, using early binding and Option Explicit...
I know that it's not going to be super-speedy given the nature of the
project (lots of nested loops, 20x1000 arrays, etc.), but I feel like
it should run faster than it does. I've tried timing sections of it,
and with a small number of iterations, the speed is not always bad
(subsections can take .3 and 1.1 seconds to run), but as I increase
the number of iterations, it always seems to run at the worst case
(e.g., the aforementioned subsections always take 1.1 seconds to run).
Without looking at the code, does anyone have any suggestions for
things I can look into to speed up runtime?
--
----------------------------------------------------------------------------------
Some important links for excel users:
1. Follow us on TWITTER for tips tricks and links :
http://twitter.com/exceldailytip
2. Join our Facebook Group @ http://www.facebook.com/group.php?gid=287779555678
3. Excel tutorials at http://www.excel-macros.blogspot.com
4. Learn VBA Macros at http://www.quickvba.blogspot.com
5. Excel Tips and Tricks at http://exceldailytip.blogspot.com
To post to this group, send email to excel-macros@googlegroups.com
<><><><><><><><><><><><><><><><><><><><><><>
HELP US GROW !!
We reach over 6,800 subscribers worldwide and receive many nice notes about the
learning and support from the group.Let friends and co-workers know they can
subscribe to group at http://groups.google.com/group/excel-macros/subscribe
To unsubscribe from this group, send email to
excel-macros+unsubscribegooglegroups.com or reply to this email with the words
"REMOVE ME" as the subject.
--
----------------------------------------------------------------------------------
Some important links for excel users:
1. Follow us on TWITTER for tips tricks and links :
http://twitter.com/exceldailytip
2. Join our Facebook Group @ http://www.facebook.com/group.php?gid=287779555678
3. Excel tutorials at http://www.excel-macros.blogspot.com
4. Learn VBA Macros at http://www.quickvba.blogspot.com
5. Excel Tips and Tricks at http://exceldailytip.blogspot.com
To post to this group, send email to excel-macros@googlegroups.com
<><><><><><><><><><><><><><><><><><><><><><>
HELP US GROW !!
We reach over 6,800 subscribers worldwide and receive many nice notes about the
learning and support from the group.Let friends and co-workers know they can
subscribe to group at http://groups.google.com/group/excel-macros/subscribe
To unsubscribe from this group, send email to
excel-macros+unsubscribegooglegroups.com or reply to this email with the words
"REMOVE ME" as the subject.
