Function Won't Calculate -- Sometimes
 

I have a very simple function I created which is reproduced below. It
just starts from a specified cell and searchs down the column to find
the first number (as opposed to text or NA or something) and returns the
offset to that cell from the original one.

Function FirstNumberOffset(TopCell)
RowOffset = 0
While Not (IsNumeric(TopCell.Offset(RowOffset, 0)))
RowOffset = RowOffset + 1
Wend
FirstNumberOffset = RowOffset
End Function

I call it in the spread sheet as:

[ ] =FirstNumberOffset(C8)

I wrote it, and it worked as expected while the VBA editor was open and
I was debugging it. When I closed the editor, the function just sits
dormant and doesn't appear to execute. So then I hit the F9 key to
force recalculation of the worksheet and it still does not update. And
my calculation mode is set to "auto" anyhow.

However I find that if I select the cell that uses the function, and
touch it in some insignificant way, then hit Enter it will make the
function recalculate and produce the proper answer.

Of all my macros, functions and sheets, this is the only one that does
not seem to recalculate properly on it's own.

Any ideas?

Thanks...

Bill

"Bill Martin -- (Remove NOSPAM from address)"
wrote in message ...
I have a very simple function I created which is reproduced below. It
just starts from a specified cell and searchs down the column to find
the first number (as opposed to text or NA or something) and returns the
offset to that cell from the original one.

Function FirstNumberOffset(TopCell)
RowOffset = 0
While Not (IsNumeric(TopCell.Offset(RowOffset, 0)))
RowOffset = RowOffset + 1
Wend
FirstNumberOffset = RowOffset
End Function

I call it in the spread sheet as:

[ ] =FirstNumberOffset(C8)

I wrote it, and it worked as expected while the VBA editor was open and
I was debugging it. When I closed the editor, the function just sits
dormant and doesn't appear to execute. So then I hit the F9 key to
force recalculation of the worksheet and it still does not update. And
my calculation mode is set to "auto" anyhow.

However I find that if I select the cell that uses the function, and
touch it in some insignificant way, then hit Enter it will make the
function recalculate and produce the proper answer.

Of all my macros, functions and sheets, this is the only one that does
not seem to recalculate properly on it's own.

Any ideas?

Thanks...

Bill

Use Application.Volatile (True) like below:

Function FirstNumberOffset(TopCell)
Application.Volatile (True)
RowOffset = 0
While Not (IsNumeric(TopCell.Offset(RowOffset, 0)))
RowOffset = RowOffset + 1
Wend
FirstNumberOffset = RowOffset
End Function

/Fredrik

Bill Martin -- (Remove NOSPAM from address) wrote:
I have a very simple function I created which is reproduced below.

Sorry -- I've resolved my problem. All appears to be well now.

Bill

Fredrik Wahlgren wrote:


Use Application.Volatile (True) like below:

Function FirstNumberOffset(TopCell)
Application.Volatile (True)
RowOffset = 0
While Not (IsNumeric(TopCell.Offset(RowOffset, 0)))
RowOffset = RowOffset + 1
Wend
FirstNumberOffset = RowOffset
End Function

/Fredrik

----------

You're right - that seems to fix it. It's much more elegant than the
solution that I stumbled upon which was to include a call parameter that
contains an entire range of cells so if any of them change, then Excel
knows to recalculate the function.

Thanks...

Bill

"Bill Martin -- (Remove NOSPAM from address)"
wrote in message ...
Fredrik Wahlgren wrote:


Use Application.Volatile (True) like below:

Function FirstNumberOffset(TopCell)
Application.Volatile (True)
RowOffset = 0
While Not (IsNumeric(TopCell.Offset(RowOffset, 0)))
RowOffset = RowOffset + 1
Wend
FirstNumberOffset = RowOffset
End Function

/Fredrik

----------

You're right - that seems to fix it. It's much more elegant than the
solution that I stumbled upon which was to include a call parameter that
contains an entire range of cells so if any of them change, then Excel
knows to recalculate the function.

Thanks...

Bill


I thought of that but I figured that it could cause a circular reference.
I'm glad I could help.

/Fredrik