0

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.

QHarr
  • 83,427
  • 12
  • 54
  • 101
HamburgerPatty
  • 11
  • 6

4 Answers4

2

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.

QHarr
  • 83,427
  • 12
  • 54
  • 101
  • Thank you for all the replies so far, what If i created my own function called CapFloor, could I call it in VBA using "Application.worksheetFunction.Product" – HamburgerPatty Aug 22 '18 at 09:07
  • The main question I asked was on this link: https://stackoverflow.com/questions/51951655/how-to-parse-cell-values-into-a-created-function-in-vba It will give you a more granular understanding of what I am trying to do. – HamburgerPatty Aug 22 '18 at 09:08
  • I agree with what you are saying with the application.run, it says the first condition is called macro and I have no idea what they are referring to. – HamburgerPatty Aug 22 '18 at 09:30
  • Having said this, the function was created by a dev team so when I open up excel I have a function called CapFloor which was created and I can call it manually by typing in a cell "=CapFloor()" , it requires 16 Inputs, if you go to this link....you will see what my main issue was, I was just not getting any traction with it so posted this one to get an idea of how to call in a function into vba etc. the link is https://stackoverflow.com/questions/51951655/how-to-parse-cell-values-into-a-created-function-in-vba – HamburgerPatty Aug 22 '18 at 09:32
  • Hi, I am answering the question you have posed above rather than your other question. For the product of numbers (9 in this case) use the syntax as I have shown at the bottom. For the Application.Run scenario there must be a macro by the name referenced as the first argument present in your workbook. If your dev team developed a macro called Sum then you need to look to this. I would say 1) don't call it Sum that confuses things given it is an existing function 2) don't call it Sum when the operation is multiplication. This is misleading at best. – QHarr Aug 22 '18 at 09:42
  • If they have a procedure/function called Sum that you need to include that in the above but to be honest, I would use syntax above unless there is something really important and different going on in their function/sub. – QHarr Aug 22 '18 at 09:47
  • Thank you for the reply, I am trying to call in a .net function created by the dev team called CapFloor, I am using sum as an analogy as it works similarly in the sense of me having to type =CapFloor(x,x,x,x,x,x) in order to access it, the 'x' are the inputs, the .net function requires 16 of them. I have 16 values in row7 across 16 columns, where i point each cell into the function where I am using this formula of temp=application.run("CapFloor",temp1,temp2,temp3,temp4.....temp16) where the temp1=cell A7 temp2=cell B7 and so forth..when I run the macro using the formula above it gives – HamburgerPatty Aug 22 '18 at 13:25
  • an error, but when i run it manually by pasting in the formula and its paramter values, it works...I am wondering if my application.run ("CapFloor",temp1,temp2) etc is wrong to use as when i hover over the temp1 and temp 2 etc, it is giving me the correct values, i am just thinking perhaps I am using the wrong syntax. – HamburgerPatty Aug 22 '18 at 13:26
0

You can also try this.

Sub Sum_num()
 Dim temp As Integer
 temp = Range("A1") + Range("A2")
 MsgBox temp
End Sub
Pang
  • 9,564
  • 146
  • 81
  • 122
Bhushan
  • 114
  • 12
  • Thank you s much for the reply, A more granular question i was asking is on this link: https://stackoverflow.com/questions/51951655/how-to-parse-cell-values-into-a-created-function-in-vba – HamburgerPatty Aug 22 '18 at 09:09
0

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

Yuki
  • 212
  • 1
  • 4
  • The test works, But not for the function I created, Please can you go to this link https://stackoverflow.com/questions/51951655/how-to-parse-cell-values-into-a-created-function-in-vba you will see what I mean as I created a function of my own, but wont work in the same manner as you described above. – HamburgerPatty Aug 22 '18 at 09:26
0

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.

L8n
  • 728
  • 1
  • 5
  • 15
  • Please can you click on this link to see exactly what I am getting at: https://stackoverflow.com/questions/51951655/how-to-parse-cell-values-into-a-created-function-in-vba – HamburgerPatty Aug 22 '18 at 09:32