Using Excel Worksheet Function in Code

[mikeTTI]

I have created the following function in a Standard Module: -

Public Function RndDwnII() As Double()

RndDwnII = Excel.WorksheetFunction.RoundDown(3.14578, 3)

End Function

(At this stage I am just trying to call it from a calculated text box on a form. Later I will add arguments to feed into the RoundDown function).

However if I set the data source of a text box on a form to

=RndDwnII()

I just get #Name! in the text box when I open the form. I think the form can find the function OK because it is available in the expression builder.

I have added to MS EXCel Object Library to the database. Rounddown is avialable in the object browser window, and when I type it into the VBA editor the small yellow function tip box appears. So VBA is recognising Rounddown as a valid function.

It's as if the form can't access the excel function via the standard module or something like that?

Can anyone help?

All the examples on the MS website are sub functions, not sure if this is relevant.

 

[OlcayM]

There is a tiny problem
Change "Function RndDwnII() As Double()" to "Function RndDwnII() As Double"

HTH
OlcayM

 

[mikeTTI]

Thanks for the help, working like a treat now.