Microsoft Excel est déjà un outil d'analyse de données performant, mais avec la possibilité d'automatiser des tâches répétitives avec des macros en écrivant du code simple dans Visual Basic pour Applications (VBA), il est d'autant plus puissant. Utilisé de manière incorrecte, cependant, VBA peut causer des problèmes.
Même si vous n'êtes pas programmeur, VBA offre des fonctions simples qui vous permettent d'ajouter des fonctionnalités impressionnantes à vos feuilles de calcul.
Peu importe si vous êtes un gourou de VBA qui crée des tableaux de bord dans Excel ou un débutant qui écrit des scripts simples qui effectuent des calculs de cellule de base. Suivez ces techniques de programmation simples pour apprendre à écrire du code propre et sans bogue.
VBA peut faire toutes sortes de choses intéressantes pour vous. De l'écriture de macros qui modifient les feuilles à l'envoi d'e-mails à partir d'une feuille de calcul Excel à l'aide de scripts VBA, il y a très peu de limites à votre productivité.
Si vous n'avez pas encore programmé en VBA dans Excel, vous devrez activer les outils de développement dans votre programme Excel. La bonne nouvelle est que l'activation des outils de développement est en fait assez facile. Allez simplement dans Fichier> Options puis Personnaliser le ruban . Déplacez simplement le développeur onglet du volet de gauche vers la droite.
Assurez-vous que la case à cocher a cet onglet activé, et maintenant l'onglet Développeur apparaîtra dans votre menu Excel.
Le moyen le plus simple d'accéder à la fenêtre de l'éditeur de code à partir d'ici consiste simplement à cliquer sur Afficher le code bouton sous Commandes dans le menu Développeur.
Vous êtes maintenant prêt à écrire du code VBA ! Cet onglet est l'endroit où vous accéderez à VBA ainsi qu'à l'enregistrement de macros dans Excel. Maintenant qu'Excel est prêt à fonctionner, passons en revue certaines erreurs courantes à éviter et les moyens de les éviter.
Maintenant que vous êtes dans la fenêtre de code, il est temps de commencer à écrire du code VBA. La première étape importante dans la plupart des programmes, que ce soit en VBA ou dans tout autre langage, consiste à définir vos variables. Ne pas nommer correctement les variables est l'une des erreurs de programmation les plus courantes commises par les nouveaux développeurs.
Au cours de mes décennies d'écriture de code, j'ai rencontré de nombreuses écoles de pensée en ce qui concerne les conventions de nommage des variables et j'ai appris quelques règles à la dure. Voici quelques conseils rapides pour créer des noms de variables :
Voici un exemple de capture d'écran d'un programme que j'utilise souvent pour effectuer des appels WMIC Windows à partir d'Excel afin de recueillir des informations sur le PC.
Lorsque vous souhaitez utiliser les variables à l'intérieur d'une fonction dans le module ou l'objet (j'expliquerai cela ci-dessous), vous devez la déclarer en tant que variable "publique" en faisant précéder la déclaration de Public . Sinon, les variables sont déclarées en les faisant précéder du mot Dim .
Comme vous pouvez le voir, si la variable est un entier, elle est précédée de int . Si c'est une chaîne, alors str . Il s'agit d'une préférence personnelle qui vous aidera plus tard pendant la programmation, car vous saurez toujours quel type de données contient la variable en jetant un coup d'œil à son nom.
Assurez-vous également de nommer les feuilles dans votre classeur Excel.
De cette façon, lorsque vous faites référence au nom de la feuille dans votre code Excel VBA, vous faites référence à un nom qui a du sens. Dans l'exemple ci-dessus, j'ai une feuille dans laquelle je récupère les informations sur le réseau, j'appelle donc la feuille "Réseau". Chaque fois que je veux faire référence à la feuille Réseau, je peux le faire rapidement sans chercher de quel numéro de feuille il s'agit.
L'un des problèmes les plus courants rencontrés par les nouveaux programmeurs VBA lorsqu'ils commencent à écrire du code est de gérer correctement les boucles.
La boucle est très courante dans Excel car vous traitez souvent des valeurs de données sur une ligne ou une colonne entière, vous devez donc boucler pour toutes les traiter.
Les nouveaux programmeurs souhaitent souvent sortir d'une boucle (boucles VBA For ou VBA Do While) instantanément lorsqu'une certaine condition est vraie.
Voici un exemple de cette méthode utilisée pour rompre une boucle VBA.
Pour x =1 à 20
Si x =6 alors sortie pour
y =x + intRoomTemp
Ensuite je
Les nouveaux programmeurs adoptent cette approche parce que c'est facile. Essayez d'éviter de casser explicitement une boucle.
Le plus souvent, le code qui vient après cette "pause" est important à traiter. Une manière beaucoup plus propre et plus professionnelle de gérer les conditions dans lesquelles vous souhaitez laisser une boucle à mi-chemin consiste simplement à inclure cette condition de sortie dans quelque chose comme une instruction VBA While.
Tant que (x>=1 ET x<=20 ET x<>6)
Pour x =1 à 20
y =x + intRoomTemp
Ensuite je
Wend
Cela permet un flux logique de votre code. Maintenant, le code bouclera et s'arrêtera une fois qu'il atteindra 6. Inutile d'inclure des commandes EXIT ou BREAK gênantes au milieu de la boucle.
Une autre erreur intéressante que commettent les nouveaux programmeurs VBA est d'essayer de tout traiter à l'intérieur de nombreuses boucles imbriquées qui filtrent à travers les lignes et les colonnes pendant le processus de calcul.
Cela pourrait également entraîner des problèmes de performances majeurs. Faire une boucle dans une colonne et extraire les valeurs à chaque fois est un tueur sur votre processeur. Un moyen plus efficace de gérer de longues listes de nombres consiste à utiliser un tableau.
Si vous n'avez jamais utilisé de tableau auparavant, n'ayez pas peur. Imaginez un tableau comme un bac à glaçons avec un certain nombre de "cubes" dans lesquels vous pouvez mettre des informations. Les cubes sont numérotés de 1 à 12, et c'est ainsi que vous y "mettez" des données.
Vous pouvez facilement définir un tableau en tapant simplement Dim arrMyArray(12) as Integer .
Cela crée un "plateau" avec 12 emplacements disponibles que vous pouvez remplir.
Voici à quoi pourrait ressembler un code de bouclage de lignes sans tableau :
Sous Test1()
Dim x En tant qu'entier
intNumRows =Range("A2", Range("A2").End(xldown)).Rows.Count
Plage("A2").Sélectionnez
Pour x =1 à intNumRows
Si Range("A" &str(x)).value <100 alors
inTemp =(Plage("A" &str(x)).value) * 32 - 100
Fin si
ActiveCell.Décalage(1, 0).Sélectionnez
Suivant
Sous-titre de fin
Dans cet exemple, le code traite chaque cellule de la plage et effectue le calcul de la température.
Si jamais vous souhaitez effectuer un autre calcul sur ces mêmes valeurs, le processus serait maladroit. Vous devrez dupliquer ce code, traiter toutes ces cellules et effectuer votre nouveau calcul. Tout pour un changement !
Voici un meilleur exemple, utilisant un tableau. Commençons par créer le tableau.
Sous Test1()
Dim x En tant qu'entier
intNumRows =Range("A2", Range("A2").End(xldown)).Rows.Count
Plage("A2").Sélectionnez
Pour x =1 à intNumRows
tabMonTableau(x-1) =Plage("A" &chaîne(x)).valeur)
ActiveCell.Décalage(1, 0).Sélectionnez
Suivant
Sous-titre de fin
Le x-1 pour pointer vers l'élément du tableau n'est nécessaire que parce que la boucle For commence à 1. Les éléments du tableau doivent commencer à 0.
Maintenant que vous avez le tableau, il est très simple de traiter le contenu.
Sub TempCalc()
Pour x =0 Vers UBound(tabMonTableau)
tabMonTemps(y) =tabMonTableau(x) * 32 - 100
Suivant
Sous-titre de fin
Cet exemple parcourt tout le tableau de lignes (UBound vous donne le nombre de valeurs de données dans le tableau), effectue le calcul de la température, puis le place dans un autre tableau appelé arrMyTemps .
Que vous programmiez en Visual Basic ou VBA à part entière, vous devrez inclure des "références" pour accéder à certaines fonctionnalités.
Les références sont un peu comme des "bibliothèques" remplies de fonctionnalités que vous pouvez exploiter si vous activez ce fichier. Vous pouvez trouver des références dans la vue Développeur en cliquant sur Outils dans le menu puis en cliquant sur Références .
Vous trouverez dans cette fenêtre toutes les références actuellement sélectionnées pour votre projet VBA actuel.
Vous devriez vérifier cette liste car les références inutiles peuvent gaspiller les ressources système. Si vous n'utilisez aucune manipulation de fichier XML, pourquoi conserver Microsoft XML sélectionné ? Si vous ne communiquez pas avec une base de données, supprimez Microsoft DAO, etc.
Si vous n'êtes pas sûr de ce que font ces références sélectionnées, appuyez sur F2 et vous verrez l'Explorateur d'objets. En haut de cette fenêtre, vous pouvez choisir la bibliothèque de référence à parcourir.
Une fois sélectionné, vous verrez tous les objets et fonctions disponibles, sur lesquels vous pouvez cliquer pour en savoir plus.
Par exemple, lorsque je clique sur la bibliothèque DAO, il devient rapidement clair qu'il s'agit de se connecter et de communiquer avec des bases de données.
Réduire le nombre de références que vous utilisez dans votre projet de programmation relève du bon sens et contribuera à rendre votre application globale plus efficace.
L'idée même d'écrire du code dans Excel effraie beaucoup de gens, mais cette peur n'est vraiment pas nécessaire. Visual Basic pour Applications est un langage très simple à apprendre, et si vous suivez les pratiques courantes de base mentionnées ci-dessus, vous vous assurerez que votre code est propre, efficace et facile à comprendre.
Ne vous arrêtez pas là cependant. Construisez une base solide de compétences Excel avec un didacticiel VBA pour les débutants. Continuez ensuite à vous renseigner sur VBA et les macros avec des ressources pour vous aider à automatiser vos feuilles de calcul.