Custom function returning VALUE error

[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

[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 :>)
--
Dana DeLouis
Win XP & Office 2003


"alex.k" <[email protected]> wrote in
message news:[email protected]...
>
> 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
>

[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

"alex.k" <[email protected]> wrote in
message news:[email protected]...
>
> 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
>

[MrShorty]

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

[alex.k]

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

[Myrna Larson]

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
<[email protected]> 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

[Dana DeLouis]

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" <[email protected]> wrote in
message news:[email protected]...
>
> 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
>