excel - 有没有办法避免在多个带有变量的 Subs 中重复相同的代码？

Question

我有一个 vba 模块，我试图通过删除在九个单独的 Subs 中剪切和粘贴的代码来使其看起来更专业，每个 Subs 都有单独的变量。有没有避免剪切和粘贴的好方法？
我将九个 Subs 中的几个的开头部分放在下面；每个 Sub 有大约 300 行被复制(但不包括在这个问题中的空间)。

Sub OpenFilterABC()
        Dim rng As Range
        Dim s123 As String
        Dim s456 As String
        Dim x789 As String
        
        s123 = "<>ABC"
        s456 = "<>*ABC*"
        x789 = "ABC"
       
        Workbooks.Open Filename:="C:\Users\Desktop\ABC Today.xlsx"
                      
        Sheets("First Sheet").Select
            Set rng = Range("$A$1:$ZZ$157000")
                rng.AutoFilter Field:=7, Criteria1:=s123
                        
        Sheets("Second Sheet").Select
            Set rng = Range("$A$1:$ZZ$157000")
                rng.AutoFilter Field:=7, Criteria1:=x789
                rng.AutoFilter Field:=24, Criteria1:=s456

'There are an additional 300+ lines per Sub, plus nine total Subs; just shortened it for this question

End Sub

Sub OpenFilterDEF()
        Dim rng As Range
        Dim s123 As String
        Dim s456 As String
        Dim x789 As String
        
        s123 = "<>DEF"
        s456 = "<>*DEF*"
        x789 = "DEF"
       
        Workbooks.Open Filename:="C:\Users\Desktop\DEF Today.xlsx"
                      
        Sheets("First Sheet").Select
            Set rng = Range("$A$1:$ZZ$157000")
                rng.AutoFilter Field:=7, Criteria1:=s123
                        
        Sheets("Second Sheet").Select
            Set rng = Range("$A$1:$ZZ$157000")
                rng.AutoFilter Field:=7, Criteria1:=x789
                rng.AutoFilter Field:=24, Criteria1:=s456
End Sub

Sub OpenFilterGHI()
        Dim rng As Range
        Dim s123 As String
        Dim s456 As String
        Dim x789 As String
        
        s123 = "<>GHI"
        s456 = "<>*GHI*"
        x789 = "GHI"
       
        Workbooks.Open Filename:="C:\Users\Desktop\GHI Today.xlsx"
                      
        Sheets("First Sheet").Select
            Set rng = Range("$A$1:$ZZ$157000")
                rng.AutoFilter Field:=7, Criteria1:=s123
                        
        Sheets("Second Sheet").Select
            Set rng = Range("$A$1:$ZZ$157000")
                rng.AutoFilter Field:=7, Criteria1:=x789
                rng.AutoFilter Field:=24, Criteria1:=s456
End Sub

Answer 1

您可以创建一个通用函数并传递变量。然后根据需要调用函数。

Sub OpenFilter( s123_Arg As String, s456_Arg As String,x789_Arg As String, Filename_Arg As String)
        Dim rng As Range
        Dim s123 As String
        Dim s456 As String
        Dim x789 As String
        
        s123 = s123_Arg
        s456 = s456_Arg
        x789 = x789_Arg
       
        Workbooks.Open Filename:=Filename_Arg
                      
        Sheets("First Sheet").Select
            Set rng = Range("$A$1:$ZZ$157000")
                rng.AutoFilter Field:=7, Criteria1:=s123
                        
        Sheets("Second Sheet").Select
            Set rng = Range("$A$1:$ZZ$157000")
                rng.AutoFilter Field:=7, Criteria1:=x789
                rng.AutoFilter Field:=24, Criteria1:=s456
End Sub

Sub OpenFilterABC()
     Call OpenFilter("<>ABC","<>*ABC*","ABC","C:\Users\Desktop\ABC Today.xlsx")
End Sub