Relative cell value in function

[Humphrey]

I have a large spreadsheet the uses a function to calculate the cell value.
I have two questions;
* is it possible to determine the value in the cell before the function is
run?
* is it possible to determine the value in the cell immediately to the left?

I've tried using ActiveCell.Offset but the result is always coming back as
Empty. Any thoughts?

H

 

[Nigel]

I think you might need to run the function to determine the value, I presume
you mean before it is applied to the cell. Without seeing the function, you
could assign to a variable then evaluate that, then update cell ... maybe?

Pseudo code.......

dim myResult variable
myResult = myFunction(myParameters)
etc...
if OK then myCell = myResult

The cell to the left is ActiveCell.Offset(0,-1).Value or =OFFSET(A2,0-1)
returns value in A1

 

[Humphrey]

Thanks Nigel, but that is exactly what is not working. Picture if you will a
grid of 40 columns and 4000 rows. In each of these cells the value of the
cell is based on a function MyFunction(parameters). If I right click in the
grid and choose refresh the cells are all sequentially correctly updated
using the function. However if I put the following into the code

Debug.Print ActiveCell.Value, ActiveCell.Row, ActiveCell.Column

it only returns the details of A1 each time the function is run. It appears
that although the value in the given cell is updated during the
AutoCalculation, the cell is never the ActiveCell. I guess looking at the
problem from a different way how do I make the cell currently being updated
the ActiveCell when the apparent ActiveCell doesn't seem to change?

H

 

[troysteadman]

I think you might need to run the function to determine the value, I presume
you mean before it is applied to the cell.  Without seeing the function,you
could assign to a variable then evaluate that, then update cell ... maybe?

Pseudo code.......

dim myResult variable
myResult = myFunction(myParameters)
etc...
if OK then myCell = myResult

The cell to the left is ActiveCell.Offset(0,-1).Value or =OFFSET(A2,0-1)
returns value in A1

--

Regards,
Nigel
(e-mail address removed)

ActiveCell.Offset(0,1).Value

 

[Nigel]

ActiveCell is the currently activated cell, the Function does not need to
activate a cell to update it's value, so using this construct is erroneous,
as you have discovered.

The best option would be to post the Function code, the Cell referencing /
assignment can then be used to intercept the cell value.

Post your code.

--

Regards,
Nigel
(e-mail address removed)

 

[Nigel]

That is to the right?

--

Regards,
Nigel
(e-mail address removed)

I think you might need to run the function to determine the value, I
presume
you mean before it is applied to the cell. Without seeing the function,
you
could assign to a variable then evaluate that, then update cell ... maybe?

Pseudo code.......

dim myResult variable
myResult = myFunction(myParameters)
etc...
if OK then myCell = myResult

The cell to the left is ActiveCell.Offset(0,-1).Value or =OFFSET(A2,0-1)
returns value in A1

--

Regards,
Nigel
(e-mail address removed)

ActiveCell.Offset(0,1).Value

 

[Humphrey]

The function is much to big to put in here. It does however use
semi-absolute references to determine the parameters for the function. The
function call and parameter list is
CalculatedBenefit($A47,$D47,$E47,$F47,$G47,$H47,VLOOKUP($A47,'MeterReading
Lookup'!$A$7:$AZ$192,O$3),O$6,$L47,$K47,$N47,$M47). Now this works but
occasionally the result of the VLOOKUP returns a zero due to missing data in
the lookup table (I'm using live data so there is no possibility of
interpolating a value). I simply want the function to return the value in
the cell to the left if the VLOOKUP is zero.
I think I've just answered my own question. I would need to do it using an
if in the cell function call. If lookup=0 then cell to the left otherwise
call function. By George I think he's got it. Thanks for your help

 

[Nigel]

I often discover a solution by discussion, rather than a formal problem
solving session. So a question "is it possible to determine the value in
the cell before the function is run" is so close to the answer but
interpreted completely differently - fascinating!!

Good luck.

--

Regards,
Nigel
(e-mail address removed)

 

[Charles Williams]

You can use Application.caller to return a range reference to the cell that
contains the function, but you cannot get its value in any straightforward
way. One possible way is to use Application.Caller.Text, but that returns
the formatted value rather than the actual value (could return ###### for
instance) so is very dependent on the user formatting etc.

To get the cell one to the left you could use
OFFSET(application.caller,-1,0,1,1)
but this would require you to make the function volatile
(Application.Volatile), which could slow down recalculation.

A better way would be to pass the cell to the left as a parameter to the
function.

You can use ISEMPTY() to determine if an input parameter has not yet been
calculated.

Charles
__________________________________________________
Outlines for my Sessions at the Australia Excel Users Group
http://www.decisionmodels.com/OZEUC.htm