user defined functions in vba

[neeraj89]

how do i use a user-define function to calculate the value of e^x or N! in vba in excel.

 

[IRstuff]

Why would you?

TTFN

FAQ731-376

 

[cowski]

The factorial function (FACT()), and natural logarithms (EXP() and LN()) are built into Excel. Were you looking for these specific functions or are you looking for an example of a user defined function?

 

[TheTick]

function Factorial (x as long) as double
if i<1 then
Factorial=0 '0=error
exit function
end if

dim i as long
Factorial=1
for i=1 to x
Factorial=i*Factorial
next
end function

Honesty may be the best policy, but insanity is a better defense.

http://www.EsoxRepublic.com

-SolidWorks API VB programming help

 

[cummings54]

Use can also calculate factorials using recursive algoritms. For example,

Function myFact(inVal As Long) As Long
  If inVal <= 1 Then
    myFact = 1
  Else
    myFact = inVal * myFact(inVal - 1)
  End If
End Function

 

[IRstuff]

Recursion does, however, have its limits, due to stack overflow limitations. For Excel the above recursion routine seems to stop work at 13!, while Mathcad's numerical processor can go up to 170! and its symbolic processor can go at least to 17000!

But, short of doing a homework assignment, why would you want to duplicate a built-in function, particularly since your function will probably run 100 times slower.

TTFN

FAQ731-376

 

[TheTick]

I would guess that our esteemed OP'er is a noob who still hasn't quite grasped the general concept of VB functions.

 

[cummings54]

The recursive routine is quite often used for factorial calculation. It is a classic example used to teach programing.

On my machine, the built in worksheet function took 1109 ms to run 100,000 calculations of FACT(170). The VBA routine myFact took 2922 ms for the same computation.

The overflow is only due to the variable typing. If you substitute double for long. The routine will work up to 170
myFact(170)=7.25741561530799E+306

 

[joerd]

@IRStuff
It's not so bad as it looks, the function takes "only" 150-200% of the time the built-in function takes. Your point stays valid, though. Also, sticking to built-in functions will avoid "macro security" issues.

Cheers,
Joerd

Please see FAQ731-376 for tips on how to make the best use of Eng-Tips.

 

[cummings54]

Another point to keep in mind is that FACT is not a VBA function. To use it within a VBA routine you must reference the excel application object ie,
X=application.worksheetfunction.fact() there is an overhead cost in making the reference call. When comparing myFact to worksheet fact I found that myFact was actually faster for arguments less than 17. Above 17, the computational efficiency in worksheet Fact makes it a faster choice.

 

[IDS]

The slow performance of using worksheetfunction is a good point, and much more noticeable in Excel 2007 than in previous versions.

For instance, I use my own function for ATAN2, which works about 3 times faster than worksheetfunction.ATAN2() in Excel 2000, but about 8 times faster in Excel 2007.

This may be only temporary, until Microsoft fix the speed problems in Excel 2007, but for a frequently used function like ATAN2 it is worth using a UDF even for a 3 x speed increase.


Function VBAATan2(ByVal DX As Double, ByVal DY As Double) As Variant

If DY < 0 Then
VBAATan2 = -VBAATan2(DX, -DY)
ElseIf DX < 0 Then
VBAATan2 = Pi - Atn(-DY / DX)
ElseIf DX > 0 Then
VBAATan2 = Atn(DY / DX)
ElseIf DY <> 0 Then
VBAATan2 = Pi / 2
Else
VBAATan2 = CVErr(xlErrDiv0)
End If
End Function

Doug Jenkins
Interactive Design Services

http://www.interactiveds.com.au