VBA存储全球使用代码

Question

我一直在创建几个函数，所有这些函数都有很多相同的代码。有问题的代码是从Dim Order_Type As Range降到Set PMethod10 = Sheets("KRONOS").Range("$BK:$BK")。

我怎样才能把它放在一个全局函数或类似的东西，以便只调用该函数，而不是每次都使用整个代码？

Public Function BANKING1(rev_date As Date) As Variant 

    Dim Order_Type As Range 
    Dim Final_Price As Range 
    Dim PaidAlt As Range 
    Dim Excl_Rev As Range 
    Dim PAmount1 As Range 
    Dim PMethod1 As Range 
    Dim PAmount2 As Range 
    Dim PayDate2 As Range 
    Dim PMethod2 As Range 
    Dim Vstatus As Range 
    Dim Team As Range 

    Application.Volatile (True) 

    Set Order_Type = Sheets("KRONOS").Range("$D:$D") 
    Set Final_Price = Sheets("KRONOS").Range("$H:$H") 
    Set PaidAlt = Sheets("KRONOS").Range("$I:$I") 
    Set Excl_Rev = Sheets("KRONOS").Range("$K:$K") 
    Set Vstatus = Sheets("KRONOS").Range("$DL:$DL") 
    Set Team = Sheets("KRONOS").Range("$DO:$DO") 

    Set PAmount1 = Sheets("KRONOS").Range("$O:$O") 
    Set First_PD = Sheets("KRONOS").Range("$Q:$Q") 
    Set PMethod1 = Sheets("KRONOS").Range("$R:$R") 

    Set PAmount2 = Sheets("KRONOS").Range("$T:$T") 
    Set PayDate2 = Sheets("KRONOS").Range("$V:$V") 
    Set PMethod2 = Sheets("KRONOS").Range("$W:$W") 

    Set PAmount3 = Sheets("KRONOS").Range("$Y:$Y") 
    Set PayDate3 = Sheets("KRONOS").Range("$AA:$AA") 
    Set PMethod3 = Sheets("KRONOS").Range("$AB:$AB") 

    Set PAmount4 = Sheets("KRONOS").Range("$AD:$AD") 
    Set PayDate4 = Sheets("KRONOS").Range("$AF:$AF") 
    Set PMethod4 = Sheets("KRONOS").Range("$AG:$AG") 

    Set PAmount5 = Sheets("KRONOS").Range("$AI:$AI") 
    Set PayDate5 = Sheets("KRONOS").Range("$AK:$AK") 
    Set PMethod5 = Sheets("KRONOS").Range("$AL:$AL") 

    Set PAmount6 = Sheets("KRONOS").Range("$AN:$AN") 
    Set PayDate6 = Sheets("KRONOS").Range("$AP:$AP") 
    Set PMethod6 = Sheets("KRONOS").Range("$AQ:$AQ") 

    Set PAmount7 = Sheets("KRONOS").Range("$AS:$AS") 
    Set PayDate7 = Sheets("KRONOS").Range("$AU:$AU") 
    Set PMethod7 = Sheets("KRONOS").Range("$AV:$AV") 

    Set PAmount8 = Sheets("KRONOS").Range("$AX:$AX") 
    Set PayDate8 = Sheets("KRONOS").Range("$AZ:$AZ") 
    Set PMethod8 = Sheets("KRONOS").Range("$BA:$BA") 

    Set PAmount9 = Sheets("KRONOS").Range("$BC:$BC") 
    Set PayDate9 = Sheets("KRONOS").Range("$BE:$BE") 
    Set PMethod9 = Sheets("KRONOS").Range("$BF:$BF") 

    Set PAmount10 = Sheets("KRONOS").Range("$BH:$BH") 
    Set PayDate10 = Sheets("KRONOS").Range("$BJ:$BJ") 
    Set PMethod10 = Sheets("KRONOS").Range("$BK:$BK") 


      BANKING1 = Application.WorksheetFunction.SumIfs(_ 
      PAmount1 _ 
      , Team, "<>9" _ 
      , Vstatus, "<>rejected", Vstatus, "<>unverified" _ 
      , Excl_Rev, "<>1" _ 
      , PMethod1, "<>Credit" _ 
      , PMethod1, "<>Amendment" _ 
      , PMethod1, "<>Pre-paid" _ 
      , PMethod1, "<>Write Off" _ 
      , First_PD, rev_date) 

End Function 

Answer 1

您没有返回任何值，因此您不需要创建函数就可以使用子例程。但是，因为你所做的全部都是设置范围变量，所以你可能只需要把它们全部命名为范围，并删除所有的代码。然后你会引用它们像这样：

Range("Final_Price").Value = 30 

这里有一个video on Named Ranges，here's another，这里的a tutorial。

Answer 2

你不能让你的变量声明别的，但你可以委托一个对象来为你保存它们。

创建一个类，并把这个代码到其中：

Option Explicit 

Public Order_Type As Range 
Public Final_Price As Range 
Public PaidAlt As Range 
Public Excl_Rev As Range 
Public PAmount1 As Range 
Public PMethod1 As Range 
Public PAmount2 As Range 
Public PayDate2 As Range 
Public PMethod2 As Range 
Public Vstatus As Range 
Public Team As Range 


Private Sub Class_Initialize() 
    Set Order_Type = Sheets("KRONOS").Range("$D:$D") 
    Set Final_Price = Sheets("KRONOS").Range("$H:$H") 
    Set PaidAlt = Sheets("KRONOS").Range("$I:$I") 
    Set Excl_Rev = Sheets("KRONOS").Range("$K:$K") 
    Set Vstatus = Sheets("KRONOS").Range("$DL:$DL") 
    Set Team = Sheets("KRONOS").Range("$DO:$DO") 

    Set PAmount1 = Sheets("KRONOS").Range("$O:$O") 
    Set First_PD = Sheets("KRONOS").Range("$Q:$Q") 
    Set PMethod1 = Sheets("KRONOS").Range("$R:$R") 

    Set PAmount2 = Sheets("KRONOS").Range("$T:$T") 
    Set PayDate2 = Sheets("KRONOS").Range("$V:$V") 
    Set PMethod2 = Sheets("KRONOS").Range("$W:$W") 

    Set PAmount3 = Sheets("KRONOS").Range("$Y:$Y") 
    Set PayDate3 = Sheets("KRONOS").Range("$AA:$AA") 
    Set PMethod3 = Sheets("KRONOS").Range("$AB:$AB") 

    Set PAmount4 = Sheets("KRONOS").Range("$AD:$AD") 
    Set PayDate4 = Sheets("KRONOS").Range("$AF:$AF") 
    Set PMethod4 = Sheets("KRONOS").Range("$AG:$AG") 

    Set PAmount5 = Sheets("KRONOS").Range("$AI:$AI") 
    Set PayDate5 = Sheets("KRONOS").Range("$AK:$AK") 
    Set PMethod5 = Sheets("KRONOS").Range("$AL:$AL") 

    Set PAmount6 = Sheets("KRONOS").Range("$AN:$AN") 
    Set PayDate6 = Sheets("KRONOS").Range("$AP:$AP") 
    Set PMethod6 = Sheets("KRONOS").Range("$AQ:$AQ") 

    Set PAmount7 = Sheets("KRONOS").Range("$AS:$AS") 
    Set PayDate7 = Sheets("KRONOS").Range("$AU:$AU") 
    Set PMethod7 = Sheets("KRONOS").Range("$AV:$AV") 

    Set PAmount8 = Sheets("KRONOS").Range("$AX:$AX") 
    Set PayDate8 = Sheets("KRONOS").Range("$AZ:$AZ") 
    Set PMethod8 = Sheets("KRONOS").Range("$BA:$BA") 

    Set PAmount9 = Sheets("KRONOS").Range("$BC:$BC") 
    Set PayDate9 = Sheets("KRONOS").Range("$BE:$BE") 
    Set PMethod9 = Sheets("KRONOS").Range("$BF:$BF") 

    Set PAmount10 = Sheets("KRONOS").Range("$BH:$BH") 
    Set PayDate10 = Sheets("KRONOS").Range("$BJ:$BJ") 
    Set PMethod10 = Sheets("KRONOS").Range("$BK:$BK") 
End Sub 

然后你的函数：

Public Function BANKING1(rev_date As Date) As Variant 
    Application.Volatile True 

    With New Class1 
    BANKING1 = Application.WorksheetFunction.SumIfs(_ 
       .PAmount1 _ 
       , .Team, "<>9" _ 
       , .Vstatus, "<>rejected", .Vstatus, "<>unverified" _ 
       , .Excl_Rev, "<>1" _ 
       , .PMethod1, "<>Credit" _ 
       , .PMethod1, "<>Amendment" _ 
       , .PMethod1, "<>Pre-paid" _ 
       , .PMethod1, "<>Write Off" _ 
       , .First_PD, rev_date) 
    End With 

End Function 

所有在您​​不是列出这些领域将需要以及列为Public 。

或者，Class1的实例可以存储在工作表中的全局变量中，因此您不必每次都创建新实例，但是Excel随后会喜欢忘记全局引用。