writing its own function

[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.

 

[AG]

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.

 

[AG]

And I am using a Module, not a class module.

 

[Dana DeLouis]

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

 

[Dana DeLouis]

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

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

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

 

[AG]

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

Thanks Dana, using Sgn() instead of sign() works fine. But I still get a
#NOM? error.

 

[AG]

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

yes, eventually.

 

[AG]

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

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

v=Sgn(-2) * Erf(Abs(-2)) ?

 

[Dana DeLouis]

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
= = = = = = = = = = = = = = = = =

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

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

v=Sgn(-2) * Erf(Abs(-2)) ?