Array like statement for multiple ANDs in an IF statement

Question

Is there to a way to improve the below? I would like to know if an array-like statement can replace the below.

I just want to like remove the multiple "And Not"s so if you can suggest something to optimize the below then I would appreciate it.

    Set ws_raw = ThisWorkbook.Worksheets("Raw")
    Set ws_master_tracker = ThisWorkbook.Worksheets("Master Tracker")
    ' more assignments here...

    For Each ws In ThisWorkbook.Worksheets

        If Not ws Is ws_raw _
            And Not ws Is ws_master_tracker _
            And Not ws Is ws_title_page _
            And Not ws Is ws_sample _
            And Not ws Is ws_closing _
            And Not ws Is ws_ref _
            And Not ws Is ws_pdf_template _
            And Not ws.Visible = xlSheetHidden Then

            project_name = ws.Range("E3").Value
            int_last_row_of_ws = 46

            For int_current_row_of_ws = 11 To int_last_row_of_ws
                cell_value = ws.Cells(int_current_row_of_ws, 3).Value

                With rng_raw
                    .AutoFilter 1, project_name
                End With

                Set rng_filtered_raw = ws_raw.Range("J3", ws_raw.Cells(int_last_row_of_raw, int_last_col_of_raw)).SpecialCells(xlCellTypeVisible)

                Select Case cell_value

                    Case Is = "Task Creation!"
                        module_to_look_for = "Task Creation"
                    ' twenty more cases

                    ' Others that are manually typed
                    Case Else
                        module_to_look_for = "MANUAL"

                End Select

                If Not rng_filtered_raw Is Nothing Then

                    If module_to_look_for = "MANUAL" Then
                        ' Do nothing
                        ' Highlight cell, etc.
                    Else
                        look_up_result = Application.WorksheetFunction.VLookup(module_to_look_for, rng_filtered_raw, 3, False)

                        If look_up_result = "" Then
                            ws.Cells(int_current_row_of_ws, 56).Value = "Blank Date!"
                        Else
                            ws.Cells(int_current_row_of_ws, 56).Value = look_up_result
                        End If
                    End If

                End If

            Next int_current_row_of_ws

        End If

    Next ws

Something like:

Dim some_array_variable As Array
Set some_array_variable = (ws_master_tracker, ws_title_page, .....)

If Not ws Is In some_array_variable Then
' some code...

Please help, I am new in VBA.

Answer 1

You could use the filter method of an array to check existance:

dim arrSheets() as string
arrSheets = Split("sheetNameOne,sheetNameTwo,sheetNameThree", ",")
if ubound(filter(arrsheets,"sheetNameOne"))>-1 then debug.print "Sheet is in list"

Edit: extended answer as requested...

I used sheet names for brevity rather than worksheet objects as you just have to list the worksheet names in a comma separated string. To use the actual objects as you have done in your code you'd need to assign them to an array like so;

Dim arrSheets(3) as string
arrSheets(0) = ws_master_tracker.name
arrSheets(1) = ws_title_page.name
arrSheets(2) = ws_sample.name

Rather than as a string;

arrSheets = Split("Master Tracker,Title Page,Sample Sheet", ",")

The Split method takes a string and splits it into the elements of an array based on a delimiter, in the above example a comma. Think of Text-To-Columns in Excel.

The Ubound property describes how many elements are in an array - three in this example. It is zero based i.e. 1 instance will return 0, 2 instances = 1, 3 = 2 and so on.

The Filter method returns a new array containing only the elements that match the specified criteria, in this case a specific worksheet name. As Tim Williams commented the method will include substrings, so if you have a sheet called 'my sheet' and one called 'some other sheet' filtering for the word 'sheet' will return both.

Putting them together: Split creates the array from the string, filtering returns an array containing the string(s) requested, Ubound tells you how many times that string is in the filtered array. If its not present Ubound will return -1, otherwise a number greater than -1.

In your original code this will tell you if a sheet name is one of the names you want to omit. You'd then need to check if that sheet is hidden separately.

if ubound(filter(arrsheets,"sheetNameOne"))=-1 then ' the sheet is not in the list
   if sheets("sheetNameOne").Visible = xlSheetHidden Then ' is it hidden?

Answer 2

It depends on the type of variable you are using. This tests for primary colors:

Sub MultiAND()

    kolor = "mauve"

    If Not kolor = "red" And Not kolor = "blue" And Not kolor = "yellow" Then
        MsgBox "secondary"
    Else
        MsgBox "primary"
    End If
End Sub

because the variables are strings, the chain of AND NOT can be replaced by:

Sub StringMeAlong()
    kolor = " mauve "
    s = " red blue yellow "
    If InStr(s, kolor) = 0 Then
        MsgBox "secondary"
    Else
        MsgBox "primary"
    End If
End Sub

Here is a simple prime number test for numbers less than 100:

Sub IsItPrime()
    s = "|1|2|3|5|7|11|13|17|19|23|29|31|37|41|43|47|53|59|61|67|71|73|79|83|89|97|"
    N = 23
    v = "|" & CStr(N) & "|"
    If InStr(s, v) = 0 Then
        MsgBox "not prime"
    Else
        MsgBox "Prime"
    End If
End Sub

If this was in a worksheet formula, you could use the MATCH() function with array constants.