#### 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?action=getinfo&userid=24637

```
 0
9/25/2005 6:41:48 PM
excel.misc 78881 articles. 5 followers.

6 Replies
584 Views

Similar Articles

[PageSpeed] 9

```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" <alex.k.1vxi2a_1127675106.7185@excelforum-nospam.com> wrote in
message news:alex.k.1vxi2a_1127675106.7185@excelforum-nospam.com...
>
> 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?action=getinfo&userid=24637
>

```
 0
nicolaus (2022)
9/25/2005 7:33:32 PM
```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" <alex.k.1vxi2a_1127675106.7185@excelforum-nospam.com> wrote in
message news:alex.k.1vxi2a_1127675106.7185@excelforum-nospam.com...
>
> 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?action=getinfo&userid=24637
>

```
 0
delouis (422)
9/25/2005 7:40:23 PM
```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?action=getinfo&userid=22181

```
 0
9/26/2005 4:51:41 PM
```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

Thank you both again.
Ale

--
alex.
-----------------------------------------------------------------------
alex.k's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=2463

```
 0
9/26/2005 6:03:31 PM
```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
<alex.k.1vz9yb_1127757908.8286@excelforum-nospam.com> 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
>
>Thank you both again.
>Alex
```
 0
anonymous (74722)
9/26/2005 7:16:06 PM
```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" <alex.k.1vz9yb_1127757908.8286@excelforum-nospam.com> wrote in
message news:alex.k.1vz9yb_1127757908.8286@excelforum-nospam.com...
>
> 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
>
> Thank you both again.
> Alex
>
>
> --
> alex.k
> ------------------------------------------------------------------------
> alex.k's Profile:
> http://www.excelforum.com/member.php?action=getinfo&userid=24637
>

```
 0
delouis (422)
9/27/2005 2:05:19 AM

Similar Artilces:

customizing quote merge template
I have 2 major problems that I just cannot seem to be able to resolve. When using the mail merge template to do a quote it seems that there are only a very limited range of "fields" that are made available to the user. These DO NOT include the actual accounts "Address" only shipping and billing address - 99% of our clients don't use these address's so all our data is in "Address_line1" etc Neither do the fields for "product description" appear in the list only the products name. All our quotes have always included a short four or five line desc...

Customize Current View: Filter Problem
Hello, Using Outlook 2002, I have created a category called "Hidden" i outlook. In my calendar, I put all the appointments I don't want t appear in this calendar (i.e. daily reminders). I then filter out all these "Hidden" items by selecting -- View--> Current View--> Customize Current View--> Filter--> More Choices--> Categories--> Hidden (the category I created)--> OK--> SQL--> Edit these criteria directly--> And then I change: ("DAV:isfolder" = false AND "DAV:ishidden" = false) AN ("urn:schemas-microsoft...

Hi, We are running business portal 4.0 for one of our customer. It was running correctly, however, they have changed the SQL server port (previously it was set as default 1433). After that the business portal becomes very slow and while creating a new request (purchase requisition) if we open the item pop up; it is showing exception "SQL Server does not exist or access denied...." Can any body tell me how can I provide the new port number to business portal connection to the database. Thanks and Regards, Waliullah, Thanks for using the newsgroups. I have a...

What's happened to the findfirst function in Access 2010?
I have a working Access 2007 Application which is now failing miserably in 2010. It hinges on location the first available working document in a table runing the following VBA code: With Me.Recordset .FindFirst "([fld1] + [fld2]) = 0" If .NoMatch Then .FindLast "([fdl1] + [fld2]) <> 0" Exit Sub End If .... FURTHER PROCESSING ... What must I do to correct this? Go through a record by record search? End With In 2007, this works correctly, stopping at the appropriate record (approc. rec 1385 in the recordset...

I have an account for some stock options in a privately-owned company. Approximately monthly, AdvisorFYI keeps telling me, "It's time to update your investment prices in Money." There isn't a price update that often. Is there a way to turn this message off without turning of the other useful AdvisorFYI Investment warnings? ...

visual basic
Hi, I trying to retrieve values from a table to calculate the 14days average value of a stock closing price. However, i encounter some problem as stated beside the code as follows: Function DaysAvgs() 'Calculate the average value of a given value. Dim db As DAO.Database Dim rst As DAO.Recordset Dim varBookmark As Variant Dim numAve, numDaysAvg As Double Dim intA, intB, lngCount As Integer Set db = CurrentDb 'Open Table Set rst = db.OpenRecordset("SGX Individual Historical", dbOpenTable) rst.MoveFirst Do While Not rst.EOF intA = 1 intB = 0 varBookmark = rst.Bookmark n...

Vlookup returns "0"
I am using the following formula =VLOOKUP(\$A\$1,'Data Sheet'!\$A\$11:\$BH\$15,3,FALSE and it is returning a zero if there is no data found in that cell. If there is no data found I would like it to display nothing. How can I do this? Carolyn, There are a couple ways so do this. One is to test it, which makes for doing the VLOOKUP function twice. 1) =if(VLOOKUP(\$A\$1,'Data Sheet'!\$A\$11:\$BH\$15,3,FALSE)=0, "" , VLOOKUP(\$A\$1,'Data Sheet'!\$A\$11:\$BH\$15,3,FALSE)) This gets a little messy. Better yet, and simpler is to hide the original cell (column, whatever), an...

Unable to click customizing outlook today button
Unbable to click the customising outlook today button for Outlook 2000 install in the windows xp profession machine. I have updated the office 200 patch 3. But it seem remain the same problem. http://support.microsoft.com/default.aspx?scid=kb;en-us;820575 might be helpful. -- Neo [MVP Outlook] Due to the Swen virus, all e-mails sent to this account will be deleted w/out reading. "Clarence" <anonymous@discussions.microsoft.com> wrote in message news:06d101c3c939\$78175620\$a401280a@phx.gbl... > Unbable to click the customising outlook today button for > Outlook 200...

crm 3.0 error 03-01-06
Hello, I'me getting this error while installing crm3.0 for SBS: "error writing to file microsoft.mshtml.dll verify that you have access to that directory" That file is in the C:\Program Files\Microsoft.NET\Primary Interop Assemblies directory. I (and 'everyone') has full access to that dir. What can I do about this?? kind regards, Thomas ...

custom field for projects with tasks past due
I am wondering how/what formula would be needed for creating a custom field at the project level to show when a project has at least one task that is past due. I have a custom field at the task level (IIf([Finish]<[Current Date] And [% Complete]<100,1,2)) that will show when an individual task is overdue, but I want to add a custom field to the Project Center > Summary view to show when a project has any overdue tasks. The PM can then drill down from there into the project and see exactly what tasks are overdue. Once I figure out the formula for this I want to assign ...

Error in Outlook Today
Whenever I go to Outlook Today, I get a runtime error, line: 297 Error: Class Not Registered. Then I get the error two more times when I click 'Customize Outlook Today...' and the list 'Show Outlook Today In This Style' is empty, and the box under it has a broken image icon. What could be the cause of this? Sorry...forgot to say...I'm using Outlook 2003 Student and Teacher Edition on Windows XP. >-----Original Message----- >Whenever I go to Outlook Today, I get a runtime error, >line: 297 Error: Class Not Registered. Then I get the >error two more time...

IsOutLookClient() returns wrong value
IsOutLookClient() returns wrong value when both web client of crm and outlook client are running on the same workstation It looks like the same cookie(used for determining what client is running) is used by the sessions of each client. Look for "LightClient" in IsOutlookWorkstationClient() in global.js Oeps...I seem to have made a wrong assumption... Between the to clients IsOutlookClient() seems to work ok... But in outlook client the IsOutlookClient() function gives false for me...after I have opened a page from the Microsoft Crm folder structure... On another workstation it...

Non-VBA way of making custom menus.
Sorry for the new thread but even on Google, the thread isn't showing up. Well, it was SOOO easy, as I knew it would be. The webpage I quoted in my message this morning didn't mention the "New Menu" at all! So, here is the non-VBA way to create a custom menu: - TOOLS > CUSTOMIZE - under the categories available choose NEW MENU and then drag the NEW MENU option under the Commands window up to the menu bar (like D'UH!! <lol> Wish all those hours spent searching yielded webpages that gave this! <g>) - THEN one can go back to the MACROS category a few line...

Input to custom reports
Hello, I'd like to be able to input values to a custom report like the Year. Is there any way to package a custom report and provide input to it? Every year the user has to go into the custom report and change the Year restriction. Alternatively, (this is a payroll report), is there any global variable or some other back door that I can use to get the current year in a custom report? Given the report writer may not be the ticket what would the recommendation be to create custom reports that require input? VB.NET? -- Russ Using VBA you can allow for input in the custom reports. B...

Re: 'Uknown Error 0x800CCC97'
I just heard back from the folks with whom I filed this bug. They say the bug is fixed in cppop 5.4 - request that your ISP upgrade to that. -- Jeff Stephenson Outlook Development This posting is provided "AS IS" with no warranties, and confers no rights "Jeff Stephenson [MSFT]" <stephenson@online.microsoft.com> wrote in message news:... > See the attached reply to another similar question. Your ISP's POP3 server > has a bug, and they should get a fixed version of the server. > > -- > Jeff Stephenson > Outlook Development > This posting...

Unable to sort on customer CRM 4.0
Hi When you try to change the campaign response views to have customer as sorting CRM gives an error. You can't even click on customer column to sort when it's now customer that is default sorting. Is this a bug ? same here. I think its a bug "Help needed" wrote: > Hi > > When you try to change the campaign response views to have customer as > sorting CRM gives an error. You can't even click on customer column to sort > when it's now customer that is default sorting. > > Is this a bug ? > > > > ...

Qrp Date functions
Where do I find the various functions to modify the Reports like cast(DatePart(Dd,[Transaction].Time) as nvarchar) and others and what they mean???? Barry Found the information at MSDN Transact-SQL Reference Barry "Barry L" <barryl@eryanjewelers.com> wrote in message news:usd3uP1CIHA.1188@TK2MSFTNGP04.phx.gbl... > Where do I find the various functions to modify the Reports > like cast(DatePart(Dd,[Transaction].Time) as nvarchar) and others > and what they mean???? > > Barry > ...

stop error defeating me
Hi, XP Pro PC. When I start the computer I can start in safe mode but when I try to start in normal mode it loads to the log on screen. I type the username and password in then it starts to load but stops after a few moments with a blue screen. The error is Stop: c000021a (fatal system error) The windows subsystem system process terminated unexpectedly with a status of 0xc0000005 (0x7c9106c3 0x0055f36c). Begininning dump of physical memory. I have uninstalled AVG, also taken out the graphics card and uninstalled all the drivers for it. I have also changed the RAM. I have also d...

Customer check data
When customers pay by check RMS asks for specific information such as drivers license number, routing number, account number, address and phone number. Does anyone have a report and or a way to extract this info from the database for cases when the check is returned for NSF? Please advise, Scott We can write you this report. Contact me for detail on price . Afshin Alikhani - [ afshin@retailrealm.co.uk ] CEO - Retail Realm = = = = = = = = = = "Scott Santorio" <scott@tt-newyork.com> wrote in message news:e8ZKkR6\$HHA.3716@TK2MSFTNGP03.phx.gbl... > When customers pa...

Error in database....
A user posted a batch in payables management. After posting, there was an error encountered. It displays that the table updating was interrupted, use batch recovery to continue the posting. But when I used the batch recovery, it was not successful to continue the update process. When I click the "More Details" button it displays, A save operation on table 'PM_Transaction_WORK' caused a sharing error. How can I resolve this issue? Thanks, John John, it is a db sharing violation. Have all users logout DELETE tempdb..DEX_LOCK DELETE tempd..DEX_SESSION DELETE dynami...

Some Excel formatting functions taking a long time to work #2
Excel 2000 SP3 When I use some formatting functions for the first time in a session, such as bold, increasing font size etc, it takes up to 30 seconds to work. Meantime Excel is locked up until it completes that formatting call. I suspect faulty DLL? Has anyone experienced this? How to fix (other than a complete re- install) ? Any advice appreciated. Piri On Nov 5, 8:39=A0am, Piri <wiremu.pare...@hotmail.com> wrote: > Excel 2000 SP3 > When I use some formatting functions for the first time in a > session, such as bold, increasing font size etc, it takes =A0up to 30 > secon...

Outlook 2007 Search always returns no matches
Any search I do in Outlook 2007's new search box returns no matches. I watched the Indexing Status as it progressed, and now it says "Outlook has finished indexing all of your items" But, searching on anything, even the word "The" or "a" returns no results. I'm searching all Outlook items. I'm on an Exchange Server via VPN. Anyone have any suggestions? I uninstalled Office and re-installed and the problem went away... "Tom" <none@none.com> wrote in message news:%23yGP6NPOHHA.2140@TK2MSFTNGP03.phx.gbl... > Any search I do i...

changing values of one field based on another
How can I best change the values of one field in a table based on values of another field of the same table. We have an existing table of thousands of entries and I would like to use the following logic to populate a new boolean field. If field1 = "Done" Then BooleanFieldCompleted = True I have some Excel VBA experience but limited Access. I dont want to do this manually! Any assistance appreciated. In general, you'd use an Update query. However, in this case I don't see why you'd need such a field. Why not just create a query with a computed field that returns True...

How to add a button to restore all altered cells original values?
I want to add a reset button to an excel spreadsheet that will restore the values of all changed cells to the original saved ones. Any help would be appreciated. Thanks Dawn Hi this would require quite some VBA code as you somehow have to store the original values for example on a separate hidden sheet -- Regards Frank Kabel Frankfurt, Germany "Dawnybros" <Dawnybros@discussions.microsoft.com> schrieb im Newsbeitrag news:3340601E-16EE-4296-8F50-B0BAC18EA387@microsoft.com... > I want to add a reset button to an excel spreadsheet that will restore the > values of all ...

error 553
The following error occurs when sending email from my business domain. It does not occur when sending through my roadrunner account. The following recipient(s) could not be reached: on 10/22/2003 2:05 PM 553 sorry, that domain isn't in my list of allowed rcpthosts (#5.7.1) What does this mean and how can it be fixed? ...