writing its own function

  • Thread starter Thread starter Bob L.
  • Start date Start date
B

Bob L.

In Excel 2000 (which I use), neither sign nor erf is supported in VBA (and
erf is not even listed as a worksheet function). (see VBA help: functions)

Bob L.
 
Hi all,

I am writing this function to be used in a sheet :

Function my_erf(x)
If (Abs(x) > 27) Then
my_erf = 1
Else
my_erf = sign(x)*erf(abs(x))
End If
End Function


I put it inside a modul. I have two moduls : one for my macros, and one
for this function.

When I try to use the function, I get the message that the sign()
function is unknown, and the same for the erf() function, which I can
both use in my sheet.

I don't understand why, and how I can solve this.

Thanks,

Alexandre.
 
Not sure, but if 'x' were -2, is this what you are trying to do?

Sub Demo()
Dim v
v = Sgn(-2) * [Erf(Abs(-2))]
End Sub

Note that in VBA, the "Sign" function is called "Sgn" (Worksheet, it is
'Sign')

HTH
 
Just a note. I believe the Erf function is 1 for numbers just short of 6 at
full machine precision.
You may want to consider:
If (Abs(x) >=6 Then my_erf = 1
If (Abs(x) > 27) Then
my_erf = 1
Click to expand...

--
Dana DeLouis
Using Windows XP & Office XP
= = = = = = = = = = = = = = = = =


Dana DeLouis said:
Not sure, but if 'x' were -2, is this what you are trying to do?

Sub Demo()
Dim v
v = Sgn(-2) * [Erf(Abs(-2))]
End Sub

Note that in VBA, the "Sign" function is called "Sgn" (Worksheet, it is
'Sign')

HTH
Click to expand...
 
Dana said:
Not sure, but if 'x' were -2, is this what you are trying to do?

Sub Demo()
Dim v
v = Sgn(-2) * [Erf(Abs(-2))]
End Sub

Note that in VBA, the "Sign" function is called "Sgn" (Worksheet, it is
'Sign')

HTH
Click to expand...

Thanks Dana, using Sgn() instead of sign() works fine. But I still get a
#NOM? error.
 
Dana said:
Just a note. I believe the Erf function is 1 for numbers just short of 6 at
full machine precision.
You may want to consider:
If (Abs(x) >=6 Then my_erf = 1
Click to expand...

yes, eventually.
 
Dana said:
Not sure, but if 'x' were -2, is this what you are trying to do? yes



Sub Demo()
Dim v
v = Sgn(-2) * [Erf(Abs(-2))]
End Sub
Click to expand...

why do you use [.] and not simply (.)

v=Sgn(-2) * Erf(Abs(-2)) ?
 
Hi AG. Yes, my mistake. I was using [] because I did not have a vba
library reference set to "atpvbaen.xls".
I see that you probably have that reference set, so the [] are not required.
:>)
Without the reference set, you would get a "sub or function not defined"
error.

--
Dana DeLouis
Using Windows XP & Office XP
= = = = = = = = = = = = = = = = =


AG said:
Dana said:
Not sure, but if 'x' were -2, is this what you are trying to do? yes

Sub Demo()
Dim v
v = Sgn(-2) * [Erf(Abs(-2))]
End Sub
Click to expand...

why do you use [.] and not simply (.)

v=Sgn(-2) * Erf(Abs(-2)) ?
Click to expand...
 
Back
Top