Skip to main content

WorksheetFunction object

Table of contents
  1. Example

Used as a container for Microsoft Excel worksheet functions that can be called from Visual Basic.


Use the WorksheetFunction property of the Application object to return the WorksheetFunction object.

The following example displays the result of applying the Min worksheet function to the range A1:C10.

Set myRange = Worksheets("Sheet1").Range("A1:C10") 
answer = Application.WorksheetFunction.Min(myRange) 
MsgBox answer

The following example uses the CountA worksheet function to determine how many cells in column A contain a value. For this example, the values in column A should be text. This example does a spell check on each value in column A, and if the value is spelled incorrectly, inserts the text "Wrong" into column B; otherwise, it inserts the text "OK" into column B.

Sub StartSpelling()
    'Set up your variables
    Dim iRow As Integer
    'And define your error handling routine.
    'Go through all the cells in column A, and perform a spellcheck on the value.
    'If the value is spelled incorrectly, write "Wrong" in column B; otherwise, write "OK".
    For iRow = 1 To WorksheetFunction.CountA(Columns(1))
        If Application.CheckSpelling( _
            Cells(iRow, 1).Value, , True) = False Then
            Cells(iRow, 2).Value = "Wrong"
            Cells(iRow, 2).Value = "OK"
        End If
    Next iRow
    Exit Sub
     'Error handling routine.
    MsgBox "The spell check feature is not installed!"
End Sub

Leave a comment

Your email address will not be published. Required fields are marked *

Format your code: <pre><code class="language-vba">place your code here</code></pre>