Any way to recalculate automatically a user-defined function?


Posted by Tim Francis-Wright on March 10, 2000 3:00 PM

I have a large Excel 97 spreadsheet with a user-defined function to put information at the top
of each of the defined print ranges. I'm using
this instead of Page Setup because Page Setup is
dreadfully slow.

Public Function Blurb() As String
Blurb = Sheets("General").Range("Property").Text & " " & _
Sheets("General").Range("City").Text & ", " & _
Sheets("General").Range("State").Text & " " & _
ActiveWorkbook.FullName
End Function

The problem is that blurb() doesn't respond to
attempts even to manually recalculate. Only editing
the cell that makes the reference and evaluating
the function will make it reflect new values for the name of the workbook, or any of the ranges in the function definition. This is annoying, because I want to keep this, and other parts, of the workbook protected.

Is there any workaround besides using Page Setup?
I know that I can have my print macro unprotect a worksheet, evaluate the function, and then reprotect the worksheet, for each worksheet, but this lacks any semblance of elegance.

Posted by Ivan Moala on March 10, 2000 4:45 PM


Try putting Application.Volatile
After your Function eg

Public Function Blurb() As String
Application.Volatile

Ivan



Posted by Tim Francis-Wright on March 13, 2000 2:45 PM

Thanks! You saved me 3.2 truckloads
of aggravation!