Re: $$Excel-Macros$$ macro efficiency
Poor calculation speed affects productivity and increases user errors. Studies have shown that a user's productivity and ability to focus on a task deteriorate as response time lengthens. Excel has two main calculation modes, which let you control when calculation occurs: * Automatic calculation Formulas are automatically recalculated whenever you make a change. * Manual calculation Formulas are only recalculated when you request it (for example, by pressing F9). ''' If you write the code for VBA it run slow.There are many cases 1.processor 2.ram 3.loop in code ''' So if it is not possible to upgrade your pc,Then try to switch on manual calculation. You have to first ensure that there is no any bug,error. ''' Sub GoToManual() Dim xlCalc As XlCalculation xlCalc = Application.Calculation Application.Calculation = xlCalculationManual On Error GoTo CalcBack 'YOUR CODE Application.Calculation = xlCalc Exit Sub CalcBack: Application.Calculation = xlCalc End Sub ''' Hope it will Help Happy to Help :) Shyam On Tue, Mar 23, 2010 at 2:56 AM, scubagirl jill.brya...@gmail.com wrote: Thanks for your suggestions/help. I checked to make sure that all of the loops use Exit For when appropriate. I am using Excel 2007 which *could* be causing some of the slowdown, but Excel only really comes in at the end for result output, it could explain why my speed tests are so slow (I've been outputting the time for some functions to complete to Excel). The following function seems to be taking up a good chunk of the time: 'create a list of tasks that are eligible to run Private Function GetEligibleTasks() As Collection Dim eligible_tasks As New Collection Dim t As cTask For Each t In tasklist 'make sure the task hasn't been started yet If t.Status = 0 Then t.StartDate = tracker.CurrentDate 'make sure it can be started If t.ReadyToStart() Then eligible_tasks.Add t eligible_tasks.item(eligible_tasks.count).EligibleTaskIndex = eligible_tasks.count End If End If Next t Set GetEligibleTasks = eligible_tasks End Function 'determine if a task is ready to start by checking 'whether its predecessors have completed Public Function ReadyToStart() As Boolean Dim pred As Tasks Dim j As Integer Dim start As Date Dim p As task Set pred = ActiveProject.Tasks(Index).PredecessorTasks 'get a list of the preceeding tasks 'if the task has no predecessors, it's ready to run If pred.count = 0 Then ReadyToStart = True Exit Function End If For Each p In pred Dim test As Date test = p.Finish 'if a predecessor hasn't been completed, this task isn't ready to run If tasklist.item(p.ID).Status 2 Then ReadyToStart = False Exit Function Else ReadyToStart = True End If Next End Function In this case tasklist is a Collection. To me, it doesn't look like this could get much more efficient - any suggestions? (I can't post the whole project because it's company protected information) -- -- 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. -- Thanks Regards Shyam Software Engineer
Re: $$Excel-Macros$$ macro efficiency
Thanks for your suggestions/help. I checked to make sure that all of the loops use Exit For when appropriate. I am using Excel 2007 which *could* be causing some of the slowdown, but Excel only really comes in at the end for result output, it could explain why my speed tests are so slow (I've been outputting the time for some functions to complete to Excel). The following function seems to be taking up a good chunk of the time: 'create a list of tasks that are eligible to run Private Function GetEligibleTasks() As Collection Dim eligible_tasks As New Collection Dim t As cTask For Each t In tasklist 'make sure the task hasn't been started yet If t.Status = 0 Then t.StartDate = tracker.CurrentDate 'make sure it can be started If t.ReadyToStart() Then eligible_tasks.Add t eligible_tasks.item(eligible_tasks.count).EligibleTaskIndex = eligible_tasks.count End If End If Next t Set GetEligibleTasks = eligible_tasks End Function 'determine if a task is ready to start by checking 'whether its predecessors have completed Public Function ReadyToStart() As Boolean Dim pred As Tasks Dim j As Integer Dim start As Date Dim p As task Set pred = ActiveProject.Tasks(Index).PredecessorTasks 'get a list of the preceeding tasks 'if the task has no predecessors, it's ready to run If pred.count = 0 Then ReadyToStart = True Exit Function End If For Each p In pred Dim test As Date test = p.Finish 'if a predecessor hasn't been completed, this task isn't ready to run If tasklist.item(p.ID).Status 2 Then ReadyToStart = False Exit Function Else ReadyToStart = True End If Next End Function In this case tasklist is a Collection. To me, it doesn't look like this could get much more efficient - any suggestions? (I can't post the whole project because it's company protected information) -- -- 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.
Re: $$Excel-Macros$$ macro efficiency
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:a10)) 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
$$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.
