Workbook Open

[Karen53]

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

 

[Dave Peterson]

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]

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.

 

[Dave Peterson]

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.

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.

 

[Karen53]

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 Tools>Addins.

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.

 

[Dave Peterson]

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.

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 Tools>Addins.

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.

 

[Karen53]

Hi Dave,

Do you feel this is most likely coming from VBA Code or a formiula on a
worksheet?

LOL, forget the Mad Cow! Right now I wish Scotty could beam me up!

I'm out of time. I've got to have this finished and I have absolutely no
idea how to find this problem. It is majorly impacting the workbooks. Are
there any other resources or options that I may not know about? What does
one do when you can't find the problem?

What I need is a tutor. Someone who can find the problem, help me fix it
and show me so I can learn from it.

Any suggestions?

 

[Dave Peterson]

The times I've seen the calls to the analysis toolpak functions cause the
problem you see is when the function is called via VBA (I think???).

I'm sure that there are billions and billions of other resource, but I don't
have any other specific suggestions.

Sorry.

Hi Dave,

Do you feel this is most likely coming from VBA Code or a formiula on a
worksheet?

LOL, forget the Mad Cow! Right now I wish Scotty could beam me up!

I'm out of time. I've got to have this finished and I have absolutely no
idea how to find this problem. It is majorly impacting the workbooks. Are
there any other resources or options that I may not know about? What does
one do when you can't find the problem?

What I need is a tutor. Someone who can find the problem, help me fix it
and show me so I can learn from it.

Any suggestions?

 

[Karen53]

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.

 

[Dave Peterson]

Glad you found the answer. And thanks for posting back.

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.