VBA - test if a value is a valid selection for a PivotField
Solution 1
You can iterate through the PivotItems
and check the Name
against your test.
Sub CheckIfPivotFieldContainsItem()
Dim pt As PivotTable
Set pt = Sheet1.PivotTables(1)
Dim test_val As Variant
test_val = "59"
Dim pivot_item As PivotItem
For Each pivot_item In pt.PivotFields("C").PivotItems
If pivot_item.Name = test_val Then
Debug.Print "MATCHES"
End If
Next pi
End Sub
Relevant data shows that a match should exist and indeed it returns MATCHES
.
Solution 2
If you want to have a helper function and do this without looping values you may use visual basic OnErrorResumeNext trick.
Private Function hasPivotItem(pField As PivotField, value As String) As Boolean
On Error Resume Next
hasPivotItem = Not IsNull(pField.PivotItems(value))
On Error GoTo 0
End Function
' somewhere in your vba program
Debug.Print hasPivotItem(ptTable.PivotFields("Level"), "1")
Debug.Print hasPivotItem(ptTable.PivotFields("Level"), "-123")
rvictordelta
Updated on June 04, 2022Comments
-
rvictordelta almost 2 years
For a pivot table (pt1) on Sheet1, I use VBA to change the value of a filter field (filterfield) using the code below. Let's say values for field can be A, B or C
Sheets("Sheet1").PivotTables("pt1").PivotFields("filterfield").CurrentPage = "A"
Occasionally, and let's say randomonly for the purposes of this question, A, B or C will not be a valid selection for filterfield. When VBA attempts to change the field, it throws a run-time error. I want to avoid this.
How can I check if my values are valid for filterfield before I run the code above? I would like to avoid using On Error and VBA does not have try/catch functionality..
-
LWC almost 6 yearsKudos on avoiding loops! But why is
hasPivotItem = False
(at least in the original edit) needed? -
Whome almost 6 years@LWC We must initialize a desired default return value before calling
pField.PivotItems(str)
. We ignore errors(OnErrorResumeNext) so return value is never set on unknown field, we want to default FALSE. -
LWC almost 6 yearsWhat can I say, when the field doesn't exist it seems to return false even without this preliminary declaration.