CODEWITHSUNDEEP
excelfunctionvba
James
James

Reputation: 737

VBA storing Code for global usage

I have been creating several functions, all of which share a lot of the same code. The code in question is from Dim Order_Type As Range down to Set PMethod10 = Sheets("KRONOS").Range("$BK:$BK").

How can I put this in a global function or something similar so as to only call that function and not use the whole code again each time? 

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

Upvotes: 0

Views: 1542

Answers (2)

GSerg
GSerg

Reputation: 78185

You can't have your variable declared somehwere else, but you can delegate an object to hold them for you.

Create a class and put this code into it:

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

Then your function:

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

All these fields not listed in your DIMs will need to be listed as Public as well.

Alternatively, an instance of Class1 can be stored in a global variable in a worksheet so you don't have to create new instance each time, but then, Excel loves to forget global references.

Upvotes: 2

Lance Roberts
Lance Roberts

Reputation: 22842

You're not returning any values so you don't need to make a function you can use a subroutine. However, since all you're doing is setting Range variables, you should probably just make all of them named ranges, and delete all that code. You'd then reference them like so:

Range("Final_Price").Value = 30

Here's a video on Named Ranges, here's another, and here's a tutorial.

Upvotes: 3

Related Questions