preferred method for summing

G

Gary Keramidas

just a general question, is there a preferred method?

if i use an array or loop to get numbers, sum those numbers and then place the
sum in the cell, is it better to:

1. put each value in the cell and add each subsequent value to it or,
2. store the numbers in a variable and then just place the value of the variable
in the cell when it's complete?

in this instance, there's no real speed issue, it takes a second to do what i'm
doing, but just curious what the professionals do.
 
T

Tom Ogilvy

this is just my personal opinion as I can't speak from the authoritative
position so many others can, but
writing to a cell takes a long time compared to writing to a variable.

Every command is a speed issue from the perspective that when you begin to
combine them, the delays accumulate.
 
B

Bob Phillips

Speaking personally, I would go for #2. I try to avoid interacting with the
worksheet/workbook until I have to

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)
 
B

Bernie Deitrick

One additional note: use natvie Excel functionality rather than doing things stepwise. For example,
if you want to sum the 1000 numbers in column A, rather than

For i = 1 to 1000
mySum = mySum + Cells(i,1).Value
next i

Range("A1001").Value = mySum

just use

Range("A1001").Value = Application.Sum(Range("A1:A1000"))

HTH,
Bernie
MS Excel MVP
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top