# Macros-commandes VBA/Création de Tableau croisé
## Définitions
### Qu’est-ce qu’un tableau croisé dynamique ?

Un tableau croisé dynamique (appelé aussi sous l’abréviation « TCD ») permet grâce à l’utilisation de certains tableurs, de synthétiser une base de données brutes. Le TCD est un outil très utile pour analyser des bases de données très fournies puisqu’il en facilite leurs analyses et leurs comparaisons.
De plus, c’est un outil totalement modulable et personnalisable. Sa particularité est qu’il est dynamique. On entend par dynamique, le fait que le tableau soit modulable en fonction des besoins d'analyse mais aussi par le fait qu’il prenne en compte toutes les modifications apportées au tableau brut, cela permet ainsi d’actualiser les données du TCD. En effet, on peut choisir les données que l’on veut faire apparaître en colonne et en ligne même après avoir généré un premier TCD. L'utilisateur a le choix de la fonction de regroupement, il peut également décider des champs à utiliser pour réaliser son analyse.
Grâce au TCD il est également possible d'afficher plusieurs fonctions (des moyennes, des sommes, etc.).
Afin de comprendre l’utilité du TCD, prenons l’exemple d’une entreprise :
Une entreprise X dans le secteur de la téléphonie veut savoir combien de contrats clients ont été résiliés au cours de l’année N et pour quels motifs.
Au cours de l’année les salariés de l’entreprise X regroupent dans un tableau les différentes résiliations de la manière suivante :
En fin d’année, le chef de service veut analyser les motifs de résiliations les plus courants. Son but : comprendre la raisons de la résiliation des clients et mettre en œuvre une solution pour y remédier. Les membres de son service réalisent alors le TCD suivant :
Ainsi, le chef de service peut en conclure que le motif de résiliation qui revient le plus souvent est le prix. Il pourra alors appliquer une solution pour réduire les prix et conserver ses clients.

### Qu’est-ce qu’une macro ?

Une macro ou macro-commande permet d'enregistrer une suite d'instructions écrites les unes après les autres. L'enregistrement d'une macro donne naissance à un code qui se fait en langage VBA (Visual Basic for Applications). Le langage VBA est un dérivé du langage Visual Basic, celui-ci a été spécialement développé pour les applications du pack Office (Word, Excel, PowerPoint).
Lorsque l’on exécute la macro, le tableur va interpréter les différentes instructions sauvegardées dans le code de la macro à exécuter. Cette exécution permettra de reproduire l'intégralité des commandes enregistrées. Chaque étape sera répétée à l'identique à chaque exécution de la macro.
La macro a pour finalité d'automatiser des tâches afin de les rendre beaucoup plus rapides que si elles avaient été effectuées manuellement.
Dans le cadre d'un projet d'entreprise, elles peuvent aussi avoir un intérêt lors de tests répétitifs de contrôle de qualité des données et/ou de tests de non-régression.
Son rôle est aussi de faciliter la vie de l'utilisateur. La macro est souvent utilisée lorsqu'une tâche est répétitive, ce qui permet à l'utilisateur de travailler beaucoup plus rapidement et efficacement sans erreurs manuelles.

## Comment actualiser avec une macro un TCD créé manuellement?
### Renseignement des données

Pour créer un TCD, il faut dans un premier temps créer une base de données dans un tableur pouvant faire appel au TCD.
Cette base de données devra alors contenir des titres en colonnes et/ou en ligne, puis devra être alimentée selon l'envie de l’utilisateur qui crée le tableau.
Il est primordial que chaque colonne possède une en-tête si l’on veut qu’elles figurent dans le TCD.
Un TCD ne peut être créé s'il n'y a pas d'en-tête dans les colonnes.
On peut reprendre l’exemple de l’entreprise X (voir « qu’est-ce qu’un tableau croisé dynamique ») qui regroupait en colonne les rubriques :
* « Nom »
* « Date d’inscription »
* « Date de résiliation »
* « Motifs de résiliation ».
Cette entreprise a ainsi alimenté son tableau au fur et à mesure de l’année.

### Création manuelle du TCD

Lorsque le moment de l’analyse arrive, l’individu va créer manuellement son TCD.
La démarche est alors simple :
* Sélectionner les données
* Cliquer sur l'onglet « insertion »
* Cliquer sur « tableau croisé dynamique »
* Une boîte de dialogue s'affiche (Annexe 1)
* Sélectionner "nouvelle feuille de calcul" ou "feuille de calcul existante"
* Cliquer sur "Ok"
Lors de la sélection des données, il est conseillé de sélectionner les colonnes afin que la sélection s'effectue jusqu'en bas. Cela évite de devoir redéfinir la source du TCD à chaque nouvelle saisie dans la base de données.
L'étape suivante est de définir les rubriques du TCD, c’est-à-dire, savoir ce que l’on va mettre en colonne, en ligne, et ce que l’on veut mettre en avant (une somme, un nombre, un pourcentage etc.).
Le choix peut être plus ou moins complexe, mais celui-ci est essentiel. Il faut que les résultats qui ressortent du TCD soient clairs et précis, afin que l’on puisse facilement les analyser. Il ne faut surtout pas que le TCD soit plus complexe que le tableau brut. Les résultats qui en ressortent doivent être évidents et "sauter aux yeux".

### Enregistrement de la macro : Actualiser le TCD automatiquement

Afin d’actualiser automatiquement le TCD, nous allons faire appel à une macro VBA. Pour que cela soit plus simple à réaliser, nous avons décidé de faire appel à l’enregistreur. Cette manipulation est simple, il suffit de faire l’actualisation du TCD manuellement tout en enregistrant une macro. Ainsi, l’enregistreur écrira tout seul la macro dans Visual Basic.
1ère étape : L’enregistreur
* Cliquer sur l'onglet « Affichage » ou sur l'onglet "Développeur"
* Cliquer sur « enregistrer une macro », une boite de dialogue va alors s'ouvrir (annexe 2) avec les rubriques suivantes :
** Nom de la macro, ex : « ActualiserTCD »
** Touche de raccourci
** Enregistrer la macro dans
** Description, ex : "Actualiser automatiquement le TCD"
** OK
* Effectuer l'action d'actualisation : Faire un clic droit sur le TCD et cliquer sur « Actualiser les données »
* Se rendre à nouveau sur l'onglet "Affichage" ou "Développeur"
* Cliquer sur « Arrêter l’enregistrement »
2e étape : Analyse du langage Visual Basic
* Cliquer sur « Macro »
* Cliquer sur la macro crée : « ActualiserTCD » ;
* Cliquer sur « Modifier » ;
Ou passer par Visual Basic dans "outil"
La macro suivante apparaît :
Il est important d’examiner les actions qui ont été enregistrées par Excel.
C’est d’autant plus important lorsque la macro ne s’exécute pas comme on le souhaite. On peut rapidement voir où se trouve l’erreur d’écriture.
De plus, analyser la macro permet également d’apporter des modifications. En effet, lors de l’enregistrement, on a tendance à vouloir aller trop vite, et on clique sur des boutons qu’il ne faut pas. Toutes ces manipulations inutiles peuvent être supprimées.
Afin de désactiver la carte vidéo et améliorer les performances, il faut rajouter :
* En début de macro : Application.ScreenUpdating = False
* En fin de macro : Application.ScreenUpdating = True
La macro finale sera donc :

### Création d’un bouton et affectation de la macro « Actualiser le TCD »

Pour finir, nous allons créer un bouton qui permettra à l’utilisateur d’actualiser le TCD uniquement en cliquant dessus.
La démarche est alors la suivante :
* Insérer une forme ou une image en cliquant sur l'onglet insertion, ex : un rectangle
* Modifier la mise en forme et ajouter un texte s'il s'agit d'une forme, ex de texte : « Cliquer ici pour actualiser le TCD »
* Faire un clic droit sur la forme et cliquer sur « Affecter une macro »
* Une boîte de dialogue va s'ouvrir (Annexe 3), sélectionner la macro « ActualiserTCD » et cliquer sur OK

## Comment créer automatiquement un TCD à l’aide d’une macro ?
### Test avec enregistrement
#### Démarche

Pour automatiser la création du TCD, on peut passer par la méthode de l’enregistreur.
La démarche est la suivante :
1ere étape : Création de la macro
* Activation de l’enregistreur
* Nommer la macro : « CREERTCD » ; « OK »
* Sélection des données : sélectionner les colonnes afin que cela prenne en compte toutes les lignes en cas d’ajout de données
* Insertion du TCD : « Insertion » ; « Tableau croisé dynamique »
* Choisir d’imposer ou non les champs du TCD à l’utilisateur :
** Si oui, choisir les rubriques à mettre dans « Etiquettes de colonnes » ; « Etiquettes de lignes » , « Filtre du rapport » ; « Valeurs »
** Si non, passer à l’étape suivante
* Actualiser le TCD : Clic droit ; « Actualiser » (cette étape permet de prendre en compte les lignes supplémentaires entre la saisie des données du tableau et la création du TCD)
* Choix des champs du TCD
* Arrêter l’enregistreur
2e étape : Exécution de la macro
Il y a une erreur qui survient lorsque l’on utilise l’enregistreur pour automatiser la création d’un TCD. En effet, si l’on exécute la macro, une boîte de dialogue s’ouvre (annexe 4).
Cette dernière a pour rôle d’indiquer lorsqu’il y a une erreur d’écriture dans la macro.
3e étape : Analyse de l’écriture dans Visual Basic
Lorsqu’il y a une erreur, il est nécessaire d’examiner ce qu'Excel a enregistré. En reprenant l’exemple de l’entreprise X, le code suivant apparaît sous Visual Basic :
Le problème vient de cette partie de l’écriture :
Le problème est que nous spécifions la feuille de destination et également la plage du tableau croisé dynamique dans l’écriture. La conséquence de ce type d’écriture est que la macro n’est pas dynamique. Ce qui explique que la macro ne puisse s’exécuter.
4e étape : Résolution du problème Pour résoudre le problème, il faut supprimer quelques lignes :
* Il faut effacer la valeur qui se trouvait dans : TableDestination
Cela revient à : TableDestination = ""
* Supprimer les lignes :
Sheets.add ; Sheets (« sheetname »).Select ; Cells (x,y).Select
5e étape : L’écriture finale pour automatiser la création d’un TCD
* Sans contrainte des champs :
Il est possible de modifier les intitulés des colonnes uniquement lorsque les champs ne sont pas définis dans la macro.
* Avec contrainte des champs :
6e étape : Création d'un bouton
* Créer un bouton : Insertion d'une forme
* Ajouter un texte : clic droit ; "modifier le texte" ; exemple : "CREER UN TCD"
* Affecter la macro : clic droit ; "affecter une macro" ; choisir la macro ; "OK"

#### Avantages

* La macro est générée automatiquement par l’enregistreur
* Méthode simple lorsque l’on ne connaît pas le langage pour créer un TCD
* Permet de se familiariser avec le langage VBA
* Les champs en lignes et en colonnes ne sont pas imposés

#### Inconvénients

* La macro peut seulement faire les actions que l’on a enregistré
* Lorsqu’il y a une erreur, la macro ne fonctionne pas du tout
* Il faut parfois revenir sur l’écriture
* Il n’est pas évident de comprendre le problème de l’écriture lorsqu’il y a une erreur

### Test avec langage Visual Basic
#### Démarches

Notre objectif est ici de créer un tableau croisé dynamique via le code VBA.
Cette macro permettra de créer un TCD avec des champs définis. Elle impose donc à l'utilisateur de programmer dans la macro les champs voulus dans le TCD.
Pour cela, nous remplissons un tableau de base avec toutes les données, nommé "Adhérents" dans la feuille "Données".
Ensuite, nous créons une nouvelle feuille, nommée "TCD automatique" afin d'y faire apparaître le TCD.
Explications des champs de l’en-tête de colonne "réglé" :
* "O" pour réglé
* "N" pour non-réglé
La démarche de notre programmation est la suivante :
1. Dimensionner les variables utilisées dans le code (phase facultative mais recommandée pour limiter les erreurs).
2. Affecter le TCD à la feuille "TCD automatique" afin de programmer que toutes les actualisations se feront sur cette feuille.
3. Supprimer tous les TCD existants de la feuille par une boucle For Each afin de rafraîchir proprement notre TCD.
4. Ajouter le TCD sur la feuille "TCD automatique" en respectant le code ci-dessous.
Attention : ce code impose à l'utilisateur de nommer son tableau de base. Ici, nous l'avons nommé "Adhérents". Nous indiquons l'emplacement du TCD; ici en cellule B5 et nous décidons de nommer notre TCD "TCD_Adhérents".
Voici ci-dessous ce que nous obtenons :
5. Ajouter les champs, une fois le TCD crée. L'utilisateur programme les champs qu’il veut insérer dans son TCD : en ligne, en colonne et en valeur.
Ici il s'agit du champ "Catégorie" en ligne, "Réglé" en colonne et "Cotisation" en valeur.
Ces noms de champs doivent correspondre aux entêtes de colonne du tableau de base.
Voici le résultat attendu :
* L’utilisateur doit penser à nommer son tableau de base afin qu’il soit repris pour créer le TCD.
* Cette macro permet de créer un TCD avec des champs définis. Elle impose donc à l'utilisateur de programmer dans la macro les champs voulus dans le TCD.

#### Avantages

Les avantages de la création d'un TCD automatique avec le langage Visual Basic :
* Automatisation des tâches répétitives.
* Suppression des erreurs humaines dès lors que le programme est débogué et qu’il n'y a pas de modification de la structure des données de base.
* Une fois créé, c’est un gain de temps pour l'utilisateur.
* Simplification de la transmission des connaissances à d'autres utilisateurs.
* Harmonisation de la structure d'un TCD pour un ensemble d'utilisateurs. Le TCD "officiel" ne pourra pas prendre une autre forme que celui programmé.

#### Inconvénients

Les inconvénients de la création d'un TCD automatique avec le langage Visual Basic :
* Nécessite une connaissance technique d'Excel et VBA pour développer le programme et le déboguer.
* Demande un investissement en temps au départ pour mettre en œuvre la solution finalisée.
* Demande une transmission des savoirs lors des changements d'organisation du travail.

## Comment peut-on paramétrer un TCD ?
### Appliquer une fonction

Cette macro applique une fonction de synthèse dans un champ défini.
Elle permet de choisir un type de calcul (somme, nombre, moyenne,...) que l’on souhaite utiliser pour résumer les données du champ sélectionné de manière automatique (par macro).
Dans l'exemple ci-dessous elle modifie la fonction de synthèse du champ "Cotisations", afin d'afficher la moyenne et de la renommer "Cotisation moyenne".
Voici les autres constantes disponibles pour la propriété Function, que l’on peut appliquer de la même manière que xlAverage ci-dessus :
* xlAverage - Moyenne
* xlCountNums - Nb
* xlMin - Min
* xlStDev - Ecartype
* xlSum - Somme
* xlVar - Var
* xlCount - Nombre
* xlMax - Max
* xlProduct - Produit
Cette macro ne pourra être utilisée qu'une fois. Après, elle demandera une modification du code VBA.
En effet, la "Somme de Cotisation" va être modifiée par "Cotisation Moyenne".
Donc si l’on répète la macro il ne trouvera pas la "Somme de Cotisation" puisque cette fonction aura été modifiée.
Il faudra la remplacée par "Cotisation Moyenne" et indiquer la fonction que l’on souhaite appliquée.
À noter que la syntaxe à une très grande importance. Une "faute de frappe" empêchera la macro de fonctionner.

### Paramétrer les sous-totaux

Nous avons la possibilité de paramétrer les sous-totaux de notre TCD. Cela permettra à l'utilisateur de figer ce mode de calcul et de l'automatiser.
* Nous pouvons tout d’abord enlever les sous-totaux de l’ensemble des champs du TCD :
* Nous pouvons aussi décider d'appliquer des sous-totaux qu’à certains champs définis :

## Comment extraire une valeur d'un TCD ?

Cette méthode permet à l'utilisateur de repérer rapidement dans le TCD une valeur spécifique.
C'est très utile lorsque l'utilisateur répète toujours une même tâche et que le TCD est assez conséquent.
Par exemple ici, dans l'exemple 2 (voir ci-dessous), l'employé doit calculer chaque mois le montant des cotisations de catégorie "E" non réglées.
Il n'aura donc qu’à lancer cette macro chaque mois afin de voir apparaître par un message (MsgBox) le montant recherché.
Voici ce que l’on obtient pour l'exemple 2 :
Il faut que tous les champs saisis dans le code VBA correspondent à des champs existants du TCD. Sinon le programme n'aboutira pas.