Langage macro Visual Basic for Application (VBA) de Excel

Algorithmique de base

L’intérêt d’une macro est de pouvoir aller au-delà des opérations qui sont habituellement faites par le tableur. Il faut donc pouvoir écrire des macros manuellement et pas seulement se contenter des macros enregistrées automatiquement.

Affectation de valeurs aux variables

a) Affectation simple
Il s’agit de donner une valeur à une variable propre à la procédure. Le nom de la variable ne doit pas comprendre d’espaces (la variable « La valeur » doit être écrite « Lavaleur »). Ne pas utiliser de mot-clé (comme Sub, End, If, etc.)
Lavaleur = 10
Lasecondevaleur = Lavaleur * 12
b) Saisie d’une valeur au clavier par l’utilisateur La fonction est : InputBox(prompt, title, default).
Le Prompt est le texte questionnant l’utilisateur. Title est le titre de la boite de dialogue, Default est la valeur par … défaut.
La valeur renvoyée est toujours une chaîne de caractères. Pour la transformer en nombre utiliser Str. Le résultat peut être affecté à une variable ou traité directement. Ex:
réponse = InputBox (prompt:= »Quel nom ? », title:= »Enregistrement », default:= » »)
If InputBox (prompt:= »Quel nom ? », title:= »Enregistrement », default:= »Rien ») <> « Rien then

Editer un message

Pour tester des macros, informer l’utilisateur ou lui proposer des options sur la suite du programme
Msgbox « texte du message » renvoient des informations permettant ou de Msgbox Lavaleurcalculée Suivre le déroulement.
Msgbox « Poursuivre », vbYesNo… ou vbYesNoCancel Permet de tester la réponse choisie
Sub essai() Sub essai2()
Lavaleur = 10 Réponse = Msgbox (« Faut-il continuer », vbYesNoCancel)
Msgbox Lavaleur If Réponse = vbYes then
Lasecondevaleur = Lavaleur * 12 Else
Msgbox Lasecondevaleur If Réponse = vbNo Then
End Sub ….

Test

Fonction If

Les tests s’écrivent comme dans tout langage structuré à partir de If… Then … Else… End If
IF condition Then
action(s) 1
Else
action(s) 2
End If
Eventuellement sur une ligne si l’action est unique. La lisibilité est cependant moins bonne.
IF condition Then action 1 Else action 2
Les tests peuvent être imbriqués
IF condition1 Then
IF condition2 Then
action 1
Else
action 2
End If
Else
action 3
End If
Ex : saisie d’une note testée pour éviter qu’elle ne dépasse 20/20 ou ne soit inférieure à zéro :
Sub leTest()
Bureautique Cours VBA – Excel Jean VICTOR
Lanote = Str(Inputbox(Prompt:= »Quelle note ? », Title:= »Test », Default:=-1))
If Lanote > 20 Then
Msgbox « Erreur de saisie par excès »
Else
If Lanote < 0 Then
Msgbox « Erreur de saisie par excès »
Else
Msgbox « La note est correcte »
End If
End If
End Sub

Fonction Select Case

Cette fonction permet de décomposer un test multiple en autant de « cas » que nécessaire.
Sub selonlecas()
Select Case Résultat logique d’un test Réponse = Inputbox(« Entrez un nombre », »Test selon »,1)
Case premier cas Select Case Réponse
Action(s) … Case Is = 1
Case second cas Msgbox « le nombre est un »
Action(s) … Cas Is = 2
Case Else autres cas Msgbox « le nombre est deux »
Action(s) … Cas Else
End Select Msgbox « le nombre est supérieur à deux »
End Select
End Sub

L’opérateur Like

Il facilite la comparaison des chaînes de caractères. Le principe est de comparer la chaîne à une image. Ex:
If Réponse Like « *a* » Then
Si la réponse est Maîtrise alors la réponse est vraie car elle contient bien un « a ».
Les images sont composées de caractères précis et de signes génériques. Les principaux sont les suivants :
? remplace un caractère (ex : « UPX » comme « UP? » renvoie vrai)
* remplace toute chaîne de caractères (ex : »UPX » comme « *X » renvoie vrai)
# remplace un nombre quelconque (ex: « 1996 » comme « 199# » renvoie vrai)
[A-Z] liste auquel doit appartenir le caractère testé (ex: « U » comme [P-V] renvoie vrai)
[!A-Z] liste auquel ne doit pas appartenir le caractère testé (ex: « U » comme [!P-V] renvoie faux) Pour des exemples et des solutions plus complexes, consulter l’aide en ligne sur Like.

Répétition

La répétition contrôlée d’une action

Elle est basée sur la syntaxe suivante
For compteur = valeur initiale To valeur finale Step lepas ‘ le pas est optionnel actions
Next compteur ‘ le rappel du compteur est optionnel
Ex: Afficher une série de 10 nombres positifs
Sub Compteur1()
For i = 2 to 20 Step 2
Msgbox i
Next i
End Sub
Les boucles peuvent être imbriquées à condition de respecter l’ordre et la dénomination des compteurs.
Sub Compteur2() Série 1 Série 2 Série 3 Série 4 Série 5
‘Les résultats de ces deux boucles 20 16 12 8 4
‘se trouvent ci-contre 40 32 24 16 8
For i = 10 to 1 Step -2 60 48 36 24 12
For J = 2 to 20 Step 2 80 64 48 32 16
Msgbox i *j 100 80 60 40 20
Bureautique Cours VBA – Excel Jean VICTOR
Next j 120 96 72 48 24
Next i 140 112 84 56 28
End Sub 160 128 96 64 32
180 144 108 72 36
200 160 120 80 40

Répétition conditionnelle

Elle est basée sur l’algorithme While… Wend
Sub Boucleconditionnelle()
ActiveWorkbook.save ‘prudence
i = 2
While i =< 20 ‘<= ne fonctionne pas
Msgbox i
i = i + 2 ‘ Ici placé pour que la valeur 2 apparaisse
Wend
End Sub
Cette boucle est très utile lorsque le nombre des répétitions à effectuer n’est pas connu. Cependant, elle peut n’avoir aucune fin. Dans ce cas, il faut « planter » le programme pour l’arrêter… et le programme est perdu s’il n’a pas été sauvegardé auparavant. Vérifier la possibilité d’en sortir et ne pas oublier la première ligne de programme qui sauvegarde le programme avant de se lancer.

La boucle For Each

Le nombre des objets à travailler n’est pas toujours connu et il n’est pas toujours facile d’en connaître le nombre.
Recourir aux boucles conditionnelles suppose de savoir compter le nombre des objets en cause.
La boucle For Each permet de résoudre ces problèmes avec une grande facilité. Elle doit donc être privilégiée en permanence.
Sa structure est la suivante :
For Each nom_objet_considére_individuellement in collection_des_objets ‘Traitement de chacun des objets de la collection grâce au nom fixé par le programmeur Next
Par exemple pour saisir toutes les cellules d’une sélection. La collection est la Selection, chacune des cellules est appelée tour à tour cl. Tout autre nom admissible aurait pu être choisi.
For Each cl In Selection
cl.Value = Application.Trim(cl.Value)
‘supprime les espaces en utilisant la fonction SUPPRESPACE() de Excel désigné ici comme Application
Next
Ce programme peut donc fonctionner qu’il y ait une seule cellule dans la sélection ou toute une plage.
Autre exemple qui cache toutes les barres d’outils :
For Each cette_barre In Application.CommandBars
On Error Resume Next
cette_barre.Visible = False
Next
Et pour ne rendre visible que les barres les plus utiles
Application.CommandBars(« Standard »).Visible = True
Application.CommandBars(« Formatting »).Visible = True
Application.CommandBars(« Visual Basic »).Visible = True
Application.CommandBars(« Drawing »).Visible = True

Les mots-clés de Visual Basic

Les indications suivantes présentent les principaux mots-clés de Visual Basic. Pour des listes complètes et des indications sur la syntaxe, l’utilité, des exemples d’utilisation se reporter à l’aide en ligne. Un fichier Listevba.xls établit la correspondance entre les termes français (Excel 5) et la version anglaise (Excel 97). Cette liste n’est plus fournie avec les versions suivantes (me la demander en cas de besoin).
Il n’est pas possible de donner la correspondance entre tous les objets et leurs propriétés et toutes les méthodes qui peuvent leur être appliquées. Généralement, l’écriture est automatique puisque le logiciel apprend et transcrit les opérations à réaliser. Un même mot-clé peut désigner à la fois un objet, une propriété et une méthode ! Tout dépend du contexte dans lequel il est utilisé. Pour simplifier, les indications ci-dessous ne reprennent que les affectations principales.

Collections et Objets

Ensemble d’objets de même nature (pour distinguer les objets et les collections, ces dernières sont au pluriel).
Tous les objets peuvent ainsi être regroupés en collections. (Des collections personnelles peuvent être créées).
Les collections sont elles-mêmes des objets !

Chapitre 1 : Les macros
A) Notion de macro
B) Le langage macro Visual Basic for Application (VBA) de Excel
C) Ecriture automatique de macros simples
D) La structure d’une procédure macro
E) Les méthodes
F) Les propriétés
G) Le lancement de la macro
H) Arrêter une macro
I) Développer la macro par apprentissage
J) Affectation de valeurs ou de formules dans des cellules
K) Lecture des valeurs dans les feuilles de calcul
L) Les sous-programmes
M) Les fonctions
Chapitre 2 : Algorithmique de base
A) Affectation de valeurs aux variables
B) Editer un message
C) Test
D) Répétition
Chapitre 3 : Les mots-clés de Visual Basic
A) Collections et Objets
B) Méthodes
C) Propriétés
D) Opérateurs
E) Instructions de VBA
F) Fonctions
Chapitre 4 : Techniques avancées de programmation
A) La déclaration des variables
B) Les tableaux
C) Appel de procédures dans des procédures.
D) La gestion des erreurs
E) Les macros automatiques
F) Description des macros
G) Exemples de macro utiles