Custom function returning VALUE error

A

alex.k

Hi,

I am trying to construct a simple function to calculate distance
between 2 points on Carthesian plane. The function I wrote:

Function FieldsXY(startx, starty, endx, endy)
FieldsXY = Application.SQRT((endx - startx) ^ 2 + (endy - starty)
^ 2)
End Function

The only result I'm getting is #VALUE! error. I tried to declare all
arguments as integer, didn't help. Help would be greatly appreciated,
as I'm no expert in this :).

regards,
Alex
 
N

Niek Otten

Function FieldsXY(startx As Double, starty As Double, endx As Double, endy
As Double) As Double
FieldsXY = ((endx - startx) ^ 2 + (endy - starty) ^ 2) ^ (1 / 2)
End Function

--
Kind regards,

Niek Otten

Microsoft MVP - Excel
 
D

Dana DeLouis

Hi. Vba has its own Sqrt function, so SQRT wasn't included under
"Application.WorksheetFunction."

FieldsXY = Sqr((endx - startx) ^ 2 + (endy - starty) ^ 2)

HTH :>)
 
M

MrShorty

Also make sure you've placed the function in an actual code module, and
not in the code page associated with a worksheet.
 
A

alex.k

Thank you. Stupid me, I knew it is something simple. I actually spen
some time looking through help to find out if VB has a SQR function
but didn't find it so I assumed it doesn't.
And maybe I should refresh my maths, as SQR is not even needed here [a
2nd answer shows:)]

Thank you both again.
Ale
 
M

Myrna Larson

Hmmm... In Excel 2003, I typed 'square root' in the Help box; the first item
returned was the SQRT worksheet function.
 
D

Dana DeLouis

Hi Alex. Here is a technique if interested. You typed "Application.SQRT"
....
FieldsXY = Application.SQRT((endx - startx) ^ 2 + (endy - starty)
Click to expand...

Don't know what version you have, but in the latest versions of Excel, I
find it slightly better to use "WorksheetFunction" instead of just
"Application"
In the vba editor, go to Tools | Options | Editor tab, and make sure you
turn on "Auto List Members"
When you type "WorksheetFunction" followed by a period, you will see a list
of valid worksheet functions that are available. Older versions of Excel
required you to type "Application.WorksheetFunction" then period to get the
Auto List Members to show the functions.
HTH :>)
--
Dana DeLouis
Win XP & Office 2003


alex.k said:
Thank you. Stupid me, I knew it is something simple. I actually spend
some time looking through help to find out if VB has a SQR function,
but didn't find it so I assumed it doesn't.
And maybe I should refresh my maths, as SQR is not even needed here [as
2nd answer shows:)]

Thank you both again.
Alex
Click to expand...
 
Top