how to call excel function through VBA Macro?

[kkc]

can anyone tell me how to call call excel sum function through macro?

[Ecniv]

If you mean you want the total (sum) of a rnage of cells, but do no want to put it in a cell - I haven't come across doing that, without making a code loop to go through all the cells and add them up if they are numeric value.

If you have a spare cell, you could put the formula in it then grab the value.

Which ever way you manage to do it- please post up how you did it, as I would be interested.

[westconn1]

you can get the sum by using exactly the same formula as you would put in a cell and assingning it to a cell or a variable


pete

[Ecniv]

Pete
Do you have an example pls? Assign to a variable I mean.

[westconn1]

Set myrange = Sheet1.Range("a1:a6")
myvar = Application.WorksheetFunction.Sum(myrange)


tested now

pete

[kkc]

a more simple example is


cells(rowindex, columnindex) = formulaExpression

where formulaExpression is a string variable

i.e

// X,Y are rowindex,columnindex
// you need to calculate x, y first then concatenate the string
formulaExpression = "=SUM(AX:AY)"
cells(rowindex, columnindex) = formulaExpression

[westconn1]

that works, but ecniv asked to assign to variable,

pete

[kkc]

pete, actually your solution is much more better than me in terms of development.
any way, since i am just writing a macro for excel i think my version is enough for me.

i think my title misleading you guys.
this is my first time writing a macro.

[Ecniv]

Pete:
Neat, thanks. Should've guessed there is a worksheetfunction method
I'll remember that for the future