Very strange calculation problem in Excel 2003 SP3

  • Hello,


    I have experienced a very peculiar Excel problem that I am at a loss to explain. I have some VBA code to iteratively calculate 4-5 sheets across a single workbook, so nothing groundbreaking.


    By and large this code runs fine except for a particular section - it's always the same set of about 15 cells - on one particular sheet that always throws #VALUE erors after the VBA has run.


    To fix this problem I either hit Shift+F9 on the problem sheet or F2+Enter on the individual problem cells. without changing anything in the workbook, these cells now calculate. This works every time, so I can't understand why the VBA fails.


    The VBA itself is very simple. Simply a series of calls like:
    Sheet1.UsedRange.Calculate
    Sheet2.UsedRange.Calculate
    Sheet3.UsedRange.Calculate, etc.


    The reason I use UsedRange is because of this issue: http://support.microsoft.com/kb/919127. I also ran some VBA code to establish the bounds of UsedRange, and I can confirm that the problem cells are within this bound (they are the last column).


    The cells that throw the error are in the last column of the UsedRange on that particular sheet. I tried to extend the UsedRange by adding some dummy TODAY() / NOW() calls beyond this column, but to no avail.


    I then ran some VBA to try and see what the dimensions of the UsedRange was, and the problem cells fell within the UsedRange.


    I then tried to replace Sheet1.UsedRange.Calculate with just Sheet1.Calculate, but again received the same error - the 15 cells threw #VALUE and everything else calculated.


    It's not difficult to rectify the broken cells. I'm just curious to know what could be tripping it up to begin with.


    I was wondering if you could throw any light on this?


    PS - I can't attach the sheet because it uses some custom functions running off a commercial add-in.

  • Re: Very strange calculation problem in Excel 2003 SP3


    What's in the cells that error? UDFs?

    Rory
    Theory is when you know something, but it doesn’t work. Practice is when something works, but you don’t know why. Programmers combine theory and practice: nothing works and they don’t know why

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!