WorksheetFunction object (Excel)
Used as a container for Microsoft Excel worksheet functions that can be called from Visual Basic.
Use the WorksheetFunction property 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
Sample code provided by: Holy Macro! Books, Holy Macro! It's 2,500 Excel VBA Examples
This 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. On Error GoTo ERRORHANDLER '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" Else Cells(iRow, 2).Value = "OK" End If Next iRow Exit Sub 'Error handling routine. ERRORHANDLER: MsgBox "The spell check feature is not installed!" End Sub
About the contributor
Holy Macro! Books publishes entertaining books for people who use Microsoft Office. See the complete catalog at MrExcel.com.
Support and feedback
Have questions or feedback about Office VBA or this documentation? Please see Office VBA support and feedback for guidance about the ways you can receive support and provide feedback.