Results 1 to 9 of 9
  1. #1
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Yilgarn region of Toronto, Ontario
    Posts
    5,453
    Thanks
    0
    Thanked 0 Times in 0 Posts

    What Cell am I in? (Excel2003+)

    <P ID="edit" class=small>(Edited by chrisgreaves on 16-Jan-09 15:27. Thought of a horrible work-around )</P>The attached workbook shows a UDF working well.
    You give the function dblYTD the address of a cell which contains a month, and a row displacement, and it caculates a cumulative total from that month to the start of the year (January), using data in the row "displacement" offset from the date cell.
    In the sample shown, 10,507 is the sum of the cells 3 rows below the date values, for the dates from July ("T$3") back to the January of that year.
    In the sample shown, 10,577 is the sum of the cells 4 rows below the date values, for the dates from July ("T$3") back to the January of that year.

    <UL><LI>It seems to me that I can do without the "Row offset" parameter if only my UDF can determine the row number of the cell being calculated.[/list]For example, the use of dblYTD in cell AA8 ought to be able to work out for itself that it is being executed in row 8, and that row 8 is 5 rows below row 3 ("T$3"), and so the row offset will be 5.
    Then there is no need for the user to supply that second parameter "lngOffsetRows".

    I can't find anything in Excel/VBA Help or on the web that indicates that a UDF can determine the target cell of its result during computation.

    Of course, this may be just another case of refrigerator blindness .....

    (later) I can fudge it by having the user supply a cell reference ("=dblYTD(Q$9,ROW(AH16))") which at least allows them to use fill-down to populate cells. My UDF is modified:
    <pre>Dim myLngOffset As Long
    myLngOffset = lngOffsetRows - rngDateCell.Row
    (snip!)
    dblResult = dblResult + rng.Offset(myLngOffset, 0).Value</pre>


    <font color=448800>Jerry and Bolingbroke: I'll deal with you two later</font color=448800>
    Attached Files Attached Files

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: What Cell am I in? (Excel2003+)

    In a user-defined function, Application.Caller is the cell containing the formula (as a Range object).
    So you can get the row number of the cell containing the formula from Application.Caller.Row.

    See the Excel VBA help for the meaning of Application.Caller in other situations.

  3. #3
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Yilgarn region of Toronto, Ontario
    Posts
    5,453
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: What Cell am I in? (Excel2003+)

    <pre>Dim myLngOffset As Long
    myLngOffset = Application.Caller.Row - rngDateCell.Row</pre>

    You know what, Hans? I really hate it when you come up with such a glib answer ..... <img src=/S/burnup.gif border=0 alt=burnup width=31 height=31> <img src=/S/hugs.gif border=0 alt=hugs width=41 height=25>

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: What Cell am I in? (Excel2003+)

    Anything to annoy you! <img src=/S/evilgrin.gif border=0 alt=evilgrin width=15 height=15>

  5. #5
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: What Cell am I in? (Excel2003+)

    If you are only interested in one cell, you can also use <code>Application.ThisCell</code>
    Regards,
    Rory

    Microsoft MVP - Excel

  6. #6
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Yilgarn region of Toronto, Ontario
    Posts
    5,453
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: What Cell am I in? (Excel2003+)

    >If you are only interested in one cell, you can also use Application.ThisCell

    Thanks Rory, but I'd better stick with Hans's suggestion because:
    (1) I have lots of cells that need the UDF and
    (2) You know what he's like when I don't follow his advice (grin!)

  7. #7
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: What Cell am I in? (Excel2003+)

    What I meant was, if you were not array-entering it into multiple cells at once. If you do that then Application.ThisCell returns just the top left cell, whereas Application.Caller returns the entire range into which you have array-entered the formula. Other than that, they work the same way, as far as I know. (e.g. if your UDF is in 87 individual cells, both will return the same info).
    Regards,
    Rory

    Microsoft MVP - Excel

  8. #8
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Yilgarn region of Toronto, Ontario
    Posts
    5,453
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: What Cell am I in? (Excel2003+)

    >What I meant was, if you were not array-entering it into multiple cells at once.

    As in "Shift-Enter", I assume.
    No.
    Indeed the curent worksheet is being assembled for proof-of-concept purposes and will probably be built by VBA code.
    I am the end-user who will make use of the UDF (fill-down) to provide the client with a visible rendition of the proposed calculated values.
    Thanks.

  9. #9
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: What Cell am I in? (Excel2003+)

    Ctrl+Shift+Enter, yes!
    Regards,
    Rory

    Microsoft MVP - Excel

Posting Permissions

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