VBA Excel: exemples de programmes. Macros en Excel

Peu de personnes savent que la première version du produit Microsoft Excel populaire est apparue en 1985. Depuis lors, il a subi plusieurs modifications et est en demande parmi des millions d'utilisateurs à travers le monde. Dans le même temps, beaucoup travaillent avec seulement une petite partie des capacités de ce processeur de table et ne deviennent même pas à la hauteur de la façon dont ils pourraient rendre la vie plus facile dans les compétences de programmation Excel.

Qu'est-ce que VBA?

La programmation dans Excel s'effectue via le langage de programmation Visual Basic pour Application, qui est initialement intégré au processeur de table le plus célèbre de Microsoft.

À leur fond, les experts attribuent la facilité de développement comparative. Comme le montre la pratique, les utilisateurs de VBA peuvent maîtriser même ceux qui n'ont pas de compétences professionnelles en programmation. La particularité de VBA est l'exécution du script dans l'environnement des applications bureautiques.

Les inconvénients du programme sont les problèmes liés à la compatibilité des différentes versions. Ils sont dues au fait que le code du programme VBA se réfère à la fonctionnalité qui est présente dans la nouvelle version du produit, mais pas dans l'ancien. En outre, les inconvénients incluent trop d'ouverture du code pour le changement par une personne non autorisée. Néanmoins, Microsoft Office, ainsi que IBM Lotus Symphony, permettent à l'utilisateur d'utiliser le cryptage du code initial et de définir un mot de passe pour le visualiser.

Objets, collections, propriétés et méthodes

C'est avec ces concepts que vous devez comprendre qui va travailler dans l'environnement VBA. Tout d'abord, il faut comprendre quel est l'objet. Dans Excel, il s'agit d'une feuille, d'un livre, d'une cellule et d'une gamme. Ces objets ont une hiérarchie spéciale, c'est-à-dire Respectez-vous les uns les autres.

Le principal est Application, qui correspond à Excel lui-même. Ensuite, suivez les cahiers de travail, les feuilles de travail, et aussi la gamme. Par exemple, pour accéder à la cellule A1 sur une feuille particulière, vous devez spécifier un chemin qui inclut la hiérarchie.

En ce qui concerne le concept de "collection", il s'agit d'un groupe d'objets de la même classe que dans l'entrée ressemble à ChartObjects. Ses éléments individuels sont aussi des objets.

Le prochain concept est une propriété. Ils sont une caractéristique nécessaire de tout objet. Par exemple, pour Range, c'est Value ou Formula.

Les méthodes sont des commandes qui montrent ce qu'il faut faire. Lors de l'écriture de code dans VBA, vous devez les séparer de l'objet d'une période. Par exemple, comme cela apparaîtra plus tard, très souvent lors de la programmation dans Excel, utilisez la commande Cellules (1,1) .Sélectionnez. Cela signifie que vous devez sélectionner une cellule avec des coordonnées (1,1) c.-à-d. A1.

Avec cela, Selection.ClearContents est souvent utilisé. Effectuer cela signifie effacer le contenu de la cellule sélectionnée.

Comment commencer

Tout d'abord, vous devez créer un fichier et l'enregistrer en attribuant un nom et en sélectionnant le type «Excel workbook with macro support».

Ensuite, vous devez aller à l'application VB, utilisez simplement les touches "Alt" et "F11". Suivant:

• Dans la barre de menus située en haut de la fenêtre, cliquez sur l'icône à côté de l'icône Excel;
• Sélectionnez la commande Mudule;
• Enregistrer en cliquant sur l'icône avec la disquette d'image;
• Écrivez, disons, un croquis du code.

Cela ressemble à ceci:

Sous-programme ()

'Notre code

End Sub

Notez que la ligne "Notre code" sera mise en surbrillance dans une couleur différente (verte). La raison en est dans l'apostrophe au début de la ligne, ce qui indique qu'un commentaire suit.

Maintenant, vous pouvez écrire n'importe quel code et créer un nouvel outil pour vous-même dans VBA Excel (voir les exemples de programmes ci-dessous). Bien sûr, ceux qui connaissent bien les bases de Visual Basic seront beaucoup plus faciles. Cependant, même ceux qui ne les ont pas, s'ils le souhaitent, peuvent s'y habituer rapidement.

Macros en Excel

Pour ce nom, les programmes écrits dans Visual Basic pour la langue d'application sont cachés. Ainsi, la programmation en Excel est la création de macros avec le code souhaité. Grâce à cette fonctionnalité, le processeur de table de Microsoft s'auto-développe, s'adaptant aux exigences d'un utilisateur particulier. Après avoir compris comment créer des modules pour écrire des macros, vous pouvez commencer à regarder des exemples spécifiques de programmes VBA Excel. Il est préférable de commencer par les codes les plus basiques.

Exemple 1

Tâche: écrivez un programme qui copiera la valeur du contenu d'une cellule puis écris dans une autre.

Pour ce faire:

• Ouvrez l'onglet "Affichage";
• Aller à l'icône "Macros";
• Appuyez sur la touche "Enregistrer macro";
• Remplissez le formulaire ouvert.

Pour plus de simplicité, le champ "Nom de la macro" est défini avec "Macro1", et dans le champ "Raccourci clavier", par exemple, hh est inséré (cela signifie que vous pouvez lancer le programme en utilisant la commande "Ctrl + h"). Appuyez sur Entrée.

Maintenant que l'enregistrement macro a déjà démarré, copiez le contenu d'une cellule vers un autre. Revenez à l'icône d'origine. Cliquez sur "Enregistrer la macro". Cette action signifie la fin du programme.

Suivant:

• Encore une fois, passez à la ligne "Macros";
• Dans la liste, choisissez "Macro 1";
• Cliquez sur "Exécuter" (la même action démarre en démarrant le raccourci clavier "Ctrl + hh").

Par conséquent, une action a été effectuée lors de l'enregistrement de la macro.

Il est logique de voir à quoi ressemble le code. Pour ce faire, revenez à la ligne «Macros» et cliquez sur «Modifier» ou «Connexion». Par conséquent, ils se trouvent dans l'environnement VBA. En fait, le code de macro lui-même est situé entre les lignes Sub Macro1 () et End Sub.

Si la copie a été effectuée, par exemple, de la cellule A1 à la cellule C1, une des lignes de code ressemblera à la plage ("C1"). Sélectionnez. Dans la traduction, il ressemble à "Range (" C1 "). Sélectionnez", en d'autres termes, il passe à VBA Excel, dans la cellule C1.

La partie active du code est terminée par la commande ActiveSheet.Paste. Cela signifie enregistrer le contenu de la cellule sélectionnée (dans ce cas A1) dans la cellule C1 sélectionnée.

Exemple 2

Les cycles VBA vous aident à créer diverses macros dans Excel.

Les cycles VBA vous aident à créer différentes macros. Supposons qu'il y ait une fonction y = x + x ² + 3x ³ – cos (x). Vous devez créer une macro pour obtenir son graphique. Vous pouvez le faire uniquement à l'aide de boucles VBA.

Pour la valeur initiale et finale de l'argument de la fonction, prenez x1 = 0 et x2 = 10. En outre, vous devez entrer une valeur constante pour l'étape de modification de l'argument et la valeur initiale pour le compteur.

Tous les exemples de macros VBA Excel sont créés en utilisant la même procédure que ci-dessus. Dans ce cas particulier, le code ressemble à:

Sous-programme ()

X1 = 1

X2 = 10

Shag = 0,1

I = 1

Do While x1 <x2 (la boucle sera exécutée alors que l'expression x1 <x2 est vraie)

Y = x1 + x1 ^ 2 + 3 * x1 ^ 3 – Cos (x1)

Cellules (i, 1) .Value = x1 (la valeur x1 est écrite dans la cellule avec des coordonnées (i, 1))

Cellules (i, 2) .Value = y (la valeur de y est écrite dans une cellule avec des coordonnées (i, 2))

I = i + 1 (le compteur fonctionne);

X1 = x1 + shag (l'argument est modifié par la taille de l'étape);

Loop

End Sub.

À la suite de l'exécution de cette macro dans "Excel", nous obtenons deux colonnes, dont la première contient des valeurs pour x, et dans la seconde – pour y.

Ensuite, ils tracent le graphique d'une manière standard pour Excel.

Exemple 3

Pour implémenter les cycles dans VBA Excel 2010, comme dans d'autres versions, avec la construction Do Do déjà donnée, For est utilisé.

Considérons un programme qui crée une colonne. Dans chacune de ses cellules, les carrés du numéro de ligne correspondant seront enregistrés. L'utilisation de la construction For vous permettra de l'écrire très rapidement, sans utiliser de compteur.

D'abord, vous devez créer une macro, comme décrit ci-dessus. Ensuite, nous écrivons le code lui-même. Nous pensons que nous sommes intéressés par des valeurs pour 10 cellules. Le code ressemble à ceci.

Pour i = 1 à 10 Suivant

La commande est traduite en langage "humain", comme "Répéter de 1 à 10 en étapes de l'un".

Si la tâche est d'obtenir une colonne avec des carrés, par exemple, tous les nombres impairs allant de 1 à 11, nous écrivons:

Pour i = 1 à 10 étape 1 Suivant.

L'étape ici est une étape. Dans ce cas, il est égal à deux. Par défaut, l'absence de ce mot dans la boucle signifie que l'étape est unique.

Les résultats obtenus doivent être stockés dans des cellules avec le nombre (i, 1). Ensuite, chaque fois que le cycle démarre, en augmentant i par la taille, le nombre de la ligne augmentera automatiquement. Ainsi, le code sera optimisé.

En général, le code ressemblera à:

Sous-programme ()

Pour i = 1 à 10 Étape 1 (vous pouvez écrire simplement pour i = 1 à 10)

Cellules (i, 1) .Value = i ^ 2 (c'est-à-dire la valeur du carré i) est écrit en cellule (i, 1)

Ensuite (en un sens, il joue le rôle d'un compteur et signifie un début de cycle supplémentaire)

End Sub.

Si tout est fait correctement, y compris l'enregistrement et l'exécution de la macro (voir l'instruction ci-dessus), chaque fois qu'il est appelé, une colonne de la taille spécifiée (dans ce cas composé de 10 cellules) sera obtenue.

Exemple 4

Dans la vie quotidienne, il est très souvent nécessaire de prendre une décision en fonction de certaines conditions. Vous ne pouvez pas vous en occuper dans VBA Excel. Exemples de programmes dans lesquels la suite de l'exécution de l'algorithme est choisie, plutôt que prédéfinie initialement, utilise le plus souvent la construction If … Then (pour les cas complexes) si … Then … END If.

Considérons un cas concret. Supposons que vous devez créer une macro pour Excel, afin que la cellule avec les coordonnées (1,1) soit écrite:

1 si l'argument est positif;

0 si l'argument est nul;

-1 si l'argument est négatif.

La création d'une telle macro pour Excel est démarrée de manière standard en utilisant les touches "chaud" Alt et F11. Ensuite, le code suivant est écrit:

Sous-programme ()

X = Cellules (1, 1) .Value (cette commande attribue la valeur du contenu de la cellule avec des coordonnées (1, 1))

Si x> 0 puis Cellules (1, 1) .Value = 1

Si x = 0 puis Cellules (1, 1). Valeur = 0

Si x <0 puis Cellules (1, 1) .Value = -1

End Sub.

Il reste à exécuter la macro et à obtenir la valeur correcte pour l'argument dans Excel.

Fonctions VBA

Comme vous l'avez peut-être remarqué, la programmation dans le processeur de table le plus célèbre de Microsoft n'est pas si difficile. Surtout si vous apprenez à utiliser les fonctions VBA. En totalité dans ce langage de programmation, créé spécifiquement pour écrire des applications dans Excel et Word, il existe environ 160 fonctions. Ils peuvent être divisés en plusieurs grands groupes. Ce sont:

• Fonctions mathématiques. En les appliquant à l'argument, vous obtenez la valeur du cosinus, le logarithme naturel, la partie entière, etc.
• Fonctions financières. En raison de leur disponibilité et de l'utilisation de la programmation dans Excel, vous pouvez obtenir des outils efficaces pour la comptabilité et la comptabilité financière.
• Fonctions de traitement de tableau. Ceux-ci incluent Array, IsArray; LBound; UBound.
• Fonction VBA Excel pour une chaîne. C'est un groupe assez important. Il comprend, par exemple, les fonctions Space pour créer une chaîne avec un nombre d'espaces égal à un argument entier, ou Asc pour traduire des caractères en code ANSI. Tous sont largement utilisés et vous permettent de travailler avec des chaînes dans Excel, créant des applications qui facilitent grandement le travail avec ces tableaux.
• Fonctions de conversion de type de données. Par exemple, CVar renvoie la valeur de l'argument Expression, en le transformant en un type de données Variant.
• Fonctions de travail avec dates. Ils étendent considérablement les fonctionnalités standard d'Excel. Ainsi, la fonction WeekdayName renvoie le nom (plein ou partiel) du jour de la semaine par son numéro. Encore plus utile est Timer. Il donne le nombre de secondes qui passent de minuit au moment spécifique de la journée.
• Fonctions pour convertir un argument numérique en différents systèmes numériques. Par exemple, Oct émet une représentation octale d'un nombre.
• Fonctions de formatage. Le plus important est le format. Il renvoie une valeur de type Variant avec une expression formatée selon les instructions spécifiées dans la description du format.
• Et d'autres.

L'étude des propriétés de ces fonctions et de leur application élargira considérablement la portée de l'application d'Excel.

Exemple 5

Essayons de résoudre des problèmes plus compliqués. Par exemple:

Compte tenu d'un document papier du rapport sur le niveau réel des coûts de l'entreprise. Obligatoire:

• Développer sa partie de modèle au moyen d'un processeur de table "Excel";
• Créez un programme VBA qui interroge les données source pour le remplir, effectuez les calculs nécessaires et remplissez les cellules correspondantes du modèle.

Considérons l'une des variantes de la solution.

Création d'un modèle

Toutes les actions sont effectuées sur une feuille standard dans Excel. Cellules gratuites réservées pour entrer les données pour le mois, l'année, le nom de la société-consommateur, le montant des coûts, leur niveau, leur chiffre d'affaires. Étant donné que le nombre de sociétés (entreprises) à l'égard duquel le rapport est établi n'est pas résolu, les cellules pour faire des valeurs basées sur les résultats et le nom du spécialiste ne sont pas précédemment réservées. La fiche de travail reçoit un nouveau nom. Par exemple, "Օ tchet".

Variables

Pour écrire un programme pour remplir automatiquement le modèle, vous devez sélectionner les symboles. Ils seront utilisés pour les variables:

• NN-numéro de la ligne actuelle de la table;
• TP et TF – chiffre d'affaires planifié et réel;
• SF et SP – le montant réel et prévu des coûts;
• IP et IF – le niveau de coûts prévu et réel.

Notez par les mêmes lettres, mais avec le "préfixe" Itog l'accumulation du total pour cette colonne. Par exemple, ItogTP se réfère à la colonne d'un tableau intitulé «chiffre d'affaires prévu».

Résolution de problèmes en utilisant la programmation VBA

En utilisant la notation introduite, nous obtenons des formules pour les écarts. Si vous souhaitez calculer en%, vous avez (F – P) / P * 100, et dans la somme – (F – P).

Les résultats de ces calculs peuvent être immédiatement ajoutés aux cellules correspondantes de la table Excel.

Pour les résultats en fait et la prévision est obtenue par les formules ItogP = ItogP + P et ItogF = ItogF + F.

Pour les écarts, utiliser = (ItogF – ItogP) / ItogP * 100, si le calcul est en pourcentages, et dans le cas de la valeur totale – (ItogF – ItogP).

Les résultats sont immédiatement écrits dans les cellules correspondantes, donc il n'est pas nécessaire de les attribuer aux variables.

Avant d'exécuter le programme créé, vous devez enregistrer le classeur, par exemple, sous le nom "Report1.xls".

Le bouton "Créer un tableau de rapport" doit être pressé une seule fois après avoir entré les informations d'en-tête. Vous devriez connaître d'autres règles. En particulier, le bouton "Ajouter une ligne" doit être pressé chaque fois que vous entrez des valeurs pour chaque activité dans le tableau. Après avoir entré toutes les données, vous devez cliquer sur le bouton "Terminer", puis passer à la fenêtre Excel.

Maintenant que vous savez comment résoudre le problème pour Excel avec des macros. La possibilité d'utiliser Excel VBA (voir des exemples de programmes. Au-dessus) et vous pouvez avoir besoin de travailler dans l'environnement des plus populaires à l'heure actuelle, l'éditeur de texte « Word. » En particulier, l'enregistrement, comme indiqué au début de cet article, ou en écrivant du code pour créer des boutons de menu à travers lequel de nombreuses opérations sur le texte peut être fait en appuyant sur les touches en service ou par l'icône « Affichage » et onglet « Macros ».