How to tell Excel to recalc, unconditionally, now?

It's easy to invalidate an Excel 2002 workbook, putting it into a
recalc-required state, yet Excel fails to do it.

For example, I have a worksheet with thousands of UDF calls
(=MyUDF(Param)). If I make an edit whose only impact is to change the
value(s) of the UDF params, that kind of edit seems too oblique for
Excel and it won't recalc.

(Or if calc is set to Manual, the Calculate flag doesn't appear, so
pressing F9 does nothing. Nor does toggling Manual/Automatic calc.
Even if you set calc to Automatic, save and reopen the WB, it stays
UNCALC'ed.)

Yes, I know I could, say: Open the VBA editor, edit some code
(introduce a syntax error, say), exit, recalc and invoke the error,
reopen VBA, correct it, exit, and recalc again.

But that kludge is not an answer I, or any user, will accept.

So where's the Excel user instruction to recalc *unconditionally,
now*?

Thanks much.

***
0
baobob (32)
6/30/2008 6:52:58 AM
excel.newusers 15348 articles. 2 followers. Follow

2 Replies
587 Views

Similar Articles

[PageSpeed] 35

Ctrl-Alt-F9?

-- 
__________________________________
HTH

Bob

<baobob@my-deja.com> wrote in message 
news:3e223f4e-2586-4519-adc2-2206d045a906@r66g2000hsg.googlegroups.com...
> It's easy to invalidate an Excel 2002 workbook, putting it into a
> recalc-required state, yet Excel fails to do it.
>
> For example, I have a worksheet with thousands of UDF calls
> (=MyUDF(Param)). If I make an edit whose only impact is to change the
> value(s) of the UDF params, that kind of edit seems too oblique for
> Excel and it won't recalc.
>
> (Or if calc is set to Manual, the Calculate flag doesn't appear, so
> pressing F9 does nothing. Nor does toggling Manual/Automatic calc.
> Even if you set calc to Automatic, save and reopen the WB, it stays
> UNCALC'ed.)
>
> Yes, I know I could, say: Open the VBA editor, edit some code
> (introduce a syntax error, say), exit, recalc and invoke the error,
> reopen VBA, correct it, exit, and recalc again.
>
> But that kludge is not an answer I, or any user, will accept.
>
> So where's the Excel user instruction to recalc *unconditionally,
> now*?
>
> Thanks much.
>
> *** 


0
BobNGs (423)
6/30/2008 7:22:10 AM
Hi,

Actually Excel has 4 non-VBA ways to recalc a spreadsheet, you could try any 
of these and see if they work:

F9 (you've already tried this one)
Shift+F9
Ctrl+Alt+F9
Shift+Ctrl+Alt+F9

No kidding!
-- 
Cheers,
Shane Devenshire


"baobob@my-deja.com" wrote:

> It's easy to invalidate an Excel 2002 workbook, putting it into a
> recalc-required state, yet Excel fails to do it.
> 
> For example, I have a worksheet with thousands of UDF calls
> (=MyUDF(Param)). If I make an edit whose only impact is to change the
> value(s) of the UDF params, that kind of edit seems too oblique for
> Excel and it won't recalc.
> 
> (Or if calc is set to Manual, the Calculate flag doesn't appear, so
> pressing F9 does nothing. Nor does toggling Manual/Automatic calc.
> Even if you set calc to Automatic, save and reopen the WB, it stays
> UNCALC'ed.)
> 
> Yes, I know I could, say: Open the VBA editor, edit some code
> (introduce a syntax error, say), exit, recalc and invoke the error,
> reopen VBA, correct it, exit, and recalc again.
> 
> But that kludge is not an answer I, or any user, will accept.
> 
> So where's the Excel user instruction to recalc *unconditionally,
> now*?
> 
> Thanks much.
> 
> ***
> 
0
7/1/2008 1:28:00 AM
Reply:

Similar Artilces:

Excel Stock List
Hi all Im trying to make a stock list using excel that shows Product, Product Code, Amount in Stock and Minimum Stock to Keep. What i want to do is when someone takes some stock and reception remove it from the Amount in Stock list, as soon as the Amount in the Stock List matches the amount in the Minimum Stock to Keep column a pop up appears and tells us we need to re-order more stock of the item that is running low. Anyone know how i would go about doing this? many thanks in advance Matt Send me a small file showing the layout of your data and I'll help you develop this thin...

V lookup in Excel 2003
Hello, I have recently installed fresh copy of Office 2003, where I am unable to find Vlookup function, (I couldnt see it in pop up also while I click on fx sign too....) Please help me on this issue as soon as possible, as heaps of work is pending on my desk due to that. Mail me at baroda@outsourcingindiainc.com Thanks What happens if you just try using the function by typing it in? -- Regards Ken....................... Microsoft MVP - Excel Sys Spec - Win XP Pro / XL 97/00/02/03 -------------------------------------------------------------------------...

Excel 2000 #24
When I left click in a cell and then move my mouse, it highlights all cells and I can't click on any menu items or anything. The only way to close Excel is to go to task manager and end task. Help please! I've seen this type of thing happen with a mouse with a scroll wheel. Sometimes the scroll wheel gets 'stuck'. Press down on the scroll wheel a couple of times to see if this clears up the problem. Gary >-----Original Message----- >When I left click in a cell and then move my mouse, it >highlights all cells and I can't click on any menu items >o...

In Excel
I want to effect a mass change to the sign of numeric values in a range of cells (rows and columns). put -1 in an empty cell, copy it, select the range of cells to be modified, select Paste Special and pick Multiply. MaryKaye wrote: > I want to effect a mass change to the sign of numeric values in a range of > cells (rows and columns). Put -1 in an un-used cell and copy it. Then select the cells you want to update and: Edit > PasteSpecial > multiply -- Gary''s Student - gsnu201001 "MaryKaye" wrote: > I want to effect a mass ch...

Excel and Notepad: how avoid additional inverted commas after copy and paste?
Hi, If I write a multi-line text in a cell (then go down with alt+enter) and after copy the cell pasting on Notepad, it display before inverted commas and after textual content. Instead, if I select directly the content from the formula bar, it isn't happen. Do exist a way for copy and paste directly from the cell without select from the formula bar? Maybe with a macro? Thank Marco __________________ http://www.idee-regalo.biz/catalogo-stereogrammi-3.html http://www.ghisirds.it/ http://scuo.la/ http://www.righettofabrizio.com/pantografo-taglio-plasma.html I used the PutOnClipboard rout...

smartlist export to excel with large record sets
I read with interest a posting on 2/19/07 and the responses. The issue raised was how long it took to export a large record set (20,000 records) from Smartlist to Excel. I wish that speed was my only problem. In our case, when attempting to export as few as 2000 smartlist records, the computer freezes, and both excel and GP have to be restarted. We are using GP version 9 and Excel 2003. Similar thing happened with GP 8, and happens on other workstations, not just mine. Woudl really appreciate some pointers. thanks. Your problem is probably memory. First RAM should be 2 gig or b...

Multiple Instances of Excel on 64-bit Win7
Cross Posted: microsoft.public.windows.64bit.general microsoft.public.excel (NOTE: This is not a question about getting each Excel file to open in a new instance. Please read carefully) Hello all, On my old system (WinXP Pro, Excel 2003, and Excel 2007): - If no instances of XL are open and I open an XL file from Win Explorer, XL would open (don't recall if it is same version every time or if it would switch), and then the file would open. - If an instance of XL is open and I open an XL file from Win Explorer, the file would be opened in the existing instance of XL. - If a...

Excel: How do I set up a cell to click it to link a database?
I keep track of pricing info in a spreadsheet. I would like to "click on a cell" to be directed to somewhere that I can keep a (purchsase) history attached to that cell. Any ideas how I might be able to do that? in the following, the counter - which determines the row the historical data is entered - is maintained in cell (1,30). The data you wish to periodically record is in cell (10,1). when you run this macro, the data is recorded in the next cell of row 3. :-) Sub recordhistory() etcount = Cells(1, 30).Value etcount = etcount + 1 Cells(1, 30).Value = etc...

Format date in excel 2000 like '31st January 2005' #6
Trying to set up field in Excel Data Source file so that the current date can be input in letters typed in this format '31st January 2005' Is it possible or am I stuck with dd/mmm/yy format? Sorry for the repeated posts but I received a message that the system was unable to send post. Can any unanswered ones be deleted please. "Malcolm Agingwell" wrote: > Trying to set up field in Excel Data Source file so that the current date can > be input in letters typed in this format '31st January 2005' > > Is it possible or am I stuck with dd/mmm/yy format?...

Function Macros inspired by the recent "date in excel" thread.
I vaguely remember the use of function macros from an early version of Excel. The date in excel thread reminded me of that because that might offer a simple macro with programming not much different from just writing a spreadsheet. IIRC this mini-spreadsheet could use data from the calling spreadsheet to control its calculation. IIRC, the function macro function is called with arguments just like an ordinary Excel function is called. The return value or values of the function is just what you can create using spreadsheet statements. For example, suppose you have a starting point A and ...

Method to Calculate APR in Excel?
Any suggestions or solutions would be appreciated. Thanks & regards, PJF Your question is very broad - more detail would have helped, with an example Here is one site that might help http://banking.about.com/od/loans/a/calculateapr.htm best wishes -- Bernard V Liengme Microsoft Excel MVP http://people.stfx.ca/bliengme remove caps from email "PJF" <pjf10@earthlink.net> wrote in message news:uz7dOTJDKHA.5780@TK2MSFTNGP03.phx.gbl... > Any suggestions or solutions would be appreciated. > > Thanks & regards, > > PJF > > "PJF" <pj...

Count Images in an Excel Sheet
I have saved excel sheets with screen prints in it. I need to know if I can get the total number of screen shots in a particular excel sheet. Try: Sub HowMany() MsgBox ActiveSheet.Shapes.Count End Sub -- Gary''s Student - gsnu201001 "Pankaj" wrote: > I have saved excel sheets with screen prints in it. I need to know if I can > get the total number of screen shots in a particular excel sheet. > PicCount = Activesheet.Pictures.Count Regards, Peter T "Pankaj" <Pankaj@discussions.microsoft.com> wrote in message ...

Excel Workspace
Is there a way to cause Excel to display multiple rows of worksheet tabs at the bottom of the excel workspace rather than just one? When a workbook has many worksheets it would be easier to switch between different worksheets if they were visable. I am aware that right clicking on the worksheet scroll bar displays a list of the worksheets and that I can drag the scroll bar on the lower right side of the workspace to the right to allow more woorksheet tabs to be displayed but these solutions don't really meet my needs. Thanks to anyone that can help. Marc Marc, Try Right Click...

extra quotation marks when excel ws is saved as txt file
I am trying to automate a process that creates a txt file needed for an application. However, this text files needs a set of double quotations, and also has some cells that will have commas. When I save the excel worksheet that contains this information, the text file ends up with this """abcd""" instead of the original "abcd". Any cell with commas is found in the text file with double quotes surrounding it as well. Is there anyway to save the excel worksheet as a text file and receive exactly what is entered into the cells? Note: I am a...

Trust access to the VBA project: Excel bug?, workaround required.
I'm fairly sure this is a VBA bug (comments welcome) and need a workaround. In Excel 2007 'm using the the function below in an add-in to return the access oft the VBA project. It appears to work correctly. Private Function IsVBProjectAvailable() As Boolean Dim lngAccessTest As Long On Error Resume Next lngAccessTest = ThisWorkbook.VBProject.VBComponents.Count If lngAccessTest > 0 Then IsVBProjectAvailable = True Else IsVBProjectAvailable = False End If End Function ....UNTIL you open a workbook containing macros. At ...

Using C# to read mass data from Excel 2003
Hi all�� I am using C# to read data from Excel 2003, However, I found the method I taken is too inefficient. Read a 250X250 sized matrix will take several minutes. May u provide me some high efficient method to solve mass data reading problem��Thanks int ColNum = mySheet.UsedRange.Columns.Count; int RowNum = mySheet.UsedRange.Rows.Count; Range range = mySheet.get_Range(mySheet.Cells[1, 1], mySheet.Cells[RowNum, ColNum]); ... for (int i = 1; i <= RowNum; ++ i) { DataRow dr; dr = dt.NewRow(); for (int j = 1; j <= ColNum; ++ j) { dr[j - 1] = ((...

Re : Excel Workbook Events Initiated by Cut-and-Paste Operations
Re : Excel Workbook Events Initiated by Cut-and-Paste Operations 1. Open an Excel workbook and deploy the following event handlers in the code module of ThisWorkbook :- a. Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range) Application.Enableevents = False MsgBox "Selection Change" [Other Code Statements] Application.Enableevents = True End Sub b. Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range) Application.Enableevents = False MsgBox "Sheet Change" [Other Code Statements] Application.Enableevents = True End...

Frequency for Histograms in Excel
I am using Excel 2002. There is a problem with the frequency function, which is used for histograms. The frequency counts for some (not all) bins is incorrect. The case that I learned of in another posting involves 101 data values: 0, 10, and values in between that are created by using an increment of 0.1. The frequency value for the first bin is (correctly) 11, and most of the other bins have the correct frequency (10). However, the bin representing the interval from 1 to 2 has a frequency of 9, which the bin representing the interval from 4 to 5 has a frequency of 11! I have al...

Saving in PDF format -excel
Can I save excel files in PDF format - there is no PDF file option in the" save as type" drop down list "Helen" <anonymous@discussions.microsoft.com> wrote... >Can I save excel files in PDF format - there is no PDF file option in the" >save as type" drop down list Microsoft hasn't bothered to implement PDFs as an output file format. There are a few alternatives. First, there are some free or cheap PDF file writers that can be used as printers (as can Adobe's full Acrobat product, which isn't the same as Acrobat Reader). Or you could downl...

Excel object model reference no longer available?
In the last couple of days I've been unable to display the normal detail information about object properties, methods and events via Excel VBA help. For example, when I click on Worksheet > Properties to see the property members, I just get a blank screen. If I try to access Worksheet > Properties in the help bookmark pane I get a message that says simply No results available. I've tried this on several machines all running Office 2007, some under XP Pro and one under W7. Same result. I don't see any other posts on this, so I don't know what to think......

Axis Labels in Excel 2007
Hello, Don't know what I've done wrong, but the primary axis labels for one of my charts is sitting right beside the secondary axis labels and I can't get them to move back over to the other side! Please help. Not sure exactly what you mean, but if you select and right click on the labels, it gives you a menu of options where you can pick "Format Legend" and then you have top, bottom, left, right and a few others that you can try to move them. (This is in Excel 2007, I would imagine it's similar in others) You can also just select the labels and drag them around t...

How do I set up my ODBC Connection from AS/400 file in Excel 2003
In Office 97, I was able to query an AS/400 file that was setup in my ODBC connections, but in OFfice 2003, I'm having extreme difficulties wih the data. Don't know what I'm doing wrong, or why it is so different than the old version ...

Opening Excel from the browser
I want to ensure that a link to an excel file will always open the file in the application, not in the browser. Is there a way of ensuring this without bhaving to adjust browser or registry settings on the PC? Thanks I think that this is a user setting controlled by that registry setting. Stuart wrote: > > I want to ensure that a link to an excel file will always > open the file in the application, not in the browser. Is > there a way of ensuring this without bhaving to adjust > browser or registry settings on the PC? > > Thanks -- Dave Peterson ec35720@msn.co...

Combo Boxes Hyperlinked to Worksheets in EXCEL
Hi, I am confused about this. How do I create a combo box on the first excel sheet, with hyperlinks which will take the user to another worksheet in the same workbook? Please advise.I need help soon! Regards Sunil The cell reference for the combo box is a number. To this number (Cell X) use this formula: =VLOOKUP(Cell X,Table,2) Where Table is 2 columns by several rows, depending on how many value you have in your drop down list. The first column lists 1, 2, 3... th second gives the Path Name to where the hyperlink would take you. This formula =VLOOKUP(Cell X,Table,2) works out what sh...

how do i recover a forgotten password in excel?
I set a password in excel 2007 to protect my sheet from changes. I've forgotten the password, how can I recover it in order to edit the sheet? look here: http://www.mcgimpsey.com/excel/removepwords.html -- Kind regards, Niek Otten Microsoft MVP - Excel "Larry" <Larry@discussions.microsoft.com> wrote in message news:0B6890AE-35D0-4519-908E-C395F693C5CD@microsoft.com... >I set a password in excel 2007 to protect my sheet from changes. I've > forgotten the password, how can I recover it in order to edit the sheet? ...