# How to use a User-Defined_function in Excell

In an Excell sheet, I want to call a function, created in VBA, in a particular cell.

Example :
Cell A1 contains : 64.0
Cell A2 contains : 30.88

Now cell A3 I would line to store the function : =Vcf(A1;A2). However, this does not work. Excell always gives the error "Name not valid". What am I doing wrong.

Vcf is the following VBA function :

Function vcf(ftemp, fapi)
If fapi = 0# Then
vcf = 0
Else
fdensity = Round(141.5 * 999.012 / (Round(fapi, 1) + 131.5), 2)
deltat = Round(ftemp, 1) - 60
term1 = Int(341.0957 / fdensity * 100000000) / 100000000
term2 = Int(term1 / fdensity * 10000000000#) / 10000000000#
alpha15 = Round(term2, 7)
term1 = Int((alpha15 * deltat) * 100000000) / 100000000
term2 = Int((0.8 * term1) * 100000000) / 100000000
term3 = Int((term1 * term2) * 100000000) / 100000000
term4 = Int((-term1 - term3) * 100000000) / 100000000
vcf = Int(Exp(term4) * 1000000) / 1000000
End If
End Function
Asked:
###### Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Commented:
Hi rdevriend

You need to use application.worksheetFunction. to call the Round function and it is a good idea to declare your variables

Try the function as below

Option Explicit

Function vcf(ftemp, fapi)
Dim fdensity As Double, deltat As Double
Dim Term1 As Double, Term2 As Double
Dim Alpha15 As Double, Term3 As Double
Dim term4 As Double
If fapi = 0# Then
vcf = 0
Else
fdensity = Application.WorksheetFunction.Round _
(141.5 * 999.012 / (Application.WorksheetFunction.Round(fapi, 1) + 131.5), 2)
deltat = Application.WorksheetFunction.Round(ftemp, 1) - 60
Term1 = Int(341.0957 / fdensity * 100000000) / 100000000
Term2 = Int(Term1 / fdensity * 10000000000#) / 10000000000#
Alpha15 = Application.WorksheetFunction.Round(Term2, 7)
Term1 = Int((Alpha15 * deltat) * 100000000) / 100000000
Term2 = Int((0.8 * Term1) * 100000000) / 100000000
Term3 = Int((Term1 * Term2) * 100000000) / 100000000
term4 = Int((-Term1 - Term3) * 100000000) / 100000000
vcf = Int(Exp(term4) * 1000000) / 1000000
End If
End Function

0
Author Commented:
Sorry antrat, I think I explained my problem not quite clear.

The problem does not lie in the Round function. This function is called from within the function vcf. I want to call vcf from a cell in a worksheet.

Example :
Cell A1 contains : 64.0
Cell A2 contains : 30.88
Cell A3 should contain the function :
=Sheet1.vcf(a1;a2)

Excell gives the error : Name not valid, because it does not understand =Sheet1.vcf

Please find below the functions, now with variable declarations. If you think you need the .xls file, please give me your email address, so I can mail it to you.

Function vcf(ftemp, fapi) As Double
Dim fdensity As Double, deltat As Double, alpha15 As Double
Dim term1 As Double, term2 As Double, term3 As Double, term4 As Double, term5 As Double

If fapi = 0 Then
vcf = 0
Else
fdensity = Round(141.5 * 999.012 / (Round(fapi, 1) + 131.5), 2)
deltat = Round(ftemp, 1) - 60
term1 = Int(341.0957 / fdensity * 100000000) / 100000000
term2 = Int(term1 / fdensity * 10000000000#) / 10000000000#
alpha15 = Round(term2, 7)
term1 = Int((alpha15 * deltat) * 100000000) / 100000000
term2 = Int((0.8 * term1) * 100000000) / 100000000
term3 = Int((term1 * term2) * 100000000) / 100000000
term4 = Int((-term1 - term3) * 100000000) / 100000000
term5 = Int(Exp(term4) * 1000000) / 1000000
vcf = IIf(term5 > 1, Round(term5, 4), Round(term5, 5))
End If
End Function

Function Round(nValue As Variant, nDec As Integer) As Double
If IsNull(nValue) Then
Round = 0
Else
Round = Int((nValue * (10 ^ nDec) + 0.5)) / (10 ^ nDec)
End If
End Function

0
Commented:
Hi rdevriend

Try just typing =vcf(a1;a2) that should work.

antrat

0
Author Commented:
Hi antrat
No, that does not work either.
Any other idea?

rene
0
Commented:
It works on my PC

Based your example I get 0.998198
if I place =vcf(A1,A2). are you sure your seperator is ; and not ,

antrat
0
Author Commented:
I wish it worked on my computer as well.

=VCF(a1;a2) does not work. Now Excell comes up with error #REF!.
Changing the ; in a , is not accepted.

By the way. Result 0.998198 is exactly what I expected.
Can I mail you my sheet? Maybe it can help.

0
Commented:
Sure, my e-mail is drhawley@geo.net.au

antrat
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Author Commented:
IT WORKS!

Good job antrat. As you can see I increased your points.

Regards,
Rene
0
Commented:
Glad to help, Thanks for the points.

antrat
0
###### It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Office

From novice to tech pro — start learning today.