Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Bill Martin -- (Remove NOSPAM from address)
 
Posts: n/a
Default 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
  #2   Report Post  
Fredrik Wahlgren
 
Posts: n/a
Default


"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


  #3   Report Post  
Bill Martin -- (Remove NOSPAM from address)
 
Posts: n/a
Default

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
  #4   Report Post  
Bill Martin -- (Remove NOSPAM from address)
 
Posts: n/a
Default

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

  #5   Report Post  
Fredrik Wahlgren
 
Posts: n/a
Default


"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


Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
I want the PMT function to calculate using 360 days not 365 amalecki Excel Worksheet Functions 6 April 30th 23 11:44 AM
Can you change the PMT function tio calculate off of 365 days? Will Excel Worksheet Functions 1 March 18th 05 08:27 PM
Looking for function or formula to calculate number that is revers Ken Excel Worksheet Functions 2 February 7th 05 12:18 PM
Need Formula or Function to calculate Margin (reverse of Percent a Ken Excel Worksheet Functions 1 February 7th 05 10:26 AM
Does Excel 2000 have a 'datedif' function to calculate the number. Kaddy Excel Worksheet Functions 7 December 11th 04 09:53 PM


All times are GMT +1. The time now is 11:47 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"