有沒有辦法避免在多個帶有變數的Sub中重復相同的代碼？

我有一個 vba 模塊，我試圖通過洗掉在九個單獨的 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

uj5u.com熱心網友回復：

您可以創建一個通用函式并傳遞變數。然后根據需要呼叫函式。

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

標籤：擅长 vba 变量

上一篇：MVC屬性路由與查詢引數不起作用

下一篇：僅當在另一個作業表中找不到匹配項時，VBA才能復制一系列單元格