Worksheet Functions in VBA
September 14th, 2007Most worksheet functions are available for use in VBA using the WorksheetFunction object. For example, the worksheet function:
=SUM(A1:A100)
will have this equivalent in VBA:
WorksheetFunction.Sum(Range("A1:A100"))
Try putting values in range A1:A10, and try this sample code:
Sub test()
Dim r As Range
Set r = Worksheets("Sheet1").Range("A1:A10")
MsgBox "Sum = " & GetSum(r) & vbNewLine & "Max Value = " & GetMax(r), vbOKOnly, "A1:A10"
End Sub
Dim r As Range
Set r = Worksheets("Sheet1").Range("A1:A10")
MsgBox "Sum = " & GetSum(r) & vbNewLine & "Max Value = " & GetMax(r), vbOKOnly, "A1:A10"
End Sub
Function GetSum(rSum As Range) As Double
GetSum = WorksheetFunction.Sum(rSum)
End Function
Function GetMax(rMax As Range) As Double
GetMax = WorksheetFunction.Max(rMax)
End Function
No Comments yet »
RSS feed for comments on this post. TrackBack URI
Leave a comment
(C) by Virgilio Adriano. All rights reserved. Powered by WordPress.
Entries and comments feeds.
It took 0.504 seconds to load this page.