Function Won't Calculate -- Sometimes

G

Guest

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
 
F

Fredrik Wahlgren

Bill Martin -- (Remove NOSPAM from address) said:
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
 
G

Guest

Fredrik said:
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
 
F

Fredrik Wahlgren

Bill Martin -- (Remove NOSPAM from address) said:
----------

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top