Custom function returning VALUE error
 

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


--
alex.k
------------------------------------------------------------------------
alex.k's Profile: http://www.excelforum.com/member.php...o&userid=24637
View this thread: http://www.excelforum.com/showthread...hreadid=470598

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

"alex.k" wrote in
message ...

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


--
alex.k
------------------------------------------------------------------------
alex.k's Profile:
http://www.excelforum.com/member.php...o&userid=24637
View this thread: http://www.excelforum.com/showthread...hreadid=470598

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

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

HTH :)
--
Dana DeLouis
Win XP & Office 2003


"alex.k" wrote in
message ...

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


--
alex.k
------------------------------------------------------------------------
alex.k's Profile:
http://www.excelforum.com/member.php...o&userid=24637
View this thread: http://www.excelforum.com/showthread...hreadid=470598

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


--
MrShorty
------------------------------------------------------------------------
MrShorty's Profile: http://www.excelforum.com/member.php...o&userid=22181
View this thread: http://www.excelforum.com/showthread...hreadid=470598

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


--
alex.k
------------------------------------------------------------------------
alex.k's Profile: http://www.excelforum.com/member.php...o&userid=24637
View this thread: http://www.excelforum.com/showthread...hreadid=470598

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

On Mon, 26 Sep 2005 13:03:31 -0500, alex.k
wrote:


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

Hi Alex. Here is a technique if interested. You typed "Application.SQRT"
....

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

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" wrote in
message ...

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


--
alex.k
------------------------------------------------------------------------
alex.k's Profile:
http://www.excelforum.com/member.php...o&userid=24637
View this thread: http://www.excelforum.com/showthread...hreadid=470598