Results 1 to 9 of 9
  1. #1
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Parameter Type (2000 SP3)

    Can anyone tell me how to mass a multi-Sheet reference like Sheet1:Sheet3!A:A to a user defined function? I would have thought the UDF definition would be something like:

    <code>
    Public Function UDF(oRng as Range)as Long
    </code>

    But that does not work. The only thing that I can get to work at all is to define the parameter as a variant. However, when I do that, I can't figure out how to use the parameter to reference the ranges on the sheets.
    Legare Coleman

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

    Re: Parameter Type (2000 SP3)

    You could pass a string argument in the form "Sheet1:Sheet3!A:A" and parse it - the part before ! refers to the sheets, the part after it to the cells.

  3. #3
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Parameter Type (2000 SP3)

    Yes, but then the formula using the UDA does not recalculate when a cell in the range is changed if I don't make the function volatile.
    Legare Coleman

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

    Re: Parameter Type (2000 SP3)

    I don't think you can pass a 3D (multi-sheet) range to a user-defined function, it is replaced by an error value.

    All examples I can find in Google and Google Groups of user-defined functions operating on 3D ranges use string arguments and Application.Volatile.

  5. #5
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Parameter Type (2000 SP3)

    OK, that is kind of what I figured. I wonder why it can't be passed like any other range?
    Legare Coleman

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

    Re: Parameter Type (2000 SP3)

    Microsoft uses some kind of trick to handle 3D ranges in functions such as SUM, but apparently this isn't available to VBA...

  7. #7
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Parameter Type (2000 SP3)

    Not sure why tricks are needed. Looks like it could be handled just like any other non contiguous range. In other words, just use the existing areas collection.
    Legare Coleman

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

    Re: Parameter Type (2000 SP3)

    Sounds reasonable, but... <img src=/S/shrug.gif border=0 alt=shrug width=39 height=15>

  9. #9
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Parameter Type (2000 SP3)

    The "trick" may be the 3D range name itself and its parsing. You can pass non-contiguous ranges to a UDF, but the "3D range" listed is not a non-contiguous range (as listed), it almost seems an "ersatz name".

    The internal code seems to recognize the 3D range and can convert it into a non-contiguous range.

    I don't understand why this is unavailable to VB, but there are many things in XL unavailable to VB...

    Steve

Posting Permissions

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