Results 1 to 3 of 3
  1. #1
    Gold Lounger
    Join Date
    Dec 2000
    Location
    New Hampshire, USA
    Posts
    3,386
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Bug in Areas collection (All)

    There is a serious bug in Excel that occurs even if the code in a UDF does not mess with the Areas collection.

    The bug occurs if the user uses an argument to a UDF that uses more than one Area and there is more than one worksheet in the workbook.

    The example shows that the bug appears even if the code uses a simple WorkSheetFunction.SUM, but SUM used directly in the worksheet does not get affected by the bug.

    So MSFT does know how to circumvent the bug within the worksheet when using, say, SUM.
    Why not within the code when using WorksheetFunction.SUM?

    Is there a workaround?

    The example is at http://www.standards.com/OhMyExcel/E...sFeatures.html.

    Note: I intentionally did not use IsMissing, anyway, that is irrelevant to the bug.

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

    Re: Bug in Areas collection (All)

    Howard, I think the underlying cause of this bug is the same as that discussed in the thread starting at <post#=205475>post 205475</post#>. Yes, it is a real bug, and it occurs in all versions of Excel, it seems. A possible workaround (not a solution) is to use a ParamArray, but you indicated in the thread just mentioned that that wasn't an option in your case.

  3. #3
    Gold Lounger
    Join Date
    Dec 2000
    Location
    New Hampshire, USA
    Posts
    3,386
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Bug in Areas collection (All)

    The example I posted demonstrates that there is a bug because using the SUM function in the worksheet produces a different (correct) result than that achieved using the WorksheetFunction.SUM within the UDF.

    The example was just a convenient way to demonstrate the bug in a UDF that has no reason to use ParamArray, or even expect such an effect from an arg that uses a multiple area range.

    My earlier example had the UDF messing directly with Areas.
    The new example eliminates that as a possible cause od the problem since it is Excel itself that is incorrectly passing the Range to the UDF.

    ParamArray is not a workaround for the general case.

Posting Permissions

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