MS Office Forum

Ask Question   UnAnswered
Home » Forum » MS Office       RSS Feeds

2003 - ActiveSheet.Calculate Problem

  Asked By: Lewis    Date: Jan 30    Category: MS Office    Views: 2854

Now we come to the tricky bit... Which I hope isn't coz I've a feeling I'm
missing something simple here.

I'm changing the ColumnWidth value of a cell in a loop.
Every time I change tha value I do an ActiveSheet.Calculate.
In another cell I have the function below.

The value in the cell with the function isn't being updated!

Any ideas why??

I have screenupdating set explicitly to True.

Function fncColumnWidth(Optional spUnits As Variant)

Dim slUnits As String

If IsMissing(spUnits) Then
slUnits = ""
slUnits = UCase(spUnits)
End If

Select Case slUnits
Case ""
fncColumnWidth = Range(Application.Caller.Address).ColumnWidth
Case "PTS"
fncColumnWidth = Range(Application.Caller.Address).Width
Case "CM"
Case "MM"
fncColumnWidth = Range(Application.Caller.Address).Width *
End Select

End Function



4 Answers Found

Answer #1    Answered By: Alyssa Kelley     Answered On: Jan 30

I'm having trouble understanding your explanation. There is no
ActiveSheet.Calculate in the code you've quoted.

> I'm changing  the ColumnWidth value of a cell  in a loop.
> Every time  I change  that value I do an ActiveSheet.Calculate.
> In another cell I have the function  below.

Do you mean the value of the cell or the width of the cell? Changing the
width of a cell will not make it "dirty". Therefore it won't necessarily
participate in a calculate. (Calculate only recalculates what needs it.)

In your function, one of the Select legs has

Range(Application.Caller.Address).Width *

and the other legs are similar, but simpler.

These will do what you expect them to do - i.e. they will use the current
values of those items when the function runs.

But is the function running?

A change to either of those two cells will not trigger the running of the
function. It will only run if the cell that includes the function call is
directly referencing a cell whose value has changed - e.g. the cell that
provides spUnits.

We probably need more information, though.

Answer #2    Answered By: Abejundio Garcia     Answered On: Jan 30

You can work around this:

add another optional parameter to your function  - you don't need to use it!

where the function is referenced in the spreadsheet enter a cell  reference
which will change  as the second parameter - like


then the function will fire every time  a cell in the range changes.

Answer #3    Answered By: Uma 1985     Answered On: Jan 30

True but ...

Changing the width of a column isn't a change  to the cell, so the function
won't be called merely for that.

Answer #4    Answered By: Angelina Gardner     Answered On: Jan 30



we need to have a cell  with the formula =CELL("width",F1) (assuming col F is
the one being altered). Then we make the formula additional parameter refer
to this cell.

The cell can have white text if you Lisa does not want the width shown.

Didn't find what you were looking for? Find more on 2003 - ActiveSheet.Calculate Problem Or get search suggestion and latest updates.