Excel Function Won't Use Formula Results

[timfoster]

Hi Folks,

I have a custom function that calculates based on a number of other columns. Some of the data is static (extracted from a database) others are the results of Excel functions.

My function will not read the values from the cells that are the results of functions. It will return 0 (or empty) every time. Even if I step the function I can step the line in question and get a result of 0 (empty) but execute the same range reference in the immediate window and get the correct value!

If I change the cell to a fixed value rather than a formula I get the expected value back. Why can't I use the formula result in my function? Surely, I should be reading the same thing as the immediate window does???

 

[AnotherHiggins]

Please post the line of code that isn't working for you.


[tt][blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ 181-2886 before posting.

 

[timfoster]

An example of the code not working is:

Dim ForecastOrder as Long

ForecastOrder = range("_ForecastOrders").Offset(OffRow, OffCol).value

The range, AR8, contains a formula that returns 64. If I hover over the range ref it shows Empty and ForecastOrders takes 0 (being an integer). Yet if I copy the same code into the immediate window I get the correct value of 64.

It is a complex spreadsheet, but that shouldn't make any difference.

Thanks for the help.

 

[SkipVought]

Your code does not tell us ANYTHING.

What is the reference of _ForecastOrders?

What is the value of OffRow?

What is the value of OffCol?

And what is the objective of this code?


Skip,
^{Don't let the Diatribe...
talk you to death!}
_{Just traded in my old subtlety...
for a NUANCE!}

 

[timfoster]

_ForecastOrders is a single cell marking the beginning of the range of forecasts. The two offset values are the reference of the current cell in relation to _forecastOrders in order that I can get a similar reference in another range. They're used to determine a number of other cell values that my function works with in order to get its result.

Using the code above I get nothing back for this value as the offset from _ForecastOrders is a calculated formula that uses a complex calculation based on completely dynamic references (the entire sheet is built from scratch each time the user runs the tool). An example of the type of formula in that cell is:

=IF(OFFSET(_ForecastOrders,-3,COLUMN()-COLUMN(_ForecastOrders),1, 1)="Y", IF(AND(DiscLine="Y",(COLUMN(_IdealCover)-1)-COLUMN()<CoverWeeks), 0, IF((OFFSET(_IdealCover, ROW()-ROW(_IdealCover), COLUMN()-COLUMN(_ForecastOrders), 1, 1)+OFFSET(_ForecastWks, ROW()-ROW(_ForecastWks), COLUMN()-COLUMN(_ForecastOrders), 1, 1))-OFFSET(_ClosingStock, ROW()-ROW(_ClosingStock), (COLUMN()-COLUMN(_ForecastOrders)-1), 1, 1)<=0,0,(OFFSET(_IdealCover, ROW()-ROW(_IdealCover), COLUMN()-COLUMN(_ForecastOrders), 1, 1)+OFFSET(_ForecastWks, ROW()-ROW(_ForecastWks), COLUMN()-COLUMN(_ForecastOrders), 1, 1))-OFFSET(_ClosingStock, ROW()-ROW(_ClosingStock), (COLUMN()-COLUMN(_ForecastOrders)-1), 1, 1))), 0)

The next value used by my function references a similar range using x = range("_xx").offset(offrows, offcols).value however this time the target cell is a fixed value that I have extracted from the database. This one works fine and gets the correct result.

I have tried changing the reference to look at the offset for another of the calculations and get the same result (nothing). If I remove the formula from the target cell and type a fixed value I get the correct results back.

I tried this last night on my machine using Excel 2007 (albeit with a very simply formula) and got the correct result from the formula and from a fixed value. The machine it doesn't work on is Excel 2003.

 

[SkipVought]

Here is a sample of what I was looking for with respect to the INSTANCE of interest...

"What is the reference of _ForecastOrders?" Q9

"What is the value of OffRow?" 8

"What is the value of OffCol?" 7

This particular value is used, bla bla bla, because it's the bla bla bla.


Skip,
^{Don't let the Diatribe...
talk you to death!}
_{Just traded in my old subtlety...
for a NUANCE!}

 

[timfoster]

Difficult to say. Each time the user runs the routine _ForecastOrders will be a different range. In the version I'm testing with it's BY37 that returns the result of my function. It's referencing _IdealCover (the actual cell that returns 0) in DL37. This should be returning 64 which is what the cell displays and what I get if I do '?range("_IdealCover").Offset(OffRows, OffCols).value' in the immediate window immediately after the code has returned 0. OffRows and OffCols are calculated relative to the relationship between the current cell and another reference. They are 0, -2 here. They're not the problem though as range("_IdealCover").Offset(OffRows, OffCols).Address' returns the correct address. The static numbers (that do get returned) are in BH37 (sales: 17), AO37 (Stock: 342) & N37 (Discont. flag: 'Y'). The remainder are all the results of formula and all return 0 (EK37: 372 and FL37: 28)

 

[SkipVought]

"Difficult to say."

Good luck!

Skip,
^{Don't let the Diatribe...
talk you to death!}
_{Just traded in my old subtlety...
for a NUANCE!}

 

[xlbo]

Try

Application.volatile

as the 1st line in your custom function. Sounds like a calc stack problem



Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[neemi]

A good site with some gd tips...

http://www.avdf.com/apr98/art_ot003.html