Is it possible to access standard Excel Functions through VBA?

Question

I'm writing some macros in Visual Basic for Applications but I want to use simple excel functions such as STDEV on these rather than write new code to do the calculations...

I can't put the functions directly into the sheet because the sheet itself is generated by the VBA code and the exact location of the data varies depending on the source datasheet coming into the Macro.

Is there any way I can just call built-in Excel Functions directly in VBA?

Thanks in advance.

Answer 1

If you are talknig about the VBA of Excel, then
Yes
There is a way
I answered this one 2 days ago

It is by using the object "WorksheetFunction"
Type
Worksheetfunction.
in your code and, you will see the list of available functions that you can use from VBA in XL.
This list will not have some big functions like OFFSET, or ADDRESS
any way
About the other solution (Putting function in your worksheet), actually, you can do the function in another worksheet and link the result to the one that you need.
This technique is better, just to allow your user to see what function you used for this result

Mail me if you need more info

Enjoy my profile, I am the VBAXLMan

Answer 2

I don't know of a way to use the Excel functions in VBA, but you could use VBA to add the functions to specific cells when it creates the worksheet. You'd have to come up with a way to determine where the data is though, since you said it varies. Example: Sub test() Cells(1, 1) = 30 Cells(1, 2).Formula = "=sin(" & Cells(1, 1) & ")" End Sub - Rich

Answer 3

I don't know of a way to use the Excel functions in VBA, but you could use VBA to add the functions to specific cells when it creates the worksheet. You'd have to come up with a way to determine where the data is though, since you said it varies.

Example:
Sub test()
Cells(1, 1) = 30
Cells(1, 2).Formula = "=sin(" & Cells(1, 1) & ")"
End Sub

- Rich