CODEWITHSUNDEEP
excelvba
pedram
pedram

Reputation: 3087

Passing a PivotTable to a Function ByRef Error in VBA

I have a simple function:

Function PtName(pt As PivotTable)
    PtName = pt.Name
End Function

In the immediate window I type the following:

set pt = ActiveSheet.PivotTables(1)
debug.Print PtName(pt)

And I get a ByRef argument type mismatch error. When I do this:

debug.Print typename(pt)

it returns PivotTable

What am I doing wrong?

Upvotes: 0

Views: 964

Answers (1)

Archias
Archias

Reputation: 383

Really not sure what you are trying to do here.

Sub Test()
    s = PtName(ActiveSheet.PivotTables(1)) ' Call function to assign pivottable name to variable "s"
    Debug.Print s
End Sub

Function PtName(pt As PivotTable)
    PtName = pt.Name
End Function

Returns: PivotTable1

You don't need this function, you could just do this.

Sub Test()
    s = ActiveSheet.PivotTables(1).Name
    Debug.Print s
End Sub

Which also Returns: PivotTable1

Also, you can't assign a variable in the Immediate window and try to reference it on a separate line of code if the program isn't even running.

Upvotes: 2

Related Questions