Vba Running Slow
I have a workbook with macros Ive created that have been running just fine for 2 years now. Ive upgraded to a top of the line new Dell D830 dual core laptop with 2gb ram and installed excel 2003 (same excel as always). Certain macros that used to take a second or two to run on a celeron dell now take 3min's! Ive stepped through the code in debug mode and found that its hanging up in simple areas like "Columns("A:A").Select" and "Selection.ClearContents". We have 16 older laptops running these exact codes just fine all day long and the 2 new fast computers with core 2 duo processors are hanging up constantly in the same places in code. Ive gone through and made sure all the "option" settings are identical in excel. Is there some system setting That I'm missing or possibly an excel setting That I may have overlooked?
View Complete Thread with Replies
Related Forum Messages:
Slow When Running Macros
I have a simple Macro. I assigned to a Form button.
when i click that button systems takes lots of time to complete the macro.
left down task bar it say calcuating cells and load from 1% to 100% ...
Slow Running Macro
Below is the entire code that I am using. It is a simple routine which checks whether a part has started its release process or not, based upon dates. The code works and does what I want.
The problem that I have is that it is very slow, for example it takes 35 seconds to go through 530 lines items. In my (limeted) experiance, based on other VBA doodlings this slow.
Macro Running Very Slow
I have the following macro in a worksheet...and it is running very slow. There are other macros in the worksheet and they all run very well. Any ideas by looking at this code why it would be so slow in running?
Slow Running Spreadsheet
I have quite a large workbook, which runs very, very slowly when I'm on a certain sheet.
Scrolling seems to lag, and there is a long delay between me clicking a cell and it becoming active.
The sheet contains a number of web queries, and a number of cells which refernce other cells, both in the same worksheet, and other worksheets.
Data Filters Running Very Slow
I have an excel sheet with about 3,000 products listed; there are 26 items of data listed against each.
I've been using Data Filters to manipulate the info, but my worksheet has suddenly started running incredibly slowly - up to 7 minutes to implement a simple filter selection.
There was no problem until I made a couple of changes last week:
1. Adding a simple pivot table
2. Using an Index column plus some linked formulae to extract a list of suppliers from the product list.
I assumed that it may be the INDEX and related formulae or the pivot table that were causing the Data Filters to run slow, so I deleted them, but to no effect. I've even gone so far as to copy the raw data into a new sheet, but it's still running incredibly slowly when I try to filter. I am convinced that the problem is related to some hidden legacy of the table or functions, as it only started once I'd added these, but I don't know if this is plausible in reality and if so how to get rid of it.
Users In Excel :: 50+ Running Slow
I have built an Application in Excel that is used by a userbase of 50+ people. Now, as standard, they are only able to access the document read only. The application writes data to an ".mdb" but the user cannot change the application directly istself.
Now, we've had the problem over the last few days that the program is running slowly when it is trying to write data back to the ".mdb" file.
I believe that it could be because connection to the sever that our application is running slowly (or the server is too highly trafficed) but our server team are telling me there is nothing wrong with the server and it's running slowly because there are too many people in one Excel application/
Copy/Paste Code Running Very Slow
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.
Macro Running Very Slow - Cell (press Esc To Cancel)
I have written some very basic code to format a report in excel. When I run the code it take a very long time to execute and I receive the following error message at the foot of the page:
Cell (press esc to cancel)
Annoyingly I have had this error before and found the solution on the web but can't remember where. If memory serves my right I deleted some temp files from a specific location on my hard drive?
Slow VBA Module
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?
Slow Saving - VBA
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.
VBA Slow To Assign A Null String To A Cell - On One Computer Only
I have a VBA program which is running very slowly on one of my computers, but none of the others.
Here's a piece of code which illustrates the problem, which is that assigning a null string to a cell is very slow.
Public Sub test()
Dim objSh As Worksheet
Dim I As Integer
Set objSh = Worksheets("Sheet1")
For I = 1 To 1000
objSh.Cells(1, 1) = ""
For I = 1 To 1000
objSh.Cells(1, 1) = " "
If I change this to assign a space instead it is very fast (about 1,000 times faster).
This slow machine is not normally slow; an ACER Aspire 9410Z with 2GB RAM, running Vista Ultimate.
Other machines run this code OK on a mix of Windows XP, 2000 and Vista Business. with Excel 2003, 2000 and 2007.
Ultimate seems to be the only unique factor of the machine where it runs slowly.
Vlookup Array Formula Too Slow - Don't Want VBA
I have this formula: =VLOOKUP(E24,OFFSET(Data!E$23,0,0,MATCH(TRUE,Data!E$23:INDEX(Data!E:E,MATCH(9.99999999999999E+307,Data!E:E))="",0)-1,14),14,0) in column e and looking up from a sheet called Data!. This formula looks up off of column e into the data worksheet from the point where I insert a blank row at a chosen point in the data. The problem is that this formula needs to calculate in excel where is shows in the bottom left of the screen...calculating 5%...50% etc. and I have to wait. Does anyone know how to rewrite this formula so it does not have to re-calculate after each move? This formula above requires a CTRL+****+ENTER.
What the formula above does is simply looks up into the other worksheet from column e and looks up only into the data where I insert a blank row. So it creates a lookup range at the point where the blank row is inserted.
Running VBA In IE
I am having trouble running Excel VBA code in an excel workbook through Internet Explorer (IE).
The code I have runs fine in Excel. But when I running by opening in IE I get an error.
For example trying to run the simple command
Results in the error message Method 'Range' of object '_Global' Failed
What do I need to change in my code to get IE to run the code properly?
Public cnn As New Connection
Public bFlag As Boolean
Dim rs As Recordset
Dim e As ADODB.Error
Dim CYExtract As Date
Dim PYExtract As Date
Public Sub LoadData()
Dim msg As String
Dim extractDate As String
On Error GoTo AnError
Application.ScreenUpdating = False..........................
VBA Running Faster Than Excel
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!
Running Macro Automatically Using VBA
Is it possible to run the following macro automatically using VBA when cells B2 and C2 equals "Yes"? I don't want any manual intervention for this to happen.
' clearcells Macro
' Macro recorded 14/10/2009
Check If Outlook Running In VBA
I am trying to establish if Outlook 2000 is currently running in VBA , I have tried the following code but this only works when the user is actually positioned in "Inbox - Microsoft Outlook" etc. At this point I don't really care where/what the user has open in Outlook but merely to check if outlook is running.
I have tried below but does not work robustly enough.
I have also tried AppActivate("Microsoft Outlook")
I have also tried IsRunning("Outlook.Application") which does not even compile.
Dim OutlookErr, OutlookBox
On Error GoTo OutlookIsNotRunning
OutlookErr = "Outlook is either not open or busy with another task." & vbCrLf & vbCrLf
OutlookErr = OutlookErr & "Please Open Outlook, Close any draft emails," & vbCrLf & vbCrLf
OutlookErr = OutlookErr & "the Global Address List or other activities and try again."
OutlookBox = MsgBox(OutlookErr, vbCritical, "Unable to access Outlook to send email")
Running Same VBA Over Multiple Sheets
I have some code which I want to run on all apart from one sheet called 'List'. At the moment I am selecting each sheet before running code for the specified sheet. This doesn't allow for any sheet name changes etc. or additional sheets. Is there a quicker way of doing this rather than having a sub to manually select each sheet?
Here is an example of my code for a button on the sheet called 'List'. The only thing Sheet1, Sheet2 does in this code is select that sheet.
VBA Code Not Running Automatically
I have written some code intended to automatically clear a cell when data appears in it from an external source. In one spreadsheet, it seems to work exactly as expected, ie when the cell is polulated, it immediately clears the contents. In another spreadsheet, with exactly the same code, it will not automatically clear. If I put it in debug mode and step through it line by line, then it does do as expected but otherwise it won't.
Stop Vba Running If Environ Name Is Not In List
My users log on with unique PIN numbers for example 1234. I have a worksheet that has approximately 10 different VBA codes. What I would like is the ability at the start of the code to say if the current users pin is not in a hidden range then exit the code. If not run it. Is this possible?
Running An Excel Macro Using VBA Code In Ms Access
I am attempting to run a MS Ecel macro that is stored on a MS Excel shared file in a MS Ecel target file (locally stored on my C drive) from a VBA module in Ms Access. That is, from a code i want to open the .xls file that houses the macro, and then open the .xls file that i want to run the file in, and then run the macro.
Here is my situation: the file that houses the macro has the workbook hidden and causes in error.
if i unhide the workbook i get a Run-Time Error 91...object variable or With block variable not set.
if i hide the workbook the vba coding can't find the macro...run time error 1004
once again, i currently have an excel file that houses macro whose workbook is hidden.
does anyone know how this can be accomplished?
here is the ....
Running Simple VBA Code Causing Screen Errors
I have a rather large workbook (30 sheets, 10MB) that has one worksheet with many INDIRECT functions in it (pulling data from the same file, different tabs). I am working to put simple code into the workbook to protect and/or unprotect all worksheets. I have gotten code to work to both protect and unprotect all the sheets, but when I run the unprotect code (see below), and then I go to edit the workbook, data from the sheet with many "indirect" functions temporarily "overwrites" the data on the active sheet (this is fixed when I scroll my mouse over the effected cells). I am developing this workbook for other users, so I'd like to fix this before sending it off to them.
This problem does not happen until after I run the following .....
Running Multiple In VBA Instances: Controlling Spread Sheet Always Waits For The Code To Return Before It Continues
i have a spread sheet which has a very complex simulaton in it. excel is not the ideal place for it, but it is not possible to port it wholesale to something more sensible. i have modified the code so that it is possible to run two copies of the spread sheet (in two seperate excel processes) at one time. Due to the dual core nature of the machine this has almost no overhead.
I have created a controlling spread sheet that is cabable of launching multiple copies of the simulation and starting the code executing. the problem is the controlling spread sheet always waits for the code to return before it continues, hence it would open two seperate versions of excel, start the first simulation executing and then wait until it completes before starting the next one. is there any way of starting a function in another work book without waiting for the execution to complete?
Running The VBA That Is Assigned To Multiple Cmd Button On One Cmd Button
I have a workbook that contains 18 sheets, on 14 of the work sheets I have a comand button that runs the following
Private Sub CommandButton1_Click()
Dim nloop As Long
For nloop = 3 To 53
If nloop 3 And nloop 12 And nloop 15 And nloop 16 And nloop 23 And nloop 28 And nloop 41 And nloop 47 Then Range("K" & nloop).Value = 0
Range("B2").Value = DateAdd("d", 7, Range("B2").Value)
I want to put a comand button on sheet18 that will run the code so it does the same thing as pressing each command button on each sheet.
the sheets that I have the cmd on are:
Sheet1, Sheet2, Sheets3, Sheet4, Sheet5, Sheet6, Sheet9, Sheet10, Sheet11, Sheet12, Sheet13, Sheet15, Sheet16, Sheet17
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:= _
If Columns("E:F").Hidden = True Then
Columns("E:F").Hidden = False
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
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?
Workbook Slow Down
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.
Slow Update Workbook
I have attached a workbook that I want to be updated by the user. It contains a number of vlookups that to refer to a data source on a server communal to all users. I wondered if there would be anything that could be done in the workbook to help speed up an update?
Slow For Loop With IF Statment
why the code below is very slow with the extra if statment in it (marked with **) i have googled around and found that my for staments are correct i have tried screen updating etc. i have added a timer to see what diffrence it makes, what i have is 3 sheets 1st has about 100 rows with a number and time same as second but that only has 2 rows of the same the 3rd sheet has about 100 rows of just a number what the code does is loop through the 3rd sheet (build list) when it finds a number it looks on the first sheet to find the same number then gets the data in the second coloum and so on until it reaches a cell that says stop.
i have added a button to start this process, with the ** line in it takes 311 seconds with out it it takes 0.17 seconds
Do LoopUntil Slow Speed
I have written a macro which is sucessful in the sense that it does what I need. However, I ran it against my data for the first time today and it took forever. To give you an idea of the data size... my code had to loop through about 10,000 rows and move the ones that met the IF criteria to another sheet.
Can anyone provide any suggestions as to what might make my code faster? Should I take a different approach? I am definitly still wet behind the ears.
My code is below...
Dim count As Integer
count = 3
PeCount = 3
If Trim(Sheets("MyWorksheet").Cells(count, 12).Value) = "1111111" Or _
Trim(Sheets("MyWorksheet").Cells(count, 12).Value) = "2222222" Or _
Trim(Sheets("MyWorksheet").Cells(count, 12).Value) = "3333333" Then
PeCount = PeCount + 1
count = count + 1
Loop Until IsEmpty(Sheets("MyWorksheet").Cells(count, 2).Value)
Slow Reaction On Cell Changes
I have a relatively complex application with a few thousand cell links and dozens of VBA macros (3.5 MB heavy). This application (one file for each project) is used by a few hundred users in about 200 projects.
What I did recently (to allow updating macro program functionality in existing files) is to move all macros into an add-in.
What happens now is every time when I change the content of any cell anywhere in the file, it takes about 3 seconds(!) to leave the cell after changing it and to jump into the one below.
Of course I'm getting complaints from users about this slow behaviour.
This also applies to cells in sheets that have no event macro program related to them.
It applies as well to cells that are not referenced in any other cell.
I tried changing the calculation method from automatic to manual - no change in speed.
I de-activated the add-in - no change in speed.
I have checked if there are any formulas that contain the today() or function or the like - there are none (actually, I had replaced them with links to a cell that contains today's date, which is automatically updated when the file is opened, but this was also this way in the previous version).
It seems that there is something going on that causes Excel to be busy with itself.
Does anyone know what might cause this strange behaviour?
I tried everything I could think of without success and I'm lost now.
We are using Excel 2003 in an XP Professional SP2 environment and most of our computers have 512 MB RAM.
Macro Runs Slow
The code below runs on a spreadsheet that has approx 600 rows which INDEX and MATCH another spreadsheet which has approx 600 rows. I takes about 4 mins to run.
ActiveCell.FormulaR1C1 = "Compared CCCD"
Columns("T:T").ColumnWidth = 15.29
Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
Application.CutCopyMode = False
Large, Slow Workbook
I have a large Excel workbook (about 70,000KB) and it takes ages to load and is slow to operate.
Is there anyway of compressing it or making it run faster and not slow down the computer?
Slow Writing To Cells
A macro in workbook 1 creates a report by reading & manipulating data from two workbooks(WB 1 and WB 2).
A new blank report sheet is copied from WB1 to WB2. The data (some text, some numeric) is collected in a 20 element, single dimension array & written to the new sheet in 20 columns using a for/next loop. This is repeated for many rows(can be more than 1000).
The array is "erased" before each row. Screenupdating is turned off, calculation is turned off. The report takes longer & longer each time it is run - Why? With the one-and-only "Write" line "remmed out" the report takes just 2 secs however many times it is re-run - including the copying of the blank master report page. If the first report takes, say, 1 min the second time takes 2 mins & the 3rd - 3 mins etc. Reset is only achieved by exiting excel. The code in question is:- For col = 1 to 20 : cells(ro,col) = d(col) : Next col. (ro = the current row number which is incremented for each pass, d() is the data array)
Trim Function Is Slow
Due to the fact that some of the raw data I get dumped out of my database has leading and trailing spaces it is necessary to trim them. The one part of my code which seems to take forever to run is the section which performs this function on around 22,000 cells in one column. Here is the code I'm using. Is there a better way to do this?
'Set ServiceType = wsTmp.Range("C2:C" & tmpRows)
'For Each cell In ServiceType
'cell.Value = Trim(cell.Value)
The thing, with much code, seems to be that the slowness of my computer in executing code is directly proportional to the amount of uptime on the machine. Often a cold start will remedy the issue. Rather than having to do this daily to remedy the situation is there some other remedy to the problem?
SUMPRODUCT Causes Slow Calculations
I made one excel file with 9 sheet. there are used sumproduct formula in 200 rows.
i am not used any vba macro for this file.
i am used sumproduct formula link to other files and other sheets.
my file size is 1.80 mb but when i am enter new data in file then file going in process of calculation and calculate all forumula it take some time. my file options calculation is automatic but for calculation it take time and i have to wait for that for Process.
There are any macro for calculation so my file work fast and don't want to wait for calulation process .
This file is not open like other files. it is take a time in update the link or calculation.
Workbook Slow To Open
I have searched the forums re my issue and have not found a relevant solution. I have a large workbook 19MB in Excel 2007. There are 400 sheets and each sheet contains various formulas and one macro. The only graphics on each sheet are color coding of some cells. The workbook takes 2minutes 55 secs to load. Once open there are no issues. Is there a reason why this is so slow or do I just have to accept that being a large file it will take this long. There is no issue with HD space or RAM as the laptop is only used specifically for this workbook. Running Intel Core 2 Duo, 160 HDD, 2mb ram.
FileCopy Extremely Slow
I am trying to make a macro that copies a file per row to a folder specified by a cell in the row (and then calls a command line tool on it). I am using FileCopy to copy the file, but it is extremely slow even for only 6 rows (I need it to work with hundreds).
I think the problem is that the folders are network folders. Is there maybe another copy file function in VBA that does not block while the operation completes? I could go through all my rows and just copy the files, then go through again and run the command line tool. By the time it reaches the end of the sheet, the first file would be done copying.
Slow Code Execution
I have the following piece of
Dim myRange As Range
Dim Write_Cell As Long
.Calculation = xlCalculationManual
.StatusBar = "Please wait - Summarising variations..."
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.
Spreadsheet Runs Slow..?
I created this spreadsheet to assist with pricing for my towing company. I struggled with getting the dropdown lists and combo boxes to work, and I created and deleted many of them before I got it to work like I wanted, but I think I left a mess. It runs slow, showing a delay when I click on different radio buttons, and there is an exclamation point in the Excel icon in the file directory. Could someone take a look in the code structure and tell me if there are any opportunities to clean it up. RateCalcApp.xlsm
Spreadsheet Very Slow To Calculate
I have a blank workbook that I am just about ready to deploy, but it is very slow to calculate. I know I can manually calculate, but I would rather the workbook just be fast and calculate on its own. The workbook is blank now, but will be used all of 2010 and will ultimately end up being around 20,000 lines. So, I have built in the formulas and conditional formatting into the 20,000 lines now so I don’t have to continually add lines.
So, the questions are…
What part of the workbook is slowing it down?
Here is how the tabs breakdown:
NOTESJust text no formulas
1 formula of conditional formatting (Covers entire 20,000 rows and all columns except A)
4 columns with formulas (2 using VLOOKUP, 1 using SUMIF, 1 simple addition)
REPORT2 pivot tables linking to DATA
Slow Inefficient Code
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
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
Very Slow To Load Files
i had a file with 10,500 lines, 18,000 kb and it seemed slow to load (about a minute) every time i made changes and then saved it or reopened it.
so i broke the file up into 3 smaller files by cutting and pasting. i deleted all empty lines and columns beyond the file content. i defragged my compter. i cleared all excess format in job history - but -
one file now has 3,900 lines, 22,300 kb and takes 4 minutes to load.
one file now has 2,000 lines, 20,100 kb and takes 4 minutes to load.
one file now has 4,900 lines, 14,500 kb and takes 1 minute to load.