Is Excel 2016 technically better than Excel 2010?

hershmab

New Member
Joined
Mar 17, 2011
Messages
38
The reason I am asking is because I am having one large and severe technical problem with Excel 2010 that many Excel gurus have not been able to help with and that Microsoft Excel support have not been able to diagnose, let alone solve.

Over the past 10 years or so I have created a number of Excel applications for accounting and financial purposes. They have a lot of cross-references and formulas, including much VBA code that I have written myself as subroutines and user-defined functions. Several of the applications that are most important to me have, in the last year or so, suffered from a persistent tendency to stop with the "Excel is not responding" message and often never complete properly, however long I wait.

I and the people I have consulted cannot find the reason for this - it seems to be something in the Excel 2010 code that is going wrong, not in the applications themselves. I have used Microsoft's OffCat tool to analyse the relevant workbook and come up with only one possible cause: input data often comes from copying and pasting online bank account tables that can include hidden and irrelevant links which Excel interprets as graphics, ie. Shapes. But even when I have eliminated these Shapes, the applications continue not to respond, particularly when closing and automatically saving the workbooks.

So my question comes down to this: is Excel 2016 likely to be any better at diagnosing and correcting such problems than Excel 2010?
 
Last edited:
I am doing a trial of Excel 2016 within Office 365 and can confirm Smitty's view that Excel 2016 is no better at preventing my "Not Responding" problem than Excel 2010!
**************
I have been doing some more analysis of what is happening within Excel when "Not Responding" occurs. It looks as if the main operations causing it are [Auto-]Save and the Calculate that often takes place before saving. I have some related questions:
(1) Is it possible to prevent all calculation on saving?
(2) Is it possible to limit that calculation to formulas dependent on data changes, i.e. to prevent redundant recalculation?
(3) How to find out what cells/worksheets need recalculation?
(4) Most of the affected workbooks contain historic data (e.g. bank accounts) that are recreated at the beginning of each year and then grow monthly with the latest transactions. The formulas that apply are largely one-time row calculations to spread the input values or cross-check them against other data. Once the formulas dependent on input values in each row have been calculated, they will never need recalculation. Is there any way to indicate that and thus avoid recalculation?
(5) Since the formulas in each cell are the same as those in the row immediately above, is there any automatic way to convert all formulas in every row EXCEPT the last to values?
 
Upvote 0

Excel Facts

How to show all formulas in Excel?
Press Ctrl+` to show all formulas. Press it again to toggle back to numbers. The grave accent is often under the tilde on US keyboards.
(1) Is it possible to prevent all calculation on saving?
(2) Is it possible to limit that calculation to formulas dependent on data changes, i.e. to prevent redundant recalculation?
(3) How to find out what cells/worksheets need recalculation?
(4) Most of the affected workbooks contain historic data (e.g. bank accounts) that are recreated at the beginning of each year and then grow monthly with the latest transactions. The formulas that apply are largely one-time row calculations to spread the
input values or cross-check them against other data. Once the formulas dependent on input values in each row have been calculated, they will never need recalculation. Is there any way to indicate that and thus avoid recalculation?
(5) Since the formulas in each cell are the same as those in the row immediately above, is there any automatic way to convert all formulas in every row EXCEPT the last to values?
1. Probably by catching the event in this workbook module:
Code:
Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
With Excel.Application
.CalculateBeforeSave = False
'.Calculation = xlCalculationManual 'this if you don't want to calculate after the save event
End With
End Sub


2. On the previous event, select the range you want to calculate and do so
Code:
Range(Desired).Calculate
Or, add the event of on data change for the desired sheet and work from there:
Code:
Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Columns("A:B")) Is Nothing Then
    Columns("A:B").Calculate
End If
End Sub


3. That's something you need to define, I think the best approach is by the sheet change event then -if your macro/user writes data there it will be catched by this event and calculated then-

4. This should be top 1 priority then, calculation on this workbook should be set to false -doing so involves many things, mainly the clipboard being lost-, so think about it as last resource. Again, in this workbook module:
Code:
Option Explicit
Private Declare Function OpenClipboard Lib "User32" _
(ByVal hwnd As Long) As Long
Private Declare Function CloseClipboard Lib "User32" () As Long
Private Sub Workbook_Activate()
Call xlCalcMan
End Sub
Private Sub Workbook_Deactivate()
Call xlCalcAuto
End Sub
Private Sub xlCalcMan()
    With Excel.Application
    .ScreenUpdating = False
    .Cursor = xlWait
    End With
    OpenClipboard 0
    Application.Calculation = xlCalculationManual
    CloseClipboard
     With Excel.Application
    .ScreenUpdating = True
    .Cursor = xlDefault
    End With
 
End Sub
 
Private Sub xlCalcAuto()
    With Excel.Application
    .ScreenUpdating = False
    .Cursor = xlWait
    End With
    OpenClipboard 0
    Application.Calculation = xlCalculationAutomatic
    CloseClipboard
    With Excel.Application
    .ScreenUpdating = True
    .Cursor = xlDefault
    End With
End Sub
5. Excel won't know that, calculation needs to be performed for every formula -even if they are the same-.
 
Last edited:
Upvote 0

Forum statistics

Threads
1,213,565
Messages
6,114,337
Members
448,568
Latest member
Honeymonster123

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top