EXCEL functions within VBA code

[RealInfo]

Hi all

Is it OK to include excell functions into VBA code ?

Thanks
EC

 

[Harald Staff]

Hi EC

It depends on how you consider "OK". See
http://newtonexcelbach.wordpress.com/2008/05/24/worksheetfunction-vs-udf-2/
for a discussion on performance. Also the Excel team blog
http://blogs.msdn.com/excel/archive/2009/03/12/excel-vba-performance-coding-best-practices.aspx

HTH. Best wishes Harald

 

[Bob Phillips]

Of course it is okay, as long as you know what you are doing, why you are
doing it, and don't overdo it.

The link that Harald provided to the MSDN site gives lots of information of
the pros and cons.

 

[Shane Devenshire]

Hi

Assuming you are just asking "can" you do this, the answer is yes. Many
ways, but we need to know what it is you are doing to be more helpful.

Here are three examples

ActiveCell = "=SUMIF(R[-11]C[-2]:R[-2]C[-2],2,R[-11]C:R[-2]C)"
ActiveCell = "=SUMIF(A12:A21,2,C12:C21)"
x = WorksheetFunction.SumIf(Range("A12:A21"), 2, Range("C12:C21"))

The first two put the sumif function into the activecell the third one
stores the result of a sumif calculation in a variable.

 

[RealInfo]

Thanks
ec

Hi

Assuming you are just asking "can" you do this, the answer is yes. Many
ways, but we need to know what it is you are doing to be more helpful.

Here are three examples

ActiveCell = "=SUMIF(R[-11]C[-2]:R[-2]C[-2],2,R[-11]C:R[-2]C)"
ActiveCell = "=SUMIF(A12:A21,2,C12:C21)"
x = WorksheetFunction.SumIf(Range("A12:A21"), 2, Range("C12:C21"))

The first two put the sumif function into the activecell the third one
stores the result of a sumif calculation in a variable.
--
If this helps, please click the Yes button.

Cheers,
Shane Devenshire

Hi all

Is it OK to include excell functions into VBA code ?

Thanks
EC