Can I have an "If" statement in a function parameter?

Question

I'd like to use an IF statement in my VBA code where I'm calling up a Function and passing to it two parameters. The reason I need the IF statement is because the parameter is an array object that I pass to the function. Here's an abbreviation of the code that contains the problem:

For i = 1 To UBound(FilterArray)
    For j = LBound(ptsheets, 1) To UBound(ptsheets, 1)
        Filter_PivotField_Master _
            pvtField:=ThisWorkbook.Worksheets(ptsheets(j, 1)).PivotTables(ptsheets(j, 2)).PivotFields(FilterArray(i, 1)), _
            FilterTypeArray:= If FilterArray(i, 2) = 1 Then
                                InvCodeArray
                                Else
                                BoardMonthArray
                                End If
    Next j
Next i

As you can see, I'm looping through the ptsheets array and for each entry, I'm calling up the Filter_PivotField_Master Function. The function requires two inputs (a pivotfield and a string array for the filtering). The array called "FilterArray" simply contains the info needed to pass along to the function. Because I can't seem to store the string array (either InvCodeArray or BoardMonthArray) within the "FilterArray," I wanted to use this IF statement to choose between either array based on if FilterArray(i,2) is equal to "1" or not. Is an "IF" statement option possible here?

Answer 1

Don't do that. Extract a method/procedure out of the inner loop, and parameterize it instead.

    For j = LBound(ptsheets, 1) To UBound(ptsheets, 1)
        Filter_PivotField_Master _
            pvtField:=ThisWorkbook.Worksheets(ptsheets(j, 1)).PivotTables(ptsheets(j, 2)).PivotFields(thePivotField), _
            FilterTypeArray:= theFilterType
    Next j

Then have the calling code (the outer loop) implement the conditional logic that determines what parameters to give it.

Dim thePivotField
Dim theFilterType

For i = 1 To UBound(FilterArray)
    thePivotField = FilterArray(i, 1)
    If FilterArray(i, 2) = 1 Then
        theFiltertype = InvCodeArray
    Else
        theFilterType = BoardMonthArray
    End If
    TheExtractedMethod i, thePivotField, theFilterType
Next i

Your code will be much easier to follow - and to debug.

---

On a related note, I made a few assumptions just to get your snippet to compile with Option Explicit on (and an IIf instead of the illegal If...Else block), and Rubberduck's last release (v1.4.3) correctly extracted the selected inner loop into its own method:

There are a few known bugs with Rubberduck 1.4.3, and v2.0 is coming soon-ish, but I thought this might interest you. I don't know any other refactoring tools for VBA. Note that Option Explicit is pretty much a hard requirement for this to work, since Rubberduck works off declarations and can't resolve undeclared variables.

Disclaimer: I wrote this refactoring tool.

Answer 2

Use an immediate IF (IIF) for this purpose.

FilterTypeArray = IIF(FilterArray(i, 2) = 1, InvCodeArray, BoardMonthArray)