Créer et utiliser une grille de saisie dans Excel en utilisant VBA sans le connaître

Pierre Fauconnier

Saisie et stockage de données au travers d'une page 'formulaire' dans Excel

- Titre : Créer et utiliser une grille de saisie dans Excel en utilisant VBA sans le connaître

- Auteur : Pierre Fauconnier

- Parution : 21 juin 2008

- Mise à jour : 2 décembre 2008

- Licence : Les sources présentées sur cette page sont libres de droits et vous pouvez les utiliser à votre convenance. Par contre, la page de présentation constitue une œuvre intellectuelle protégée par les droits d'auteur. Copyright ® 2008 Pierre Fauconnier. Aucune reproduction, même partielle, ne peut être faite de ce site et de l'ensemble de son contenu : textes, documents, images, etc. sans l'autorisation expresse de l'auteur. Sinon vous encourez selon la loi jusqu'à trois ans de prison et jusqu'à 300 000 € de dommages et intérêts. Droits de diffusion permanents accordés à Developpez LLC.

Sommaire

- I. Introduction

- I-A. Prérequis

- I-B. Niveau

- I-C. Objectifs du cours

- I-D. Avertissement

- II. Analyse des données et mise en place dans Excel

- II-A. Préparation de la feuille de stockage, la "table des contacts"

- II-B. Règles pour une bonne gestion des données dans Excel

- II-B-1. Une seule table par entité

- II-B-2. Calage des données dans le coins supérieur gauche de la feuille

- II-B-3. Pas de cellule vide en ligne 1 entre la colonne A et la dernière colonne de la table

- II-B-4. Pas de cellule vide en colonne A entre le premier et le dernier enregistrement de la table

- II-B-5. Clé primaire en colonne A

- II-C. Préparation de la feuille de données

- II-C-1. Utilisation du formulaire Excel de saisie de données

- II-D. Préparation du formulaire de saisie

- III. Saisie des données et transfert dans la table

- IV. Récupération d'un enregistrement de la table

- V. Annexe: Adaptation et "réécriture" du code généré par l'enregistreur

- VI. Remerciements

I - Introduction

Une question revient régulièrement sur le forum Excel de DVP: Comment saisir des données dans une feuille "formulaire" et stocker ces données dans une feuille.

Cette question en appelle une autre: Comment récupérer des données dans une feuille "base de données" et les placer dans une feuille à des fins de modifications ou d'impression.

Je vous invite à découvrir les réponses au travers d'un tutoriel progressif, détaillé et illustré.

I-A - Prérequis

Ce cours requiert des connaissances de base en Excel. Vous devez être à l'aise avec la terminologie d'Excel. Vous savez saisir des données, manipuler des onglets. Les techniques de copier-coller dans Excel vous sont connues.

La partie Automatisation ne requiert pas de connaissances en VBA, le but du cours étant justement de réaliser les opérations d'automatisation en utilisant (presqu'exclusivement) l'enregistreur de macros.

Toutefois, j'ai développé une annexe à ce cours, dans laquelle nous aménagerons le code VBA créé par l'enregistreur. Cette partie, facultative pour l'objectif principal de ce tutoriel, requiert des connaissances de base dans la manipulation des objets Excel en VBA.

I-B - Niveau

Ce cours est de niveau intermédiaire

I-C - Objectifs du cours

Ce cours a pour objectif de vous apprendre à saisir des données au moyen d'une feuille de calcul, de stocker ces données dans une feuille de données et de récupérer une donnée de cette feuille pour remplir le formulaire d'impression.

Nous réfléchirons aux différentes étapes à réaliser, en fixant quelques règles de base de la gestion de données.

Nous apprendrons à créer un formulaire de saisie ergonomique, notamment en utilisant:

- La validation des données

- Le verrouillage des saisies

- Le parcours au sein des cellules de saisie

Ensuite, nous organiserons la feuille de stockage des données, en appliquant les règles liées à la gestion des données sous Excel:

- Ordonnancement des données

- Entêtes de colonnes (Intitulés des champs)

- Une ligne de feuille de calcul pour le stockage d'un enregistrement

- Une plage nommée pour trouver la ligne qui stockera la nouvelle donnée

Nous utiliserons l'enregistreur de macros pour transférer les données du formulaire vers la 'table de données', notamment en

- Comprenant ce qui est effectivement enregistré

- Plaçant Excel dans la configuration requise pour le démarrage de l'enregistrement

- Utilisant l'enregistrement des déplacements vers des cellules absolues ou relatives

I-D - Avertissement

Pour qui n'a qu'un marteau, tous les problèmes ont la forme d'un clou...

Excel n'est pas un gestionnaire de bases de données, et donc, Excel n'est pas adapté pour gérer des données...

Pour une gestion professionnelle des données, je recommande vivement de vous tourner vers des outils tels que Access, par exemple. Ces outils vous permettront de créer rapidement et facilement des tables pour contenir vos données, des formulaires de saisie pour entrer de nouvelles donnéees ou modifier des données existantes, des rapports pour imprimer des listes de données.

Les requêtes, les filtres et les tris vous permettront des manipulations aisées sur l'ensemble de vos données, ou sur certaines d'entre elles seulement.

Le présent tutoriel n'a pas pour ambition de permettre à Excel d'égaler les outils dédiés à la gestion des données, mais de vous permettre les manipulations basiques des données avec Excel.

II - Analyse des données et mise en place dans Excel

Le but de ce tutoriel n'est pas de réaliser une analyse approfondie d'une table de données. Il me semble toutefois intéressant de démarrer par une analyse rapide des données que l'on souhaite gérer avec Excel

Pour illustrer ce tutoriel, nous allons gérer des données liées à des contacts, ce qui nous permettra de rester très généraliste.

Nous allons stocker les données suivantes dans la feuille qui servira de table des contacts

- Identifiant: Code mnémonique défini par l'utilisateur

- Nom du contact

- Prénom du contact

- Localité: Choisie dans une liste déroulante

- Date de naissance

Pour illustrer ce cours, nous n'avons pas besoin de données supplémentaires.

II-A - Préparation de la feuille de stockage, la "table des contacts"

De façon assez classique, nous calerons les données dans le coin supérieur gauche de la feuille.

II-B - Règles pour une bonne gestion des données dans Excel

II-B-1 - Une seule table par entité

Bien qu'Excel possède 256 colonnes et 65 536 lignes (16 384 colonnes et 1 048 576 lignes pour Excel 2007), il faut garder à l'esprit la règle suivante:

Une feuille EXCEL PAR TABLE DE DONNEES

La seule dérogation éventuellement acceptable à mes yeux consiste à stocker sur la même feuille des petites tables annexes consistant en une seule colonne de données par table.

II-B-2 - Calage des données dans le coins supérieur gauche de la feuille

Les feuilles qui servent de table de données ne sont en général pas imprimées. On évitera donc les fioritures, les colonnes et lignes masquées pour améliorer la présentation, les lignes multiples d'entête et autres mises en page qui empêcheront une gestion correcte des données.

Nous prendrons donc soin de placer l'entête du premier champ en A1, et nous utiliserons une seule ligne, la première, pour placer les intitulés des colonnes, c'est-à-dire les noms des champs de la table.

Cette ligne pourra éventuellement être pourvue d'un filtre automatique

II-B-3 - Pas de cellule vide en ligne 1 entre la colonne A et la dernière colonne de la table

Cela revient à dire: Pas de solution de continuité entre les colonnes de la table. Cela veut dire aussi que chaque colonne possède un intitulé de champ.

On comprendra aisément que si des colonnes entièrement vides sont intercalées entre des colonnes de données, il sera malaisé de faire comprendre à Excel où se trouve le dernier champ de la table, et l'on risquera donc de "perdre" certaines colonne, c'est-à-dire de faire en sorte qu'Excel les considère comme étrangères à la table.

Le fait de ne pas avoir de colonne sans intitulé permet évidemment de connaître la nature du champ, permet à Excel d'utiliser ce nom de champ dans la gestion de la table (fonction de base de données, tableau croisé dynamique, filtre élaboré, ...)

II-B-4 - Pas de cellule vide en colonne A entre le premier et le dernier enregistrement de la table

A nouveau, pour permettre à Excel d'interpréter correctement les limites de la table, on s'assurera que chaque enregistrement possède au moins une donnée, stockée en A.

Dans certains cas, il arrive que l'on puisse déroger à la règle, notamment lorsque l'on souhaitera se servir de la feuille de stockage pour l'impression de rapports. Il sera alors parfois utile de pouvoir "orner" la feuille de lignes de titres, et donc de déroger à certaines des règles que j'expose ici. Dans ces cas-là, on veillera à ce qu'il n'y ait pas de cellules vides entre le premier et de dernier enregistrement de la table, et on s'arrangera pour définir la table de manière à ce qu'Excel comprenne les limites de la table.

II-B-5 - Clé primaire en colonne A

Pour les connaisseurs, la clé primaire est le champ qui permet de sélectionner sans équivoque un enregistrement au sein de la table. Il s'agit d'une donnée qui ne peut pas apparaître en doublon au sein de la table.

Il peut s'agir du numéro de registre national, d'une plaque d'immatriculation, d'un numéro d'ordre...

Excel n'étant pas un gestionnaire de données, c'est au développeur du classeur de mettre en place, éventuellement par VBA, un système de rejet ou à tout le moins de détection des doublons (format conditionnel, contrôle VBA à la saisie ou lors du transfert, fonction NB.SI, ...
Excel n'a nativement aucune fonction de contrôle d'intégrité référentielle, et il incombera également au développeur de mettre éventuellement un tel système en place (je lui souhaite bon courage...)

II-C - Préparation de la feuille de données

Après cet exposé un peu théorique et rébarbatif pour certains, passons à la pratique en créant la feuille qui servira de "table" pour les données.

N'oubliez pas de toujours nommer vos onglets. Il m'est déjà arrivé d'ouvrir des classeurs dans lesquels je trouvais une cinquantaine de feuilles nommées Feuil1, Feuil2, ..., Feuil50.

Sur base de la structure développée plus haut, voici comment nous créerons notre feuille de données

II-C-1 - Utilisation du formulaire Excel de saisie de données

Comme annoncé plus haut, nous pouvons utiliser un formulaire créé par Excel pour la saisie de nos données.

Après avoir activé une cellule d'entête de la feuille de données, allons sur le menu Données/Formulaire...

Ce qui donne parfois un beau message de la part de notre tableur préféré.

En substance, ce message nous indique qu'Excel a des doutes sur la plage qui va servir de données, et nous propose de choisir ce qui lui semble le plus adéquat.

Cliquons sur OUI et Excel va créer le formulaire de saisie selon les intitulés des champs de notre "table de données

Il suffit alors de remplir les champs

puis de cliquer sur pour ajouter de nouvelles données à la table. Excel repérera automatiquement la nouvelle ligne, videra les champs de saisie et vous pourrez continuer la saisie.

Comme vous l'aurez sans doute constaté, ce formulaire de saisie est assez rudimentaire. Il n'existe aucun moyen de contrôler la saisie des données avec un formulaire de ce type.

Ainsi, nous pouvons sans problème entrer une donnée erronée dans le champ de date de naissance, ce qui n'est pas souhaitable.

Nous voyons donc très vite les limites de ce formulaire, qui sera donc utilisé avec toutes les réserves d'usage.

II-D - Préparation du formulaire de saisie

Au vu des limites du formulaire créé par Excel, nous allons créer nous-même un formulaire de saisie qui utilisera une feuille de calcul.

Cette feuille sera préparée pour faciliter et canaliser la saisie des informations de notre contact, notamment grâce aux possibilités de validation des données dans les cellules.

Il est possible, avec Excel, de créer un formulaire de saisie à l'aspect professionnel avec un userform et de la programmation en VBA. Ces techniques dépassent le cadre de ce logiciel, et ne seront donc pas abordées.

Je suis loin d'être un expert en esthétique. Dès lors, le lecteur excusera la pauvreté artistique de mon formulaire de saisie. Je me contenterai de faire en sorte qu'il soit fonctionnel.

III - Saisie des données et transfert dans la table

IV - Récupération d'un enregistrement de la table

V - Annexe: Adaptation et "réécriture" du code généré par l'enregistreur

VI - Remerciements