Cut/paste hangs forever in Excel 2007 spreadsheet.

Here is a macro that will delete all conditional formatting from your workbook. Because the macro runs against the activeworkbook, it does not need to be installed in the workbook with the problem.

Sub DeleteConditionalFormats()
Dim ws As Worksheet
For Each ws In ActiveWorkbook.Worksheets
    ws.Cells.FormatConditions.Delete
Next
End Sub

Select allOpen in new window

1. Do you see the word Calculate in the status bar at the bottom of the Excel window? This means your workbook is so complex, that everything must recalculate when you make a change--and a cut and paste qualifies as two changes.

2. Does the problem with cut/paste occur if you use the file on a different computer? If so, the problem is likely related to something in the file. If not, the problem may be related to corruption or conflicts in your Excel install.

Microsoft offers a free tool called OfficeCAT that will analyze your Office configuration. The database for this tool is updated every month. From personal experience, the tool is quite thorough and took over an hour to analyze all my Office applications. Outlook takes more time than any other application, so consider analyzing just Excel the first time through.

Thanks for the response byundt:

1.
Ran the kill formats macro, didn't help.

2.
Issue is the same when calculation set to manual ... plus it calculates so fast that you can't really see the "calculate" pop up at the bottom ... nevertheless I checked with calculation both auto and manual and didn't see the "calculate".  What shows the whole time after clicking "paste" is "Select destination and press ENTER or choose Paste" ... i.e., the same message that gets put there after selecting "cut" ... so in other words, nothing in the statusbar changes during the entire hang time.

3.
Same on a different computer.

4.
Will try the OfficeCAT and report back - but all my Excel installations are very well-behaved and stable so I don't expect to learn anything there.

You might also try repairing the file in question.

If you use the File...Open menu item, there is an arrow to the right of the Open button in the file browser. If you click that arrow, the popup will give you the opportunity to Open and Repair...

That option doesn't exist in Excel 2007 ... please let me know if there is another way to do it.

Sorry. I spaced the fact that Excel 2007 uses the Office meatball (logo) to access what future and previous versions of Excel call the File menu.

Click the Office meatball at upper left of the window, then choose Open from the resulting dialog. You should then see a file browser dialog, and the Open button will have an arrow on its right side.

Ok, did that, didn't help, got this report:

<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
<recoveryLog xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main"><logFileName>error056520_01.xml</logFileName><summary>Errors were detected in file 'D:\$OnyTrade\Spx_Pricing.xlsm'</summary><additionalInfo><info>Excel completed file level validation and repair. Some parts of this workbook may have been repaired or discarded.</info></additionalInfo></recoveryLog>

Ran the OfficeCAT and the report didn't note anything worth reporting ...

If you try to Open and Repair the file again, does Excel continue to report errors? If so, you have a file corruption problem that the built-in tool is apparently unable to fix. File corruption is a frequently reported cause of slow cut and paste or copy and paste operations in Excel 2007.

The manual way of troubleshooting file corruption is to see if it is limited to one worksheet or range of cells. You delete pieces of your workbook and then testing whether you have fixed your problem. Ideally, your tests will lead you to the conclusion that deleting one worksheet or one range of cells on that worksheet will fix the problem.

Sometimes the solution is to rebuild the workbook by copying your formulas and formats over to a freshly created workbook, one worksheet at a time. if you have done your testing ahead of time, you will know which worksheets are apt to cause problems--go especially slow on those.

There is a commercial software called ExcelFix that claims to rebuild your workbook. They offer a free trial version that may give you some peace of mind before you spend $89 to buy it. I have used an earlier version of the software (works only on .xls files) and was glad I purchased it.

Got the same report on second repair.

I'm well versed in your "manual way of troubleshooting" and "rebuild the workbook" ... spent countless hours of my life doing those things back in Office97 and prior versions, and can do it in this case if necessary.  I will rebuild rather than use anything like ExcelFix.

Regarding manual troubleshooting, this case is atypical from my experience because (as mentioned in original post) the issue is incrementally improved in a linear way as I remove formulas by copy/paste values, and only completely gone when I remove the last batch of formulas ... never seen anything like that before with a corrupted workbook having only ordinary formulas.

Well, I guess I know how you will be spending the rest of your day. I don't envy you.

Good luck!

Brad

I have isolated this issue as a problem with many named ranges in function calls.  I created a new xlsm workbook with a dummy VBA function (return value = 1) having arguments similar to the main function used in the old workbook.  I created fresh named ranges in the new workbook similar to the named ranges in the old workbook.  The issue reproduces in the new workbook.

The function has 24 arguments of which 10 are named ranges.  If I progressively replace named range arguments with normal range arguments the cut/paste delay shortens progressively until it becomes unnoticeable at around 3 named range arguments.

Sure wish there was a workaround for this ... would also like to know if it's the same in more recent versions of Excel.  Any comments from experts welcome.

If you post the new workbook that reproduces the problem, I can test it in Excel 2010 and 2013.

Ok thanks ... it occurred to me that I could avoid the boggy confusions of having a VBA function called thousands of times by simply using a native Excel function ... so the attached workbook uses the AVERAGE() function to illustrate the issue.  The key metric is the time "Select destination and press ENTER or choose Paste" shows in the status bar after clicking "paste" after cutting.  The behavior is a little different depending on whether you have calculate = auto or manual, but the same illustration is made.  It's clearer with calc = auto.  I'm just cutting/pasting one blank cell anywhere on the sheet.
Average_new.xlsx

I reproduce your annoyance using 32-bit Excel 2007 & 2013 and 64-bit Excel 2016--but only when I move one of the cells containing a formula to a new cell, and then perform an Undo. The Undo step is what takes the long time.

My 32-bit Excel versions are running on a 2008 MacBook Pro with Windows 7 running on a Parallels Virtual Machine with 2 GB of RAM assigned to the Virtual Machine. The time delay after cutting and pasting a cell with a formula is about 2 seconds. The time delay for the Undo is about 19 seconds. The time on the sheet with direct cell references is identical to the sheet with named ranges.

My 64-bit Excel 2016 runs on a 2012 iMac with a Parallels WIndows 10 Virtual Machine with 4 GB of RAM assigned to the Virtual Machine. The time delay for the Undo is about 12 seconds. The timings are identical on the sheets with direct cell references and with named ranges.

On all machines, when I copy a blank cell and paste it anywhere else, the operation is instantaneous.

64-bit Excel is able to handle up to 8 terabyte of RAM (if you can afford that much). 32-bit Excel is limited to 2 GB.

Unless your computer is memory restricted, it ought to be faster than my eight year old Mac running Windows on a Virtual Machine. The fact that you are seeing longer delays than I do suggests to me that you have conflicts or corruption with your Excel installation.

I do not see "Calculate" in the Status Bar as described by longtime Microsoft Excel MVP Charles Williams in 'Calculate' always in the Status Bar  And there is no perceptible delay when I try to perform a full recalc with CTRL + ALT + F9.

Thanks for the legwork ... I'm testing on 5 different healthy boxes, all modern enough and with plenty of RAM and processor so no comments on that score are necessary.  On the 5th box I just tested, I noticed Excel 10 was installed so I tried that and got the same results.  These are different boxes used for different things; they are definitely not clones in any sense.

Another comment is that I've been seeing this issue for years - say, at least 4 years and perhaps much longer - so it's nothing recent.

I should have given the expected results for the particular spreadsheet I uploaded, since it is much faster than the 35 second hang I was reporting for the workbook I actually care about ... for the uploaded spreadsheet, on a box like yours with Excel 2007 or 2010, the no-names page should be more or less instantaneous while the names page should show the paste text for about 1-2 seconds, maybe even less than 1 second sometimes, but very noticeable compared to the no-names page if you are watching carefully.  Are you sure you weren't seeing this in Excel 2007?

In Excel 2007, when I copy and paste a blank cell on the with names worksheet, the Paste operation is instantaneous. When I copy a cell with a formula and paste it in a blank cell, the Paste operation might be slower, but certainly under 0.1 second. Same as when I copy a cell with static text.

The behavior with Cut and Paste is different in 2007. The Cut is instantaneous, but the Paste might take two seconds. And the Undo 13.7 seconds on one trial and 13.9 on the next. This behavior is the same no matter whether I am cutting static text (like a header label), a cell with a formula or a completely blank cell. On the no names worksheet, it is a little faster. The Paste is pretty much instantaneous, while the Undo took 12.3 seconds on one trial and 12.7 on a second.

So just to be clear in case of typos, you are talking about only Excel 2007 above, with paragraph 1 regarding copy/paste and paragraph 2 cut/paste?

And I hate to drive this into the ground, except that if you are NOT seeing the issue in Excel 2007 then I'm going to dive into settings, add-ins, Norton and all those usual suspects.  So, to drive it into the ground:

1.
We are only talking about cut/paste of a blank cell.

2.
We are only talking about the status bar text immediately AFTER you paste (doesn't matter when you cut).

3.
We are talking about how long the status bar continues to show the text "Select destination and press ENTER or choose Paste" AFTER you paste.

4.
I'm getting no lag for the no-name sheet and 0.5 - 2 seconds for the name sheet.

5.
And you are getting what?

My timings were until the value appeared in the Paste destination cell after a Cut. I was seeing virtually no lag with the no name worksheet, and about 2 seconds with the name worksheet in Excel 2007.

When I did the Undo, my timing was until I saw the value disappear from its old location. Its reappearance in the new location was at the same time as its disappearance from the old location.

Still can't tell for sure if we're comparing apples to apples as you don't say what you are cutting ... can you do the cut/paste test with a blank, unformatted cell?

I tested cutting cells that were blank and unformatted, contained static text and contained a formula. I did these tests using Excel 2007 on both the no names and with names worksheets. The behavior is the same no matter whether I am cutting static text (like a header label), a cell with a formula or a completely blank cell.

I stated the results in https://www.experts-exchange.com/questions/28936843/Cut-paste-hangs-forever-in-Excel-2007-spreadsheet.html?anchorAnswerId=41533247#a41533247

Ok thanks, I read all that carefully and several times when you posted but as indicated I was still confused because you are doing several other things along with.  To continue with eliminating my confusion:

1.
So with Excel 2007 your blank cell cut/paste results match mine (unfortunate as this means there is likely no fix).

2.
Can you please restate for me your test results in later versions of Excel, for just the blank cell cut/paste?

Excel 2013 32-bit, launched fresh, then opened test workbook

Using no names worksheets: cut a blank cell--instantaneous;  paste a blank cell--flicker of the screen; Undo--first repetition 25.2 seconds, second repetition 22.6 seconds

Using with names worksheets: cut a blank cell--instantaneous;  paste a blank cell--3.4 seconds first repetition, 3.5 seconds for the second repetition; Undo--first repetition 26.6 seconds, second repetition 27.1 seconds

Wow, so it gets worse in Excel 2013 ... this seems to be pretty close to a bug, at least with the blank cell cut/paste ... particularly because it gets a LOT worse with a spreadsheet (like my 35-second one) that is still relatively small and simple (mine has only about 8000 function calls with all the names, but some of the arguments are functions of multiple names which I'm guessing is what really boosts the time delay).

Hats off to you byundt for all the help with this.  Please let me know if you think of anything to try that might conquer this issue ... even reducing my time from 35 seconds to 5 or 10 seconds would be a victory.

Thanks, that is great out-of-the-box thinking for this issue.  I'm familiar with the programming techniques and the pros and cons of the array formula approach.  This spreadsheet is actually an instance of a template that has existed since 1999, and even back then I was aware that an array formula version would be advantageous for reasons unrelated to the named ranges issue, but I never really needed it because the spreadsheet is so fast and stable - except for this issue.

There are 2 distinct structures on the problematic worksheet: One is very amenable to your VBA approach - I can potentially reduce about 50% of the 9000 function calls to 28 calls.  The other is not amenable to VBA, but I can code it in a C++ XLL and it would work well.  This would involve bundling blocks of 11 function calls into 1 array call returning a 3 x 4 array, so the second 50% of the 9000 function calls would be reduced by a factor of about 10.

It remains to be seen if the array approach would crush the buggy issue of the names, but it's worth trying (hate to do it for a bug as it reduces the flexibility of the spreadsheet, but oh well).

Can I ask you one question about the VBA approach:  What I want to do is to code the VBA array function with an array argument 200 x 1 and returning an array 200 x 1.  I've never done VBA array functions this big.  Do you have any experience with this kind of thing, to the extent that you could comment on possible VBA limitations or potential stability issues, or other issues?

I have done UDF that return far larger arrays than 200 x 1, and there was never a problem with stability. The first time I did it in Excel VBA was in 1994 using a 168 row x 12 column return array--and that workbook has been used heavily ever since.

One thing worth noting is the fact that a statement like MyUDF = Array(1, 2, 3, 4, ... 200) will return a 1 x 200 array of results. If you want a 200 x 1 array of results, you might say MyUDF = Application.Transpose(Array(1, 2, 3, 4, ... 200)). Or you could Dim an array within the UDF that is 200 x 1, populate it with values and then say MyUDF = MyArray

Thanks for the feedback on size.  I always use the Dim return(1 to 1, 1 to 200) or Dim return(1 to 200, 1 to 1) method.

Will report back on whether or not this vanquishes the names problem - I expect it will.

This project is coming along well and looks like it will be successful, but there is one problem that byundt or some other reader might be able to advise on:  For the VBA array functions I'm calling the worksheet function 2 ways: For some arguments I can call a locally declared function from a C++ dll (not an XLL), and this is very fast - the VBA array function handling 200 spreadsheet cells on one call is about 2-3 times as fast as doing the 200 calls as originally configured (pretty good in my opinion as the VBA code is fairly involved and VBA UDFs are naturally slow).  For other arguments I have to call the actual XLL worksheet function, and the only way I know how to do this is with Excel.Run ... I'm using the REGISTER_ID so there is no Excel overhead for resolving the function name, but it's still much slower than I expected.  For a direct comparison I used Excel.Run for the same function calls I can also call from the dll ... the array function using Excel.Run was 2-3 times slower than the original spreadsheet configuration, or in other words 4-6 times slower than the array function with the dll calls, for exactly the same arguments in all the calls.

So the questions are, (1) is there a better way to use Excel.Run and (2) is there a better way than Excel.Run to call the XLL function ... the XLL is my project so I can add code to it if necessary.

I am absolutely the wrong person to consult with best practices for XLL to VBA communication, but Microsoft has used the approach shown below in their Analysis ToolPak (VBA) add-in since at least Excel 2003. I am guessing that it is similar to the approach you are already using with Excel.Run.

' ANALYSIS TOOLPAK  -  Excel AddIn
' The following function declarations provide interface between VBA and ATP XLL.

' These variables point to the corresponding cell in the Loc Table sheet.
Const XLLNameCell = "B8"
Const MacDirSepCell = "B3"
Const WinDirSepCell = "B4"
Const LibPathWinCell = "B10"
Const LibPathMacCell = "B11"

Dim DirSep As String
Dim LibPath As String
Dim AnalysisPath As String
Dim WorkbookName As String

Dim FunctionIDs(37, 0 To 1)

Private Function GetMacroRegId(FuncText As String) As String
    For i = LBound(FunctionIDs) To UBound(FunctionIDs)
        If (LCase(FunctionIDs(i, 0)) = LCase(FuncText)) Then
            If (Not (IsError(FunctionIDs(i, 1)))) Then
                GetMacroRegId = FunctionIDs(i, 1)
                Exit Function
            End If
        End If
    Next i
End Function

'Procedures
 
Sub Anova1(inprng As Variant, Optional outrng As Variant, Optional grouped As Variant, Optional labels As Variant, Optional alpha As Variant)
    xAnova1 = Application.Run(GetMacroRegId("fnAnova1"), inprng, outrng, grouped, labels, alpha)
End Sub

Select allOpen in new window

Right, that's what I'm doing except the call to get the RegId takes no time at all since it's stored static in the XLL.  I was hoping there was a faster alternative to Excel.Run since it seems silly that Excel.Run takes so much longer than calling the same function from a cell on the worksheet.

I had been thinking about inviting Microsoft Excel MVP Charles WIlliams to offer advice on how best to call functions in your XLL from VBA. He makes a living increasing the speed of Excel calculations in large workbooks--so knowing the best way to make VBA calls to XLL functions is important to him. I was pleasantly surprised to see he has a blog page discussing this very problem. https://fastexcel.wordpress.com/2014/12/13/calling-xlamxllautomation-udfs-from-vba-evaluate-run-or-reference/ 

I was interested to read that you have to pass parameters by value rather than by reference, so passing an array to your XLL function is going to slow things down.

Thanks again byundt for the legwork.  I scoured your links with great interest and feel that they have provided resolution for the question: Specifically, there is no easy way for me to speed up my Excel.Run function calls, so I'll actually have to do some work.  The best way in my case is probably to write the necessary wrapper code in VBA so I can call my DLL function library as I'm doing already when the arguments permit ... it will be slow as the wrapper code is nontrivial, but the example I already have indicates that the speed will be acceptable, and faster than both the original spreadsheet and the Excel.Run version of the array formula.

When I read it today, I remembered from long ago that thing about XLL parameters being passed by value.  My Excel.Run function call has two Excel.Range arguments, so to see what would happen I tried calling the array function with arguments that didn't use the ranges, and comparing calculation speed with/without setting the range arguments to 0.  The null range version speeded up the calculation about 20% - 30%, an improvement which surprised me.  Also just for fun, I tried calling my array function with all the Excel.Run call parameters hard-coded as 1s and 0s, but it didn't change the speed beyond the improvement gained by setting the range arguments to zero.

I'm finishing this project up tonight and will make a final report on how close I came to achieving the objective ...

Ok, this thing is done, here's the report for posterity:

Final version of the old spreadsheet took 50 seconds to cut and paste a blank cell (I added another thousand or so formulas compared to the version that took 35 seconds).

Replaced 4488 of the old formulas with 374 custom XLL array formulas that return blocks of 12 single calls.

Replaced 5600 of the old formulas with 28 custom VBA array formulas that return columns of 200 single calls.

Spreadsheet still has several dozen of the old formulas sprinkled around.

New spreadsheet takes less than 1 second to cut and paste a blank cell ... just a blink longer than a new, empty workbook.

Seems to take exactly the same amount of time to cut and paste something nontrivial, like one of the named ranges that appears in all the function calls.

Amazing!  Thanks byundt for all your help and key suggestion regarding the array formulas.

P.S As discussed above, the calculation speed of the spreadsheet is NOT much faster and is even slower in some cases ... but that was expected because I was adding a lot of VBA overhead to a spreadsheet that was pure XLL previously.  Not a problem because it was already very fast before ... just the cut/paste thing was driving me crazy.

If I were to hazard a guess, by using the array formulas, you have made a huge reduction in the number of cells containing formulas. Excel can therefore keep track of its dependency tree, and decide which cells, if any, need to be recalc'd when you cut and paste a blank cell.

Without the reduction in number of formulas, Excel gave up on its dependency tree and recalc'd everything when there was a cut and paste.

Thanks for publishing such a thorough analysis of the benefits of your final approach. It's not every day that one can claim a fifty-fold improvement in a performance metric.

FWIW, Microsoft is interested in seeing examples of workbooks with performance problems. They try to figure out why the performance problem occurs, and whether they can make a change to Excel to eliminate the bottleneck. As an Excel MVP, I know exactly who to contact and would be glad to forward your workbook if you would like to participate in this effort--but I would understand completely if you felt it must remain completely proprietary.

Brad

No need to send this thing to Microsoft, which is good since nobody in their right mind would touch it.  All they need is the spreadsheet I uploaded to this thread earlier, which I believe illustrates the issue in full - some buggy thing in Excel where resolving names takes far longer than it should.  Compared to that workbook, mine got extra-extremely bogged down because of one or two additional factors - maybe because my workbook contains 154 named ranges, and/or maybe because my formulas all have arguments that are functions of multiple named ranges, and/or maybe because my formulas all have array arguments.  Anyway, consider the spreadsheet I uploaded: 11 named ranges of which 10 must be resolved for each function call ... but if Excel is working properly then the first call should leave the operation with an ultra-fast pointer table for all the name resolutions ... I could code the whole lookup thing in slow VBA so it would be way faster than what is happening on that worksheet.