Exemples de créations avec Excel VBA

Vous trouverez ci-dessous des situations réelles pour lesquelles nous avons développé des solutions à partir d'Excel-VBA

Cette liste est non exhaustive et à pour but d'illustrer la diversité des champs d'application du "VBA" dans Excel.

Préambule et avertissement

Comme n'importe quel langage de programmation les possibilités d'application sont immenses et leurs limites sont essentiellement celles de votre capacité à organiser et structurer les informations.

La réussite de tout développement informatique est d'abord et avant tout dépendant d'une préparation rigoureuse pour définir les types d'informations requis, les fonctionnalités et surtout l'architecture du système, ce qui inclus la structure des tableaux, et des rapports...

Pour illustrer notre propos, nous pourrions vous demander de fermer les yeux et d'imaginer votre maison idéale avec un maximum de détails. Puis ouvrez les yeux et dessinez votre maison avec précision, sans rien omettre pour qu'elle puisse être construite... A moins que vous ne soyez vous-même architecte ou dans le domaine de la construction, vous constaterez qu'il y a un gouffre entre l'idée, l'idéal et sa réalisation même sous forme d'un dessin.

En informatique et dans le cadre de la programmation ce constat s'applique aussi...

Finance/Budget :

Création d'un système complet pour la gestion budgétaire d'une organisation internationale ayant des secteurs d'activités très différents.
Le système est entièrement construit à partir de formulaires Excel qui sont déployés en fonction des cas rencontrés dans chaque pays.
Les principales fonctionnalités qui déployées sont :

• Production de formulaires pré-remplis avec reprise des données historiques, de l'année en cours et des prévisionnels existants. Cette phase semi automatisée permet la création de l'ensemble des fichiers nécessaires au budget pour un pays donné à partir de documents standards par métier (ie: normes SIA pour la construction)
• Intégration d'un processus de contrôle qualité des données et de la consolidation permettant de vérifier la cohérence financière du budget à l'échelle locale puis globale
• Production d'un fichier "plat" (type .txt) pour l'export des informations à partir d'un pays vers dans une base de données centralisée permettant de suivre la progression du processus budgétaire en temps réel.
• Création automatique d'un identifiant unique par projet repris en comptabilité analytique, pour le reporting et le suivi des KPI de l'organisation

En moyenne un pays type représente entre 80 et 140 fichiers, contenant en moyenne 4-5 feuilles de calculs actives.

Grâce aux fonctionnalités d'import des données préexistantes, le travail de préparation à été réduit de plus de 60% dès la 2ème année. Les données récoltées se sont affinées au fil du temps amenant une plus grande précision budgétaire et une meilleure maîtrise des dépenses.

Par ailleurs ce système a permis de faire face aux imprévus et d'en mesurer les impacts à l'échelle du projet, du pays et de l'organisation. Cette approche, de manière plus indirecte facilite la vérification des factures fournisseurs et leur approbation, grâce à la structure du budget qui est reprise dans la comptabilité analytique de l'organisation.

A terme cette étape préliminaire à rendu possible la mise en place d'une solution globale de reporting ("web-based", sécurisée), intégrée à une infrastructure SharePoint, ce qui permet de suivre en temps réel toutes les activités menées au sein de l'organisation, y compris dans les pays.

Comptabilité

Vous trouverez ci-dessous quelques développements simples liés à des besoins en comptabilité et gestion des ressources humaines.

• Création d'une solution pour la gestion des salaires et des absences pour une fiduciaire genevoise. Avec des fiches de salaires modulaires, des synthèses mensuelles et annuelles pour l'employé, et l'employeur. Calcul mensuel détaillé des charges salariales pour les besoin de la comptabilité. Suivi du décompte des jours de vacances au niveau de l'employé et de l'employeur. Gestion des changements du mode de rémunération, départ et arrivées en cours d'années. Reprise des informations pour créer automatiquement une nouvelle année fiscale.

• Création d'un logiciel de comptabilité simplifié pour les besoins d'une organisation publique. Enregistrement simplifié des transactions. Production de quittances pour les "entrées" et les "sorties" de fonds. Assistance à la saisie des transactions pour des utilisateurs sans connaissances comptables. Production d'une synthèse de type "Pertes & Profits", calcul automatique du solde de chaque compte et global. Alimentation d'un rapport de synthèse global, et mise à disposition d'un Bilan simplifié pour faciliter la clôture annuelle. Mise à disposition d'indicateurs de performance en fonction des valeurs budgétées, avec mise à disposition d'un tableau croisé-dynamique. L'ensemble des mises à jour des informations consolidées se fait par un simple bouton.

• Création d'un logiciel de comptabilité simple pour MS-Office Mac destiné aux PME, ONG et aux particuliers. Mise à disposition de plans comptables prédéfinis en fonction des cas: Chaque plan comptable peut être modifié, adapté. La saisie des données se fait au moyen de 4 grands livres (2 pour la caisse, 2 pour la banque). Le système a été simplifié au maximum ce qui le rend très accessible: Les personnes enregistrent les "entrées" et les "sorties" d'argent. Des tableaux de synthèse permettent de suivre l'évolution de la situation si des montants pour le budget ont été renseignés. Un tableau croisé-dynamique est également inclus, ainsi que des rapports annuels/mensuels de type "pertes & profits", bilan. Les données sont automatiquement mises à jour par l'utilisateur au moyen d'un simple bouton et le solde des comptes est mis à jour après chaque saisie.

Construction

• Création d'un système de gestion des métrés et de suivi financier avec facturation pour un projet multi-zone, sur plusieurs années et cofinancé par près d'une dizaine de bailleurs. Le micro-logiciel a été conçu pour planifier les métrés, relever les métrés, calculer les contre-valeur financières. Il permet également de générer les factures intermédiaires pour les bailleurs sur la base des contrats établis avec chacun d'eux: Un bailleur peut financer plusieurs zones et/ou certaines activités. A titre indicatif ce système est composé de plus de 45 feuilles de calculs contenant chacune un tableau d'environ 800 lignes avec près de 140 colonnes... La consolidation des données est effectuées en moins de 2 minutes sur Excel.

• Création d'un système de planification de projets de type GANNT sur mesure pour répondre aux besoins métier d'un bureau d'architectes travaillant sur MS-Office pour Mac. Planification standardisée avec codes couleurs spécifiques, calcul des échéances de chaque étape clé et révision automatique de l'ensemble du projet en cas de changements de délais, retards. Vérification de la cohérence de la planification...

• Création d'un système de planification et de suivi financier d'un projet de construction d'immeubles pour des investisseurs privés. Suivi du des paiements fournisseurs, calcul des retenues de garantie, rapports d'activité mensuels, trimestriels et annuels. Système pour la simulation de scénarios d'amortissements et de prêts hypothécaires avec plusieurs bailleurs. Gestion et suivi des comptes courants du projet avec les relevés bancaires.

Tourisme

Reconstruction et expansion d'un système destiné à la création de voyages sur mesure haut de gamme, avec planification du voyage dans ses moindres détails: horaires des vols, trains, hébergement, descriptif des lieux visités, production des vouchers, coordonnées des guides et des contacts sur site, coordonnées d'urgences, gestion des données clients. Puis production d'un livre de voyage au format Word, qui est imprimé et transmis au client. Le processus initialement prenait entre 4 et 6 heures de travail pour établir une dossier simple avec environ 65% des informations requises pour le client. La version finale génère un document complet en moins de 2 heures avec 100% des données attendues. Le système permet également la reprise de données existantes, leur mise à jour sans risque d'erreur, ce qui n'était pas le cas avant (problèmes de copier/coller). L'agence peut désormais produire rapidement et efficacement plusieurs offres pour répondre aux attentes de leurs clients dans une même journée. Le système amène un gain d'efficacité significatif y compris pour les réservations de billets d'avion par une plus grande réactivité.

Sécurité au travail - toxicologie

Mesures et rapports de toxicologie

Création d'une solutions pour établir des mesures de polluants au sein d'entreprises actives dans le secteur de la chimie. Production de rapports détaillés individualisés ou groupés sur mesure. Cartographie des polluants identifiés et de leur concentration. Identification des pathogènes représentant un risque pour la santé.

Le système mis en place permet de réaliser des rapports complets et approfondis en moins de 2 minutes, alors qu'à l'origine cette opération essentiellement manuelle prenait au minimum 2 par dossier. Auquel il faut ajouter d'éventuelles erreurs de rédaction des rapports (copier/coller).

Le système mis en place augmente la précision des rapports et permet d'utiliser des formats prédéfinis qui n'étaient pas disponibles avant. La solution est construite plutôt comme un ensemble cohérent composés de plusieurs sous-systèmes autonomes, ce qui permet de la faire évoluer dans le temps pour répondre aux besoins du client.

Le choix d'une approche Excel-VBA a été motivé par notamment par 2 facteurs clés :

• L'absence de solutions professionnelles adaptées sur le marché et qui répond aux attentes du client.
• Le coût élevé et la durée d'un développement "classique" pour obtenir logiciel autonome.
• La contrainte de pouvoir modifier la solution rapidement en fonction des attentes du métier. L'entreprise étant confrontée à une complexification des attentes de ses clients et d'exigences croissantes dans le domaine de la sécurité au travail.

Évaluation et rapport de fonctionnement d'installation

Création d'une solution pour évaluer l'état de fonctionnement de chapelles utilisées dans le cadre de manipulations de produits chimiques, parfois hautement dangereux pour la santé. Création de rapports simples ou complets qui évaluent l'état d'une ou plusieurs chapelles selon des critères de sécurités prédéfinis. Ces documents sont produits en série au format pdf à partir de chablons. Chaque document doit en outre inclure une photo de la chapelle avec ses données techniques ce qui a nécessité la mise en place d'un protocole pour associer les photos aux chapelles et de les renommer en conséquence.

Le processus de développement est passé par plusieurs phases d'élaboration pour refléter les contraintes du clients sur les sites (sécurité, restrictions d'accès, pas de réseau wifi) ainsi que les attentes imprévues pour certains cas particuliers.

Les éléments clés qui ont favorisé le choix d'Excel-VBA sont le besoin de faire évoluer la solution ainsi que d'avoir une rapidité de traitement statistiques modulable. La mise en place de ce nouveau système a permis à l'entreprise de passer d'un mode essentiellement manuel combinant des fichiers Excel et Word, à une solution automatisée qui repose uniquement sur Excel. Par ailleurs la capacité de traitement est passée de minimum 2 heures pas dossier à moins d'une minute actuellement en plus de nouvelles fonctionnalités.

Santé publique

Création d'un système de gestion administratif dans le domaine du handicap, pour fluidifier et améliorer le traitement des dépôts de dossiers destinés à l'obtention d'un financement permettant d'accéder aux services de prise en charge pour personnes en situation de handicaps.

La solution mise en place fonctionne en deux temps, pour permettre le tri des dossiers reçus et la vérification de certaines informations pour qu'ils puissent être traités. L'une des contraintes métier dans ce secteur sous tension est la complexité des demandes aussi bien sur le plan administratif, que juridique et surtout humain.

Dans un deuxième la solution va permettre d'automatiser un maximum de tâches et d'opérations administratives jusqu' à la fin du processus de traitement des dossiers. Les fonctionnalités essentielles couvrent la préparation de dossiers pour leur mission à une commission d'experts, la prise des procès-verbaux et de leur décisions. La mise à jour des données des personnes concernées. Le suivi du dossier dans le temps depuis son admission afin de trier et gérer le flux des demandes mais aussi pour traiter les demandes particulières ou urgentes.

Toujours à partir d'Excel, le système alimente des statistique et des indicateurs de performances requis dans les rapports annuels d'activités. Des tableaux-croisés dynamiques sont inclus avec une mise à jour automatisée pour faciliter la recherche d'informations dans la base de données.

La mise en place de cette solution s'est fait en même temps qu'un travail de fond pour clarifier les processus métier, les bases juridiques, et les informations clé requises.

Cet exemple est intéressant car a première vue, rien ne semble compliqué, s'agissant de gérer de l'information administrative. Toutefois la modélisation des processus métier à révélé un environnement difficile à canaliser dès lors qu'il s'agit de traiter des situations délicates sur le plan humain. Dans ce cas le défi a été de proposer une solution qui met un cadre pour la gestion administrative des demandes sans tomber dans le piège de la rigidité face à la détresse humaine.

Conclusion

Soyez mesuré et pragmatique dans vos développements avec Excel ou le VBA. Privilégiez la modération, écrivez vous objectifs le plus précisément possible et tenez-les malgré les tentations d'ajouter des fonctionnalités ou d'autres tableaux. Si nécessaire faites vous une liste d'améliorations à prévoir mais pour plus tard...

Dans certaines situations, il n'y a pas d'autres choix que d'avancer par étape ou par couche, parce que la maîtrise des processus métier, ou leur documentation n'est pas disponible. Il faudra dans une telle situation faire preuve de retenue et n'avancer que lorsqu'un minimum d'éléments seront suffisamment clarifiés et précisés: quelles informations, quel format, quel rapport est attendu...

L'approche que nous recommandons pour gérer un projet de ce type est la méthode "AGILE " parce qu'elle s'appuie sur une planification adaptative, un développement évolutif, qui permet de tester et de valider les étapes clés du projet par les personnes concernées. Le risque de perdre de vue l'objectif est souvent mieux maîtrisé, mais l'approche sera plus longue dans son exécution.

Pour vous aider dans la phase préparatoire de votre futur développement sur Excel / Excel-VBA, et même s'il s'agit de créer un simple tableau Excel, nous vous proposerons un article qui met en avant un méthodologie simple pour créer vos documents, fichiers, de manière structurée et maîtrisée dans le temps.

Si vous avez hâte d'avancer avant notre prochain, pour :

• Être conseillé dans un développement à venir sur des fichiers Excel avec/sans VBA
• Vous former sur Excel VBA
• Être accompagné, coaché dans vos projets de développement sur Excel

Nous sommes là pour vous répondre, à tout moment :

Et si cela vous est possible, faites-le avant d'aller trop loin dans votre démarche, ce qui vous fera économiser des heures de travail...

Et pour terminer cet article, nous souhaitons partager 2 recommandations :

• Vous ferez des erreurs, encore et encore, mais à chaque fois vous aurez appris quelque chose qui vous servira plus tard
• La programmation ne s'acquiert que par la pratique, la théorie est une aide précieuse, mais elle n'a de valeur que si vous l'avez testé

Témoignage

"J'utilise MS-Office depuis 1998 et j'ai commencé à me servir d'Excel-VBA dès 1999.
Aujourd'hui encore je fais des "erreurs", alors je recommence et c'est ce qui me fait avancer.
Mes "erreurs" m'apprennent à faire autrement, à chercher des voies ou des codes plus simples, plus efficaces, plus rapides.
C'est ce qui me permet de créer pratiquement n'importe quelle solution en VBA pour répondre à un besoin métier particulier.
A ce jour j'estime que je connais et que je l'utilise au mieux 60% à 70% des possibilités d'Excel-VBA.
Il y a encore beaucoup à découvrir, donc je vais pouvoir m'améliorer, et dès lors, trouver de nouvelles idées pour mes clients"

A. Lavergnat

Pour aller plus loin : découvrez notre approche Excel stratégique — comment structurer vos outils Excel en micro-logiciels métier robustes et documentés.