 上传我的文档
 下载
 收藏
该文档贡献者很忙，什么也没留下。
 下载此文档
正在努力加载中...
尾1锛_鍗婁钩绯栧熀杞Щ閰垛厾鍜屸叅鍦ㄨ偪鐦ゅ拰鍑嬩骸涓殑浣滅敤鐮旂┒
下载积分：2498
内容提示：尾1锛_鍗婁钩绯栧熀杞Щ閰垛厾鍜屸叅鍦ㄨ偪鐦ゅ拰鍑嬩骸涓殑浣滅敤鐮旂┒
文档格式：PDF|
浏览次数：4|
上传日期： 05:25:11|
文档星级：
该用户还上传了这些文档
尾1锛_鍗婁钩绯栧熀杞Щ閰垛厾鍜屸叅鍦ㄨ偪鐦ゅ拰鍑嬩
官方公共微信高一数学题 在线等 急~~~~~~~~~_百度知道Using Worksheet Functions from VBA Code in Excel 2010
Using Worksheet Functions from VBA Code in Excel 2010
Expand the table of content
This documentation is archived and is not being maintained.
Using Worksheet Functions from VBA Code in Excel 2010
Office 2010
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.
Last modified:
June 05, 2011
Applies to:
Excel 2010 | Office 2010 | SharePoint Server 2010 | VBA
In this article
Published:
Provided by:
Dr. Gerard M. Verschuuren,
| Mark Roberts, Microsoft Corporation
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
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
sample workbook.
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
method, which produces the same results as the MAX function.
The first line of code (after activating the Employees worksheet) uses the
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
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
method to Selection.Address and setting the Type parameter to 8 creates a
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, _
objSelect.Select
The next lines of code pass the selected range to the , , and
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
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, _
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)
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
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)
Cells(i + 2, 1) = FormatCurrency(curAmount + (curAmount / 100) * 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 ( 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)
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)
Cells(i + 2, 1) = FormatCurrency(curAmount + (curAmount / 100) * 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)
' Format cells as bold, and autofit columns.
Cells.Font.Bold = True
Cells.EntireColumn.AutoFit
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
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)
NoteCalling 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)
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
method to trim leading and trailing spaces and the
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
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 . For more information about Excel worksheet functions, including examples with sample data, see .
IN THIS ARTICLE
Is this page helpful?
Your feedback about this content is important.Let us know what you think.
Additional feedback?
1500 characters remaining
Thank you!
We appreciate your feedback.}