an extreme newbie vba question

[n64play]

How do I get a the sum of a range of cells to display in a msgbox when the user clicks on a command button? In excel, its using the SUM function, but VBA doesnt have that. For instance, i want the sum of cells J10 to :J30....

[D-niss]

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

[n64play]

I keep getting an error saying "Subscript out of range". any ideas?

[D-niss]

try this:

Option Explicit
 
Sub test()
Dim myRange As Range
Dim answer As String
 
Set myRange = Worksheets("Sheet1").Range("A1:C10")
answer = Application.WorksheetFunction.Sum(myRange)
MsgBox "The sum is " & Format(answer, "currency")
End Sub