MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Doing a Simple Sum in VBA


Posted by John L. on May 12, 2001 10:06 AM

Can anyone tell how to do a simple sum in VBA.

On a spreadsheet I can manually place the cursor at the
bottom of a column of numbers and double-click on the "sum" icon
and get a total.

I can place the cursor using VBA, but I can not seem to
find the equivalent of a "sum" command. When I use the macro
recorder it gives a defined range name, which is not useful
since I would like this to work on any column of numbers I want
to sum.

This seems very simple, but I am struggling with it.


Posted by Kevin James on May 12, 2001 5:01 PM

Hi John,

I just picked up John Walkenbach's book: Excel 2000 Programming for Dummies. Though previously unknown to me, Mr. Walkenbach is apparently nationally renowned.

His book can be of benefit for beginners like you (and me).

To answer your question, you must enter the VBE and manually type the code

VariableName = YourCells

Where VariableName is whatever name you want to hold the total and YourCells can be either single cell references such as:

A1 + B1 + C1

or a range of cells such as:

range("A1:D10")

Get the book, its worth it.

Kevin.

Posted by John L. on May 12, 2001 6:11 PM

Thanks, but it does not seem to give me a total.

It only picks up the value of the first cell in the
range. I am working on Excel '97...maybe that is the
problem.


Posted by Anon on May 12, 2001 7:46 PM

There are many ways to do it. For example :-

Sub EnterSumFormula()
Dim startRow As Integer
Dim startCell As range, endCell As range
startRow = 1
With ActiveCell
Set startCell = Cells(startRow, .Column)
Set endCell = .Offset(-1, 0)
.Formula = "=SUM(" & startCell.Address & ":" & endCell.Address & ")"
End With
End Sub

Sub EnterSumValue()
Dim startRow As Integer
Dim startCell As range, endCell As range, rng As range
startRow = 1
With ActiveCell
Set startCell = Cells(startRow, .Column)
Set endCell = .Offset(-1, 0)
Set rng = range(startCell, endCell)
.Value = Application.WorksheetFunction.Sum(rng)
End With
End Sub


Posted by Kevin James on May 12, 2001 10:19 PM

Hi John:

Indeed, just as Anon, who has more experience in this than I do, just indicated, there are many ways. Just with the little I've learned in the aforementioned "Dummies" book, here is what I put together in less than 2 minutes and it works.

Sub ShowTotal()
Dim ShowTotal As Integer
ShowTotal = Range("A1") + Range("A2")
Worksheets("Sheet1").Range("A3").Value = ShowTotal
End Sub

Kevin

Posted by Dave Hawley on May 13, 2001 2:18 PM

On a spreadsheet I can manually place the cursor at the

Hi John

There is actually no need to place the cursor, or even select cells in VBA (very rarely). Let's say the numbers you want to Sum are in Column A, then this code would place the Total underneath the last cell in column A.


Sub SumCells()
'Written by OzGrid Business Applications
'www.ozgrid.com
Dim rTotalCell As Range
Set rTotalCell = Range("A65536").End(xlUp).Offset(1, 0)
rTotalCell = WorksheetFunction.Sum _
(Range("A1", rTotalCell.Offset(-1, 0)))
Set rTotalCell = Nothing
End Sub

Dave

OzGrid Business Applications

Posted by John L. on May 13, 2001 5:52 PM

Kevin, thanks for your input. But, I am really looking
for an all-purpose tool. That would sum up any column
of cells on the worksheet as long as the cursor was
placed at the bottom of the column. This is what the
"Auto-sum" icon does when I manually double-click it.

So, I would not be able to call out specific cells like
"A1" + "A2".

It occurs to me that it is very hard to explain these
things in writing.

Hi John: Indeed, just as Anon, who has more experience in this than I do, just indicated, there are many ways. Just with the little I've learned in the aforementioned "Dummies" book, here is what I put together in less than 2 minutes and it works. Sub ShowTotal()

Posted by Ivan Moala on May 13, 2001 10:17 PM

John
If you want this function then this
routine will do it. Just place cursor
as normal @ bottom of column and run the routine.

Sub AutoSum()
CommandBars.FindControl(Id:=226).Execute
Application.SendKeys "~"
End Sub


Ivan Kevin, thanks for your input. But, I am really looking

Posted by John L. on May 15, 2001 9:26 PM

Ivan, this works great, but...........

Ivan, this works great, but the it works more like
a single click on the AutoSum icon, rather than a
double click. So, the program gets "Hung up"...
I tried using the SendKeys command again, but it
doesn't work...Any thoughts????