Excel VBA

The following code, executed in response to a click on a button named "cmdSumNumbers", will create a Range object for cells A1 through C5, and then display a message box with the sum of that range:

Private Sub cmdSumNumbers_Click()
Dim xlApp As Excel.Application
Dim xlWSheet As Excel.Worksheet
Dim xlRange As Excel.Range

Set xlApp = New Excel.Application
xlApp.Workbooks.Open "Book1.xls"
Set xlWSheet = xlApp.Worksheets("Sheet1")
Set xlRange = xlWSheet.Range("A1:C5")
'Set xlRange = xlWSheet.Range(xlWSheet.Cells(1, 1), xlWSheet.Cells(5, 3))

'Display result of Sum function
MsgBox "Answer is " & xlApp.WorksheetFunction.Sum(xlRange)

Set xlRange = Nothing
Set xlWSheet = Nothing
Set xlApp = Nothing
End Sub

The second "Set xlRange" statement that is commented out shows how the "Cells(x,y)" notation can be used to specify a range. The advantage to using the "Cells(x,y)" notation is that variables can be used to select the beginning and ending cells for the range, rather than hard-coding a cell reference like "A1".

The "WorksheetFunction" object serves as a container for the function.

Hope this helps...

Try something like this:

Set xlRange = xlWSheet.Range("A1:C5")
'Set xlRange = xlWSheet.Range(xlWSheet.Cells(1, 1), xlWSheet.Cells(5, 3))

'Display result of Sum function
MsgBox "Answer is " & xlApp.WorksheetFunction.Sum(xlRange)