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
rdevriendAsked:
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.

antratCommented:
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
rdevriendAuthor 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
antratCommented:
Hi rdevriend

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

antrat

0
The Ultimate Tool Kit for Technolgy Solution Provi

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy for valuable how-to assets including sample agreements, checklists, flowcharts, and more!

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

rene
0
antratCommented:
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
rdevriendAuthor 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
antratCommented:
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.

Start your 7-day free trial
rdevriendAuthor Commented:
IT WORKS!

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

Regards,
Rene
0
antratCommented:
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.