How to create a sum function in VBA

Question

How can one use a sum function in VBA, i am trying the below piece but does not seem to work.

temp=Application.run("Sum",range("A1").value,range("A2").value)
Range("A1").value=temp

i.e If A1=3 and A2=3 I want the end value to be 9.

Answer 1

Sorry, was a quick one, was meant to be sum of 3*3

So you don't want the sum, but the product of the cells.

Use the WorksheetFunction.Product(Range, Range2,.....) instead of sum, the function allows for multiple ranges as parameters.

Answer 2

Base on my test, please try the VBA code as below:

Sub sum()
    Dim myRange As Range
    Dim tmp As Long
    Set myRange = Worksheets("Sheet1").Range("A1:A2")
    tmp = Application.WorksheetFunction.sum(myRange)
    Worksheets("Sheet1").Range("A3") = tmp
End Sub

For more information, please have a look: WorksheetFunction.Sum Method

Answer 3

Use

Application.WorksheetFunction.Sum(Range("A1"),Range("A2"))

Be aware that if you don't state the worksheet name in front then it will implicitly use the current Activesheet which may not always be what you want.

You could use a With statement to give the parent sheet reference e.g.

With ThisWorkbook.Worksheets("Sheet1")
  temp = Application.WorksheetFunction.Sum(.Range("A1"),.Range("A2"))
End With

As it seems you note in your prior question: 3+3 is 6.

If you want multiplication then do:

temp = .Range("A1") * .Range("A2")

or,

temp = Application.WorksheetFunction.Product(.Range("A1") ,.Range("A2"))

---

Application.Run is for running macros by their name and passing the required arguments. Unless you have a macro called "Sum", I am surprised that line compiled. I also don't like macros being named after functions. It is confusing. Using the WorksheetFunction method as shown above is nice and clear.

Answer 4

You can also try this.

Sub Sum_num()
 Dim temp As Integer
 temp = Range("A1") + Range("A2")
 MsgBox temp
End Sub