CODEWITHSUNDEEP
vbaexcel
Philipe Laperche
Philipe Laperche

Reputation: 11

Using string variables within a command in VBA

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.

Upvotes: 1

Views: 170

Answers (2)

GSerg
GSerg

Reputation: 78210

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

Upvotes: 1

Chris Staffa
Chris Staffa

Reputation: 46

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.

Upvotes: 0

Related Questions