Results 1 to 10 of 10
  1. #1
    5 Star Lounger
    Join Date
    Apr 2001
    Location
    Arriving Somewhere but not Here
    Posts
    698
    Thanks
    0
    Thanked 0 Times in 0 Posts

    charts, dynamic ranges and volatile functions (XL2

    The MS Knowledge Base Article 213683 describes how:
    1) If you save an XL2000 workbook with a chart as the active sheet
    2) AND the chart refers to a worksheet that refers to formulae that refer to user defined functions
    3) AND those UDFs use the volatile method
    then you'll get a "not enough memory" error when you open the sheet and you're stuffed because "MS has confirmed this is a problem...etc." OK, they do offer a couple of workarounds but they are not a real fix.

    My workbooks were originally created in XL97 and those that have been saved with a chart as the active page give the 'not enough memory' message when opened in XL2000 BUT while I do have UDFs on the worksheet from which the chart is drawn none of them explicitly use the line 'Application.volatile'. However, the charts are constructed from dynamic ranges [i.e. using =OFFSET(reference,rows,cols,height,width), where height is set using COUNT()] that are volatile.

    I'm still trying different permutations of the workbooks, removing different bits of code, saving on a chart, saving on a sheet, etc., to see if it's just a hurdle I have to get over as part of the change from XL97 to XL2000. Meanwhile, has anybody out there got more info on this issue?

    stuck

  2. #2
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: charts, dynamic ranges and volatile functions (XL2

    No, no info I'm afraid. I am willing to check other XL versions with a sample book if you like, but that will not help much I suspect.
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  3. #3
    5 Star Lounger
    Join Date
    Apr 2001
    Location
    Arriving Somewhere but not Here
    Posts
    698
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: charts, dynamic ranges and volatile functions

    ARGHHH! Jan Karel doesn't know more, that makes it worse than I thought.

    Your implication that more recent versions are unlikely to help is particularly worrying, surely this must have been fixed by now?

    stuck

  4. #4
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: charts, dynamic ranges and volatile functions

    I didn't mean the newer versions would be no better, I meant that should it be solved on a newer version, that is of no help to you when you're stuck with version 2000.

    Send me one and I'll test it.
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  5. #5
    5 Star Lounger
    Join Date
    Apr 2001
    Location
    Arriving Somewhere but not Here
    Posts
    698
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: charts, dynamic ranges and volatile functions

    <img src=/S/doh.gif border=0 alt=doh width=15 height=15> <img src=/S/stupidme.gif border=0 alt=stupidme width=30 height=30>

    stuck by name stuck by nature

  6. #6
    5 Star Lounger jujuraf's Avatar
    Join Date
    Jun 2001
    Location
    San Jose, California, USA
    Posts
    1,061
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: charts, dynamic ranges and volatile functions

    I have no solution, but I did read an article not too long ago that talked about this 'bogus' Not Enough Memory error. It said that Excel has built-in memory limits which have nothing to do with your physical memory <img src=/S/bash.gif border=0 alt=bash width=35 height=39>. How dumb is that?! It's hard-coded in Excel and changes with each version and it seems you've stumbled on the right combination of pot holes to generate the error.

    There are many articles on 'Not Enough Memory' if you do a search on the MS KB. All of the ones I looked at didn't mention the built-in memory limits; rather they gave the typical 'fix' of uninstall/reinstall. I wish I could find that article, I think I saved it for future use.

    Deb

  7. #7
    5 Star Lounger
    Join Date
    Apr 2001
    Location
    Arriving Somewhere but not Here
    Posts
    698
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: charts, dynamic ranges and volatile functions

    My continuing experiments have shown the following:

    If I find a workbook that makes XL2000 gives the 'not enough memory' message then acknowledging it lets me into the workboook but the active chart t is toast. Switching to the worksheet that generates the chart and forcing a recalc doesn't fix the chart. Switching to a worksheet that doesn't have anything to do with charts and saving the workbook in this 'safe' position means that when I try to reopen the workbook then XL2000 is toast..! It hangs as it loads the workbook. Eventually Win 2000 reports 'not responding'. Alternatively, I have to resort ot Task Manager to 'End Now'.

    However, if I open the problem workbook in XL97 (with no grief) and then resave it in a 'safe' position Xl2000 is quite happy to open the workbook everything seems fine. Better still, once I get a 'clean' open and save in XL2000 the 'not enough memory' error doesn't appear even if I leave the previously problem chart as the active sheet on close.

    I am cautiously optimistic that this is just an irritation I'll have to deal with as part of the move from XL97 to XL2000. Of course, that's for my case, it may not hold for others!

    (not quite as) stuck

  8. #8
    5 Star Lounger jujuraf's Avatar
    Join Date
    Jun 2001
    Location
    San Jose, California, USA
    Posts
    1,061
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: charts, dynamic ranges and volatile functions

    Yet another reason to keep multiple versions of Excel lying around. <img src=/S/evilgrin.gif border=0 alt=evilgrin width=15 height=15> There are quite a few problems that can be fixed by opening/saving/whatever a workbook from one version of Excel to another (older or new, depending on problem). I have Excel 97/2000/2002 installed on various computers and it has saved my butt several times (as well as allowing me to better test all those undocumented version incompatibilities with VBA).

    Glad you found a workable solution.

    Deb

  9. #9
    Super Moderator
    Join Date
    May 2002
    Location
    Canberra, Australian Capital Territory, Australia
    Posts
    5,054
    Thanks
    2
    Thanked 417 Times in 346 Posts

    Re: charts, dynamic ranges and volatile functions

    Hi Stuck,

    A couple of things to try:

    1. You don't need Application.Volatile in every user-defined function - only in functions that refer to cells that are not passed in as an argument to the function. So you can safely delete Application.Volatile from UDFs affecting your chart under those circumstances.

    2. If (1) doesn't work for you, you can still eliminate Application.Volatile from a troublesome function and force them to recalc automatically by including the NOW() function in the associated formula. For example, if the formula using the UDF returns a text string, you can append:
    &IF(NOW()=0,"","")
    Alternatively, if the formula using the UDF returns a numeric value, you can append:
    *(NOW()<>0)
    Note, though, that because NOW() is itself volatile, Excel will prompt you to save the workbook even if no changes have been made.


    Cheers
    Cheers,

    Paul Edstein
    [MS MVP - Word]

  10. #10
    5 Star Lounger
    Join Date
    Apr 2001
    Location
    Arriving Somewhere but not Here
    Posts
    698
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: charts, dynamic ranges and volatile functions

    Hmm, things for me to think about.

    As I said above, none of my code explicitly uses the statement Application.Volatile. Perhaps, NOW() will help, I'll look into that one but if that itself is volatile I'm not convinced it will help. I have a funny feeling the problem goes wider than volatile UDFs and under peculiar circumstances not yet noted in the MS KB built-in XL functions that are volatile (e.g. =OFFEST, with a count function in it to create a dynamic range) will trigger the problem.

    So far, reverting to an old copy of XL97 (sound advice to keep the odd dinosaur lying around from jujuraf above!) and resaving off a chart, has enabled me to get into the sheet. Once saved in XL2000, even if a chart is the active sheet, the workbook is stable again.

    Thanks again one and all.

    (still only slightly) stuck

Posting Permissions

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