Stepping Thru Code Very Slow
Mar 8, 2007
I do those things, I set every object = nothing... I even moved them up to right after I use the object. When I open the workbook, my process's shows excel at 38000K. my first go of running code, stepping thru is great... I watch my process and see that I get up to 78000K... I then exit my code, properly... and the process goes only down to about 50000K. If I run it a 2nd time excel process show almost a 100000k and stepping thru is slow, slow motion... a 3rd time and I am at 120000k. I am setting everything to nothing and can't figure it out... I have done bigger projects with more forms, recordsets, collections and never have had this problem.
View 2 Replies
ADVERTISEMENT
Jul 12, 2006
Whilst stepping through code in any new sub/ function that I create in VBA, VBA Editor (seemingly arbitrarily) jumps to a line in a completely different function.
View 4 Replies
View Related
Jun 15, 2008
When stepping through my macro it moves through each line of the code showing me the code as it steps through. Is there a way to step through and see the results of the code as it is going through. I have an "IF THEN" statement in the macro that is not giving me any results so I think I must have an incorrect reference in the code, but I can't see it. I think if I could watch what it is supposed to be doing as I step through I might be able to find the error.
View 4 Replies
View Related
May 22, 2013
I have just written a macro to validate/clean up some data which I get on a monthly basis. Some of the columns come from a pdf file and thus excel recognises these as strings. I have written some code that would convert this string to an integer which runs fine when I step through the code. However when I run the macro from a button the number value goes from e.g. 5000 to 0.
The code is below:
VB:
Sub Condition2()
For k = 8 To LastRow
Worksheets("Bordx Format").Cells(k, i - 1).Value = CDec(Cells(k, i - 1).Value)
Next k
End Sub
View 2 Replies
View Related
Oct 29, 2009
We have had a macro running for a few years (Excel 2007 now, but started in 2003) that imports about 35 text files into separate sheets, creates a calculated SUM field for each sheet and copies that value to a title (or summary) sheet. We use this 2 to 4 times per year at inventory time, copying to a new file and deleting the old data before running the macro. The imported files initially create new workbooks, but the data is copied to the initial workbook into a distinct sheets for each file.
Recently (well, last August) this macro started hanging after importing (Workbooks.OpenText) a number of files, and not necessarily the same file every time (on repeated runs.) While trying to figure out the problem, I have now managed to get it to hang every time on the first file! However, if I am stepping through in the debugger it continues past the OpenText command and on F5:Run/Continue will then continue processing the rest of the files normally. If I delete the first file before running the macro, it then hangs on the second file instead.
Without debugging, the first file will import, display on-screen, and there it stops. If I put a break-point on the very next instruction after the import, that break-point is never reached. THINGS I'VE TRIED:.......
View 2 Replies
View Related
Nov 6, 2008
My code runs so slow! The sheet only has 233 rows and 6 of them are not in the loop.
View 6 Replies
View Related
Mar 27, 2009
Very slow small vba code. I have the following
View 2 Replies
View Related
May 9, 2007
I have the following piece of
Sub Summarise_Variations()
Dim myRange As Range
Dim Write_Cell As Long
With Application
.Calculation = xlCalculationManual
.StatusBar = "Please wait - Summarising variations..."
End With
It happily goes through a list and sums multiple records. It might not be the most efficient or best way to do it but it works and when I run it on a couple of stand alone sheets it takes a couple of seconds to process if that.
Unfortunately when I run it as part of the overall application that I've developed it takes ages to run, i.e. more like ten minutes.
The spreadsheet has a few graphs and about 250 sumproduct and array formula live in it but all other formula on other sheets are created and then paste valued as part of other VBA routines. As you can see I've also turned calculation off as the procedure runs so don't understand why it is suddenly taking so long.
View 9 Replies
View Related
Aug 4, 2008
way the code below can be made more efficient? Basically, I drop down a combobox, and based on the value i select, it populates a lot of textboxes etc based on the listindex.....
Private Sub ComboBox3_Click()
On Error Resume Next
With Me.ComboBox3
If .ListIndex = -1 Then Exit Sub
Label23.Caption = Sheets("overview").Cells(.ListIndex + 2, "c").Value
Label24.Caption = Sheets("overview").Cells(.ListIndex + 2, "b").Value
Label38.Caption = Sheets("overview").Cells(.ListIndex + 2, "d").Value
TextBox1.Text = Sheets("overview").Cells(.ListIndex + 2, "e").Value
TextBox2.Text = Sheets("overview").Cells(.ListIndex + 2, "f").Value
TextBox3.Text = Sheets("overview").Cells(.ListIndex + 2, "g").Value
It takes about 10 seconds every time i choose another project
View 9 Replies
View Related
Sep 15, 2009
I have some code which looks for the word "Out" in column "L" and hides the row if it finds it. This is running from row 24 to 160 and takes about a minute to run. I've tried two different solutions, neither really quicker than the other.
View 3 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
Aug 18, 2009
I have a some code that we use in our office that works ok on my pc. When others in the office try to use the macro the year gets changed to 19 instead of 09. The only problem I have is that the code seems to slow down when it is trying to print the worksheet out. Can the code be shortened up/cleaned up. We are looking for gridlines with inside /outside lines, landscape and left/right margins of .25
Sub IC_Delays()
Application.ScreenUpdating = False
Application.DisplayAlerts = False
Workbooks.Add
Windows("Delays List.xls").Activate
Range("F15:N" & Cells(Rows.Count, "F").End(xlUp).Row).Copy
Windows("Book4").Activate
Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats
Range("A1:I" & Cells(Rows.Count, "A").End(xlUp).Row).Select.........
View 9 Replies
View Related
Aug 23, 2006
If you open the file sets.xls, you will see that I have listed 5 sets of 10 numbers.
A21:J21
A22:J22
A23:J23
A24:J24
A25:J25
These sets are such that all 10 numbers matches with sets of 20 numbers listed vertically in the range K1:S20 atleast twice (T21:T25 -twice)
I know there are 50+ sets like these and I have listed just 5 of them.
How can I search the other sets?
I have uploaded a zip file which has an excel file. Run the module "DoIt" and you will get all 50+ sets like these.
The problem is that the module takes 42 minutes on my Celeron 800 Mhz 256 MB SDRAM. I want to reduce this time.
View 4 Replies
View Related
Apr 4, 2009
The code opens a varying workbook with the same format as the source workbook and copies specific columns into the source workbook.
It is currently taking about 20seconds to run.
The worksheets are protected and have merged cells hence the call TestMe line.
View 13 Replies
View Related
Oct 1, 2006
I am making a small push button calculator to enter data into a textbox on a userform in an add-in file and this works fairly slowly. I am trying to concatenate a list of numbers in a textbox that simulates a calculator screen when entering numbers.
Private Sub CommandButton6_Click()
Dim Val
Val = "6"
Dim valand As Range
Set valand = ThisWorkbook.Worksheets("Stageing").Range("K65536").End(xlUp).Offset(1, 0)
valand = Val
Dim A As Range, B, C, d, E, F, G, H, I, J
Set A = ThisWorkbook.Worksheets("Stageing").Range("K2")
Set B = A.Offset(1, 0)
Set C = B.Offset(1, 0)
Set d = C.Offset(1, 0)
Set E = d.Offset(1, 0)
Set F = E.Offset(1, 0)
Set G = F.Offset(1, 0)
Set H = G.Offset(1, 0)
Set I = H.Offset(1, 0)
Set J = I.Offset(1, 0)
TextBox1.Value = A & B & C & d & E & F & G & H & I
End Sub
View 4 Replies
View Related
Jan 23, 2008
I am having a little trouble with this code, which runs in my simple but efective ressource overview. It loops through a rather large range and assigns interior colour to the cells based on certain criteria. On my stationary machine (Excel 2003) it takes approximately 15-30 seconds to run the code which is acceptable. On my laptop (Excel 2007) it takes 5+ minutes which is unacceptable. Is there a workaround so as to optimize the speed? Further, when I run this workbook on Excel 2007, even entering an integer in the sheet takes 3-4 seconds, and no code is running! In my first version I used a Change_Event to colour cells on the fly but this was slow and prevented multible cell editing as well as pasting values into the appropriate range.
Public Sub Farvelade()
Dim icolor, Navn As Integer
Dim TargetRow, LastRow, Previous As Long
Dim Target As Range
MsgBox "Det kan tage 15-30 sekunder at opdatere ressourcekalenderen"
Range("A5").Activate
Range("A5"). CurrentRegion.Select
'Set range
ActiveCell.Offset(Selection.Rows.Count, 0).Activate
LastRow = Cells(Cells.Rows.Count, "A").End(xlUp).Row.....................
View 8 Replies
View Related
Oct 7, 2013
this code to minimize it and avoid copy to clipboard.
VB:
Sub SapOutputRun()
Application.EnableEvents = False
Application.ScreenUpdating = False
[Code].....
View 2 Replies
View Related
Feb 19, 2012
I am using the below to open a text file and copy the used range to my working workbook. The opening of the file makes the code slow and and also it may be bcoz i am trying to paste 11800 lines of data from one file to another.
Also i can see the flickering of the screen when the files are open. How to make the code faster and more efficient.
Code:
Sub readSimFile()
Application.ScreenUpdating = False
'//code//
Application.Calculation = xlCalculationManual
[Code] ......
View 5 Replies
View Related
Jun 22, 2006
I have some code which steps through a range of columns from A to Z ( using Chr(asc(col))+1 as the stepper.
This works fine until it encounters col AA then fails - I have added the function that does this - can you help me get beyond col Z?
Function findcol(strTitle As String, ws As Worksheet)
If Left(strTitle, 1) = "0" Then
strTitle = Right(strTitle, Len(strTitle) - 1)
End If
col = "A"
findcol = ""
While ws.Range(col & "1").Value <> "" And findcol = ""
If ws.Range(col & "1").Value = strTitle Then
findcol = col
End If
col = Chr(Asc(col) + 1)
Wend
End Function
View 8 Replies
View Related
Jul 21, 2014
I have a very simple macro that copies data from one worksheet to the other. This macro worked fine in Excel 2007. I have since migrated to to 2013. The newly arisen problem is that when I run the macro, the data is not copied to the 'Previous Part Data' worksheet. However, if I step through the code line by line, it works fine.
Here is the code:
VB:
Sheets("Current Part Data").Select 'Select the 'Current Part Data' worksheet
'Copy the data to the 'Previous Part Data' worksheet then clear the data
Range("A4:V" & CurrentPartDataFinalRow).Copy
[Code]....
I have two questions:
1. Why did this work in 2007 and not in 2013?
2. Is there something I can do to get rid of the sheet selection statements to avoid cluttering my code?
View 3 Replies
View Related
Mar 25, 2008
my existing macro, as the run takes to much time to complete. (at least 20min) I've already tried several loops, but no one worked for me. Following situation: There are two excel files, entries in column 73 - 85 will be copied from WorkbookRust to the other workbook if the numer in column 5 is the same. Not every cell within this columns contains data, so the macro should automatically jump to the last entry in each of the above mentioned columns, instead of predefine the range as you see in the code below. After the data is copied to the other workbook, it will be filtered acc. to Sub FilterMain and then copied back to WorkbookRust. As already said, the whole thing works, just to lame.
Sub Allmacros()
Dim WorkbookRust As String
WorkbookRust = ActiveWorkbook.Name
ChDir "C:Documents and Settings vogtMy DocumentsRüstplausch"
Workbooks.Open Filename:= _
"C:Documents and Settings vogtMy DocumentsRüstplauschCH_Revenue_2008.xls"
Sheets("Main_Overview").Select
Windows(WorkbookRust).Activate
Application.run ActiveWorkbook.Name & "!UpdateEntries"
Application.run ActiveWorkbook.Name & "!FilterMain"
'not ask to overwrite existing file
Application.DisplayAlerts = False
Workbooks("CH_Revenue_2008.xls").Save
Workbooks("CH_Revenue_2008.xls").Close
End Sub
Sub UpdateEntries()......................
View 7 Replies
View Related
Dec 30, 2012
I have excel 2007. I have a workbook with 2 tabs set up as follows:
Tab ATab B
Tab A contains a column with the source data, which will be constantly overwritten
Tab B has a row which references the data in tab A
I would like to copy the reference formula in Tab B down for say 100 rows, but when I use the fill tool, it automatically moves the reference cell up one. I want it to remain the same as the original.
E.g
Tab B, Row 1
ColA = Tab A, Row 1
ColB = Tab A, Row 2
ColC = Tab A, Row 3
When I fill copy the above; on Tab B, Row 2, the formula changes to ColA = Tab A, Row 2, ColB = Tab A, Row 3 etc. I would like them to remain as ColA = Row 1, ColB = Row 2 etc.
I will then use Tab A to input each rows data in Tab B, having converted the previous row into just text so it doesn't change with each update.
View 6 Replies
View Related
Sep 11, 2007
we would use a byte data type as a counter in a For Loop, but just today I have been having trouble with this. When I try to count backwards with " Step -1" I get an "error 6: overflow" on the FOR LOOP line. When I count forwards it works fine.
For example ....
View 9 Replies
View Related
Mar 30, 2009
I have a workbook made up of worksheets containing source data, pivot tables and charts but it is now becoming very slow since I have added a worksheet with many formulas.
If I move that worksheet containing many formulas (20 columns by 150 rows) to a different workbook will that help speed both of them up? The formulas will obviously still by referring to the original workbook.
Or is it simply by using formulas it slows things down whatever you do and I will have to think of a clever way of using pivot tables instead? Are there any other things that could be slowing it down? Other workbooks I maintain are fine.
View 9 Replies
View Related
Dec 5, 2009
I got a work sheet with 672 columns of information that im trying to cross compare against. I wanna compare each column against every other column in that row. I have 200 rows of data. That means each i need excel to do 226,128 comparion calculations each row. So that means in the entire work sheet its gotta do 91 million comparisons. Im on a dual core 1.8ghz core 2 duo cpu and 2 gigs of ram on xp pro with excel 2007. I even bumped up my virtual memory by 3 times the size it was yet still its taking forever.
Its taking over 3 hours to do this whole page of calculations. So i opened up visual c++ and quickly programmed in the same code with some generic values and within 3 seconds it computed it all. My guess is that the bottle neck is when excel has retrieve data from the cells because other than that i cant figure out why its so slow. Heres a section of my
View 14 Replies
View Related
Nov 14, 2006
My spreadsheet is chugging through the following code. The data being filtered is rows A:AC, rows 10:193 (so not an absurdly large amount of data). It takes about 15 seconds to complete the filter. Ideas?
Private Sub EuropeButton_Click()
HeaderRegion = "Europe"
ActiveSheet.AutoFilterMode = False
Range("A2:A8").Value = 0
Range("A2").Value = "TEG"
Range("A3").Value = "TUK"
Range("A4").Value = "TEF"
Range("A10").CurrentRegion.AdvancedFilter Action:=xlFilterInPlace, CriteriaRange:= _
Range("A1:A4"), Unique:=False
If Columns("E:F").Hidden = True Then
Columns("E:F").Hidden = False
End If
End Sub
View 9 Replies
View Related
Mar 14, 2007
I'm rather new at VBA
I have a small workbook with a userform and some buttons. Each time I press a button a counter increases and the workbook is saved.
This saving seems much too long for such a small file.
I use
Activeworkbook. save
View 9 Replies
View Related
Mar 19, 2007
I'm working working with this spreadsheet that is moving incredibly slow. Every time I enter anything, it takes anywhere from 10 seconds, to a couple minutes to calculate and let me proceed. It is a pretty big file (4.60 MB), but I also work with another spreadsheet that is a little smaller (2.95 MB) that has never taken more than a fraction of a second to calculate anything. What could I do to spead up the spreadsheet?
View 9 Replies
View Related
Sep 24, 2009
This module consists of app. 25 sub routines. Every sub routine is a For-Next Loop, with 70 For-Nexts to work through. The loops check certain input cells and if these cells are empty, the row is hidden. I have the reverse also where I can unhide these rows.
When I had about 8 of these loops in this module, it would work quite fast and it took about a second to run one of these subs. Now that I have 25 if I run one of these subs it takes about 20-30 seconds.
Before I break them all down into individual modules just to find out it doesn't work, is there anything else I should be doing or could try to speed these subs up? If breaking them down into more than one module is the solution, how do I do this best?
View 9 Replies
View Related
Nov 10, 2009
I have a function grabbing latitudes and longitudes from a zip code look up. The table array used is approximately 48k rows.
Is there a way to speed up the lookup?
View 9 Replies
View Related
