Nettoyer la “cache” des tableaux croisés dynamiques

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 filtres ou si vous gérez 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).

Pas familiers avec VBA? Voici comment procéder…

Si vous ne vous y connaissez pas en VBA et vous voulez résoudre ce problème uniquement avec Excel, c'est également possible…

• Effectuez le click droit sur le Tableau croisé dynamique
• Choisissez "Options du tableau croisé dynamique"
• Choisissez le volet "Données"
• Dans la partie "Conserver les éléments supprimés de la source de données", pour l'option "Nombre d'éléments à retenir par champ", choisissez "Aucun"
• Ensuite, il faudra encore Rafraîchir le TCD (click droit sur le TCD, puis "Actualiser")

Sachez que vous devez effectuer cette manipulation pour chaque Tableau croisé dynamique séparément.

Si votre Classeur contient plusieurs TCD, vous pouvez utiliser la procédure VBA qui suit…

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 particulier.

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…

• Grand tutoriel sur le Tableau croisé dynamique dans Excel
• 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
• et aussi… Comment utiliser RECHERCHEV en VBA