Search code examples
excelvbafor-loopif-statement

Writing Multiple IF <> then across multiple macros. Can this be condensed?

I am writing multiple For If Then statements in different Macros. and when I add a sheet I want to be able to change a single "constant" and it will affect all macros where it is involved. Here is what I have First Macro

For x = 1 To Sheets.Count
    If Sheets(x).Name <> "Payroll Expenses" And Sheets(x).Name <> "Paystub" Then
    
    i = i + Sheets(x).Cells(sourcename, 4).Value

Second Macro

For x = 1 To Sheets.Count
    If Sheets(x).Name <> "Payroll Expenses" And Sheets(x).Name <> "Paystub" Then
    
    i = i + Sheets(x).Cells(sourcename, 5).Value

Multiple Macros Following

I have a ton of macros that's say to not use "Payroll Expenses" sheet and "Paystubs" sheet in the if then statements. I have all of these macros and then I go and add another sheet to my document and then I have to go through every single macro and add the new sheet as a statement. i.e. if i add a new sheet and I want it to be ignored in my If then statement I have to write another Sheets(x).Name <> "NEW SHEET" to every single macro I have been using Private Const to refer to variables across an entire module that has been great. Is there someway to incorporate something like this when it comes to all of the sheets I want to ignore in my if then statments?

I've tried just using "and" and "or" between each sheet to shorten things but it doesn't recognize it unless I ad Sheets(x).Name to each new sheet. I have tried using a Private Const "Unused Sheets As String = "all of the unused sheets". But it won't recognize this either. I'm newer to VBA so hopefully this makes sense and someone can help

Solution

  • Create a function like this (for example):

    Function IgnoreSheet(ws As Worksheet) As Boolean
    Select Case ws.Name
        Case "Payroll Expenses", "Paystubs" 'add new names here
            IgnoreSheet = True
        Case Else
            IgnoreSheet = False 'don't really need this, but more explicit...
    End Select
End Function

    Which you can call like this from multiple places:

    If Not IgnoreSheet(Worksheets(x)) Then
   'do something with the sheet
End If