CODEWITHSUNDEEP
vbaexcel
mits
mits

Reputation: 926

VBA How to add cell values without looping

I have read about Application.WorksheetFunction.Sum but I was wondering if there is a specific method or property of the Range-object that does the job. I have looked into Range's members but I didn't find anything.

Is there a way to add without the use of a worksheet function? And without having to loop through each cell. Something like:

Sub test()
    Range("A1") = 1
    Range("A2") = 0
    Range("A3") = 2
    Range("A4") = Range("A1:A3").Add
    MsgBox Range("A4")
End Sub

With output 3.

Upvotes: 0

Views: 412

Answers (2)

Nathan_Sav
Nathan_Sav

Reputation: 8531

go on then, a 4th way :o)

evaluate(join(application.transpose(range("a1:a5").Value),"+"))

Upvotes: 2

Vityata
Vityata

Reputation: 43585

In general, there is a third way, which you did not mention:

Application.Sum

It is a bit different than WorksheetFunction.Sum, in the fact that it is a bit more discrete - it does not throw errors with a MsgBox, even when it should. Something like this will have only 2 errors thrown with MsgBox and you will get 2 errors in the immediate window:

Option Explicit

Public Sub WaysOfSumming()

    'This is Div/0 Error:
    Range("A1").Formula = "=5/0"

    Debug.Print Excel.WorksheetFunction.Sum(Range("A1"), 3, 5)
    Debug.Print Application.Sum(Range("A1"), 3, 5)

    Debug.Print Excel.WorksheetFunction.Sum(Range("A1"), 3, 5)
    Debug.Print Application.Sum(Range("A1"), 3, 5)

End Sub

Upvotes: 2

Related Questions