Pass on cell address to an UDF?

[Charlotte E]

Is it possible to pass on the cell, in which an UDF is located, to the UDF
itself?


Example:

In cell C4, I have an UDF, like:

=CELLTELL()

....and the UDF could return something like:

Function CELLTELL()

If Celladdress = "C4" Then
CELLTELL = "Explosive cell!"
Else
CELLTELL = "Harmless cell"
End If

End Function


Hope you get the picture...


Thanks,

 

[AltaEgo]

Using inbuilt functions in cells:

=IF(ADDRESS(ROW(),COLUMN())="$C$4","Explosive Cell","Harmless")

Expand on this and pass some parameters if you need it in a UDF:

Function CELLTELL(addr)
'use this way in cell
' =CELLTELL(ADDRESS(ROW(),COLUMN()))

CELLTELL = "Harmless cell"
If addr = "$C$4" Then CELLTELL = "Explosive cell"

End Function

 

[Charlotte E]

Using inbuilt functions in cells:

=IF(ADDRESS(ROW(),COLUMN())="$C$4","Explosive Cell","Harmless")

Expand on this and pass some parameters if you need it in a UDF:

Function CELLTELL(addr)
'use this way in cell
' =CELLTELL(ADDRESS(ROW(),COLUMN()))

CELLTELL = "Harmless cell"
If addr = "$C$4" Then CELLTELL = "Explosive cell"

End Function

Thanks, but that's the whole point: I was kind of hoping to be able to avoid
the ADDRESS(ROW(),COLUMN()), and just use =CELLTELL().

I was hoping that VBA had a way of 'reading' what cell the function was
located in?

Actually, just lige =ROW()
Somehow that function must be able to 'read' what cell it is located in?

Anyone know how to do this?


TIA,

 

[Tim Williams]

Something like this ?

Function CellTell()
If Application.Caller.Address(False, False) = "C4" Then
CellTell = "Explosive"
Else
CellTell = "Harmless"
End If
End Function

Tim

 

[Rick Rothstein]

Try this UDF...

Function CellTell()
CellTell = Application.Caller.Address
End Function

This returns an absolute address. If you want the ability to specify the
absolute/relative addressing mode, then here is a more general function...

Function CellTell(Optional ColAbsolute = True, Optional RowAbsolute = True)
CellTell = Application.Caller.Address(RowAbsolute, ColAbsolute)
End Function

So, if you wanted a fully relative address, you would use this UDF call...

=CellTell(FALSE,FALSE)

 

[Rick Rothstein]

I think I misread what you were looking to do... it looks like Tim's
response may be what you were after.

 

[Charlotte E]

Sorry for a late reply (out playing flag-football all day

Thanks, Tim - that's excately what I was looking for