LeCompagnon.info en reconstruction

VBA pour Excel

VBA pour Excel
Introduction

Sur cette page

Introduction
Connaissances préalables
Excel et la sécurité
Premier exemple
Second exemple
Troisième exemple
But et limites du tutoriel
Références


Concepts de programmation
VBE: l'éditeur VBA
Déclarations, types et références expressions et assignations
Tests et branchements
Les boucles
Gestion d'erreur
Collections et tableaux
Dialogues et formulaires
Objets et événements Excel
Conseils de programmation
Liste d'instructions

Introduction

Visual Basic for Application (VBA) est un environnement de programmation qui accompagne et permet d'automatiser la plupart des applications bureautiques de Microsoft.

On peut trouver une description générale du langage sur Wikipedia.

 

Accueil
Concepts de programmation
VBE l'éditeur VBA
Déclarations, types, références, expressions et assignations
Tests et branchements
Les boucles
Gestion d'erreur
Collections et tableaux
Dialogues et formulaires
Objets et événements Excel
Conseils de programmation
Liste d'instructions
Autres formations

Word 2003
Word 2007
Excel
Excel
PowerPoint 2003
PowerPoint 2007
Access
Access 2010
Internet
Général
Intelligence financière
Vidéos

Navigation
Précédent
Suivant
Page Principale
FAQ
Nouveautés
Plan du site
Références
Motivations
Manuels de formation
Fichiers de démonstration
Nous joindre
Par courriel

Abonnez-vous à
l'infolettre LCI
Partager


Abonnez-vous à l'infolettre LeCompagnon.info


 

Connaissances préalables

La compréhension de ce qui suit requiert une connaissance fonctionnelle de Excel.
Vous pourrez améliorer vous connaissances sur ce logiciels en lisant le contenu des sections sur Excel 2003 et Excel 2007.

Excel et la sécurité

Excel refuse souvent d'exécuter les programmes VBA (qu'il nomme aussi macro-commandes ou macros). À l'ouverture d'un classeur contenant du code VBA, l'avertissement suivant apparaît sous le ruban:

Appuyez sur le bouton Options...:

*Sélectionnez puis

Il est aussi possible que VBA soit complètement désactivé sur votre ordinateur.

Dans Excel 2007:

*Cliquez le bouton Office Office 2077 - Bouton Office.


*Cliquez le bouton Options Excel.

 

 

*Sélectionnez le Centre de gestion de la confidentialité.
*Cliquez le bouton Paramètres du Centre de gestion de la confidentialité.

*Sélectionnez l'option Paramètres des macros.
*Sélectionnez le choix Désactiver toutes les macros avec notification.
*Appuyez sur le bouton OK.

Vous devez quitter Excel pour que le changement à cette option soit activé.

Premier exemple: Identifier par une couleur les cellules déverrouillées de la feuille de travail active

Ce premier exemple illustre l'utilisation de VBA pour automatiser une tâche Excel qu'il serait fastidieuse de faire manuellement:
colorer en jaune chaque cellule déverrouillée.

Excel 2007 - menu contextuel

*Démarrez Excel avec un classeur vierge.
*Sélectionne le cellules A2, B1 et D1.

Gardez undoigt sur la touche Ctrl afin de pouvoir sélectionnez plusieurs cellules éloignées les unes des autres.

*Placez le pointeur par-dessus la cellule A2.
*Appuyez sur le bouton droit de la souris.
*De la liste des options du menu contextuel, sélectionnez l'option Format de cellule.

*Sélectionnez l'onglet Protection.
* Désactivez la case Verrouillée.
*Répétez pour les cellules B1 et D1.

 

*Ouvrez l'onglet développeur à droite:

S'il n'est pas affiché:

*Appuyez sur le bouton Office Office 2077 - Bouton Office.
*Appuyez sur le bouton Options Excel (en bas à droite).

*Dans le menu Standard, sélectionnez la 3e case à cocher: Afficher l'onglet Développeur dans le ruban.
*Appuyez sur le bouton Visual Basic.

*Du menu Insertion, sélectionnez module:
*Changez le nom du module dans la zone de texte (Name) en bas à gauche. RemplacezModule1 par un nom significatif ne contenant pas d'espace ApprentissageVBA par exemple.
Sub ArrièrePlan()
'Auteur: Michel Berthiaume
'Mettre en jaune les cellules non protégées de la feuille active

Dim rCellule As Range

For Each rCellule In ActiveSheet.Cells
    If Not rCellule.Locked Then
        rCellule.Interior.Color = vbYellow
    End If
    Application.StatusBar = "Traitement de la cellule " & rCellule.Address
Next
End Sub
 *Tapez (ou copiez-collez!) le code VBA suivant dans la zone de texte à droite.

Cela devrait donner le résultat ci-dessous.

Testez votre code:

*Placez le curseur sur la première ligne (SUB).
*Appuyez sur la touche F8.

La ligne se colore en jaune.

VBA exécute une ligne à chaque fois que vous enfoncez la touche F8, en répétant les instructions encadrées par For et Next
pour chaque cellule de la feuille Excel active.

Vérifiez les changements dans la feuille Excel. Remarquez que la barre d'état d'Excel indique l'adresse de la cellule traitée par VBA.

*Pour terminer l'exécution du programme, cliquez sur les boutons.

Continuer

Le programme s'exécutera pour chaque cellule. Si le sablier tarde à disparaître, arrêtez le programme en enfonçant ensembles les touches Ctrl-Pause/Arrêt (ou Break).

ou
Réinitialiser: le programme s'arrêtera.

ATTENTION: vous devez terminer l'exécution d'un programme VBA selon la procédure ci-dessus avant d'en exécuter un autre. En effet, si un programme est en débogage,  VBA est en pause.

Rendez le programme disponible en Excel.

*Retournez dans la feuille Excel, onglet développeur.
*Dans le ruban développeur, bloc Insérer Contrôle, sélectionnez le Contrôle de formulaire Bouton.

 

*Dessinez un bouton dans la feuille active.
*Sélectionnez ArrièrePlan dans la liste.

*Verrouillez quelques cellules jaunes, déverrouillez-en d'autres.
*Cliquez sur le bouton pour que les cellules déverrouillées se colorent en jaune.

Quelques commentaires sur ce  programme:

-Il illustre l'un des 3 types de programme qu'on peut réaliser avec VBA: une procédure SUB.
-Il contient plusieurs catégories d'instructions: -Il contient aussi une maladresse de programmation: une cellule jaune qui est verrouillée reste jaune après avoir été déverrouillée.

Second exemple: Compter le nombre de cellules d'une couleur donnée dans une plage donnée

Dans ce second exemple, on veut pouvoir écrire dans une cellule Excel la formule: =fnNbCellulesCouleur(A1:D3,D1) et ainsi afficher dans cette cellule le nombre de cellules de la plage A1:D3 dont la couleur est identique à la couleur de la cellule D1. Évidemment, on veut pouvoir remplacer A1:D3 et D1 par toute autre référence à une plage ou une cellule valide.

*Démarrez Excel avec un classeur vierge (ou utilisez le classeur créé pour l'exemple précédent).
* Colorez l'arrière-plan de quelques cellules.
* Démarrez l'éditeur VBA (voir l'exemple précédent).
* Créez un module ou utilisez un module existant.

Function fnNbCellulesCouleur(Plage As Range, Couleur As Range) As Long
'Auteur: Michel Berthiaume
'Compter le nombre de cellules d'une couleur donnée dans une plage donnée
'Plage: plage de cellules à inspecter
'Couleur: cellule de la couleur cherchée

Dim rCellule As Range

For Each rCellule In Plage
    If rCellule.Interior.Color = Couleur.Interior.Color Then
        fnNbCellulesCouleur = fnNbCellulesCouleur + 1
    End If
Next
End Function

Sub Test()
Dim t

    t = fnNbCellulesCouleur(Range("A1:D3"), Range("D1"))

End Sub

*Tapez (ou copiez-collez!) le code VBA suivant dans la zone de texte à droite.

Le résultat devrait être comme l'image ci-dessous.

 

VBA n'offre pas de moyen de tester directement une fonction. Procédons indirectement.

*Placez le curseur sur la ligne SUB TEST.

* Appuyez sur la touche F8. La ligne se colore en jaune.

VBA exécute une ligne à chaque fois que vous enfoncez la touche F8
D'abord la 3e ligne de SUB TEST.               
 

Puis en exécutant les lignes de la fonction.

*Appuyez sur la touche F8 pour exécuter
chaque ligne de la fonction.

*Pour terminer l'exécution de la fonction, cliquez sur les boutons.

Continuer: la fonction s'exécutera pour chaque cellule de la plage.
Si le sablier tarde à disparaître, arrêtez la fonction en enfonçant ensembles les touches Ctrl-Pause/Arrêt (ou Break).

OU

Réinitialiser: la fonction s'arrêtera.

ATTENTION:
Vous devez terminer l'exécution d'une fonction VBA selon la procédure ci-dessus avant d'exécuter un autre programme VBA. En effet, si un programme est en débogage,  VBA est en pause.

Utilisez la nouvelle fonction dans Excel.

*Dans une feuille du classeur Excel, colorez l'arrière plan de quelques cellules (A2, B1 et D1 par exemple).
*Dans une cellule de la feuille, inscrire la formule=fnNbCellulesCouleur(A1:D3,D1) en remplaçant A1:D3 par la référence à une plage qui contient les cellules à dénombrer,
et D1 par la référence à une cellule de la couleur voulue.  

Quelques commentaires sur le programme:

-Il illustre l'un des 3 types de programme qu'on peut réaliser avec VBA: une fonction personnalisée.
-Il contient plusieurs catégories d'instructions: