Results 1 to 13 of 13
  1. #1
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    Is a cache necessary? (Excel XP)

    Hi all,

    Between Excel 97 and Excel XP, I notice that in VBA, there is a new Pivot Object called a Pivot Cache. Although I found some code to illustrate how to create and use a cache in VBA, I also notice that the pivot table can still be created simply by using the PivotTableWizard object. Is is theirfore necessary to use the cache object whengenerating VBA code to construct a pivot table?

    Tx
    Regards,
    Rudi

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

    Re: Is a cache necessary? (Excel XP)

    You don't need to create a pivot cache explicitly, but if you don't, Excel will create one silently when you create a pivot table. Each pivot table is associated with a pivot cache.

  3. #3
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Is a cache necessary? (Excel XP)

    If this is the case, why have the cache object? If I program it into the code (to use the cache), will it benefit by running the pivot faster. ie: Is there significant advantages in programming the cache in VBA?

    Any ideas?
    Regards,
    Rudi

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

    Re: Is a cache necessary? (Excel XP)

    The cache becomes useful when you create two pivot tables based on the same source data. Reusing the pivot cache of the first pivot table for the second one results in significant savings in resource use.

  5. #5
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Is a cache necessary? (Excel XP)

    Tx Hans
    Regards,
    Rudi

  6. #6
    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: Is a cache necessary? (Excel XP)

    Maybe I missed the original question, but I though you could do this with the old wizard method and not the newer pivot cache method.

    I have not used the pivot cache method to create pivots, still utililizing the pivot wizard method in code (partly familiarity, partly since it is the code that works in XL97 on my home computer).

    Fundamentally, is there really a difference in the 2 methods?

    Steve

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

    Re: Is a cache necessary? (Excel XP)

    I'm not really an expert in this, but I don't think there's a fundamental difference.

  8. #8
    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: Is a cache necessary? (Excel XP)

    That was my understanding (though I am no expert in Pivots, especially in code) which made me think the answer to the original question:
    <hr>Is is theirfore necessary to use the cache object whengenerating VBA code to construct a pivot table?<hr>

    was "no", but your answer seemed to imply a "yes", (but it seemed to more answer the "Title question" and not the question in the post)

    Steve

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

    Re: Is a cache necessary? (Excel XP)

    I thought I replied No to Rudi's question. <img src=/S/confused.gif border=0 alt=confused width=15 height=20>

  10. #10
    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: Is a cache necessary? (Excel XP)

    Perhaps you did and i missed it. It just seemed you were answering the title and not the question in the post. I saw the question more as the inherent differences between the older wizard method and the newer pivot cache method and I did not see any thing about that issue and not so much the issue of what cache is used by excel with the pivottables, but perhaps I misunderstood

    [i guess there should be no "perhaps", I obviously have either misunderstood the question, your answer, or perhaps both - those little gray cells don't always seem to work as well as they should... <img src=/S/smile.gif border=0 alt=smile width=15 height=15>]

    Steve

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

    Re: Is a cache necessary? (Excel XP)

    Never mind. I think we agree that there is no fundamental difference between the two methods. <img src=/S/smile.gif border=0 alt=smile width=15 height=15>

  12. #12
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Is a cache necessary? (Excel XP)

    I guess we can say : Use what you're familiar to...excel does not seem to mind!

    Tx for the indepth discussion.
    Cheers
    Regards,
    Rudi

  13. #13
    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: Is a cache necessary? (Excel XP)

    <hr>I guess we can say : Use what you're familiar to...excel does not seem to mind!<hr>

    I think that is a good general statement on most things in excel (as well as other things in life). There are generally multiple ways to accomplish some thing and most ways are not that different from other ways, so doing it a way you know how and are comfortable with, will most times, make the most sense.

    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
  •