Return a Ref from a UDF?

  • Thread starter Thread starter Dick Watson
  • Start date Start date
D

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!
 
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 __
 
Comments below.

Héctor Miguel said:
- how/where (exactly) do you plan to use this UDF ?
(through vba code and/or as a worksheet function ?)
Click to expand...

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(... (???)
Click to expand...

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)))
Click to expand...

That's what I ended up doing.
 
Back
Top