Forcing a function to calculate

[Floyd]

What is the syntax to force a function to calculate from another sheet?

Function Test()

End Function

Worksheets("Sheet1").Test.Calculate

This won't work, but I think I am getting close.

Thanks in advance.

Floyd

 

[Tom Ogilvy]

Worksheets("Sheet1").Calculate

If you want to force an individual cell to calculate and Excel doesn't think
it needs to be calculatee

With worksheets("Sheet1").Range("B9")
.Formula = .Formula
End with

would be one way if the formula is in B9 of sheet1.

 

[Guest]

are you talking about you want to calculate that worksheet or you want to run
that function from VBA?
ben

 

[Fredrik Wahlgren]

What is the syntax to force a function to calculate from another sheet?

Function Test()

End Function

Worksheets("Sheet1").Test.Calculate

This won't work, but I think I am getting close.

Thanks in advance.

Floyd

Test is a user defined function, right? You only need to add one line of
code to the function, like this:

Function Test()
Application.Volatile True
End function

"Test" will now be recalculated whenever the sheet is reaclculated.

/Fredrik

 

[Floyd]

All:

I have never seen anything like this.

I have all of the following commands in the function:
Application.Volatile True
Application.Calculate
Application.Calculation = xlCalculationAutomatic

I have verified that the function will compile and that
Tools-Options-Calculation-Automatic is setup.

I have tried the code segment suggested by Mr. Ogilvy in this post and
still none of this works.

The only thing that works is to open Sheet1 and hit F9. Only then will
it calculate the function.

I spent the weekend reading/searching this group's archives. Although
I found quite a bit of interesting material, I did not find a solution.

Could this be a bug in Excel?