4 erreurs à éviter lors de la programmation de macros Excel avec VBA

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 beaucoup plus puissant. Utilisé de manière incorrecte, cependant, VBA peut provoquer des problèmes.

Même si vous n’êtes pas programmeur, VBA propose 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.

Débuter avec VBA

VBA peut faire toutes sortes de choses intéressantes pour vous. De l’écriture de macros qui modifient des 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 programmé dans VBA dans Excel avant, 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 simple. Allez à Fichier > Les options puis Personnaliser le ruban. Déplacez simplement le Développeur onglet du volet gauche vers la droite.

Ruban de l'onglet Développeur Excel

Assurez-vous que la case à cocher a cet onglet activé, et maintenant l’onglet Développeur apparaîtra dans votre menu Excel.

Raccourci de l'onglet Développeur Excel

Le moyen le plus simple d’accéder à la fenêtre de l’éditeur de code à partir d’ici est de cliquer sur le Afficher le code bouton sous Contrôles 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, examinons certaines erreurs courantes à éviter et les moyens de les éviter.

1. Noms de variables horribles

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 dénomination des variables et j’ai appris à la dure quelques règles. Voici quelques conseils rapides pour créer des noms de variables:

  • Rendez-les aussi courts que possible.
  • Rendez-les aussi descriptifs que possible.
  • Préfacez-les avec le type de variable (booléen, entier, etc…).

Voici un exemple de capture d’écran d’un programme que j’utilise souvent pour passer des appels WMIC Windows à partir d’Excel pour recueillir des informations sur le PC.

Variables VBA Excel

Lorsque vous souhaitez utiliser les variables à l’intérieur d’une fonction dans le module ou l’objet (je vais l’expliquer ci-dessous), alors vous devez la déclarer comme variable «publique» en faisant précéder la déclaration de Publique. Sinon, les variables sont déclarées en les faisant précéder du mot Faible.

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 la variable contient en jetant un coup d’œil au nom.

En relation :  Comment créer un formulaire dans Microsoft Access

Veillez également à nommer les feuilles dans votre classeur Excel.

Nommer des feuilles Excel dans le code VBA

De cette façon, lorsque vous faites référence au nom de 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 où je récupère les informations réseau, donc j’appelle la feuille «Réseau». Chaque fois que je veux faire référence à la feuille Réseau, je peux le faire rapidement sans rechercher le numéro de la feuille.

2. Briser au lieu de boucler

L’un des problèmes les plus courants rencontrés par les nouveaux programmeurs VBA lorsqu’ils commencent à écrire du code concerne 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 effectuer une boucle pour toutes les traiter.

Les nouveaux programmeurs veulent souvent simplement sortir d’une boucle (boucles VBA For ou boucles VBA Do While) instantanément lorsqu’une certaine condition est remplie.

Instruction de sortie dans le code VBA Excel

Voici un exemple de cette méthode utilisée pour rompre une boucle VBA.

For x = 1 To 20
 If x = 6 Then Exit For
 y = x + intRoomTemp
Next i

Les nouveaux programmeurs adoptent cette approche car c’est facile. Essayez d’éviter de rompre explicitement une boucle.

Plus souvent qu’autrement, le code qui vient après cette «rupture» 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.

While (x>=1 AND x<=20 AND x<>6)
 For x = 1 To 20
 y = x + intRoomTemp
 Next i
Wend

Cela permet un flux logique de votre code. Maintenant, le code passera en boucle et s’arrêtera une fois qu’il aura atteint 6. Plus besoin d’inclure des commandes maladroites EXIT ou BREAK en milieu de boucle.

3. Ne pas utiliser de tableaux

Une autre erreur intéressante que font 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 pour 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 baie auparavant, n’ayez crainte. 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 «mettez» des données dedans.

Vous pouvez facilement définir un tableau simplement en tapant Dim arrMyArray (12) en tant que nombre entier.

Cela crée un «plateau» avec 12 emplacements disponibles pour que vous remplissiez.

Voici à quoi pourrait ressembler un code de bouclage de lignes sans tableau:

Sub Test1()
 Dim x As Integer
 intNumRows = Range("A2", Range("A2").End(xldown)).Rows.Count
 Range("A2").Select
 For x = 1 To intNumRows
 If Range("A" & str(x)).value < 100 then
 intTemp = (Range("A" & str(x)).value) * 32 - 100
 End If
 ActiveCell.Offset(1, 0).Select
 Next
End Sub

Dans cet exemple, le code est en cours de traitement dans chaque cellule de la plage et effectue le calcul de la température.

En relation :  3 formules Excel folles qui font des choses incroyables

Si jamais vous souhaitez effectuer un autre calcul sur ces mêmes valeurs, le processus serait maladroit. Vous devez dupliquer ce code, parcourir toutes ces cellules et effectuer votre nouveau calcul. Tout pour un changement!

Voici un meilleur exemple, en utilisant un tableau. Commençons par créer le tableau.

Sub Test1()
 Dim x As Integer
 intNumRows = Range("A2", Range("A2").End(xldown)).Rows.Count
 Range("A2").Select
 For x = 1 To intNumRows
 arrMyArray(x-1) = Range("A" & str(x)).value) 
 ActiveCell.Offset(1, 0).Select
 Next
 End Sub

le x-1 pour pointer vers l'élément de tableau n'est nécessaire que parce que la boucle For commence à 1. Les éléments de tableau doivent commencer à 0.

Maintenant que vous avez le tableau, il est très simple de traiter le contenu.

Sub TempCalc()
 For x = 0 To UBound(arrMyArray)
 arrMyTemps(y) = arrMyArray(x) * 32 - 100 
 Next
End Sub

Cet exemple parcourt l'intégralité du 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.

4. Utiliser trop de références

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 Les références.

Références Excel dans VBA

Vous trouverez dans cette fenêtre toutes les références actuellement sélectionnées pour votre projet VBA actuel.

Références VBA Excel

Vous devriez vérifier cette liste car des références inutiles peuvent gaspiller les ressources du système. Si vous n'utilisez aucune manipulation de fichier XML, pourquoi garder 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.

Explorateur d'objets VBA Excel

Une fois sélectionné, vous verrez tous les objets et les 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.

Gardez les références faibles dans VBA

Réduire le nombre de références que vous utilisez dans votre projet de programmation est tout simplement logique et contribuera à rendre votre application globale plus efficace.

Programmation dans Excel VBA

L'idée d'écrire du code dans Excel fait peur à 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.

Mais ne vous arrêtez pas là. 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.

Moyens Staff
Moyens I/O Staff vous a motivé, donner des conseils sur la technologie, le développement personnel, le style de vie et des stratégies qui vous aider.