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
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
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
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
DATA20,000 Rows 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)
I have a home finance spreadsheet that I've been using for a few years and in the last couple of days it's ground to a halt. Whenever I enter a value in a cell and hit [ENTER] there is a long delay with the spinning wait icon (Windows 7) and the Excel window reports "Not responding". Eventually (3-4 minutes) the update occurs and everything is fine.
The workbook has 9 sheets on it but only one summary sheet references the others. The sheet I'm updating has about 2200 rows, 50 columns and the formulas are simple A+B-C types. Performance DOES NOT improve if I change recalculation to manual. The only VBA is some macros to perform tasks and they don't execute dynamically. Adding comments and changing formats responds immediately, it is only updating values that exhibits the problem.
I've tried replacing all formulas with their values (paste special/values) across the sheet but again this didn't work.
We have created a macro that basically looks for rows that contain an "H" and hides the row if it does.
Users can add new rows throughtout the year to this spreadsheet. and based on certain criteria, an H or U will be placed in a hidden column which the macro looks at and hides any row it finds an H.
The user has to click on the button that has the macro assigned to it once they have finished working on the spreadsheet.
The problem we're finding is that for users who insert/delete rows, once they click the button it takes up to 15 seconds to run through macro (which is ok). However, users who haven't added or deleted any rows and who click the button, they have to wait upto 5 minutes (which isn't ok) for the macro ro run.
We can't figure out why the macro takes longer to run when no changes have been made?
Private Sub Worksheet_Change(ByVal Target As Range) If Range("H5") = "Y" Then Columns("L").EntireColumn.Hidden = True Else Columns("L").EntireColumn.Hidden = False End If End Sub
If the value of H5 is "Y" it hides column "L". It was working fine at one time. Then I wrote a bunch of UDF's at the workbook module level. I then went back it use the "hide" function and it didn't work.
I created a couple new worksheets in the same workbook, and the same VBA works fine on them. I created a copy of the "non-working" worksheet and added the VBA to the copied sheet and it fails to work.
If I create an error in the VBA (like: Column("L").EntireColumn.Hidden = True), I get an error when I change the (non-working) worksheet - so I know it is "trying to run", at least sort of.
I have written a number of UDFs, which work fine in my spreadsheet. Two of these are more complex, and I'm having a few problems...
When I run any VBA routine (i.e. control passed to VB), these two UDFs return #VALUE! errors, but I can't see why. As these are Functions, I can't think of a way to test where the error is being generated, like I would in a Sub
When I return to Excel, any recalculation fixes the problem, which makes me think something is declared incorrectly. All named cell references relate to other VBA functions, calling the relevant row / column numbers, and I think these are all correct. I've tried removing AS INTEGER etc, to avoid type mismatches, and adding APPLICATION in front of worksheetfunction...
Function precedentStart(Target As Range) As Integer Application.Volatile With ThisWorkbook.Sheets("sheet1") Dim splitPrecedent As Variant, lookupI As Integer, rowI As Integer
For i = LBound(splitPrecedent) To UBound(splitPrecedent) rowI = WorksheetFunction.Match(splitPrecedent(i), .Range("schedRefs"), 0) lookupI = .Cells(rowI, colAdjEnd).Value If lookupI > precedentStart Then precedentStart = lookupI Next i End With End Function
I have a macro which filters data on one sheet and copies that data onto 34 new sheets . This works perfectly until the 25th pass when Excel advises that there is insufficient memory to continue. I assumed that this was connected to the clipboard and its ability to remember 24 chunks of data so I turned it off but still Excel gave up at the 25th pass. Is there a way that I can clear the clipboard during the macro's execution or is there something else going on?
'Other actions are located here, but have been removed. These actions take place if the Find is succesful.
CubeNotFound: Message = MsgBox("WS ID " & WSID & " was not found! Excel will continue with the next WS ID.", vbOKOnly, "WS ID NOT FOUND")
I have that statement there because if WS ID is not found then VB generates an error that the user won't be able to interpret, and also stops the code. I want VB to continue to the next record if one is not found.
It copies certain sheets out of a workbook (for a new workbook) Unprotect the sheets in the new workbook Deletes a particular cell on each sheet Unhides a sheet Sets all of the new worksheets to normal view Protects the new sheets Lets the user save and name the new workbook.
All is working fine Except it will not change the new workbook from Page Break View to Normal view.
The code I'm using = Sheets("name").Activate Activewindow.View=xlNormal
Here's the strangest part. When I run the macro from a command button, the newly created workbook retains the page break view (as is in the original).
When I run the macro line by line via F8, IT WORKS JUST FINE
I have created a macro in xl2000 which was working fine - although through corporate upgrades my machine has now been upgraded with xl2003 and the macro runs for a bit and then crashes on the code below, although it only highlights teh 'rnum'
rnum = ActiveCell.Row Is there a difference between xl2000 & xl2003?
I want to display a progress bar for when those macros are run. I've read many different ways to do this when I search it on google but I would prefer to use the cleanest method where I can simply call the appropriate procedure for each different macro, without changing any of the other code of the progress bar.
I wrote some code that runs with no problem on my computer as well as another coworkers computer, but will not work on a 2nd coworker's computer. The "subscript out of range" error shows up at the point when the EMInput worksheet in the EMInput workbook is copied to the new workbook called "Out of Plane Section Cuts" (SEE BELOW). Is there an option in EXCEL or somewhere that could affect this. I don't see why you would get a subscript out of range error since the EMInput workbook is open....
Private Sub Workbook_Open() Dim x As Date x = InputBox("Enter End Date!") Range("B2") = x With Application. CommandBars("File") .Controls("Save").Enabled = False .Controls("Save").Visible = False .Controls("Save As...").Enabled = False .Controls("Save As...").Visible = False End With With Application.CommandBars("Standard") .Controls("Save").Enabled = False .................
I have an Excel workbook which runs a query via DAO against an Access database. The query usually takes about 6-7 seconds. Id like to do some animation while the query is running. My animation is just a simple Autoshape that I rotate (1 degree at a time) in a loop. Is there a way I can excecute my query and then continue to run my code while the query is excecuting?
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 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?
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?
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?
When running the following code for NSLookup in a module within the current workbook, it runs fine but when I put it in a XLAM file and try and use it as an addin I only get #NAME? as a result. I believe that that addin is installed properly because I have a second module with a sub routine in it (GetIPStatus) that is working, and when entering the command =NSLookup(A2) in a cell it shows up properly like it's an available function, but it does not seem to execute. Do I have to do anything special for a function to run as an addin?