Return a Ref from a UDF?

[Dick Watson]

I have a UDF that needs to return an Excel Ref for a given Range object. How
do you do it?

Public Function GetPivotFieldRef(data_field As String, pivot_table As Range)
As Range

' return a ref to the data_field within the PivotTable containing Range
pivot_table

GetPivotFieldRef =
pivot_table.PivotTable.PivotFields(data_field).DataRange

End Function

Since I called with a Ref and got a Range, I was expecting I could return a
Range and get a Ref. This appears to not be the case. I've also tried
DataRange.Address with the function return type As String.

My test case formula:

=isref(GetPivotFieldRef("FieldName",pvt_MyPivotTable))

Break-pointed, I do get the right range. Just returning it as a Ref seems to
be the issue.

Thanks in advance!

 

[Héctor Miguel]

hi, Dick !

- how/where (exactly) do you plan to use this UDF ?
(through vba code and/or as a worksheet function ?)

- *IF* you need to pass to this UDF the argument (pivot_table As Range)
=> *pointing* to a cell within a pivotTable in any worksheet...
=> why do you need to *test* if =isref(... (???)

- for this case, change your UDF to return a string (i.e.)
Public Function GetPivotFieldRef(data_Field As String, pivot_Table As Range) As String
' return a ref to the data_field within the PivotTable containing Range pivot_table
GetPivotFieldRef = pivot_Table.PivotTable.PivotFields(data_Field).DataRange.Address(External:=True)
End Function

=> and use indirect for your test case formula (i.e.)
=isref(indirect(GetPivotFieldRef("FieldName",pvt_MyPivotTable)))

hth,
hector.

__ OP __

 

[Charles Williams]

You need to use Set

Function ReturnRange(InputRange As Range) As Range
Set ReturnRange = InputRange
End Function

regards
Charles
___________________________________
The Excel Calculation Site
http://www.decisionmodels.com

 

[Dick Watson]

Comments below.

- how/where (exactly) do you plan to use this UDF ?
(through vba code and/or as a worksheet function ?)

Worksheet function.

- *IF* you need to pass to this UDF the argument (pivot_table As Range)
=> *pointing* to a cell within a pivotTable in any worksheet...
=> why do you need to *test* if =isref(... (???)

Since the larger intent was to use the returned ref in things like count() I
was using isref() to figure out what was, or wasn't being returned. It was
just for test purposes.

- for this case, change your UDF to return a string (i.e.)
Public Function GetPivotFieldRef(data_Field As String, pivot_Table As
Range) As String
' return a ref to the data_field within the PivotTable containing Range
pivot_table
GetPivotFieldRef =
pivot_Table.PivotTable.PivotFields(data_Field).DataRange.Address(External:=True)
End Function

=> and use indirect for your test case formula (i.e.)
=isref(indirect(GetPivotFieldRef("FieldName",pvt_MyPivotTable)))

That's what I ended up doing.

 

[Dick Watson]

Duh! Light bulb moment! Many thanks.