Such a thing as CurrentCell..? ThisCell?

[embirath]

Hi everyone

I'm trying to create a function which uses some neighboring cel
values. The neighboring cells will always be at a constant relativ
offset from the cell containing the formula.

To do this, I think I need to use the Offset object to specify whic
neighboring cells to use. But how do I specify the "current cell", o
"this cell", ie the cell which contains the formula? What will be th
"parent" of this Offset object?

If there was such a thing as a "CurrentCell", my formula might loo
something like

If CurrentCell.Offset(-1,0) = "OneThing" then
CurrentCell = CurrentCell.Offset(0,-2)
CurrentCell.Offset(0,-1)
Else if CurrentCell.Offset(-1,0) = "SomethingElse" then
CurrentCell = CurrentCell.Offset(0,-2)
CurrentCell.Offset(0,-1)
End if

Is there something equivalent to the "CurrentCell"?? "ActiveCell" doe
NOT work since some other cell might be active if/when the worksheet i
recalculated.

Let me know if you have any ideas.

Thanks!
Emm

 

[Tim Williams]

Why don't you just pass the cells in as parameters?
If you don't, your function won't be recalculated when the other cell's
values change (because Excel relies on the functions arguments to determine
if it needs to be recalculated).

Tim

 

[JMB]

In a custom function, I believe you can do that with
Application.Caller.Offset(rowoffset, coloffset).Value

 

[JMB]

Considering Tim's point on the recalculation issue (which I had not thought
of), using Caller would probably be a poor choice.

 

[embirath]

Hi everyone

Thanks for all your input!

In my work, I frequently have to cut and paste (and copy and paste) th
cells, and move them around to rearrange their orders etc. I find tha
when I use input parameters for the functions, the parameters get al
mixed up, and I have to go in and "fix" them after a move. I know abou
the "relative" vs "absolute" references (A7 vs $A$7 etc), but still, th
parameters are not always copied/cut the way they need to be. This i
why I wanted a function that just always looks at, for example
Offset(-1,2), no matter where the cell is located or moved to.

But, I didn't realize the problem with the cells not bein
recalculated... That might be another problem.. Hm.

I'll look into the "Caller" object. I'll see what I can do with it.

Thanks again all!
Emm

 

[JMB]

Off the top of my head, there may be some other options you could explore to
address recalculation:

1) Perhaps set up a relative named range. With cell E5 selected, click
Insert/Name/Define, enter a name (let's say Test) and, in the refers to box,
enter

=OFFSET(E5, -1, -1, 1, 1)

which should refer to the cell 1 row above and 1 column to the left of
whatever cell "Test" is used in. For example, you could enter 12 in cell
B46, then enter =Test*2 in cell C47 and it will return 24. Then you could
use parameter passing in your function to pass Test to your function as a
range argument. Recalc seemed to work okay with this setup.

2) You could make the function volatile so it will recalculate whenever any
cells on the worksheet are calculated. I wondered if you could change a cell
that affects your function, but does not cause any recalculations in any
other cells (therefore not causing your function to recalculate), so I set up
a function that referenced an input cell using Caller and made it volatile,
then set up an input cell used by the function, but not used anywhere else on
the sheet, and the function recalculated every time the input cell changed -
so it seemed to work okay.

Application.Volatile True


3) I doubt it is practical, but if nothing else seems to be an option, maybe
you could use a worksheet event handler (such as worksheet_change event) to
force the worksheet to recalculate. You would have to test to see how much
it would slow things down to see if it would be workable.

 

[JMB]

One other thing I forgot to mention - instead of using Offset to set up a
defined name, you could use it directly as an argument to pass to your
function.

If your entering your function in cell E5:

=MyFunction(Offset(E5, -1, -1, 1, 1))

which would pass D4 to your function.

 

[embirath]

Thanks so much for all your help. I got it to work with the Caller
function.

Thanks!
Emma