Solved: Function Won't Execute On Spreadsheet

[Cyberdude]

The title is a bit misleading, because the following function will execute once:
[VBA]Function ShowCellFormat(CellAddr As String) As String 'Displays the number format string for the specified cell
ShowCellFormat = Range(CellAddr).NumberFormat
End Function[/VBA]

On the worksheet I have the following formula:
=ShowCellFormat("H4")
This is a nifty function when developing weird format code strings. When it executes, it displays the format string that has been assigned to the specified cell.
My problem is getting it to execute each time I change the format on cell "H4". I'm sure it's tied to the Volatile thing, but I can't seem to coax the system into doing a recalculate each time I change the format.
(Also, if the Preview is working, I can't make the "VBA" to work.)

[Zack Barresse]

Right after your "Function ShowCellFormat.." line, put this ..

[vba]Application.Volatile[/vba]

This will make it calculate everytime any other calculation takes place. Too many of these and you will greatly notice the performance of your spreadsheet change.

Also, be careful if using this and changing sheets and/or workbooks, as they are not set. If you want to use in the worksheet at all times (i.e. not call from VBE), use the Application.Caller method to define it's location. An example would look like ...

[vba]Function ShowCellFormat(CellAddr As String, _
Optional wsName As String, _
Optional wbName As String) As String
With Application.Caller
If wbName = "" Then wbName = .Parent.Parent.Name
If wsName = "" Then wsName = .Parent.Name
End With
ShowCellFormat = Workbooks(wbName).Sheets(wsName).Range(CellAddr).NumberFormat
End Function[/vba]

HTH

[Cyberdude]

Zack, you're not going to believe this, but I had the Volatile statement in there just like you said, and I took it out. Maybe I misread the Help, but I got the impression that "Volatile TRUE" is the default. I'm actually unclear about how to write the statement.
Application.Volatile TRUE
or
Application.Volatile(TRUE)
or just
Application.Volatile (with TRUE the default)??
Anyhow I put the statement back in, and now it seems to be working. I swear it wasn't working before. Pffft!

[Zack Barresse]

LOL! I hear ya. Done the same thing myself. It is (I believe) techically Application.Volatile = True. Application is the object, volatile is a method, and it has a Boolean value. Although I just drop the = True part, as it's implied (assumed). Probably shouldn't to be on the safe side, especially if that is done away with in future versions, but I'm lazy, what can I say.

[Cyberdude]

I guess I'm still unclear about one point. It was my understanding that functions are by default set to Volatile. If that's true, then why would you ever need to use the Application.Volatile statement, except perhaps to undo an Application.Volatile = False.

[Bob Phillips]

I guess I'm still unclear about one point. It was my understanding that functions are by default set to Volatile. If that's true, then why would you ever need to use the Application.Volatile statement, except perhaps to undo an Application.Volatile = False.

NO, they are not volatile by default, but they do automatically get invoked if they refer to a cell that has been changed when a recalculation fires in. Volatile is expensive, it means the function gets invoked in every recalcualtion, so False is the better deafult. That is also why it is better to avoid volatile functions, such as OFFSET, where possible.

BTW, I have a toolbar textbox that does a simialr thing as your function, it shows the format of the active cell. I find it amzingly helpful.

[Cyberdude]

Aha! So the default is NonVolatile. That explains a lot. I suppose I was confused by the fact that a non-Volatile function is invoked "if they refer to a cell that has been changed when a recalculation fires in". Thanx for clearing that up.

[Zack Barresse]

..That is also why it is better to avoid volatile functions, such as OFFSET, where possible.

Um, er, I wouldn't say that too loud, might bring over the giants. LOL! (Inside joke I guess.) I would use an example of a function such as NOW() instead of OFFSET. That's a freak (IMHO), and may get fixed down the road by MS.