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?
 
antratConnect With a Mentor Commented:
Sure, my e-mail is drhawley@geo.net.au

antrat
0
 
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
The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

 
antratCommented:
Hi rdevriend

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

antrat

0
 
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
 
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
All Courses

From novice to tech pro — start learning today.