Using string variables within a command in VBA

Question

I was trying to get a string to work with a code where I automate multiple tabs to be filtered by the same criteria, for a sheet that gets the subtotal out of other sheets.
So, I did some If and Elses to check for some criteria and try to make the filtering after this analysis, instead of having to do multiple AutoFilters withing multiple Ifs.

So, the part bellow works just fine, it is making the string (subStr) accordingly

If subBacia = "TODOS" Then
    subStr = """<>"""
Else
    subStr = """=" & subBacia & QUOTE & " , Operator:=xlOr, Criteria2:=" & QUOTE & "=TOTAIS" & QUOTE & ""
End If

But this part, that I am avoiding to do multiple ifs for, is not working:

ActiveSheet.Range("$A$12:$EX$2025").AutoFilter Field:=4, Criteria1:=subStr

My idea was that it would end up as the construction bellow, for example, with multiple criteria:

ActiveSheet.Range("$A$12:$EX$2025").AutoFilter Field:=4, Criteria1:="=AN6b" _
    , Operator:=xlOr, Criteria2:="=TOTAIS"`

So, what am I doing wrong, or what should I do that I am not aware of? Edit: Here goes the full code

Sub Filter()
    Dim subBacia, encarregado As String
    Dim bm As String
    Dim subStr, encStr, bmStr As String

    'Application.Calculation = xlCalculationManual
    'Application.ScreenUpdating = False

    Sheets("Resumo").Select
    encarregado = Range("T3")
    subBacia = Range("T5")
    bm = Range("T4")
    Const QUOTE = """"
    On Error Resume Next

    If encarregado = "TODOS" And bm = "TODOS" And subBacia = "TODOS" Then
        Sheets("Dem. Rede").Select
        ActiveSheet.ShowAllData
        Sheets("Dem. Interceptor").Select
        ActiveSheet.ShowAllData
        Sheets("Dem.Ramal").Select
        ActiveSheet.ShowAllData
    Else
        If encarregado = "TODOS" Then
            encStr = """<>"""
        Else
            encStr = """=" & encarregado & QUOTE & " , Operator:=xlOr, Criteria2:=" & QUOTE & "=TOTAIS" & QUOTE & ""
        End If
        If bm = "TODOS" Then
            bmStr = """<>"""
        Else
            bmStr = """=" & bm & QUOTE & " , Operator:=xlOr, Criteria2:=" & QUOTE & "=TOTAIS" & QUOTE & ""
        End If
        If subBacia = "TODOS" Then
            subStr = """<>"""
        Else
            subStr = """=" & subBacia & QUOTE & " , Operator:=xlOr, Criteria2:=" & QUOTE & "=TOTAIS" & QUOTE & ""
        End If
        Debug.Print encStr
        Debug.Print bmStr
        Debug.Print subStr
        Sheets("Dem. Rede").Select
            ActiveSheet.Range("$A$12:$EX$2025").AutoFilter Field:=2, Criteria1:=bmStr
            ActiveSheet.Range("$A$12:$EX$2025").AutoFilter Field:=3, Criteria1:=encStr
            ActiveSheet.Range("$A$12:$EX$2025").AutoFilter Field:=4, Criteria1:=subStr
        Sheets("Dem. Interceptor").Select
            ActiveSheet.Range("$A$12:$EM$137").AutoFilter Field:=2, Criteria1:=bmStr
            ActiveSheet.Range("$A$12:$EM$137").AutoFilter Field:=4, Criteria1:=encStr
            ActiveSheet.Range("$A$12:$EM$137").AutoFilter Field:=3, Criteria1:=subStr
        Sheets("Dem.Ramal").Select
            ActiveSheet.Range("$B$11:$Z$1214").AutoFilter Field:=3, Criteria1:=bmStr
            ActiveSheet.Range("$B$11:$Z$1214").AutoFilter Field:=2, Criteria1:=encStr
            ActiveSheet.Range("$B$11:$Z$1214").AutoFilter Field:=1, Criteria1:=subStr
        Sheets("Cadastro Ramal").Select
            ActiveSheet.Range("$A$9:$K$841").AutoFilter Field:=2, Criteria1:=bmStr
    End If

    'Application.Calculation = xlCalculationAutomatic
    'Application.ScreenUpdating = True    

End Sub

Edit: BTW the Debug.Print is returning for example: "=9" , Operator:=xlOr, Criteria2:="=TOTAIS"
or simply "<>"
And it is exactly the way it should, but, it seems I can't concatenate the string in the code the way I hoped for.

Can I see the code you are using for the call. ActiveSheet.Range("$A$12:$EX$2025").AutoFilter Field:=4, Criteria1:=subStr ActiveSheet.Range("$A$12:$EX$2025").AutoFilter Field:=4, Criteria1:= & subStr — Josh Anstead, Jul 07 '17 at 00:33

score 1 · Answer 1 · answered Jul 07 '17 at 18:34

You cannot construct pieces of VBA code on the fly and use them to complement incomplete statements you have hardcoded. I don't think that ever worked anywhere. You can dynamically create a complete sub and execute it though, but I do not recommend that.

However you don't need dynamic code generation in your case anyway. You want to predetermine values for the parameters, so do that explicitly. The trick is that you will need to use the Missing special value explicitly to pass to parameters you don't want:

Private Type CriteriaTuple
  Criteria1 As Variant
  Operator As Variant
  Criteria2 As Variant
End Type

Private Function MissingValue(Optional ByVal DoNotPassAnything As Variant) As Variant
  If Not IsMissing(DoNotPassAnything) Then Err.Raise 5, , "I said do not pass anything"
  MissingValue = DoNotPassAnything
End Function

Sub Filter()
  ....

  dim subStr as CriteriaTuple

  if ... then
    subStr.Criteria1 = """<>"""
    subStr.Operator = MissingValue
    subStr.Criteria2 = MissingValue
  else
    subStr.Criteria1 = """<>"""
    subStr.Operator = xlOr
    subStr.Criteria2 = """=TOTAIS"""
  end if

  ...

  ActiveSheet.Range("$A$12:$EX$2025").AutoFilter Field:=4, Criteria1:=subStr.Criteria1, Operator:=subStr.Operator, Criteria2:=subStr.Criteria2
End Sub

score 0 · Answer 2 · answered Jul 07 '17 at 00:17

0

Unless you can show otherwise, I don't think you can make VBA interpret your string as a parameter list. I haven't understood what you're trying to save by this method.

answered Jul 07 '17 at 00:17

Chris Staffa

46
3

You can use the IIF function in your arguments to avoid duplicating code, q.v. https://excelmacromastery.com/vba-if/#The_IIF_function – Chris Staffa Jul 07 '17 at 00:44
I have added the full code so you can see what i am trying to achieve, since i would autofilter the same criterias in multiple sheets, that i was trying to save. Otherwise i guess i will have to make a lot of if and elses – Philipe Laperche Jul 07 '17 at 17:43

Using string variables within a command in VBA

2 Answers2