Avez-vous déjà vécu la situation où votre tableau croisé dynamique (TCD) affiche une valeur qui ne devrait pas se trouver dans vos données? Si oui, sachez qu'il s'agit d'un problème de mémoire cache des tableaux croisés dynamiques (TCD). Et que l'on peut facilement y remédier grâce à VBA.
Résoudre le problème de "mémoire cache" des TCD
Il arrive qu'après un certain temps et après des manipulations de données, le tableau croisé dynamique affiche des valeurs qui ne devraient plus y figurer. Ceci est surtout vrai lorsque vous travaillez avec les "Pivot tables" via VBA. Lors de l'énumération des champs et des valeurs disponibles (par ex. en créant un filtre du champ), les anciennes valeurs (déjà effacées) peuvent resurgir. Ce qui est assez ennuyeux et peut même être dangereux (cela peux biaiser les résultats de vos calculs).
Il n'est pas très connu que les tableaux croisés dynamiques ont leur propre mémoire cache (mémoire temporaire) qui peut, avec le temps, devenir problématique.
La procédure VBA qui suit "nettoie" tous les tableaux croisés dynamiques dans le Classeur (Workbook). N'hésitez pas à l'utiliser au cas où vous construisez ou manipulez ces tableaux via le code VBA. Il vaut mieux prévenir que guérir. Le mieux c'est de lancer cette procédure avant de manipuler le tableau croisé dynamique (par exemple dans une boucle, lancez le au début de chaque itération).
Code VBA pour nettoyer la mémoire cache des tableaux croisés dynamiques
La procédure VBA qui suit fait une boucle dans tous les tableaux croisés dynamiques dans le Classeur et vide leur mémoire cache. Le code est prêt à l'emploi – il vous suffit de le copier & coller dans un Module de votre projet.
Sub NettoyerCacheTablePivot() 'par: Excel-Malin.com ( https://excel-malin.com ) On Error GoTo ProcedureErreur Dim TablePivot As PivotTable Dim Feuille As Worksheet For Each Feuille In ActiveWorkbook.Worksheets For Each TablePivot In Feuille.PivotTables With TablePivot.PivotCache .MissingItemsLimit = xlMissingItemsNone .Refresh End With Next TablePivot Next Feuille Set TablePivot = Nothing Set Feuille = Nothing Exit Sub ProcedureErreur: MsgBox "Une erreur est survenue..." End Sub
Vous pouvez aussi adapter le code pour nettoyer la mémoire cache d'un tableau pivot en particulier. En ciblant un TCD particulier, vous gagnez du temps en ne vidant le cache que de ce TCD.
Pour aller plus loin en VBA et dans la gestion des tableaux croisés dynamiques
Voici quelques articles et tutoriels pour vous aider à mieux gérer les TCD et également pour approfondir vos connaissances de VBA…
- Détails des Tableaux croisés dynamiques dans le Classeur
- Autres Codes sources VBA utiles
- Liste de toutes les fonctions VBA
- Page web de Microsoft consacrée à l'Objet
PivotCache
11 commentaires sur “Nettoyer la “cache” des tableaux croisés dynamiques”
Merci pour ce code. Il fonctionne à merveille et a résolu mon problème de filtrage de tables pivot.
super nickel, merci pour ces lignes !!
Je suis content d'avoir pu être utile…
Bonjour,
J'ai essayé de mettre ce code dans mon fichier mais cela me bloque à la ligne ".refresh" j'ai une erreur 1004.
Je ne comprends pas pourquoi :'(
Je suis sous office 2016, peut-être que c'est la raison ?
Bonjour Bory,
je viens de tester le code dans Excel 2016 et il fonctionne correctement.
Par contre j'ai réussi à reproduire cette erreur: elle survient lorsqu'une Feuille qui contient le (un des) tableaux croisés dynamiques est protégé par le mot de passe.
C'est peut-être le cas dans votre fichier?
Assurez vous que toutes les Feuilles de votre Classeur soient disponibles (= pas protégées).
J'espère que ma réponse vous aidera. Bien à vous, Martin
Bonsoir Martin,
Bizarre, je n'ai aucune protection de la feuille. Les particularités avec les TCDs que j'utilise dans ce fichier sont :
– L'utilisation d'un add-on logiciel appelé MicroStrategy qui me créé mes TCDs dans le fichier excel
– L'utilisation d'une option lors de la création du TCD qui me permet de faire un count distinct :/
Cordialement
Un tout grand merci pour cette information. J'ai adapté le code pour mes besoins et le résultat fut exceptionnel. La création automatique de présentations PowerPoint me prenait près de deux heures et je ne comprenais pas pourquoi (pour 19 fichiers et 26 slides).
J'ai rajouté la ligne de code suivant régulièrement car je fais beaucoup d'opération avec mon TCD =
ActiveSheet.PivotTables("Tableau croisé dynamique13").PivotCache.MissingItemsLimit = xlMissingItemsNone
Grâce à ce nettoyage du cache, l'ensemble tourne en une minute et trente secondes !!! Et encore, c'était si rapide que j'ai dû mettre une instruction "wait" entre mes différentes macros, sinon Excel ne prenait pas les bonnes valeurs.
Super! Ravi d'entendre que mon code vous a aidé et vous a économisé le temps de travail…
Cordialement, Martin
Merci, cela permet aussi de ne plus voir apparaître dans les filtres d'anciennes valeurs qui n'existent plus dans les tableaux
Exactement!
Merci, super code! 🙂