Hi Dave,
I thought you might like to know what the cause was.
The Worksheet Calculates all running on workbook open was formuluas using
the 'Indirect' function. I had removed them to test but I had a wayward one
I didn't realize was there. I've removed them and this has stopped.
The screenupdating changing to True after the coded 'Replace' statement was
caused because the 'Replace' functions generated a 'hard' change on the
worksheet triggering the worksheet change rather than the worksheet
calculate. Once I removed the screenupdating code from worksheet change,
this was resolved.
--
Thanks for your help.
Karen53
"Dave Peterson" wrote:
If the analysis toolpak isn't checked, then you're not using it. So I don't
think I'd spend much time going down that road.
But I still don't have a better guess.
Karen53 wrote:
Hi Dave,
Thank you so much for those links!
I've opened and closed excel several times and opened the workbook several
times. Analysis Toolpack is not check under ToolsAddins.
Offhand I didn't recognize any of the functions they show as toolpack. I
tried one they said was toolpack =iseven(Number). It errored out with #Name.
Tomorrow I'll make a list of absolutely every funciton I use in this thing.
--
Thanks for your help.
Karen53
"Dave Peterson" wrote:
This is my guess...
It's not the =replace() function itself that's causing what you see. You're
changing the formula in a cell. That means that every formula that depends on
that cell is going to recalculate.
And if there's a function from the analysis toolpak addin in your workbook that
is in a cell that's recalculating, then that could be the problem.
If you look at Tools|Addins, you'll see if you have the analysis toolpak addin
checked. If that's unchecked, then the formulas that rely on those functions
should break. (Maybe close excel and reopen, then reopen your workbook to see
it.)
There are a lot of functions in that analysis toolpak.
Peter Noneley's Excel Function Dictionary has lots of information about excel
functions:
http://www.xlfdic.com
Norman Harker has his version at Debra Dalgleish's site:
http://www.contextures.com/functions.html
Norman's looks like a more complete list.
Karen53 wrote:
Hi Dave,
Let me see if I understand correctly. It's not the 'replace' command itself
but the fact that 'replace' changes the formula causing this toolpak entity
to recalc?
I know nothing about the analysis toolpack. Is there something online that
lists what is in it so I know what I am looking for? Is it like a group of
commands, something I might have called not knowing it's toolpack? I tried a
help search in VBA but the toolpack does not come up.
--
Thanks for your help.
Karen53
"Dave Peterson" wrote:
I'm not sure if it's really the =replace() worksheet function.
I tried this simple test:
Option Explicit
Sub testme()
Dim myCell As Range
Application.ScreenUpdating = False
Debug.Print "After step 001: " & Application.ScreenUpdating
Set myCell = ActiveSheet.Range("A1")
Debug.Print "After step 002: " & Application.ScreenUpdating
myCell.Formula = "=REPLACE(B1,1,3,""xx"")"
Debug.Print "After step 003: " & Application.ScreenUpdating
Application.ScreenUpdating = True
Debug.Print "After step 004: " & Application.ScreenUpdating
End Sub
And got:
After step 001: False
After step 002: False
After step 003: False
After step 004: True
But my guess is that you have something from the analysis toolpak on that sheet
(or in that workbook) that recalcs after you change the formula. And that call
to the analyis toolpak turns on .screenupdating.
Your next challenge <vbg is to search for that offending "something".
Karen53 wrote:
Hi Dave,
I discovered this while trying to trouble shoot something else. Earlier in
this post we were talking about volatile code. I've found "Replace" in the
code changes the screenupdating status to True.
Sub AddFormulaLinks(ShName, NextRow)
' add the formulas to link Main Page to the new sheet
Debug.Print "Starting AddFormulaLinks " & Application.ScreenUpdating
Dim iCtr As Long
Dim Cell As Range
Sheets(ShName).Activate
With Sheets(ShName)
'Unit Status Current? from Main Page =IF(ISBLANK('Master
Page'!$B$16),"", 'Master Page'!$B$14)
With .Range("B1")
.HorizontalAlignment = xlCenter
Debug.Print "AddFormulaLinks center " &
Application.ScreenUpdating
.WrapText = False
Debug.Print "AddFormulaLinks WrapText " &
Application.ScreenUpdating
.NumberFormat = "General"
Debug.Print "AddFormulaLinks No Format General " &
Application.ScreenUpdating
.FormulaR1C1 = "=If(ISBLANK('" & Replace(MainPagepg.Name, "'",
"''") & "'!R" _
& NextRow & "C2) ,"""", '" &
Replace(MainPagepg.Name, "'", "''") & "'!R" & NextRow & "C2)"
Debug.Print "AddFormulaLinks Replace " &
Application.ScreenUpdating
End With
Immediate window:
AddSheet call AddFormulaLinks False
Starting AddFormulaLinks False
AddFormulaLinks center False
AddFormulaLinks WrapText False
AddFormulaLinks No Format General False
AddFormulaLinks Replace True
--
Thanks for your help.
Karen53
"Dave Peterson" wrote:
I don't know. I exhausted my calculation expertise when I posted that link to
Charles Williams's web site.
Sorry.
Karen53 wrote:
Well, I just don't know what else to try. I can't find what on this page is
triggering worksheet Calculate to run twice on workbook open. Nothing
changes. I've checked for volatiles. I do use Indirect, but a lot more than
twice. Wouldn't it run once for each time I use it? I tried removing them
but it didn't make a difference. It wouldn't be so bad if it only ran once,
but twice?
There are no cells containing conditional formating, no autofilter.
I do use index but I'm in 2003 so it should no longer be volatile according
to www.decisionmodels.com. I use it many times. Again, wouldn't it run once
for each time I use it?
--
Thanks for your help.
Karen53
"Karen53" wrote:
Hi Gord,
Never mind. The sheet was protected. The result is no conditional
formating. Back to my search...
--
Thanks for your help.
Karen53
"Gord Dibben" wrote:
F5SpecialConditional Formats
Gord Dibben MS Excel MVP
On Tue, 19 Feb 2008 10:41:01 -0800, Karen53
wrote:
Is there
a way to locate any conditional formating on a worksheet?
--
Dave Peterson
--
Dave Peterson
--
Dave Peterson
--
Dave Peterson