×
INTELLIGENT WORK FORUMS
FOR ENGINEERING PROFESSIONALS

Are you an
Engineering professional?
Join Eng-Tips Forums!
• Talk With Other Members
• Be Notified Of Responses
• Keyword Search
Favorite Forums
• Automated Signatures
• Best Of All, It's Free!

*Eng-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

#### Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

# user defined functions in vba

## user defined functions in vba

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

Why would you?

### RE: user defined functions in vba

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?

### RE: user defined functions in vba

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

### RE: user defined functions in vba

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

#### CODE

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

### RE: user defined functions in vba

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.

### RE: user defined functions in vba

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

### RE: user defined functions in vba

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

### RE: user defined functions in vba

@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: Eng-Tips.com Forum Policies for tips on how to make the best use of Eng-Tips.

### RE: user defined functions in vba

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.

### RE: user defined functions in vba

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

#### CODE

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
www.interactiveds.com.au

#### Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

#### Red Flag Submitted

Thank you for helping keep Eng-Tips Forums free from inappropriate posts.
The Eng-Tips staff will check this out and take appropriate action.

Close Box

# Join Eng-Tips® Today!

Join your peers on the Internet's largest technical engineering professional community.
It's easy to join and it's free.

Here's Why Members Love Eng-Tips Forums:

• Talk To Other Members
• Notification Of Responses To Questions
• Favorite Forums One Click Access
• Keyword Search Of All Posts, And More...

Register now while it's still free!