Using Worksheet Functions from VBA Code in Excel 2010
Summary: Learn how to use the methods of the WorksheetFunction object in Visual Basic for Applications (VBA) so that your code can access the power of the worksheet functions in Microsoft Excel 2010.
Applies to: Excel 2010 | Office 2010 | SharePoint Server 2010 | VBA
In this article
Overview of Using VBA Methods to Access Excel Functions
Assigning the Next Available ID by Using the Max Method
Displaying Summary Information for the Selected Range
Creating a Table of Monthly Loan Payments
Displaying a Sum Based on Multiple Criteria
Cleaning Data by Using the Trim and Proper Methods
Conclusion
Additional Resources
Published: June 2011
Provided by: .jpg "Community Member Icon")
Dr. Gerard M. Verschuuren, Mr. Excel | Mark Roberts, Microsoft Corporation
Contents
Overview of Using VBA Methods to Access Excel Functions
Assigning the Next Available ID by Using the Max Method
Displaying Summary Information for the Selected Range
Creating a Table of Monthly Loan Payments
Displaying a Sum Based on Multiple Criteria
Cleaning Data by Using the Trim and Proper Methods
Conclusion
Additional Resources
.jpg "Click to get code")
Download sample workbook: Using Worksheet Functions from VBA Code (WorksheetFunction.xlsm)
Overview of Using VBA Methods to Access Excel Functions
The WorksheetFunction object makes most of the built-in Microsoft Excel 2010 worksheet functions accessible to you as methods in Visual Basic for Applications (VBA), so you don't have to keep reinventing the wheel. Although you can access the WorksheetFunction object through the Application object by using the WorksheetFunction property, doing so is not necessary for VBA code written in an Excel workbook. You can use the WorksheetFunction object as a top-level object, and access its methods directly as shown in this simple code example.
Dim mySum As Integer
mySum = WorksheetFunction.Sum(100,200)
Given the ease of using the WorksheetFunction object directly, you might wonder why it is also accessible from the Application object. The reason is that you might need to use the Excel worksheet functions from code running in other applications. For example, if you add three content controls to a Microsoft Word document, open the VBA code editor, and set a reference to the Microsoft Excel 14.0 Object Library, you can then write code such as the following. This example sums the values entered in the first two controls, and then enters that value in the third control.
Dim xlApp As Excel.Application
Dim x, y As Integer
Set xlApp = New Excel.Application
x = ActiveDocument.ContentControls(1).Range.Text
y = ActiveDocument.ContentControls(2).Range.Text
ActiveDocument.ContentControls(3).Range.Text = _
xlApp.WorksheetFunction.Sum(x, y)
Unlike a worksheet function in the Excel user interface, the parameters of a method of the WorksheetFunction object don't have descriptive names. For example, when using the SUMIF function directly in a workbook, Excel displays SUMIF(range,criteria,[sum_range]). But, when using the corresponding SumIf method in VBA code, IntelliSense displays its parameters as SumIf(Arg1 As Range, Arg2, [arg3]) As Double. If you need more information about the parameter values, click the function name in the code editor, and then press F1 to display the corresponding help topic, which describes each parameter.
To run the code described in the remainder of this article with sample data, download the WorksheetFunction sample workbook.
Assigning the Next Available ID by Using the Max Method
The NewRecord macro in Module1 of the WorksheetFunction sample workbook adds a new record to the Employees worksheet and assigns the next available ID number by using the Max method, which produces the same results as the MAX function.
The first line of code (after activating the Employees worksheet) uses the End and Offset properties to select the first empty cell after the last cell in column A.
Range("A1").End(xlDown).Offset(1, 0).Select
The last line of code then calls the Max method, increments the value by 1, and assigns that value to the current cell.
ActiveCell = WorksheetFunction.Max(ActiveCell.EntireColumn) + 1
This macro works well when no numbers in the first column are below the table of Employee data. Also, it always returns the next maximum number, without replacing any lower numbers that have been deleted.
The following listing shows the complete code of the NewRecord macro.
Sub NewRecord()
' Add new record and increment the value in the first column.
Sheets("Employees").Activate
' Select the first cell after the last filled cell in column A.
Range("A1").End(xlDown).Offset(1, 0).Select
' Determine the maximum value in the column and add 1.
ActiveCell = WorksheetFunction.Max(ActiveCell.EntireColumn) + 1
End Sub
Displaying Summary Information for the Selected Range
The Summary macro gives you summary information for a selected range of cells.
The initial lines of the Summary macro select the Employees worksheet and then prompt the user to select the range of data to chart. Setting the Default parameter of the InputBox method to Selection.Address and setting the Type parameter to 8 creates a Range object with its Address property set to reference the range selected by the user. The objSelect.Select line of code at the end of this excerpt ensures that the range is selected in case the user typed the range in the input box.
Sheets("Employees").Activate
Set objSelect = Application.InputBox(Prompt:="Select range of values to summarize", _
Default:=Selection.Address, _
Type:=8)
objSelect.Select
The next lines of code pass the selected range to the Count, Sum, and Average methods.
iCount = WorksheetFunction.Count(objSelect)
pSum = WorksheetFunction.Sum(objSelect)
pAvg = WorksheetFunction.Average(objSelect)
The final line of code displays the values in a message box.
MsgBox "Count: " & iCount & vbCr & _
"Sum: " & FormatNumber(pSum, 2) & vbCr & _
"Avg: " & FormatNumber(pAvg, 2)
This is the same set of information that Excel already displays in the status bar, but you could call other worksheet functions to create a custom summary. You can also override the default values displayed in the status bar by setting the StatusBar property of the Application object.
The following listing shows the complete code of the Summary macro.
Sub Summary()
' Displays the sum, average, and count of the selected range.
Dim objSelect As Range
Dim iCount As Long, pSum As Double, pAvg As Double
Sheets("Employees").Activate
' Prompt user for range to summarize.
Set objSelect = Application.InputBox(Prompt:="Select range of values to summarize", _
Default:=Selection.Address, _
Type:=8)
objSelect.Select
' Use Count, Sum, and Average worksheet functions
' on the selected range.
iCount = WorksheetFunction.Count(objSelect)
pSum = WorksheetFunction.Sum(objSelect)
pAvg = WorksheetFunction.Average(objSelect)
' Display values.
MsgBox "Count: " & iCount & vbCr & _
"Sum: " & FormatNumber(pSum, 2) & vbCr & _
"Avg: " & FormatNumber(pAvg, 2)
End Sub
Creating a Table of Monthly Loan Payments
The PMT_Table macro inserts a new worksheet and then creates a table of monthly payments for a range of loan amounts within a range of annual percentage rates (APR). Excel enables you to do this with the function PMT, which you can call from VBA by using the corresponding Pmt method.
The first line of code calls the InsertSheet macro, which adds a new worksheet and prompts the user to name it. The following two lines prompt the user to provide the loan amount and APR.
Next, the nested For/Next loops shown in the following code sample create ten column headings that increment the initial APR percentage by 5 percent, and then create ten row labels that increment the initial loan amount by 10 percent.
For i = 0 To 9
For j = 0 To 9
Cells(1, j + 2) = FormatPercent(fAPR + 0.0005 * j)
Next j
Cells(i + 2, 1) = FormatCurrency(curAmount + (curAmount / 100) * i)
Next i
After the column and row labels are created, another set of nested For/Next loops shown in the following code excerpt supplies the values of the payment table by using the Pmt method. Because the table needs to provide monthly payments, Arg1, which corresponds to rate, is divided by 12. Similarly, because the table represents a 30-year loan of monthly payments, Arg2, which corresponds to nper (the number of payments), is 360 (30 years x 12 months). The value of Arg3, which corresponds to pv (present value; in this case, the loan amount), is read from the first cell of the row being filled.
For i = 2 To 11
For j = 2 To 11
curPMT = WorksheetFunction.Pmt(Arg1:=Cells(1, j) / 12, Arg2:=360, Arg3:=Cells(i, 1))
Cells(i, j) = FormatCurrency(curPMT)
Next j
Next i
The final lines of code format the contents of the table as bold and adjust the column width.
The following listing shows the complete code of the PMT_Table macro.
Sub PMT_Table()
' Creates a table of mortgage payments for specified loan amount and APR.
Dim i As Integer, j As Integer, curAmount As Currency, fAPR As Single
Dim objCell As Range, curPMT As Currency
' Call InsertSheet Sub procedure.
InsertSheet
' Prompt user for loan amount and APR.
curAmount = InputBox(Prompt:="Loan amount?", _
Default:=60000)
fAPR = InputBox(Prompt:="APR?", _
Default:=0.06)
' Create APR column headings, and loan amount row labels.
For i = 0 To 9
For j = 0 To 9
Cells(1, j + 2) = FormatPercent(fAPR + 0.0005 * j)
Next j
Cells(i + 2, 1) = FormatCurrency(curAmount + (curAmount / 100) * i)
Next i
' Fill in payment table values.
For i = 2 To 11
For j = 2 To 11
curPMT = WorksheetFunction.Pmt(Cells(1, j) / 12, 360, Cells(i, 1))
Cells(i, j) = FormatCurrency(curPMT)
Next j
Next i
' Format cells as bold, and autofit columns.
Cells.Font.Bold = True
Cells.EntireColumn.AutoFit
End Sub
Displaying a Sum Based on Multiple Criteria
The CalcSalaries macro sums the salaries for employees in a specific department and a specific location. Creating a macro like this would require lots of looping if we didn't have the SumIfs method, which was first added in Excel 2007.
After activating the Employees worksheet, the following code sets three Range object variables to the columns that correspond to the DEPT, LOCATION, and SALARY columns.
With ActiveCell.CurrentRegion
' Set ranges for the department, location, and salary columns.
Set objDept = .Columns(4)
Set objLoc = .Columns(5)
Set objSal = .Columns(6)
Note
Calling the CurrentRegion property at this point in the code assumes that the user has selected at least one of the cells within the table of employee data. If the selection is in a blank cell outside of this region, the code does not work.
The code continues by prompting the user for the department and location. The If statements enable the user to leave the prompt blank or to cancel if he or she does not want a specific filter. (The asterisk [*] character acts as a wildcard.)
strDept = InputBox(Prompt:="Which department (blank or cancel for all departments)?", _
Default:="Finance")
If strDept = "" Then strDept = "*"
strLoc = InputBox(Prompt:="Which location (blank or cancel for all locations)?", _
Default:="Boston")
If strLoc = "" Then strLoc = "*"
The final set of lines call the SumIfs method to do the hard work for you, and then displays the result.
curSum = WorksheetFunction.SumIfs(objSal, objDept, strDept, objLoc, strLoc)
MsgBox "The total for " & strDept & " in " & strLoc & " is: " & FormatCurrency(curSum)
The following listing shows the complete code of the CalcSalaries macro.
Sub CalcSalaries()
' Calculates the sum of saleries for the specified department and location.
Dim objDept As Range, objLoc As Range, objSal As Range
Dim strDept As String, strLoc As String, curSum As Currency
Sheets("Employees").Activate
' This With statement returns a Range object that represents the range
' that surrounds the active cell.
With ActiveCell.CurrentRegion
' Set ranges for the department, location, and salary columns.
Set objDept = .Columns(4)
Set objLoc = .Columns(5)
Set objSal = .Columns(6)
' Prompt for department and location.
strDept = InputBox(Prompt:="Which department (cancel or blank for all departments)?", _
Default:="Finance")
If strDept = "" Then strDept = "*"
strLoc = InputBox(Prompt:="Which location (cancel or blank for all locations)?", _
Default:="Boston")
If strLoc = "" Then strLoc = "*"
' Calculate and display sum of specified salaries.
curSum = WorksheetFunction.SumIfs(objSal, objDept, strDept, objLoc, strLoc)
MsgBox "The total for " & strDept & " in " & strLoc & " is: " & FormatCurrency(curSum)
End With
End Sub
Cleaning Data by Using the Trim and Proper Methods
The CleanUpData macro in the WorksheetFunction sample workbook is a simple macro that cleans up a table of data by properly capitalizing entries and taking out spaces at the beginning and the end of each cell. It does this by looping through each cell in the current region and calling the Trim method to trim leading and trailing spaces and the Proper method to fix capitalization.
The following listing shows the complete code of the CleanUpData macro.
Sub CleanUpData()
' Trims irregular spacing, and corrects capitalization.
Dim objCell As Range
For Each objCell In ActiveCell.CurrentRegion
objCell = WorksheetFunction.Trim(objCell)
objCell = WorksheetFunction.Proper(objCell)
Next objCell
End Sub
Conclusion
This article and the WorksheetFunction sample workbook provide examples of using the Excel worksheet functions in VBA code. To view a list of all the WorksheetFunction object methods that map to worksheet functions you can use in Excel 2010 VBA code, see WorksheetFunction Members (Excel). For more information about Excel worksheet functions, including examples with sample data, see List of worksheet functions (alphabetical).