SUM in VBA

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Gary''s Student - 23 Jan 2006 17:32 GMT

In the worksheet:
=SUM(Sheet1!A1:Sheet1!A13)
works just fine, giving the sum of cells.
In VBA:
Dim i As Variant
i = Application.SUM(Sheet1!A1:Sheet1!A13) just give a compile error.

Why?

Signature

Gary's Student

Reply to this Message

Charles Maxson - 23 Jan 2006 18:01 GMT

Try this:

i = WorksheetFunction.Sum(Range("Sheet1!A1:Sheet1!A13"))

> In the worksheet:
> =SUM(Sheet1!A1:Sheet1!A13)
[quoted text clipped - 4 lines]
>
> Why?

Reply to this Message

Toppers - 23 Jan 2006 18:01 GMT

i = Application.Sum(Worksheets("Sheet1").Range("A1:A13"))

> In the worksheet:
> =SUM(Sheet1!A1:Sheet1!A13)
[quoted text clipped - 4 lines]
>
> Why?

Reply to this Message

Bob Phillips - 23 Jan 2006 18:01 GMT

VBA works on a range object,

i = Application.SUM(Range("Sheet1!A1:Sheet1!A13")

--

HTH

Bob Phillips

(remove nothere from the email address if mailing direct)

> In the worksheet:
> =SUM(Sheet1!A1:Sheet1!A13)
[quoted text clipped - 6 lines]
> --
> Gary's Student

Reply to this Message

JackL - 23 Jan 2006 18:17 GMT

Try this:

Sub test()
Dim myrange As Range
Dim i As Variant
Set myrange = Worksheets("Sheet1").Range("A1:A13")
i = Application.WorksheetFunction.Sum(myrange)
End Sub

> VBA works on a range object,
>
[quoted text clipped - 18 lines]
> > --
> > Gary's Student

Reply to this Message