VBA/Excel/Access/Word/Excel/WorksheetFunction

A function with a range argument

 
  =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

Calculating a mortgage payment

 
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

Finding the maximum value in a range

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

Insert the values directly as the function arguments:

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

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

 
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

Use case select statement to call common functions

 
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

Use fully qualified properties:

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

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

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

use the Range property as an argument within another Range property

 
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

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

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

Using a lookup function

 
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