Is a cache necessary? (Excel XP)

[Rudi]

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

[HansV]

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.

[Rudi]

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?

[HansV]

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.

[Rudi]

Tx Hans

[sdckapr]

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

[HansV]

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

[sdckapr]

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

[HansV]

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

[sdckapr]

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

[HansV]

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>

[Rudi]

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

Tx for the indepth discussion.
Cheers

[sdckapr]

<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