# 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
###### Who is Participating?

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

antrat
0

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

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

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