+ Reply to Thread
Results 1 to 3 of 3

Passing a table reference to a user defined function?

  1. #1
    Registered User
    Join Date
    04-24-2012
    Location
    Macon, Ga
    MS-Off Ver
    Excel 2003
    Posts
    2

    Passing a table reference to a user defined function?

    I am trying to write a function that returns the straight line distance between two points in a 3 dimensional space. The worksheet has a table called "Coordinates" that has 4 columns: Column 1 is the point label, 2 through 4 are the X,Y and Z coordinates. The number of rows varies depending on the number of points. The function receives two point labels and the location of the table, does a Vlookup on each point to find its coordinates and calculates the distance.

    Here is what I thought should work but it blows out on the first Vlookup. How should I do it?

    In the spreadsheet I have:
    =Wirelength(A1,A2,Coordinates)

    And the UDF is:
    Function WireLength(Point1, Point2, ARYLOC1 As Range)
    X1 = WorksheetFunction.VLookup(Point1, Range(ARYLOC1), 2)
    X2 = WorksheetFunction.VLookup(Point2, Range(ARYLOC1), 2)
    Y1 = WorksheetFunction.VLookup(Point1, Range(ARYLOC1), 3)
    Y2 = WorksheetFunction.VLookup(Point2, Range(ARYLOC1), 3)
    Z1 = WorksheetFunction.VLookup(Point1, Range(ARYLOC1), 4)
    Z2 = WorksheetFunction.VLookup(Point2, Range(ARYLOC1), 4)
    Xdif = Abs(X1 - X2)
    Ydif = Abs(Y1 - Y2)
    Zdif = Abs(Z1 - Z2)
    WireLength = Sqr(Xdif ^ 2 + Ydif ^ 2 + Zdif ^ 2)
    End Function
    Last edited by WGAshmore; 04-24-2012 at 04:13 PM. Reason: remove exrta line

  2. #2
    Forum Guru
    Join Date
    04-13-2005
    Location
    North America
    MS-Off Ver
    2002/XP and 2007
    Posts
    15,802

    Re: Passing a table reference to a user defined function?

    It is easier to help you if you explain what "blows out on the first Vlookup" means. Does it mean that you got an error message? If so, what error message?

    That said, I suspect that the error is in your use of the Range object. Usually when you use the range object like that, the argument is a text string telling the code what range you mean.
    When I've passed a range to a udf like that, I use it as if it is it's own range object/variable. So aryloc1.cells(1,1) would refer to the upper left corner of the range.

    I would expect something more like X1=worksheetfunction.vlookup(point1, aryloc1,2) would be the syntax you are looking for.

  3. #3
    Registered User
    Join Date
    04-24-2012
    Location
    Macon, Ga
    MS-Off Ver
    Excel 2003
    Posts
    2

    Re: Passing a table reference to a user defined function?

    Well, what do you know. It worked by removing Range from the vlookup. I thought I had tried that permutation. Thanks!

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1