Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Dumb newbie Q re. passing cell addresses to a UDF.
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. *** |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Dumb newbie Q re. passing cell addresses to a UDF.
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) 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. *** |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Dumb newbie Q re. passing cell addresses to a UDF.
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 mo 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) -- Tim Zych SF, CA 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. *** |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Dumb newbie Q re. passing cell addresses to a UDF.
Jim & Tim:
Thanks VERY much for replying. I'll try both suggestions. *** |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Dumb newbie Q re. passing cell addresses to a UDF.
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. *** |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Help! Probably dumb newbie question but need help with if/then?? | New Users to Excel | |||
*Dumb newbie* How to automatically import data from 1 workbork to another | New Users to Excel | |||
Passing Addresses Instead of Values | Excel Programming | |||
total dumb newbie question | Excel Discussion (Misc queries) | |||
Newbie stuck on Passing a Variable into a Range | Excel Programming |