CODEWITHSUNDEEP
excelvbassasolap-cube
ZelelB
ZelelB

Reputation: 2010

Filter OLAP Cube pivot table, to exclude two values from very long list

I am trying to filter an OLAP cube pivot table, so that I exclude from a specific column 2 values. The number of unique values in that column is very high (in the thousands).

This is the code I am using, but it is giving me an error:

Sub FilterOLAPCube()
    Dim pt As PivotTable
    Dim pf As PivotField
    Dim excludeValues As Variant
    Dim filterCriteria As String
    
    excludeValues = Array("value1", "value2") ' Values to exclude
    filterCriteria = ""
    
    For Each Val In excludeValues
        filterCriteria = filterCriteria & Val & ","
    Next Val
    
    filterCriteria = Left(filterCriteria, Len(filterCriteria) - 1) ' Remove the last comma
    
    Set pt = ActiveSheet.PivotTables("PivotTable1") ' Change PivotTable name as needed
    Set pf = pt.PivotFields("[MasterData Publisher Groups].[Publisher Group].[Publisher Group]") ' Change [Field Name] to the actual field name
    
    ' Clear any existing filters on the field
    pf.ClearAllFilters
    
    ' Filter out specific values
    pf.PivotFilters.Add Type:=xlCaptionDoesNotEqual, Value1:=filterCriteria
End Sub

Sub filterMacro()
'
' filter Makro
'
'

    FilterOLAPCube
        
    
End Sub

When trying to excude / debug the code step by step, it is giving me an error at the line where I am calling the function FilterOLAPCube, and saying "Compile error: Argument not optional"

Any idea how to overcome this?

Upvotes: 0

Views: 131

Answers (1)

FunThomas
FunThomas

Reputation: 29586

That's an easy one - however you are confusing us with terms like "OLAP Cube". Your problem is that you want to use a variable called val without declaring it.

val is a VBA function (to convert a string into a number). If you insist, you can declare a variable with that name, this will "hide" the function and you can use the variable in your code like any other variable.

If you don't declare a variable (and don't use Option Explicit), the VBA runtime will automatically create the variable on the fly. However, in your case the VBA runtime assumes that you want to use the function val, and that function has one mandatory parameter (the string to convert), that's the reason you get the Argument not optional error.

  • Always use Option Explicit

  • Declare all variables

  • Avoid to use names for variables (or routines) that are already is use by VBA.

Hint: When you get a compiler error like that, press [Shift]+[F2] to jump to the definition. This usually reveals the reason for the error (in your case, you would see that there is a build-in function with that name):

enter image description here

Upvotes: 0

Related Questions