Free Microsoft Excel 2013 Quick Reference

Force Recalculation

I have a couple of Advance Filter tables where the criteria changes based on
the current row. I would like a macro that will trigger whenever the focus
changes to another cell.

Thanks in Advance,
Ron


I have a VB function in spreadsheet A, rotacode(Name, Date) that calculates a rotacode from tables in spreadsheet B. If A is open, B is always.

Name & Date don't often change, but the data in B changes each week, which means that the result of the function should change, but the function doesn't recalculate unless name or date changes. I have automatic recalculation on, and in any case F9 doesn't recalculate the function.

Is there a way to force recalculation of all functions, whether or not their arguments have changed?

Hi,
Can you tell me how can I force Excel to recalculate all my functions? The obvoious solution (Application.Calculate) does not help, as it (from some strange reason) does not recalculate those of my UDFs, which calls an external DLL. (Nor will the values of these cells are actualised, when I press F9 (recalculate). The only way I can force recalculation of those UDFs with DLL calls, to modify any of the input parameters / or I close and reopen Excel. (I do not want to define these functions as volatile as I do not want them to recalculate permanently, only at some occasions. Any idea?
Thanks,
hege

My spreadsheet is very large & takes sometimes 5 to 10 seconds to recalculate. The problem is that is was wanting to recalc every time I edited a cell, which I do constantly all day long. Due to this, I have turned off automatic recalculation. Is there is a way I can use VBA to force just one cell or just one row to recalculate? Keep in mind that currently no cells recalculate until I hit F9 or go in & manually hit Calculate Sheet in tools > options > calculation.

Hi

I am looking for some code to put in a worksheet module which forces the workbook to recalculate whenever the user hides or unhides a column in that worksheet.

The act of hiding or unhiding would not be triggered by any VBA routine. The user would simply select the columns with the mouse, right-click and choose hide or unhide as desired.

Thanks for any help.

I'm quite new to this VBA stuff and am obviously having difficulty understanding the different worksheet functions and where to place my code. I have a Summary Worksheet which (each time the workbook opens)I list in column A every worksheet name along with the sum of each corresponding worksheets range E2:E1000 to get a value off each sheet. My purpose of listing each worksheet name dynamically is because the user may Add, ammend, delete or rename these. This all works fine every time I open the work book but what I cannot get my head around is how I get the workbook to recalculate if anything is changed on any of the worksheets without closing the book and reopening it.

I have searched the web and thought I could use the following in the Workbook_SheetChange event
just as a test but this failed. I thought You guys would obviously know why. I have had a similar problem on forcing a change
event before and Skip pointed me in the right direction but I obviously am not getting the strategy of where to put my
code.

The code I am using is as follows;

Private Sub Workbook_Open()

Dim ws
Dim Counter As Integer
Counter = 0

MsgBox "There are " & ThisWorkbook.Worksheets.Count & " Worksheets in  this Excel Workbook"
For Each ws In ThisWorkbook.Worksheets
    Counter = Counter + 1
    'MsgBox ws.Name
    If Counter > 1 Then ' This is just to ignore the 1st sheet as that is the summary one
        'This next line is just to place a total value spent on each individual sheet
        Worksheets(ws.Name).Cells(1, 6).Value =
Application.WorksheetFunction.Sum(Worksheets(ws.Name).Range("E2:E1000"))
        'This next line is just to list all worksheet names onto Sumamry Sheet
        Worksheets("Summary").Cells(Counter, 1).Value = ws.Name
        'This next line is to place a total value spent on the summary sheet alongside each sheet name
        Worksheets("Summary").Cells(Counter, 2).Value =
Application.WorksheetFunction.Sum(Worksheets(ws.Name).Range("E2:E1000"))
    End If
Next ws

End Sub


I am using the following code from the ozgrid web site to calculate each sheet in the workbook:


	VB:
	
 CalcActiveOnly() 
    Application.Calculation = xlManual 
    For Each sh In  ActiveWorkbook.Sheets 
        sh.Calculate 
    Next sh 
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
However, I get a message "sh variable not found".

I introduced a line "Dim sh as worksheet" and the error goes away but the it appears that sub does not result in the actual recalculation of each worksheet.

Any comments on resolving this appreciated.

Thanks

Background:
I'm trying to fit a 2 parameter Weibull distribution to some (waiting time until an event) data. I want to use the (statistical) "maximum likelihood" method, using Solver, but struggle to get initial values for the parameters. In a small "spreadsheet A" I have the data and use Solver to get a least squares fit - essentially minimise sum of (data distribution - Weibull distribution) squares. In this spreadsheet Solver takes 0.1 second to come up with satisfactory initial values for the maximum likelihood approach.

The plan:
I want to import spreadsheet A into spreadsheet B, a much larger spreadsheet - just over 2Mb - which nonethless takes much less than a second to calculate. Spreadsheet B does the maximum likelihood calulations and will use the least squares results as starting values.

The plan is therefore:
a) Run Solver to get least squares estimates (cf 0.1 second above)
b) Run Solver to get maximum likelihood estimates (which might take a minute currently)
both in spreadsheet B.

The problem:
When I do a) above Solver takes perhaps 60-90 seconds (cf the 0.1 seconds above).

I don't know what's going on here; I had thought that it might be that I'd got the Solver settings/options wrong in spreadsheet B, but *both* spreadsheets have the following Solver settings:


	VB:
	
 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
I sense that there might be a real learning point here for me, and perhaps others.

Thanks
Andrew Auto Merged Post;

Coolactuary back again, perhaps embarrassing myself in my first post.

I can now (probably) solve my issue, but don't really understand why it is happening.

Apologies, I have now realised that spreadsheet B had many thousands of VLOOKUPs in the background, which spreadsheet A did not have. Given that I'd used the recaulation option "automatic except for tables" and that a spreadsheet B CALC took less than a second I had not expected the Solver run to take nearly so long.

Does Solver force recalculation of all tables, whatever the CALC setting? I still don't see why even this would lead to such a slow solver run (15 recalcs should take around 10 seconds).

Andrew

Environment - XP/SP2 or 2000, Excel 97-2003, Excel experience - I get by, VB experience - pretty good, VBA experience - just started.

I am using the "count cells by color" VBA I found on the FAQ here, to count only the correctly color-coded cells within a large range, testing on cell.interior.colorindex and using cell.value, and it works a treat : D , but, changeing background color is not an event so does not force recalculation, as confirmed by looking through the FAQ here.

If I "dirty" a cell within the range of the function, it recalcs fine. But I'm writing this application for others to use, and want to specify a method whereby they can be confident all cells are correct without changing the input data.

So how do I force a recalc of the entire worksheet? I have clicked on a cell and then F9 recalcs that particluar cell for me, but I want all cells recalced in one go.

I have tried F9 with the whole sheet selected, CTRL+ALT+F9, tools->options->calculations Calc Now(F9) or CalcSheet, even save/close and then re-open the spreadsheet, and none of those activities causes the VBA function to update its result. That last failure really surprised me. I notice that in tools->options->calculation there is a "recalute before save" check box, but it's greyed out for me.

What obvious thing am I missing?

Clicking on a cell and F9 does NOT recacluate, you have to select a cell, then click in the formula bar, then F9, then it works. I've spotted the greyed out "recalc before save" thing, I was on auto recalc. Changing that to manual gave me the option, but, it still doesn't recalc on save. The ONLY thing that recalcs the function seems to be to dirty the data in range, nothing else. Euggh!

I have created a UDF (actually two - see below) that sums a dynamic range based on the values in some other columns related to the range. Because of its dependence on values in several places, I have marked it as Volatile.

My problem is that the sum seems to recalculate even when a change on another sheet is made - using the values on that other sheet! If there are invalid values on that other sheet, an error value appears "#VALUE!". Even if there are valid values, they are not the values I meant to be summed. So, when I return to look at the sheet where the function is used, the results are totally invalid and I have to force recalculation.

I tried putting a worksheet.Activate event handler in to force the recalulation. However, that fixed the calculation problem, but made it impossible to paste a value from another sheet into the sheet where the Activate event was, because the Calculate method causes any cut/copied selection to be cancelled!

There are actually two functions that I am trying to use. The SumPhase function will sum back to the beginning of the phase (or back to the beginning of the items list if no phase begin is found), only the items whose type matches the 2nd parameter value. The SumTotal function sums the entire item list, except for any SumPhase lines (to eliminate multiple summing).

Here are the two functions that I am trying to use (one for subtotals and one for grand totals):


	VB:
	
 
    '   SumPhase UDF must be used on the same page As the StartOfItems 
    '   named range Is defined.  Anytime this Function Is recalculated 
    '   (which may be due To a change on another worksheet), the recalc 
    '   Is done on the same sheet As the StartOfItems named range. 
     
    Application.Volatile 
    Dim ItemsWS As Worksheet 
    Dim ItemCol As Long 
    Dim TypeCol As Long 
    Dim FirstRow As Long 
    Dim ThisRow As Long 
    Dim SumCol As Long 
    Dim PhaseRow As Long 
    Dim i As Long 
    Dim accum As Currency 
    Dim TypeCell As Range 
    Dim SumCell As Range 
     
    On Error Resume Next 
    Set HereWS = Here.Worksheet 
    Set ItemsWS = Range("StartOfItems").Worksheet 
    If ItemsWS Is Nothing Then 
        ' SumPhase won't work without StartOfItems defined range. 
        ' SumPhase = "#N/A!" 
        Exit Function 
    End If 
    ItemCol = Range("StartOfItems").Column 
    TypeCol = Range("StartOfTypes").Column 
    FirstRow = Range("StartOfItems").Row 
    ThisRow = Here.Row + Here.Rows.Count - 1 
    SumCol = Here.Column + Here.Columns.Count - 1 
    PhaseRow = 0 
    For i = ThisRow To FirstRow + 1 Step -1 
        Set ItemCell = ItemsWS.Cells(i, ItemCol) 
        If ItemCell.Text = "Text: Phase Begin" Then 
            PhaseRow = ItemCell.Row 
            Exit For 
        End If 
    Next 
    If PhaseRow = 0 Then 
        PhaseRow = FirstRow 
    End If 
    accum = 0 
    For i = PhaseRow To ThisRow - 1 
        Set TypeCell = ItemsWS.Cells(i, TypeCol) 
        Set SumCell = ItemsWS.Cells(i, SumCol) 
        If ItemType = "All" And TypeCell.Text  "Subtotal" And TypeCell.Text  "" Then 
            accum = accum + SumCell.Value 
        ElseIf ItemType = "Both" _ 
            And (TypeCell.Text = "Assembly" Or TypeCell.Text = "Material") Then 
                accum = accum + SumCell.Value 
            ElseIf ItemType = TypeCell.Text Then 
                accum = accum + SumCell.Value 
            End If 
        Next 
        SumPhase = accum 
    End Function 
     
    Public Function SumTotal(Here As Range) As Variant 
        '   SumTotal UDF must be used on the same page As the StartOfItems 
        '   named range Is defined.  Anytime this Function Is recalculated 
        '   (which may be due To a change on another worksheet), the recalc 
        '   Is done on the same sheet As the StartOfItems named range. 
         
        Application.Volatile 
        Dim ItemsWS As Worksheet 
        Dim ItemCol As Long 
        Dim TypeCol As Long 
        Dim FirstRow As Long 
        Dim ThisRow As Long 
        Dim SumCol As Long 
        Dim i As Long 
        Dim accum As Currency 
         
        On Error Resume Next 
        Set ItemsWS = Range("StartOfItems").Worksheet 
        On Error Goto 0 
        If ItemsWS Is Nothing Then 
            ' SumTotal won't work without StartOfItems defined range. 
            SumTotal = "#N/A!" 
            Exit Function 
        End If 
        ItemCol = Range("StartOfItems").Column 
        TypeCol = Range("StartOfTypes").Column 
        FirstRow = Range("StartOfItems").Row 
        ThisRow = Here.Row + Here.Rows.Count - 1 
        SumCol = Here.Column + Here.Columns.Count - 1 
        accum = 0 
        For i = FirstRow + 1 To ThisRow - 1 
            If ItemsWS.Cells(i, ItemCol).Text  "ST Phase Subtotal" _ 
            And ItemsWS.Cells(i, TypeCol).Text  "" Then 
                Set ItemCell = ItemsWS.Cells(i, SumCol) 
                accum = accum + ItemCell.Value 
            End If 
        Next 
        SumTotal = accum 
    End Function 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines


I have a workbook with several sheets in it, each one has a button which I
use to force recalculation of various things for a game based idea.

When I do Alt-F11 I see that I have the following code

Option Explicit
Private Sub CommandButton1_Click()
Application.Calculate
End Sub

but this appears under "VBAProject", "Microsoft Excel Objects" and then the
code is on each sheet that I have a button for, obviously

What I want to know is, can I retain the functionality I have BUT have it so
that if I press a button on say the "Solitaire" worksheet, it only
recalculates the stuff on that sheet

Actually the fact that the buttons recalculate all sheets is OK for all
buttons except one, so I guess what I really need is just a way to isolate
the one button on the one sheet, so that it only randomises/recalculates on
its own specific sheet

does that make sense?

tks in advance

Hi,

I have a user defined function in VB as follows

	VB:
	
 ABBB(TEST) 
     
     
    Calculates TEST fees 
    Const Tier1 = 0.0012 
    Const Tier2 = 0.0011 
    Const Tier3 = 0.001 
    Const Tier4 = 0.0009 
    Const Step1 = 500000000# 
    Const Step2 = 1000000000# 
    Const Step3 = 1500000000# 
    Select Case TEST 
    Case Is

Hi all,
I have a data workbook (values, and matrices) and a workbook does the
calculation (contains UDF). Calculation workbook with circular references is
linked to Data workbook.
When a value is changed in data workbook, the calculation workbook ALWAYS
returns a wrong answer (although the UDF is thoroughly tested and always
yields expected results). It can be corrected when the FIRST (only the
first) cell of the matrices is modified (even by adding 0 to the formula).
Once that problem is fixed, we can then change the value and it will always
returns the right value. Modifing non-leading cells on the matrix does force
recalculation, but it will always give wrong answer.
Does anyone have any idea on how to troubleshoot the problem?
Thanks

Hi all,

I posted this to no avail late June and now I'm in trouble with my project
and would be really keen for some help!

Summary of problem:
I have a spreadsheet with custom views.
When my users selects a custom view, the tab seems to do forced recalculate
INCLUDING externally links even if I said 'don't update' when i opened the
spreadsheet

This problem ruins my approach and I need to either:
a. confirm this is a bug in excel and find a work-around
or
b. confirm this is supposed to happen (I can't imagine why it should though)
and find a work-around

Any ideas gratefully received.

Thanks

-----------------------------------------
Detailed Background
I have some formulas in a tab looking up data from an external
workbook.

As usual, if the external workbook is open, there is no issue; but with
the source data workbook closed, I get the usual #N/A link

Nothing unusual so far

When I open up the workbook and say 'Dont update' to external links, as
expected, I see the last data that was in the spreadsheet when I
saved/closed it.

I can use the spreadsheet with no issues at all.

On the tab, there is a filter. I can change the filter, play around it
with, no issue

However, I have a custom view with filter settings saved in it.

When I select the custom view - BANG.
All the #N/As are back. For some reason, selecting the custom view
seems to be refreshing the data in my externally linked values

I created a function that reads data from other files and returns the result
in a cell. So, I would like to automatically force recalculation every time
my WB is openned.
How can it be done?
Tks.

I have a very simple function I created which is reproduced below. It
just starts from a specified cell and searchs down the column to find
the first number (as opposed to text or NA or something) and returns the
offset to that cell from the original one.

Function FirstNumberOffset(TopCell)
RowOffset = 0
While Not (IsNumeric(TopCell.Offset(RowOffset, 0)))
RowOffset = RowOffset + 1
Wend
FirstNumberOffset = RowOffset
End Function

I call it in the spread sheet as:

[ ] =FirstNumberOffset(C8)

I wrote it, and it worked as expected while the VBA editor was open and
I was debugging it. When I closed the editor, the function just sits
dormant and doesn't appear to execute. So then I hit the F9 key to
force recalculation of the worksheet and it still does not update. And
my calculation mode is set to "auto" anyhow.

However I find that if I select the cell that uses the function, and
touch it in some insignificant way, then hit Enter it will make the
function recalculate and produce the proper answer.

Of all my macros, functions and sheets, this is the only one that does
not seem to recalculate properly on it's own.

Any ideas?

Thanks...

Bill

Hi all,
I have a data workbook (values, and matrices) and a workbook does the
calculation (contains UDF). Calculation workbook with circular references is
linked to Data workbook.
When a value is changed in data workbook, the calculation workbook ALWAYS
returns a wrong answer (although the UDF is thoroughly tested and always
yields expected results). It can be corrected when the FIRST (only the
first) cell of the matrices is modified (even by adding 0 to the formula).
Once that problem is fixed, we can then change the value and it will always
returns the right value. Modifing non-leading cells on the matrix does force
recalculation, but it will always give wrong answer.
Does anyone have any idea on how to troubleshoot the problem?
Thanks

Hi all,

I posted this to no avail late June and now I'm in trouble with my project
and would be really keen for some help!

Summary of problem:
I have a spreadsheet with custom views.
When my users selects a custom view, the tab seems to do forced recalculate
INCLUDING externally links even if I said 'don't update' when i opened the
spreadsheet

This problem ruins my approach and I need to either:
a. confirm this is a bug in excel and find a work-around
or
b. confirm this is supposed to happen (I can't imagine why it should though)
and find a work-around

Any ideas gratefully received.

Thanks

-----------------------------------------
Detailed Background
I have some formulas in a tab looking up data from an external
workbook.

As usual, if the external workbook is open, there is no issue; but with
the source data workbook closed, I get the usual #N/A link

Nothing unusual so far

When I open up the workbook and say 'Dont update' to external links, as
expected, I see the last data that was in the spreadsheet when I
saved/closed it.

I can use the spreadsheet with no issues at all.

On the tab, there is a filter. I can change the filter, play around it
with, no issue

However, I have a custom view with filter settings saved in it.

When I select the custom view - BANG.
All the #N/As are back. For some reason, selecting the custom view
seems to be refreshing the data in my externally linked values

Hi, does anybody know how to force recalculation in VBA without having data tables recalculated as well ? if i simply include application.calculate in the macro code , excel updates also tables ...

Thanks

Paolo

Hi all, new guy on this forum, but have used the search resource pretty extensively to no success

I have a (A) master summary excel 2010 (xlsx) worksheet linking 29405 individual cells to about (B) 34 individual reporting worksheets (xlsx). On each of these (B) individual worksheets, they each link to (C) multiple testing worksheets in xls format.

In general, it's info feeding from (C)->(B)->(A). All the information feeding from (C)->(B) link fine and are updated automatically. However, I have seemingly random fields not being updated in my master summary (A), which link to only cells in (B).

I understand that it's a reference of a reference, but it'd make a lot more sense if all weren't being updated on (A) vs. some.

I've checked my settings and everything is set to auto calculate and I've tried to force recalculate (alt + ctrl + f9) without success. When I do some tests to link (A) directly to (C), it works fine, but this would prompt me to update many cells that break the methodology I'm trying to set with these 3 different kinds of spreadsheets.

The 1 suspicion I have while doing some investigation is that users are taking copies of (C), putting them on their desktop, manipulating it, then reuploading to the shared drive. The (B) worksheets still are reporting accurately though regardless of whether or not they've done this.

*edit* - I determined that while (B) will update fine, if I save (B) with the most up to date values, and close it (or leave it open), (A) will be updated. This tells me that for some reason, (A) is not checking the latest saved record for (B)?

Any insight would be much appreciated.

Hi,

Sheet1, Col. A has "Auto-Filter".

The cells were C.F., by 2 different Interior colors, as per Even/Odd rows.

So far so good.

The problem starts when the list is filtered (loosing the requested format).

I'm looking for a solution that will keep the same format when the range is filtered/not filtered.

Cell E1 was added in order to force recalculation because I didn't find a filtering event.

I hope I made myself clear.

Thanks, Elm

I have a worksheet that has cells reading from other worksheets over a network. Is there a way to "force" recalculation based on changes made in the other linked worksheets without having to close and reopen the original worksheet? Thanks!

I have a very simple function I created which is reproduced below. It
just starts from a specified cell and searchs down the column to find
the first number (as opposed to text or NA or something) and returns the
offset to that cell from the original one.

Function FirstNumberOffset(TopCell)
RowOffset = 0
While Not (IsNumeric(TopCell.Offset(RowOffset, 0)))
RowOffset = RowOffset + 1
Wend
FirstNumberOffset = RowOffset
End Function

I call it in the spread sheet as:

[ ] =FirstNumberOffset(C8)

I wrote it, and it worked as expected while the VBA editor was open and
I was debugging it. When I closed the editor, the function just sits
dormant and doesn't appear to execute. So then I hit the F9 key to
force recalculation of the worksheet and it still does not update. And
my calculation mode is set to "auto" anyhow.

However I find that if I select the cell that uses the function, and
touch it in some insignificant way, then hit Enter it will make the
function recalculate and produce the proper answer.

Of all my macros, functions and sheets, this is the only one that does
not seem to recalculate properly on it's own.

Any ideas?

Thanks...

Bill

Hello everyone

Whilst on Sheet 1, I set the zoom property of Sheet 2 with:


	VB:
	
Sheet2.PageSetup.Zoom = 44 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
If I go to sheet two and hit print preview, excel appears to calculate its automatic pagebreaks at that point and Sheet 2 then prints as expected.

But Excel doesn't seem to recalculate them until I hit print preview. I want to find a way to update the automatic page breaks without doing this.

I thought if I did the following it would force excel to calculate them, but no:


	VB:
	
 
Sheet2.DisplayPageBreaks = False 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
And I found on another forum post that somebody had the same problem (see here - http://www.ozgrid.com/forum/showthre...ic+page+breaks), which they solved by using:

	VB:
	
ActiveWindow.View = xlPageBreakPreview 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
But my problem is I'm on Sheet1 setting the zoom of Sheet2, so I can't use activewindow.view as a solution.

I want to set the zoom and update the page breaks of Sheet 2 without the user noticing whilst they remain on Sheet1.

Really appreciate any help guys and girls.

Thank you,

Cnsleepy

Hello again,

I have an interesting question for everybody here. Let me see how to best explain it.

I start in the code making sure column Action Plan completion has a value in it. Copying in the issue modified column if neccesary.

	VB:
	
 'I need to start with the first data row (3) and the action plan completion column
Let intcurrentrow = 3 
Let intcurrentcol = intactionplancompletion 
Do Until intcurrentrow > intmaxrow 
    If Cells(intcurrentrow, intcurrentcol).Value = "" Then 
        Let Cells(intcurrentrow, intcurrentcol).Value = Cells(intcurrentrow, intissuemodified).Value 
         'Need to ensure that everything is formatted as a date
        Let Cells(intcurrentrow, intcurrentcol).NumberFormat = "m/d/yy h:mm AM/PM" 
    End If 
    Let intcurrentrow = intcurrentrow + 1 
Loop 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
Later, I place this formula in a given cell.

	VB:
	
(I9="Closed-Remediated and Validated",M9-K9,"" )) 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
The info I copied above is placed into the column K.

When I run the macro, the cell formula outputs "#value".

However, if I go double-click then enter the cell calculates just fine.

I've tried excels overall recalculate function, but that doesn't change this.

Does anybody know how to simulate a double-click on a cell?

Any other suggestions?

Thanks,

Tom