Is there a way to make my code shorter?

[Angelfish13]

Hi,

I am still fairly new to VBA and have another question. I have a very repetitive code and am wondering if there is a way to make it cleaner by creating a variable or array or something. Here is my dilemmna:

I have about 10 names. Each person has a macro assigned to them that filters a database by their name. I need to calculate functions for each particular person to see how they are performing. I have several functions I need to perform for each person. (The functions are all SUM, COUNTIF, and SUBTOTAL functions that pull info from another spreadsheet) To perform my code, I run each person's macro, then call the particular function. Its very repetitive so I wonder if there is a way to shrink it down.

I have posted my code below. You can very clearly see the pattern. Imagine there are about 30 functions. Is there a way I don't have to type in each person's name like a hundred times? Especially if I want to add/delete 1 person in the future, editing the code becomes cumbersome. Thank you so much for your help!

    Application.Run "PERSONAL.XLSB!Bob"
    Call Function1
    
    Application.Run "PERSONAL.XLSB!Sally"
    Call Function1
    
    Application.Run "PERSONAL.XLSB!Sue"
    Call Function1
    
    Application.Run "PERSONAL.XLSB!David"
    Call Function1
    
    Application.Run "PERSONAL.XLSB!Mary"
    Call Function1
    
   Application.Run "PERSONAL.XLSB!Bob"
    Call Function2
    
    Application.Run "PERSONAL.XLSB!Sally"
    Call Function2
    
    Application.Run "PERSONAL.XLSB!Sue"
    Call Function2
    
    Application.Run "PERSONAL.XLSB!David"
    Call Function2
    
    Application.Run "PERSONAL.XLSB!Mary"
    Call Function2

   Application.Run "PERSONAL.XLSB!Bob"
    Call Function3
    
    Application.Run "PERSONAL.XLSB!Sally"
    Call Function3
    
    Application.Run "PERSONAL.XLSB!Sue"
    Call Function3
    
    Application.Run "PERSONAL.XLSB!David"
    Call Function3
    
    Application.Run "PERSONAL.XLSB!Mary"
    Call Function3

[shg]

I would modify the individual macros to be a single macro that accepts a name as an argument. Then you could do

Sub x()
    Dim asName()    As String
    Dim iFunc       As Long
    Dim iName       As Long

    asName = Split("Bob,Sally,David,Mary", ",")

    For iFunc = 1 To 3
        For iName = 0 To UBound(asName)
            FilterByName asName(iName)
            Run "Function" & iFunc
        Next iName
    Next iFunc
End Sub

Sub FilterByName(sName As String)
    ' ...
End Sub

[Angelfish13]

Hi shg,

I see what you are doing and I am definitely going to give this a try. The only problem is that all of my names are "wildcard" arguments that accept a certain string. Thanks for the starting point though!