Speed- How Do I Determine What Is Causing My Workbook To Be Slow
Apr 16, 2009
I have a workbook with many lookups, sumproducts, dynamic named ranges and cse formulas. How do I determine what is causing my workbook to be slow? Are there more efficient formula types that I can use?
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
I have a spread sheet that contains many user defined functions from a 3rd party provider that downloads financial data from an external database. Often not all of the data is required by the user, and as the UDF's take a long time to calcuate, i'd like to provide the ability to easily switch them on and off. Originally I had been looping through cells and either adding an apostrophe in front of the = or removing it. However running this loop is very slow.
I found this thread (Optimize VBA Loop For Inserting Formula) and adapted it to convert formula strings (with ' in front) to formulas in a single pass by setting:
myRange.formula = myRange.value. This works very well. I can't, however, work out how to go back the other way in a single pass. Have tried: myRange.formula = "'" & myRange.formula. This works for individual cells, but not for more than one at a time.
Where D3 is a starting date, E3 is a closing date, and A5 is a sorting code. It looks at my general journal and looks for all the entries between the two dates, and then sums up all the entries linked to the sorting code in A5. The sorting code is in column H, and the amount to sum is in column I, and this is repeated 3 times.
You are already probably signering at how long I must have to wait while excel calculates all these formulas (* 220). I have resorted to a macro that turns caluculation to manual when I open this workbook, and back to auto when it closes.
I have an Excel file that has a certain amount of Array Formulas. It takes 30 seconds to calculate every time the user makes a change. Some users do not have the Status Bar showing, and they think that Excel is "locked up". I want to make a user form that will appear when the Stats Bar is "Calculating..." There are a lot of "progress indicator" tutorials on this site, but they all seem to relate to VBA code being inserted in between certain lines. That doesn't apply. My Excel file has no VBA. It just takes a long time to recalculate. I want a VBA form to appear, that will match the % progress shown on the Status Bar, whenever a change is made to the spreadsheet.
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
ActiveWorkbook.Worksheets("MyWorksheet").Activate
Do
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
i am currently using the following code to copy records from one sheet to new sheets that that are created and named in the first part of the if(). this works fine, however when i am dealing with 50,000 records it still takes 5-10min to get them all sorted. I think this although functional is horribly inefficiant, and am wondering how i might be able to speed up the process. possibly rather than check each record, then copy then paste individually to sort them, then read and select the list of cells until value changes, then copy over at once. i dont know if this would be faster or not, let me know what you think.
Sub autorec ()
Dim wSheet Dim newSheetName As Variant Dim FNAC As Double Dim OU As Double Dim DS As Double Dim CCY As String
I am trying to determine in code whether a file exists in a certain location. This seems to be very slow even in a folder which contains just one file. Is there any way to speed up this process? Three seconds seems like a long time especially given that I will have to loop through this Sub many times. The files I am looking for are CSV so I can't use msoFileTypeExcelWorkbooks unless I can modify what file extensions this looks for. I only started dabbling with VBA a few weeks ago so it is entirely possible I'm barking up the wrong tree and should be using another method to acheive my aims.
Sub CISORTEST() With Application.FileSearch .NewSearch .LookIn = "C:TEMP" .SearchSubFolders = False .Filename = "MYCSVFILENAME" .MatchTextExactly = True .FileType = msoFileTypeAllFiles
I recently added some command buttons to 6 excel files each with about 20 sheets. most of the sheets got 6 new buttons. After I added the buttons I would get memory errors when I treied to open other files. So I upgraded to Office 2003 and Excel 2003 because I read that it had increased memory available. It did not seem to help much, and now on average about evry say 10 minutes Excel hangs for about 1o seconds, sometimes saying "Not Responding" up the top. Then it starts working again. MY PC has Server 2003 and 1GHz and 512MB of Ram. Here is some info:
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.
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.
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?
I have a range of cells ( C2, C5, C8:N1007, P8:P1007 ....) on a worksheet that I want to be able to clear the contents of through a macro - worksheet is entitled Database
I have 3 additional worksheets all of which have summary information on and some of the formulas are huge.
I've inserted a module and produced this code;
Sub ClearContents() Application.Calculation = xlCalculationManual Range("C2, C5, C8:N1007, P8:P1007, ....").ClearContents Application.Calculation = xlCalculationAutomatic End Sub
Because of all the 3 summary page formulas the code is taking a few seconds to run.
I'm new to VBA and just wondered whether I could switch the calculation setting for the entire workbook onto manual at the beginning of the code and then switch it back to automatic at the end of the code? Think this might speed things up.
Over a period of time I entered dozens of worksheets into a very important workbook. Finally, I decided to put them in alphabetical order. I found a method/formula to perform the task on line. I made the suggested entry and it ran for some time. After it finished: the worksheets were in order. but since then Excel has been slow and many times unresponsive.
If I have a multiple workbooks open and I want to determine if a workbook is open for example "Test 1.xls", how would I do that. I want it to msgbox once to say "open" if open or msgbox "not open" if not open.
I cannot cut and paste from my working computer to the internet access computer.
This code is intended to check for a workbook already open and if it contains a particular worksheet. This function is called from a routine that reads a master workbook then opens a PCM workbook. The PCM workbook might already be open.
WB_PCM is declared as a workbook and the name is set. WS_PARAMETERS is declared as a string and its name is set to an existing worksheet. The workbook and worksheet do exist, but are not open.
Code: Public Function Is_Workbook_Open( workbook_ref As Workbook, sheet_name As String) as Boolean Dim check_sheet as Worksheet On Error Resume Next If( workbook_ref Is Nothing ) Then Is_Workbook_Open = False Else Set check_sheet = workbook_ref.Worksheets( sheet_name ) ' < error
[code].....
During this time the master workbook is open but either not in focus or is minimized within the Excel windows. If I select that worksheet and select a cell in it, then this code runs. What should I do different to get this code to check for WB_PCM being open and being able to handle it when either open or not open.
Is it possible to write code that can identify how a workbook has been opened i.e. has the workbook been opened by another workbook or has it received a double click event on the workbook itself.
If it is possible I would like to place the code in the open event of the workbook that is being opened.
Trying to determine who has a file open, the code below works for simple files. However if the workbook contains macros and user forms it seems there are multiple occurances of the search terms strFlag1, strFlag2. How can I determine the last user
Function LastUser(path As String) Dim text As String Dim strFlag1 As String, strflag2 As String Dim i As Integer, j As Integer strFlag1 = Chr(0) & Chr(0) strflag2 = Chr(32) & Chr(32) Open path For Binary As #1 text = Space(LOF(1)) Get 1, , text Close #1 j = InStr(1, text, strflag2) i = InStrRev(text, strFlag1, j) + Len(strFlag1) LastUser = Mid(text, i, j - i) End Function
I was wondering how i can code a if then loop to determine if a workbook by the name i specify is opened.
If Windows(vCriteria & "ISPR.XLS"). = True Then MsgBox "true", vbCritical, "Ferguson Enterprises, Inc." Else MsgBox "false", vbCritical, "Ferguson Enterprises, Inc." End If
thats what i got.... not sure if that will work... i'm missing the definition for: If Windows(vCriteria & "ISPR.XLS"). i keep wanting to type isopened, but alas it is not avaliable in the list. There seems to be no extention avaliable that appears to work with such a function. Is it possible to do what i am trying to do?
I am looking for a macro that will run on worksheet activation. it needs to run only if the file had been saved from the previous week. would something like this work?
Private Sub Worksheet_Activate() If application.worksheet.lastsaved = msolastweek Then range("RANGE").clearcontents End If End Sub
Split from Macro To Insert Rows In Protected Worksheet. I have found this code wgich appears to be very close to what i want. Just to re-iterate -
A) i want to detect if any other user has the shared book open, if yes-display msg and exit - it seems to do this by just adding an appropriate msg box
B) if no - open it and lock other users out by temporarily remving share or allowing them in but as read only - not sure if it does that.
Also not sure if it works for a shared file - cannot test as i do not have access to shared network from location.
Function IsFileOpened(StrFilePath As String) As Integer Dim FileNum As Integer 'First check filepath exists If Len(Dir(StrFilePath)) > 0 Then FileNum = FreeFile() On Error Resume Next Open StrFilePath For Input Lock Read As #FileNum ' Open file and lock it. If Err.Number <> 0 Then IsFileOpened = 1 'File open Else IsFileOpened = 0 'File Closed End If Close FileNum Else IsFileOpened = 2 'File not found End If End Function
how do you set a return value for a function in VBA? I've looked at examples of functions and I don't understand.
All I want to do is call a function to see if a workbook is open and have it return true or false. Here is the code I have, but it won't let me use go "Return isOpen" at the end like VB would! The IDE complains.
Function IsWorkBookOpen(ByVal name As String) As Boolean Dim wBook As Workbook Dim isOpen As Boolean
wBook = Workbooks(name)
If wBook Is Nothing Then isOpen = False Else isOpen = True End If
'I can't reutrn the value of is open? Return isOpen
End Function
I've noticed other VBA functions I've looked at don't use "Return"....how does does the function return a value then?
I have written a few macros, which use date type variables. Because VBA uses the 1900 date system, and some workbooks use the 1904 date system, I have to first check and see if a workbook uses the 1904 date system, and if so, subtract off 1462 days where appropriate. My macros used to check the date system by using the command:
If ( ActiveWorkbook.Date1904 = True) Then nh_stringToDate = nh_stringToDate - 1462 End If
But, sometimes the macro resides in one workbook (call it macro.xls) but the currently active workbook is some other workbook (something.xls). What I really care about is the date system of macro.xls and NOT something.xls. So, instead of "ActiveWorkbook" is there some other object I can use to refer to the workbook in which the macro itself resides? I don't want to rely on using the name of the macro workbook (macro.xls) because this could change! So I need a way to simply refer to the workbook in which the macro resides.