Optimizing Macro To Run Faster - Timer Code
May 3, 2007
Here is exactly what was described:
A good place to start is to add some "timer code" in your macro. Add a small routine that saves a time value, and another routine that compares that saved value to the current time and displays the difference. At the beginning of a section of code you want to analyze, you call the first routine (which saves the start time), and then at the end of the section of code you call the second routine. In that way, you can determine which portions of your code are taking the longest time to execute. These are the code sections you then focus on, so you can figure out what they are doing that is taking so long.
This sounds like a great tool for optimization...
View 9 Replies
ADVERTISEMENT
Sep 7, 2006
I am looking for a way to measure how long it takes to complete a sub routine in VBA. I have code which posts data to matlab and then calls it back. What I need to know is how long does it take to complete each subroutine.
create a code which will measure this? I need to measure the time taken in 100ths of a second.
Is there any simple code to complete this? I am pasting a sample of my code which is using Matlab as a COM server.
Dim Matlab As Object
Dim MReal(10, 0) As Double
Dim i As Integer
Dim j As Integer
Dim MImag() As Double
Dim value As Double
Dim RealValue As Double
I am sure I woould need to declare the timer but I do not know where and I am not sure of the syntax.
View 9 Replies
View Related
Apr 27, 2014
The idea is that I start out with a number of XML files, which I'm trying to manipulate to calculate descriptives and make graphs of in Excel.
So I've made a large "script" in VBA to accomplish these goals, which works, but I'm trying to optimize the code step by step.The overall process that I want to achieve, consists of :
1) Open the XML in Excel as an XML Table
2) Use the filters in the top row of that XML Table to select the data that I want to use
3) The resulting selection consists of data in 6 columns of which I only wish to select column 2, 5 and 6
4) I only wish to select the data in the table of column 2, 5 and 6 (without the header and only the cells in the table) and copy
5) Last step is to paste this data in Sheet "Calculations"
The step I'm working on right now, is step 3-4. My current code is: [Code] ......
This does the job, but I'm trying to optimize it, since I'm handling tons of XML files and I'm performing this copy function a number of times per XML file.
This copies the data of all the columns in the table for the cases that meet the selected criteria, but I'd like to make an even stricter selection of only column 2, 5 and 6 to not waste space and processing power.
So I tried to select the data from the 3 columns and combine them with the Union function, to copy them as one array to the sheet Calculations.
Optimized a similar VBA code to: [Code] ........
But I'm having issues with getting the right selection method and when I run the macro, I get the error message that the Copy and Paste area aren't the same size.
I tried to alter my initial code to something in the line of : [Code] ..........
But I get error messages 1004 : Method 'Range' of Object '_Global' failed.
I've also tried [Code] ........
But this seems to select the whole column, including the header and tons of rows even after the table ended.
View 2 Replies
View Related
Sep 5, 2008
I have the following code kindly supplied to me by someone else and I'd like to know if anyone can suggest any ways to speed up the running of it. It currently takes about 4 minutes to run (there are about 5000 cells to loop through)
Basically it looks for a value in Col C - works out the number of rows to fill up by searching a range in Col D - then copies value in Col C up by that number of rows.
View 10 Replies
View Related
May 30, 2009
way to bring more efficiency in this code.
I need to run this code on 100,000 rows every week and this takes hours to finish.
View 14 Replies
View Related
Dec 27, 2008
I have the following section of code and i am trying to make it faster. Currently when this procedure runs the screen "changes" durings its execution. Now i know i can "turn off screen updating" and this will increase speed but are there commands in the code which will prevent the screen from updating. I know activating or selecting things causes the screen to change. Also, any inputs to make this faster/better would be greatly appreciated.
View 14 Replies
View Related
Nov 21, 2006
I have the following code performing SumIf and searching roughly 9,000 rows of data. Calculation takes roughly 15 seconds on the high side.
Bonus question from a rookie - This code runs well in Sheet2, but errors out on the SumIf's when placed in a module or userform. What needs to be changed for it to work outside of Sheet2?
Private Sub DistNeg_Click()
Dim BotRow As Long
Application.ScreenUpdating = False
Worksheets("Sheet2").Range("A4:E65536").ClearContents
'Copy current selected Distributor names to sheet2
Worksheets(1).Range("DIST1").Copy Destination:=Worksheets(2).Range("A4")
'Define the bottom row of distributors in this list.
BotRow = Worksheets(1).Range("DIST1").Rows.Count + 3
'Sum for PY, CY and $CHG sales. Create percentage Chg column.
Worksheets(2).Range(Cells(4, 2), Cells(BotRow, 2)).FormulaR1C1 = "=SUMIF(Sheet1!
View 9 Replies
View Related
May 3, 2009
I am only looping it through about 4 thousand records, looking for match and dups and moving it over to the other sheet, so why is my code running so slow?
I have run other programs where the records are in 40 thousand and it doesn't take as long as it is right now. Is there a way to make code run faster?
View 9 Replies
View Related
Oct 30, 2011
Operating System: Windows XP, Excel version: 2003
Aim: To create a Macro to hide all rows where the date in column D is before today. Column D has about 600 rows.
Current solution:
Code:
Sub Hide_Old2()
'Worksheet name
With Worksheets("Schedule")
'set start of date range
Set rngStart = .Range("D2")
'find end of date range
[Code] .....
The problem with this solution is its speed, or lack thereof. It causes the screen to hang and flicker while it cylces through. Is there some way to create a range based on the date and hide the range? or another solution?
Note: Autofilter is not an option, as the spreadsheet with the dates needs to be kept simple for other stakholders and the macro is being run from another sheet.
View 3 Replies
View Related
Nov 12, 2013
I am trying to insert a timer into a column cell range that will countdown in hours, mins, and sec, when I enter the time. example: when I enter the "time in hr,mins,sec" it will start to countdown to Zero automatically, when I hit the enter key.
View 3 Replies
View Related
Feb 18, 2008
I have a macro which is able to run very fast in Excel 2000. Almost 5s. When i run it in Excel 2003, it takes almost 4 to 5 mins to complete. Is there any patch in 2003 i need to run the macro smoothly?
View 4 Replies
View Related
Jun 2, 2014
The only change I made was to change the "Sheet1" to "Journal" to match the worksheet name in my workbook.
As you close and reopen the workbook, the timer should start with =NOW() in A1 (formatted as "HH:MM:SS") and count up with the current time until you close the workbook.
I use Excel 2010, could that be the problem, that I copied a VBA code for an older version of Excel???
Question: What exactly is a regular module, do I use Module 1 for the first portion of the code or place it in ThisWorkbook?
AUTO TIME UPDATE VBA
PLACE IN REGULAR MODULE
[Code] ....
View 7 Replies
View Related
Nov 18, 2009
I am transferring 10000 rows from 1 workbook to another..
It takes place smoothly for the first 30000 rows, but after that on the 'select' line it gives me an overflow error.
The code that I am using is as following: .....
View 10 Replies
View Related
Aug 8, 2007
To speed up copy and paste operations in a macro, I've heard that one can replace the following:
Sheet1.Range("A1:A200").Copy
Sheet2.Range("B1").pasteSpecial
with
Sheet2.Range("B1:B200").Value= Sheet1.Range("A1:A200").Value
The second code should run faster as the clipboard is not used to store the copied info.
When I tried this, though, I didn't notice any improvement in macro running speed.
View 9 Replies
View Related
Jun 22, 2007
I am trying to write code which takes a reading off of a device every .005 seconds.
When I write the code as follows to take the reading every .004 seconds things work well.
PauseTime=5
Start = Timer
Oldtime=Timer
Do While Timer < Start + PauseTime
If Timer > Oldtime + .004 Then
Oldtime = Timer
Read(count) = Orbit.Networks(0).Modules(0).ReadCurrent
count = count + 1
End If
Loop
Finish = Timer
If however in the above code I only change .004 to .005 instead of taking readings every .005 seconds, I end up with readings roughly .015 seconds apart (approx)
Is there something unstable about the timer function to do this? Is there a different way to get a more stable result?
View 9 Replies
View Related
Sep 21, 2012
I want the macro to run 20 seconds after data has been input to the worksheet. See below codes
Private Sub Worksheet_Change(ByVal Target As Excel.Range)
ActiveSheet.Unprotect
For Each cell In Range(Target.Address)
If cell.Value "" Then
cell.Locked = True
[Code]....
View 7 Replies
View Related
Mar 18, 2014
I have the Macro
[Code]....
That starts a timer macro (Recalc) in my excel sheet when the workbook is opened, my issue is that when the work book closes it continues to run.
Will the following macro fix this problem, or is there some command like Workbook_Open() but for closing?
[Code] ....
View 1 Replies
View Related
Apr 14, 2014
I have a macro that runs when a spreadsheet is opened however i sometimes need it not to run. Rather then furiously hit escape i was hoping to get a dialog box that would prompt yes/no to run the macro and if an option was not chosen after a few second then to run the macro anyway (so basically only by clicking "no" would you stop the macro from running).
I found this code here that gives me the yes/no message box and the 5 second time-out to get rid of the message box, however if the message times out it doesn't run the macro. what i need to tweak to make it do so if no input comes from the user? [URL]
View 2 Replies
View Related
Jun 2, 2013
Is there a way to have a count up timer display in a cell while a macro is running, and then stop when the macro stops
View 5 Replies
View Related
Aug 2, 2006
What I am trying to do is have a macro that do a comparison between 2 cells every 100 milliseconds. One of the cell is constant updating from RTD (Real Time Data). And if its false, to nothing. However if true, I would want it to run some codes and exit the macro/procedure. I also need some way of stopping the macro at any time.
View 6 Replies
View Related
Jun 3, 2008
I have made this vba to go out and look at 2 different locations which contain simular folder names.
The script then writes the folder names in Column A, followed by the Size of both the folders from the 2 locations. The script is comparing the second location folder name to what the first process put in Column A so the folder names will be aligned with the file sizes. It is also converting the file sizes from bytes to the most appropriate one.
The problem I am having here, is once I put the second part in for the second folder, the script is either taking forver (More than an hour) or is freezing. So I am not sure if I put it in a loop somewhere or what exactly is happening. I am just wondering if I can get some help troubleshooting and optimizing this code....
View 9 Replies
View Related
Feb 17, 2014
I have a worksheet that has macro events attached to command buttons. When a ‘Start’ command button is clicked, a timer begins counting in seconds and displays the value in cell B3 until a ‘Stop’ command button is clicked. The event works flawlessly as long as I don’t open up another workbook (to continue working in Excel).
When the second workbook is open…then time stops working in the workbook where the macro was created…and starts displaying the ‘timer count in seconds’ in the new active workbook--which overwrites/destroys the data in the newly opened workbook. When I go back to the original book that called the macro…it continues counting again.
I need this timer to continue running in the workbook with the command buttons (in the background) while I work on other worksheets in other books. Is there a way to keep the timer running no matter how many other workbooks are opened and prevent it from overwriting whatever sheet is active? This code in the Increment Sub below ‘overwrites’ the contents of cell B3 on whatever sheet is active---and stops the timer in the on the sheet with the ‘Start’ command button.
View 1 Replies
View Related
May 21, 2014
I created an Excel tool into which really thousands of records are based on weighted averages calculations. And discovered Excel struggling in term of permormance.
The issue is: the user needs to uploads a couple of times in a day new data for further analysis, and the only way to make the Excel spreadsheat humanely usable (performance) was to calculate everything upfront.
To optimize the tool performance, I had to:
- First I made all weighted average calculations upfront when data is uploaded the first time. Leaving formulas in the tool was not viable, since this tool run live analysis, every click would have been a pain of 30 sec at best to re-calculate new conditions,
- Second optimization was to not leave formulas into columns > as soon as a calculation is done, the macro copy/paste values
The formula I chose is sumproduct based weighted averages..
Here is my question:
1. Does it exits specific formulas in Excel to handle nicely large amount of weighted averages to calculate ?
2. Should I consider to use an external application (an Access table, SQL or whatever) to perform those weighted average calculations quickly/quicker.
View 2 Replies
View Related
Mar 30, 2014
I'm trying to find definitions/commands to make excel find the best combinations of several defined scopes and restrictions to make an output number as high as possible.
This is by using a combination of several different equations, maybe excel has a magic trick for all the variables and formulas.
Attached File: forum_heatexchanger.xlsx
View 2 Replies
View Related
Aug 9, 2013
I've attached a file that is the result of a product forecast.
What I'd like to do is minutely adjust the data in B4:K12 so that the totals shown in blue colour on Row 15 and Column O are respected.
Book1.xlsx
View 3 Replies
View Related
Apr 4, 2007
I'm using the following code to populate the columns next to the dynamic named range dataforgraph (varies in length). It works, but the problem is that it takes ages. Is there a more efficient way of doing it. It is important that it only populates the cells next to the varying dataforgraph. The avg1 and stadev1 are the same for each entry (thus constants)
For Each c In Sheet3.Range("dataforgraph")
c.Offset(0, 1) = Sheet3.Range("avg1")
c.Offset(0, 2) = Sheet3.Range("avg1") + Sheet3.Range("stadev1")
c.Offset(0, 3) = Sheet3.Range("avg1") - Sheet3.Range("stadev1")
c.Offset(0, 4) = Sheet3.Range("avg1") + 2 * Sheet3.Range("stadev1")
c.Offset(0, 5) = Sheet3.Range("avg1") - 2 * Sheet3.Range("stadev1")
Next c
View 3 Replies
View Related
Aug 21, 2009
I have an entire row of if statements that basically check other sheets if a number occurs. If it does then it will display "Pending Approval" ,"Open", or "Closed" depending on which sheet the # appears on .. Just wandering if their was a better formula for this..
View 7 Replies
View Related
Jan 22, 2009
Is there a faster (maybe a non-loop) way of doing this?
Private Sub Worksheet_Activate()
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
Dim Test1 As Single
Dim Test2 As Single
Dim Test3 As Single
With Sheet2
For Test1 = 33 To 52
If .Range("J" & Test1).Value = "End" Then
.Rows(Test1).Hidden = True
Else: .Rows(Test1).Hidden = False
End If
Next Test1
This causes a bit of a delay (7 seconds or so) when switching to the sheet, which is causing the user to think that excel has locked up.
View 9 Replies
View Related
Sep 1, 2008
Is there a way to count if there are 5 or more in a range without
using 'select case'
or not using the 'intcounter' bit bit of code?
Set Startcell = Sheets("meetingstodate").Cells(xlrow, 2)
Set Endcell = Sheets("meetingstodate").Cells(xlrow, 7)
Set rng = Range(Startcell, Endcell)
lastrow = Sheets("meetingstodate").Cells(xlrow, 2).End(xlDown).Row
For xlrow = 2 To lastrow
arr = Range(Startcell, Endcell).Value
For j = 1 To UBound(arr)
For k = 1 To UBound(arr, 2)
Select Case arr(j, k)
Case A
intcounter1 = 1
Case B .....................
View 9 Replies
View Related
Nov 6, 2009
I have discovered a strange problem with my VBA code. After testing it on several machines, I've found no problems with my workbook until now. After being run on a rather slow Windows Vista laptop, I've found that VBA appears to be running faster than Excel, and consequently it is creating errors as VBA asks Excel to do things before it has finished a previous action. This is causing macros to fail several times in each run, though can be solved simply by pressing 'debug' and then F5 to continue the code. Is there a way to get VBA to run slower to solve this? As I can't think of any other way of doing it? I've certainly not seen it happen on any XP or Win 7 computer!
View 9 Replies
View Related
