Excel VBA: How to manipulate a cell/range address within a UDF
I'm try to transfer a range name or cell address into a function so that it can be manipulated within the function.
My simplified test function is shown below:
However it appears that the putting the Named Range or a cell address as the function parameter transfers the value contained in the cell to the function, rather than the cell/named range address, consequently the result returned by the function is #VALUE!
Function JunkTestAddress(rngA As Range)
' Usage: =JunkTestAddress(rngA)
' Where rngA = Named Range, eg Data Table with range $A$3:$C$12
Dim Msg, Button, Title, Response As String
JunkTestAddress = Application.WorksheetFunction.Address (1,Application.WorksheetFunction.Column(rngA), 4)
Title = "JunkTestAddress Function..."
Button = vbExclamation
Msg = "rngA: " & rngA & vbCrLf & _
"JunkTest Address: " & JunkTestAddress
Response = MsgBox(Msg, Button, Title)
Here is my named range:
Commenting out the JunkTestAddress line results in the MessageBox displaying the value contained in cell referenced by rngA:
The result I am looking for is rngA = $C$3.
How can I transfer the function cell/range address parameter into the function so I can manipulate it? (Putting "" around rngA parameter when using the function and treating it as a string does not appear to resolve the issue.)
All assistance in resolving this will be appreciated.
Thanks in anticipation