VBA/Excel/Access/Word/Excel/WorksheetFunction

A function with a range argument

   <source lang="vb">

 =TopAvg(Data,5)
 Function TopAvg(InRange, N)
     Dim Sum As Double
     Dim I As Long
     Sum = 0
     For i = 1 To N
         Sum = Sum + Application.WorksheetFunction.LARGE(InRange, i)
     Next i
     TopAvg = Sum / N
 End Function          
</source>
   
  

Calculating a mortgage payment

   <source lang="vb">

Sub PmtCalc()

   Dim IntRate As Double
   Dim LoanAmt As Double
   Dim Periods As Integer
   IntRate = 0.0825 / 12
   Periods = 30 * 12
   LoanAmt = 150000
   MsgBox WorksheetFunction.pmt(IntRate, Periods, LoanAmt)

End Sub

</source>
   
  

Finding the maximum value in a range

   <source lang="vb">

Sub ShowMax()

   Dim TheMax As Double
   TheMax = WorksheetFunction.Max(range("A1:C3"))
   MsgBox TheMax

End Sub

</source>
   
  

Insert the values directly as the function arguments:

   <source lang="vb">

Sub pmt()

   MsgBox WorksheetFunction.pmt(0.0825 / 12, 360, -150000)

End Sub

</source>
   
  

Test the effect of the Option Base statement: declare that arrays are 1-based

   <source lang="vb">

Option Base 1

    Sub Array1()
        Dim aiData(10) As Integer
        Dim i As Integer
        For i = LBound(aiData) To UBound(aiData)
            aiData(i) = i
        Next i
        Debug.Print "Lower Bound = " & LBound(aiData)
        Debug.Print "Upper Bound = " & UBound(aiData)
        Debug.Print "Num Elements = " & WorksheetFunction.Count(aiData)
        Debug.Print "Sum Elements = " & WorksheetFunction.Sum(aiData)
    End Sub
</source>
   
  

Use case select statement to call common functions

   <source lang="vb">

Function STATFUNCTION(rng, op)

   Select Case UCase(op)
       Case "SUM"
           STATFUNCTION = WorksheetFunction.Sum(rng)
       Case "AVERAGE"
           STATFUNCTION = WorksheetFunction.Average(rng)
       Case "MEDIAN"
           STATFUNCTION = WorksheetFunction.Median(rng)
       Case "MODE"
           STATFUNCTION = WorksheetFunction.Mode(rng)
       Case "COUNT"
           STATFUNCTION = WorksheetFunction.Count(rng)
       Case "MAX"
           STATFUNCTION = WorksheetFunction.Max(rng)
       Case "MIN"
           STATFUNCTION = WorksheetFunction.Min(rng)
       Case "VAR"
           STATFUNCTION = WorksheetFunction.Var(rng)
       Case "STDEV"
           STATFUNCTION = WorksheetFunction.StDev(rng)
       Case Else
           STATFUNCTION = CVErr(xlErrNA)
   End Select

End Function

</source>
   
  

Use fully qualified properties:

   <source lang="vb">

Sub sumDemo()

    MsgBox WorksheetFunction.Sum(Sheets("Sheet1").Range(_
                               Sheets("Sheet1").Range("A1"), _
                               Sheets("Sheet1").Range("A10")))

End Sub

</source>
   
  

Use the LARGE function to determine the kth-largest value in a range

   <source lang="vb">

Sub func()

   SecondHighest = WorksheetFunction.Large(range("NumberList"), 2)

End Sub

</source>
   
  

use the Range property as an argument within another Range property

   <source lang="vb">

Sub range()

   WorksheetFunction.Sum (Worksheets("Sheet2").range(Worksheets("Sheet2"). _
       range("A1"), Worksheets("Sheet2").range("A7")))
   With Worksheets("Sheet2")
       WorksheetFunction.Sum (.range(.range("A1"), .range("A7")))
   End With

End Sub

</source>
   
  

Use the Transpose function to turn the one column into one row

   <source lang="vb">

Sub TransposeArray()

   Dim myArray As Variant
  myArray = WorksheetFunction.Transpose(range("myTran"))
  MsgBox "The 5th element of the Array is: " & myArray(5)

End Sub

</source>
   
  

Using a lookup function

   <source lang="vb">

Sub GetPrice()

   Dim PartNum As Variant
   Dim Price As Double
   PartNum = InputBox("Enter the Part Number")
   Sheets("Prices").activate
   Price = WorksheetFunction.VLookup(PartNum, range("PriceList"), 2, False)
   MsgBox PartNum & " costs " & Price

End Sub

</source>