Dumb newbie Q re. passing cell addresses to a UDF.

[baobob]

I'm still making the transition from Object Pascal, and cells and
other concepts in Excel are throwing me a bit.

I've written a function to which I pass one or more cell addresses,
e.g.:

Function MyFunc(ParamArray SomeCellAddresses()) As SomethingOrOther

It itself gets called from a cell, e.g.:

=MyFunc(A1, C45, B13, etc...)

Inside MyFunc, for each argument passed, I'd like to know BOTH the
cell address that was passed and that cell's contents. As it stands,
Excel seems to pass these arguments as the LATTER, and the cell refs
seem lost.

There isn't something I can do inside MyFunc to have my cake and eat
it too, is there? Namely, additionally retrieve the passed addresses?

Or does Excel indeed do just what I'm observing--convert cell address
arguments to the cell contents before function calls, thereby
requiring me to, say, pass the addresses as strings and use INDIRECT
in MyFunc, instead?

Thanks much.

***

 

[Jim Cone]

A function called from a worksheet cell can only return a value to that cell.
It cannot change other cell values and cannot change the physical
characteristics of any cell.
Also, the tips and advice here can help keep answers coming ...
http://www.cpearson.com/excel/newposte.htm
--
Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware
(Excel Add-ins / Excel Programming)




<[email protected]>
wrote in message
I'm still making the transition from Object Pascal, and cells and
other concepts in Excel are throwing me a bit.
I've written a function to which I pass one or more cell addresses,
e.g.:

Function MyFunc(ParamArray SomeCellAddresses()) As SomethingOrOther

It itself gets called from a cell, e.g.:

=MyFunc(A1, C45, B13, etc...)

Inside MyFunc, for each argument passed, I'd like to know BOTH the
cell address that was passed and that cell's contents. As it stands,
Excel seems to pass these arguments as the LATTER, and the cell refs
seem lost.

There isn't something I can do inside MyFunc to have my cake and eat
it too, is there? Namely, additionally retrieve the passed addresses?
Or does Excel indeed do just what I'm observing--convert cell address
arguments to the cell contents before function calls, thereby
requiring me to, say, pass the addresses as strings and use INDIRECT
in MyFunc, instead?
Thanks much.
***

 

[Tim Zych]

This worked for me.

Function MyFunc(ParamArray mycells()) As String
Dim i As Integer, cell As Range
For i = LBound(mycells) To UBound(mycells)
Set cell = mycells(i)
MyFunc = MyFunc & "'" & _
cell.Parent.Name & "'!" & cell.Address(0,0) & _
" has a value of " & cell.Value & ";"
Next
End Function

=myfunc(A1, A2)
=myfunc(Sheet2!A1, A2)

For areas that can consist of one or more cells, I'd modify it, so this does
everything the above one does and more:

Function MyFunc(ParamArray mycells()) As String
Dim i As Integer, cell As Range, rng As Range
For i = LBound(mycells) To UBound(mycells)
Set rng = mycells(i)
For Each cell In rng.cells
MyFunc = MyFunc & "'" & cell.Parent.Name & _
"'!" & cell.Address(0, 0) & _
" has a value of " & cell.Value & ";"
Next
Next
End Function

=myfunc(A1:A2, Z1:Z3, Sheet2!A5:A7)
=myfunc(A1, A2)

 

[baobob]

Jim & Tim:

Thanks VERY much for replying.

I'll try both suggestions.

***

 

[baobob]

P.S.:

It turns out that my problem was that I was not using syntax "Set".

In copying each value of ParamArray to a Range var, I was merely using
"=", and execution simply went into limbo on that line.

"Set" makes it work.

Thanks again.

***