Creating Custom Function - LN() 3

[Storyteller]

Hello All,

I am trying to create a custom function in Access to be used in a Query. The function is LN(), it does exist in Excel. Unfortunately, LN() is not available to the Access or VBA Library. Hence why I need to create a custom function.

This is what I have done so far:

Public Function LN(ByVal Q As Double)
Dim xl As Excel.Application
Dim x As Double

Set xl = New Excel.Application

x = xl.WorksheetFunction.LN(Q)
Debug.Print x

End Function

The Debug.Print x does show the proper result, but the functon does not show the result.

I would like to be able to use the function in the following manner: LN([Q]) in an Access query.

I know that the LOG() function will do almost the same thing, but not quite. That is why I need the LN() function.

Can anyone shed some light on what I am missing?

Thanks,
Michael

 

[TonyJollans]

Hi Storyteller,

The reason the function does not return the result is simply because you are not setting it. Adding the line ..

LN = x

.. at the end should do the trick.

But your function is more complicated than it needs to be. There is no need to invoke an instance of Excel just to use one of its functions. Provided you have a reference to Excel you can just use an Excel function directly ..

Public Function LN(ByVal Q As Double)
    LN = Excel.WorksheetFunction.LN(Q)
End Function

Enjoy,
Tony

 

[combo]

The LOG( ) function in VBA is equivalent to excel WorksheetFunction LN( ) - both are "e" based, so there is no need to refer to excel.
For 10-based logarithm a formula log(x)/log(10) is faster than referencing excel.

combo

 

[Bowers74]

The LOG() Worksheet function returns the logarithm of a number to the specified base.

The LN() Worksheet function returns the natural logarithm of a number (natural logarithms are based on the constant 2.71828182845904)

This means that =LOG(2,2.71828182845904) will return the natural logarithm for the number 2.

The LOG() VBA function returns the natural logarithm of a given number (i.e. same as LN() worksheet function).

In short:

Worksheet function LOG() in is written in VBA Log(x)/Log(10)

Worksheet function LN() in is written in VBA Log(x)

This means that your custom function to return the natural logarithm in Access should read:

Public Function LN(ByVal Q As Double)
    LN = Log(Q)    
End Function

I hope this clears up a few things! ;-)



Peace!

Mike

Never say Never!!!
Nothing is impossible!!!

 

[Storyteller]

Hello All,
Thank you for you quick responses. This is why I always look to and reccommend this site to my clients.

Tony, your simplified code has made my life easier not only with this function but with understanding more about VBA. Thanks.

Mike, I have passed along your comments about LOG() and LN() to the Engineer that requires this function. Wtth this information she should be able to determine what function she truely needs to use.

A star for both of you.

Regards,
Michael