Programmation VBA

Le langage Visual Basic For Applications a pour but de manipuler les objets des applications Microsoft Office (Excel, Word, Powerpoint).
On trouve donc dans ce langage :
    - des instructions clés et des fonctions, comme dans tout langage, qui permettent d'élaborer le code.
    - des objets et des méthodes applicables à des objets spécifiques à chaque application office ou bien communes (certaines méthodes d'une application peuvent exister mais porter un nom différent dans une autre).

Des instructions clés et des fonctions seront expliquées dans cette page, voilà également des choses que vous devrez apprendre à connaître (quelques unes sont aussi abordées dans cette page)

   - les variables sous VBA (voir plus bas) ; la création de compteur (aussi abordé) ; les variables tableaux
   - la programmation évènementielle (voir plus bas)
   - la gestion d'erreurs
   - l'existence de collection d'objets

Les variables sous VBA

Je laisse ici la main à une explication fournie pas Laurent L., qui jettera la lumière sur les problèmes de déclaration et d'utilisation de variables.
Message de L.L., objet: Re: Variable d'état de niveau module, date : vendredi 30 juin 2000 13:10

Une variable de niveau module est une variable déclarée au début du module, avant toute procédure ou fonction.
Elle s'oppose aux "variables locales" par sa portée (le module tout entier) et sa durée de vie ("illimitée"). Pour illustrer ceci, voici un petit exemple

Dim Toto As Boolean ' <= variable globale de niveau module

Sub Test
Dim Tata As Boolean ' <= variable locale à la procédure Test
End Sub

La variable Toto est accessible à partir de n'importe quelle procédure ou fonction du module, et éventuellement des autres modules et / ou d'autres classeurs si elle est déclarée à l'aide du mot-clé "Public". Elle conserve sa valeur tant que le classeur n'est pas fermé ni le projet VBA réinitialisé.

La variable Tata de la procédure Test n'est accessible qu'à l'intérieur de la procédure où elle est déclarée. Elle est "détruite" à la fin de l'exécution de cette procédure, à moins qu'elle soit déclarée à l'aide du mot-clé "Static".

Voici en résumé le comportement des variables

Variable de niveau module :
- déclarée avec Dim : portée module, durée de vie "illimitée"
- déclarée avec Public : portée classeur, durée de vie "illimitée"

Variable locale à une procédure :
- déclarée avec Dim : portée "procédure", détruite à la fin de celle-ci
- déclarée avec Static : portée procédure, durée de vie "illimitée"

Si une variable est destinée à conserver sa valeur entre chaque exécution de la macro, et / ou si elle doit être accessible par un grand nombre de procédures différentes, il faut la déclarer au niveau module, avec ou sans le mot-clé Public selon que l'on veut la partager ou non avec les autres modules du projet.

Quant à "variable d'état", ça désigne en général une variable booléenne ou entière destinée à mémoriser si une action particulière s'est produite, de manière par exemple à éviter d'exécuter une macro dans un contexte où elle déclencherait une erreur. Par nature, ce type de variable est souvent de niveau module.

Par exemple, la variable suivante permet d'indiquer à la procédure Workbook_Open si la fermeture du classeur est demandée par une autre procédure (AutoriserFermeture = True) ou par l'utilisateur (AutoriserFermeture = False par défaut).

Dans le module thisworkbook :
Public AutoriserFermeture As Boolean
private sub Workbook_BeforeClose(Cancel As Boolean)
Cancel = Not AutoriserFermeture
End Sub

Et ensuite, pour fermer le classeur par macro :
Sub FermerClasseur()
thisworkbook.AutoriserFermeture = True
thisworkbook.Close
End Sub

Cette "variable d'état" empêche la fermeture "manuelle" du classeur par l'utilisateur.

Comme autre type de "variable d'état", il y a par exemple les variables stockant l'état de certains contrôles des dialogsheets avant leur fermeture, de manière à orienter l'exécution ultérieure de la macro selon l'état de ces contrôles, etc.

Je reprends là main pour dire un ou deux mots :

Sous VBA, les variables ne sont pas Public par défaut, mais Private.
Seules les variables Public de modules standard peuvent être manipulées directement (en utilisant simplement leur nom) à partir des autres modules. Avec les modules objet, comme thisworkbook, il faut préfixer le nom de la variable par celui du module suivi d'un point.

Par exemple, dans le module thisworkbook :

Public Prénom As String
private sub Workbook_Open()
Prénom = "Toto"
Test
End Sub

Et dans un autre module quelconque (objet ou standard) :

Sub Test()
msgbox thisworkbook.Prénom
End Sub

Idem pour les UserForms, par exemple: pour manipuler la variable "Zaza" définie Public dans UserForm1, il faut la désigner dans les autres modules sous la forme "UserForm1.Zaza". Ce n'est qu'avec les variables de modules standard que ce n'est pas nécessaire.

Lorsqu'on déclare une variable avec Public, elle est vidée :
- Par l'instruction End
- Par la commande Exécution -> Réinitialiser dans l'éditeur VBA
- Lorsque la macro est interrompue par une erreur d'exécution et que l'on clique sur "Fin".

D'autre part, les variables publiques de modules de UserForms sont réinitialisées :
- Après que le UserForm ait été déchargé de la mémoire par l'instruction UnLoad
- Lorsque l'on désactive le UserForm en cliquant sur la croix de fermeture

Déclaration de variables : news & news
Autre message de news sur les déclarations de variables : news
Explication de la déclaration de variables BYREF : news, news

Programmation et instructions clés

Vous trouverez ici quelques exemples de fonctions et instructions clés utilisables en VBA. Ce sont des termes que vous employerez dans vos procédures pour une manipulation plus facile des données et des objets de votre application excel.

Tableau récapitulant des fonctions et instructions abordées :

Les termes de la première colonne sont des instructions pour :
   - incrémenter ou décrémenter une variable : pour une valeur i allant de 1 à 10, afficher la valeur de la cellule de la colonne A à la ligne i
   - boucler sur une collection d'objets : pour n'agir que sur un objet particulier, à savoir des cellules avec formules, des graphiques, des feuilles ...
   - construire des tests logiques : la variable est égale à XXX, l'objet est-il un graphique,

Ceux de la seconde colonne autorisent notamment à :
   - contrôler le type d'un objet : est-ce une feuille, une cellule, un classeur, un graphique ...
   - contrôler la présence d'un objet, d'une variable
   - extirper une information d'une chaîne de caractère : sa taille, la présence d'une chaîne dans cette chaîne
   - afficher un message et attendre une non une action de l'utilisateur

Les méthodes de la troisième colonne permettent notamment
   - l'affichage de messages
   - la temporisation
   - la simulation de l'activation de touches par l'utilisateur


Je n'expliquerai pas tous les arguments possibles de ces fonctions, mais si vous avez besoin d'éclaircissements, demandez-moi toujours.

Boucle Do .....Loop Until

On demande à excel d'effectuer certaines actions tant qu'une condition n'est pas ou est satisfaite.
Dans l'exemple ci-dessous, la procédure demande à excel de trouver les cellules qui contiennent le mot Zaza.

sub ChercheZazaDésespérément()
dim PremCell As String, Cell As range
Set Cell = cells.find("Zaza")
If Not Cell Is Nothing Then     'si au moins une occurence de Zaza a été trouvé, alors ...
PremCell = Cell.Address        'on garde en mémoire l'adresse de la première occurence
do                                          'debut de la boucle
      msgbox Cell.Address              'on affiche à l'écran l'adresse de l'occurence
      Set Cell = cells.findNext(Cell) 'on recherche l'occurence suivante
loop until Cell.Address = PremCell    'tant que la recherche de l'occurence suivante ne redonne pas à nouveau la première occurence, on boucle
end If
end sub

Autoriser l'interruption d'une boucle loop : news

Boucle Do While ... Loop

Le code ci-dessous permet de trouver la cellule non vide qui fait suite dans la colonne A. La recherche commence par A1
La procédure boucle pour contrôler le contenu des cellules de la colonne A & s'arrête si la cellule est pleine

sub CellulePleine
i=0:ange("A1").select 'initialisation de la variable i à 0 & sélection de A1
do while isempty(c.offset(i,0))
i=i+1
loop
msgbox "La cellule J" & i+1 & " n'est pas vide"
end sub

Boucle For

For sert à incrémenter ou décrémenter une variable selon une incrémentation ou décrémentation déterminé. Il existe là aussi une technique pour interrompre la boucle (exit for). Dans l'exemple ci-dessous, la boucle va de 5 à 20 avec un pas incrémenté de 3, lorsque i prend la valeur 17 la boucle est interrompue.

Sub boucle_for()
For i = 5 To 20 Step 3
If i = 17 Then MsgBox "I s'élève à 17" & Chr(13) & "la boucle est interrompue": Exit For Else MsgBox i
Next i
End Sub

Boucle For Each ... In ...

Autrement dit, pour chaque objet dans une collection d'objets => For Each Un_Objet In Un_Ensemble_dObjets
Avec cette boucle, on demande à excel d'exécuter des actions pour eu égard à chaque objet d'un certain type qu'il peut trouver.
Il existe de nombreuses collections d'objets sous excel. On peut boucler sur l'ensemble des pages, sur les cellules d'une plage de cellules, sur l'ensemble des graphiques/boutons/classeurs/sous-objets d'un groupe, sur chaque élément d'une variable tableau.
Comment écrire cette boucle => vous allez considérez une variable objet XYZ d'une collection de votre choix, agir dessus et passer au XYZ suivant.

Sub un_exemple
for each XYZ in range("A1:C10")
if XYZ.interior.color=vbred then SommeCellulesRouges=SommeCellulesRouges+XYZ.value
next XYZ
end sub

Instruction If

Vous pouvez construire une sorte de syllogisme, ce qui équivaut à : "Si Ceci Alors Cela, Sinon ..."

Un bloc conditionnel if peut s'écrire ainsi :

if range("A1").value=2 then
  msgbox ("A1 égal à 2")
else
msgbox ("A1 différent de 2")
end if

'Ou encore en une seule ligne (où le "End If" n'est pas nécessaire, ni le signe : en guise de séparateur.
if range("A1").value=2 then   msgbox ("A1 égal à 2") else msgbox ("A1 différent de 2")

Else permet de gérer le cas échéant d'un test if
De même pour ElseIf, il permet lui d'imbiruqer et de gérer plus facilement différents tests.
Ci-dessous une illustration d'utilisation de ElseIf (bien que l'exemple soit inutile)

Sub demo_if_elseif()
If Range("A1").Value > 100 Then
   MsgBox ("A1 > à 100")
   ElseIf Range("A1").Value > 50 Then
       MsgBox ("A1 > à 50")
   ElseIf Range("A1").Value > 20 Then
       MsgBox ("A1 > à 20")
End If
End Sub

La méthode Select Case est souvent bien plus pratique, elle est expliquée plus bas, ici, vous pouvez aussi être intéresser par la fonction Switch.

Instruction IIf

Cette méthode permet d'évaluer une expression, et de renvoyer une réponse cela que l'expression est vraie ou fausse.

MsgBox IIf([A1] = 2, "A1 égal à 2", "A1 de différent de 2")

Remarque de l'aide : la fonction IIf évalue toujours truepart et falsepart, même si elle ne renvoie qu'un seul de ces arguments. Vous devez donc être particulièrement attentif à certains effets secondaires indésirables de cette fonction. Par exemple, si l'évaluation de falsepart engendre une division par zéro, une erreur se produit même si la valeur de expr est True.

Voilà un exemples de macro utilisant iif.

Instruction Select Case

Elle permet de construire des syllogismes en sconsidérant d'une manière plus pratique les alternatives. L'exemple ci-dessous,, après avoir assigner des valeurs à des variables, fait un test sur leur valeur et en fonction de celle-ci, attribue une certaine valeur à la variable mystr

sub test_select() 'par S.H.
dim var1, var2, var3, myStr As String
var1 = 2 : var2 = 7 : var3 = 2 'var1, 2 et 3 ont ici des valeurs de test
Select Case var1
Case var1 = 1 To 3 And var2 > 1 And var2 < 7 And var3 = 1 : myStr = "var1=[1,3], var2=]1,7[, var3=1" 'si 1<var1<3 & 1<var2<7 ....
Case var1 = 1 ,2, 3 And var2 > 1 And var2 < 7 And var3 = 2 : myStr = "var1=[1,3], var2=]1,7[, var3=2" 'si var1=1 ou 2 ou 3 & 1<var 2<7 ...
Case var1 = 1 To 3 And var2 > 1 And var2 < 7 And var3 = 3 : myStr = "var1=[1,3], var2=]1,7[, var3=3"
Case Else : myStr = "zaza"
end Select
msgbox myStr
end sub

'le signe : permet un raccourci syntaxique, il évite le retour à la ligne

EXEMPLE RECAPUTILATIF

Cet exemple utilise l'instruction Exit pour quitter une boucle For...Next, il utilise une boucle do...loop et un select case.

Sub ExitStatementDemo()
Dim I, MyNum
do ' Définit une boucle infinie.
For I = 1 To 1000 ' Effectue la boucle 1000 fois.
MyNum = Int(Rnd * 1000) ' Génère des nombres aléatoires.
Select Case MyNum ' évalue le nombre aléatoire.
Case 7: Exit For ' Si le nombre est 7, quitte For...Next.
Case 29: Exit do ' Si le nombre est 29, quitte do...loop.
Case 54: Exit Sub ' Si le nombre est 54, quitte la procédure Sub.
End Select
Next I
loop
End Sub

Is

To be or not to be ....... Is permet de poser la question "est-ce que ?" [est-ce que c'est ?]

La cellule sélectionnée fait-elle partie d'une plage :
if Intersect(activecell, range("A1:C10")) Is Nothing then msgbox "Cellule Active hors A1:C10" 'si l'intersection n'est pas une réalite
if not intersect(activecell,plage) is nothing then   'si la non-intersection n'est pas une réalité

MyCheck = ThatObject Is ThisObject => on compare ici deux variables et le résultat de la comparaison est une valeur booléenne.
Si les deux objets sont différents, MyCheck a la valeur False/Fausse

Not

Not permet de poser la question "est-ce que ce n'est pas"

Soit A=10, B=8
MyCheck = Not(A > B) ' ceci renvoie False, puisque que l'affirmation "A n'est pas supérieur à B" est fausse

Like

Nothing

Cela sert à initialiser une variable objet, non réelle. (?)
Set MyObject = Nothing 'mon objet = rien du tout

Len

Len retourne le nombre de caractères d'une chaîne

La cellule a-t-elle un commentaire ?
dim HasComment As Boolean
HasComment = Len(Target.NoteText)
if HasComment = true then msgbox ("voici le commentaire" & Chr(13) & Target.NoteText) Else msgbox ("pas de commentaires")

LenB : Au lieu de renvoyer le nombre de caractères d'une chaîne, la fonction LenB renvoie le nombre d'octets utilisés pour représenter cette chaîne

IsMissing

Il est arrive que l'on est pas forcément besoin d'une information pour exécuter une procédure, dans quel cas on peut déclarer une variable comme optionnelle, Ismissing permet de savoir si la variable a été renseignée ou non =>L'utilisateur a-t-il renseigné un argument ? news

IsNull : Renvoie une valeur de type Boolean qui indique si une expression ne contient aucune donnée valide (Null).
IsDate : Renvoie une valeur de type Boolean qui indique si une expression peut être convertie en date
IsNumeric : Renvoie une valeur de type Boolean qui indique si une expression peut être interprétée comme un nombre.

Sub SelectionEnMajuscules()
For each mot In Selection
If Not IsNumeric(mot.value) And Not IsDate(mot.value) Then
mot.value = UCase(mot.value)
End If
Next
End Sub

IsObject : Renvoie une valeur de type Boolean qui indique si un identificateur représente une variable objet
La fonction IsObject renvoie la valeur True si l'argument identificateur est une variable de type Object ou d'un type de classe valide, ou si l'argument identificateur est une valeur de type Variant et de VarType vbObject (sous-type), ou un objet défini par l'utilisateur ; sinon, elle renvoie la valeur False. La fonction IsObject renvoie la valeur True même si la variable est de type Nothing.

Dim MyInt As Integer, YourObject, MyCheck Déclaration des variables.
Dim MyObject As Object
Set YourObject = MyObject ' Affectation d'une référence d'objet.
MyCheck = IsObject(YourObject) ' Renvoie True.
MyCheck = IsObject(MyInt) ' Renvoie False.

TypeName

Renvoie une valeur de type String qui fournit des informations sur une variable, à savoir son type.
Voir dans l'aide "Résumé des types de données". Cela peut être un donnée numérique, monétaire, un objet (bouton, feuille, graphique ...)

Afficher le type des valeurs dans la cellule à côté : news
L'exemple ci-dessous affiche le nom, le type de la sélection et son adresse si c'est une plage, seulement son type et son nom sinon.

If TypeName(Selection) = "range" Then
MsgBox "Type = " & TypeName(Selection) & "Name = " &
Selection.Address
Else
MsgBox "Type = " & TypeName(Selection) & "Name = " & Selection.Name
End If

L'objet vous l'aurez compris peut être un contrôle de formulaires.
Par exemple, pour lister sur la feuille active le nom de chaque contrôle et son type:

Dim Ctrl As MSForms.Control, I As Integer
Application.screenupdating = False
For each Ctrl In UserForm1.Controls
I = I + 1  : cells(I, 1) = Ctrl.Name  : cells(I, 2) = TypeName(Ctrl.Object)
Next Ctrl

TypeOf

MsgBox

Excel affiche un message dans une boîte de dialogue et attend que l'utilisateur clique sur un bouton, pour renvoyer une une valeur de type Integer qui indique le bouton choisi par l'utilisateur. En voilà la syntaxe : Syntaxe : MsgBox(prompt[, buttons] [, title] [, helpfile, context]).

Vous devez définir obligatoirement un message à afficher (1024 caractères maximum y compris retours à la ligne & retour chariots) pour cette boîte de dialogue. Vous pouvez définir optionnellement un titre, un fichier d'aide, un contexte, et le type de boutons que vous voulez employez.

La spécification du paramètre buttons offre plusieurs possibilités individuellement ou simultanément:

[

J'indique individuellement ou simultanément parceque la boîte de dialogue que vous crééz ne peut tenir compte que d'une des caractéristiques suivantes ou de toutes à la fois. Exemples :
   - MsgBox "Hello", vbExclamation + vbMsgBoxRight => le texte est aligné à droite et un point d'exclamation apparaît
   - MsgBox "Hello World",vbInformation => un icône information apparaît.
   - MsgBox "Hello World",,"Le titre" => on omet le paramètre buttons mais on indique tout de même une virgule comme s'il était présent.

Pour information, vous pouvez renseigner les paramètres en les nommant (nul besoin de respecter leur ordre) ou alors sans les nommer mais en respectant leur ordre. Exemples :
   - MsgBox prompt:="Hello", Buttons:=vbExclamation + vbMsgBoxRight
   - MsgBox "Hello World",vbInformation,"Le message"

]

Possiblité (1) donner une certaine valeur informative à la boîte de message à l'aide d'icônes apparaissant dans la boîte
-> pour cela donner une des valeurs chaîne suivantes (ou leur constante numérique associée) au paramètre buttons

vbCritical 16 -> icône Message critique.
vbQuestion 32 -> icône Requête d'avertissement.
vbExclamation 48-> icône Message d'avertissement.
vbInformation 64 -> icône Message d'information.

Possiblité (2) de caractériser l'interaction avec l'utilisateur, l'utilisateur comprendra en association avec la valeur informative et lemessage de la boîte quelles actions il peut entreprendre
-> pour cela donner une des valeurs chaîne suivantes (ou leur constante numérique associée) au paramètre buttons

vbOKOnly 0 -> bouton OK uniquement.
vbOKCancel 1 -> boutons OK et Annuler.
vbAbortRetryIgnore 2 -> boutons Abandonner, Réessayer et Ignorer.
vbYesNoCancel 3 -> boutons Oui, Non et Annuler.
vbYesNo 4 -> boutons Oui et Non.
vbRetryCancel 5 -> boutons Réessayer et Annuler.

vbMsgBoxHelpButton -> 16384 Ajoute le bouton Aide à la zone de message.

(3) de sélectionner le bouton par défaut, c'est-à-dire celui qui sera présélectionné (celui qui aura le focus) à l'affichage de la boîte.
-> pour cela donner une des valeurs chaîne suivantes (ou leur constante numérique associée) au paramètre buttons

vbDefaultButton1 0 -> le premier bouton est le bouton par défaut.
vbDefaultButton2 256 -> le deuxième bouton est le bouton par défaut.
vbDefaultButton3 512 -> le troisième bouton est le bouton par défaut.
vbDefaultButton4 768 -> le quatrième bouton est le bouton par défaut.

(4) de caractériser la boîte de message elle-même (dépend peut-être des versions)
-> pour cela donner une des valeurs chaîne suivantes (ou leur constante numérique associée) au paramètre buttons

vbApplicationModal 0 -> la boîte de dialogue est modale : l'utilisateur doit répondre au message affiché dans la zone de message avant de pouvoir continuer de travailler dans l'application en cours.

vbSystemModal 4096 -> Modal système. Toutes les applications sont interrompues jusqu'à ce que l'utilisateur réponde au message affiché dans la zone de message.

VbMsgBoxSetForeground 65536 -> indique la fenêtre de zone de message comme fenêtre de premier plan.

(5) spécifier l'alignement du texte (dépend peut-être des versions)
-> pour cela donner une des valeurs chaîne suivantes (ou leur constante numérique associée) au paramètre buttons

vbMsgBoxRight 524288 -> le texte est aligné à droite.
vbMsgBoxRtlReading 1048576 -> indique que le texte doit apparaître de droite à gauche sur les systèmes hébraïques et arabes.

Attention, on ne peut positionner ces boîtes de messages Excel (msgbox), ni même les colorier ou y incorporer des images.
C'est toutefois réalisable avec utilisant les libraires de fonction (cf par exemple ce site http://disciplus.simplex.free.fr).

Par ailleurs, il n'est pas possible de fermer ou de répondre automatiquement à ces boîtes de messages.
De nombreuses solutions existent toutefois ; des solutions mettent en oeuvre des formulaires.
Cf. fichiers.html pour vous inspirer
Cf. entre autre la partie sur les boîtes temporaires plus bas pour vous procurer des solutions prêtes à l'emploi.
(Pour la fermeture automatique, je vous déconseille les solutions utilisant a méthode SendKeys).

Exemples

Un exemple simple de msgbox

Sub demo_simple()
If MsgBox("Oui ou non", vbYesNo) = vbYes Then
  MsgBox ("vous avez cliqué sur oui")
Else
  MsgBox ("vous avez cliqué sur non ou annuler")
End If
End Sub

Lorsqu'on affiche simplement un message sans spécifier le paramètre Buttons autrement dit sans spécifier le type de cette boîte message, il n'est pas nécessaire de contrôler la réponse de l'utilisateur ou de la stocker dans une variable. Vous verrez dans l'exemple plus alambiqué comment stocker au préalable la réponse de l'utilisateur et contrôler ensuite quelle est-elle. L'exemple ci-dessous montre comment faire des boîtes de messages un peu plus stylisés.

Sub demo_un_peu_plus_stylise()
If MsgBox("Oui ou non", vbYesNo) = vbYes Then
x = MsgBox("Vous avez cliqué sur oui", vbOKOnly + vbExclamation, "Attention")
Else
 y = MsgBox("Vous avez cliqué sur non", vbOKOnly + vbInformation, "Process annulé")
End If
end sub

Un exemple plus alambiqué

Sub tester1()
res = MsgBox("Save or what?", vbYesNoCancel)
If res = vbYes Then
   MsgBox "You said yes"
ElseIf res = vbNo Then
   MsgBox "You said no"
ElseIf res = vbCancel Then
   MsgBox "you said cancel"
End If
End Sub

Sub le_meme_en_plus_simple()
Select Case MsgBox("Oui ou non", vbYesNoCancel)
Case vbYes: MsgBox ("vous avez cliqué sur oui")
Case vbNo: MsgBox ("vous avez cliqué sur non")
Case vbCancel: MsgBox ("vous avez annulé")
End Select
End Sub

Des instructions qui peuvent servir

Exemple de msgbox avec concaténation de données

L'exemple bidon ci-dessous déclare puis concatène des variables pour formuler la question qui sera posée à travers un msgbox ; il utilise aussi une constante vbcrlf (vbcarriagereturnlinefeed) pour effectuer un retour à la ligne dans ce mesage. Des boutons de type vbYesNo sont définis. La réponse à la boîte de dialogue est stockée dans la variable "LaReponse", la procédure vérifie si la réponse est oui (vbYes) ou non (vbno), si oui le nom et le prénom sont stockés dans la cellule C4.

 

Sub testMsgBox()
Dim prénom As String, nom As String, nombre% 'déclaration de variables chaînes de caractère et d'une variable integer (nombre%)
nom = "Saint": prénom = "Thomas": nombre = 1   'détermination de leur valeur    
If nombre > 0 Then                                              'tester si nombre est supérieur à 1
      LaReponse = msgbox(prénom & " " & nom & " a été en infraction à " & nombre & " occasion(s)." _
           & vbCrLf & "Voulez-vous imprimer une lettre d'avertissement? ", vbYesNo)
      If LaReponse = vbYes Then sheets("Feuil1").range("c4").value = nom &" " & prénom       'changement de la cellule C4 de la feuille "Feuil1"
End If
End Sub

'Vous pouvez faire un essai chez vous en remplaçant "Saint" & "Thomas" chacun par une syntaxe qui retournerait la valeur d'une cellule spécifique.

'Pour les sauts de ligne, regardez l'aide Excel sur les constantes vbcr, vblf, vbcrlf.

Autre exemple définissant dans des variables tous les paramètres attribués à la boîte de dialogue

Sub test2MsgBox()
Dim Msg, Style, Title, Help, Ctxt, Response, MyString
Msg = "Souhaitez-vous continuer?" ' Définit le message.
Style = vbYesNo + vbCritical + vbDefaultButton2 ' Définit les boutons.
Title = "Démonstration de MsgBox " ' Définit le titre.
Help = "DEMO.HLP" ' Définit le fichier d'aide.
Ctxt = 1000 ' Définit le contexte de la rubrique.
Response = MsgBox(Msg, Style, Title, Help, Ctxt) ' Affiche le message.
If Response = vbYes Then ' L'utilisateur a choisi Oui.
MyString = "Oui" ' Effectue une action.
Else ' L'utilisateur a choisi Non.
MyString = "Non" ' Effectue une action.
End If
end sub

Boîtes de dialogue (type msgbox) temporaires

Il n'existe aucune boîte de dialogue de ce type sous excel, il faut passer par de la programmation VBA.

Il faut pour utiliser la méthode ci-dessous, cocher la référence à Windows Script Hosting (à travers la boîte de dialogue Références accessible sous VBE dans le menu Outils).

De: Jim Rech <jarech@kpmg.com>
Objet: Re: Code to close MsgBox?
Date : mercredi 20 septembre 2000 19:43
If you have the Windows Scripting Host Obj model installed (WSHOM.OCX)

Sub SelfClosingMsgBox()
CreateObject("WScript.Shell").Popup "Hello", 2, "This closes itself in 2 seconds"
End Sub

Inputbox

Attention il existe une FONCTION InputBox & une METHODE InputBox

La fonction InputBox

Elle affiche une invite dans une boîte de dialogue, puis attend que l'utilisateur tape du texte ou clique sur un bouton, suite à quoi le contenu de la zone de texte est renvoyé sous la forme d'une valeur de type String. Syntaxe : InputBox(prompt[, title] [, default] [, xpos] [, ypos] [, helpfile, context])

Cette boîte de dialogue est positionnable grâce à ces arguments xpos & ypos.
Attention tout de même on ne peut ni la colorier, ni y incorporer des images, ni la fermer ou y répondre automatiquement (pour ces deux derniers points, si vous y tenez vraiment, vous pouvez le faire à l'aide de la méthode SendKeys).

Les paramètres importants
  - prompt : une chaîne texte décrivant ce que doit saisir l'utilisateur dans l'invite de texte (peut aller jusqu'à 1024 caractères en comprenant les retours à la ligne ou retours chariot)
   - default : vous pouvez indiquer une valeur par défaut dans l'invite de texte
   - xpos & ypos : cette boîte est positionnable, à vous de faire jouer ces paramètres. Pour la positionner par rapport à d'autres objets d'excel, récupérez la hauteur et la position gauche de ceux-ci avec leur propriété Top & Left.

Consultez également mes informations sur la boîte de dialogue MsgBox.

Exemple de la fonction InputBox

Dim Message, Title, Default, MyValue
' Définit le message.
Message = "Entrez une valeur comprise entre 1 et 3"
Title = "Démonstration de InputBox" ' Définit le titre.
Default = "1" ' Définition la valeur par défaut.
' Affiche le message, le titre et la valeur par défaut.
MyValue = InputBox(Message, Title, Default)

' Utilise le fichier d'aide et le contexte.
' Le bouton Aide est ajouté automatiquement.
MyValue = InputBox(Message, Title, , , , "DEMO.HLP", 10)

' Affiche la boîte de dialogue sur la position 100, 100.
MyValue = InputBox(Message, Title, Default, 100, 100)

La méthode InputBox

Syntaxe : expression.InputBox(Prompt, Title, Default, Left, Top, HelpFile, HelpContextId, Type)

Tout comme la fonction du même nom, la méthode InputBox affiche une boîte de dialogue dans laquelle l'utilisateur peut saisir des données, mais elle est différente de cette dernière car elle permet de valider de façon sélective les entrées de l'utilisateur et peut aussi être utilisée avec des objets, des valeurs d'erreur et des formules de Microsoft Excel.

En effet, son argument Type permet de spécifier le type des données renvoyées (le type de réponse qui doit être apportée par l'utilisateur).
Si vous ne spécifiez pas cet argument, la boîte de dialogue renvoie du texte
Le type peut avoir une ou la somme des valeurs suivantes.

Valeur Signification
0

Une formule.
1 Un nombre.
2 Texte (une chaîne).
4 Une valeur logique (True ou False).
8 Une référence de cellule, sous la forme d'un objet Range.
16 Une valeur d'erreur, telle que #N/A.
64 Un tableau de valeurs.

Remarquez que l'instruction Application.InputBox appelle la méthode InputBox, InputBox sans qualificateur d'objet appelle la fonction InputBox

Exemples de la méthode InputBox

La macro ci-dessous utilise un inputbox de type refedit (type 8), pour que l'utilisateur puisse sélectionner une plage de cellules. Cette macro cherche les cellules non vides dans une plage et compte leur nombre. Si vous l'utilisez, spécifier une petite plage de cellules.

Sub Temporary()
Dim i As Integer, Counter As Integer, Myrange As range, X As range : Counter = 0
Set Myrange = Application.InputBox(prompt:="Select a range to fit to", Type:=8 + 64)
For each X In Myrange
If Not (IsEmpty(X)) Then Counter = Counter + 1: MsgBox X.value
Next X
MsgBox "Counter = " & Counter
End Sub

InputBox & Clic sur OK ou Annuler ?

Autement dit, est-il possible de savoir dans un InputBox, comme dans un msgbox genre vbOK/ vbCancel (ou autrement genre qui a le focus), si on a cliqué sur OK ou sur Annuler, indépendamment du retour même de l'Input (chaîne vide si Annuler, contenu tapé si OK).

Temporisation Méthode OnTime & Wait

Quelqu'un sait il comment introduire dans une subroutine une temporisation rapide ?

Discussion "temporisation d'une macro durée inférieure a la seconde", Novembre 1999. Réponse le 24/11 par D.H.

Méthode Wait :
---------------------
Marque une pause dans l'exécution de la macro jusqu'à une heure spécifiée.

Important La méthode Wait suspend toute activité de Microsoft Excel et peut vous empêcher d'accomplir toute autre opération sur votre ordinateur pendant la durée de la pause. Seules les tâches de fond telles que l'impression ou le recalcul se poursuivent.

Méthode OnTime :
--------------------------
Cette méthode programme l'exécution d'une procédure à un moment précis (soit à une heure précise soit après l'écoulement d'un délai précis).

Fonction doEvents :
-----------------------------
Arrête momentanément l'exécution afin que le système d'exploitation puisse traiter d'autres événements.

Méthode Wait & DoEvents

Pour la première méthode, regardez l'aide Excel

Pour DoEvents, placez là avant certaines actions pour lequel le système de votre PC requiert du temps, afin qu'Excel patiente jusqu'à ce qu'il en ait finit.
La main est donnée au système.

Méthode OnTime

La méthode Timer de Visual Basic n'existe pas, on utilise ici l'instruction OnTime.

Comment déclencher la procédure "MaMacro" dans dix secondes ? Application.OnTime Now + TimeSerial(0,0,10), "myMacro"

Comment déclencher la macro zaza à 16h ?

Cet exemple nécessite que vous copiez la macro Workbook_Open dans la feuille de code "thisworkbook" de votre classeur et que vous l'activiez, copiez le reste du code dans un module standard. Assurez-vous aussi que votre classeur contienne une fueille appelée "feuil1"Option Explicit

private sub Workbook_Open()
Application.OnTime "16:00:00", "zaza"
Call zaza
End Sub

Sub zaza()
Dim c As range
c = thisworkbook.sheets("feuil1").cells(1, 1)
c.value = c.value + 10
End Sub
' -> à 16h la procédire zaza change la valeur de A1 en lui ajoutant 10.

Annuler une macro OnTime : news, news (réponse toute simple)

Autres timers

• Pour faire une temporisation d'environ une demi-seconde

  Dim T As Single
  T = Timer
  do: loop While Timer - T < 0.5

• Public Sub InsertDelay()

  MsgBox "Press OK to start timer."
  Pausetime = 5 ' Set duration.
  Start = Timer ' Set start time.
  
  do While Timer < Start + Pausetime
  doEvents ' Yield to other processes.
  loop

  MsgBox Pausetime & " seconds have elapsed."
  End Sub

• private sub CommandButton1_Click()
  Dim PauseTime, Start, Finish, TotalTime
  If (MsgBox("Press Yes to pause for 5 seconds", 4)) = vbYes Then
  PauseTime = 5 ' Set duration.
  Start = Timer ' Set start time.
  do While Timer < Start + PauseTime
  doEvents ' Yield to other processes.
  loop
  Finish = Timer ' Set end time.
  TotalTime = Finish - Start ' Calculate total time.
  MsgBox "Paused for " & TotalTime & " seconds"
  Else
  End
  End If
  
• 'Un exemple
  private sub MyTimer(lag As Integer)
  dim Starttime as long, elapse as long
  Starttime = timer * 100
  elapsed = 0
  do While elapsed < lag
  elapsed = (timer * 100) - Starttime
  loop
  
• 'Un autre
  Sub Report()
  Dim sngStart As Single
  sngStart = Timer
  For i = 1 To 100
  Application.StatusBar = i & " " & Timer - sngStart & " secs"
  For k = 1 To 100000
  j = j + k
  Next
  doEvents
  Next
  Application.StatusBar = False
  End Sub
  'ce code inscrit à des moments précis la progression de la macro
  

Suspendre une application :

Declare Sub Sleep Lib "kernel32" (byval dwMilliseconds As long)
Sub test()
Sleep 1000
End Sub

Sendkeys

Cette méthode sert à simuler l'activation de touche par l'utilisateur. Toutes les touches et caractères peuvent être communiquées à l'application excel. L'aide est bien documentée à ce sujet (taper sendkeys dans une feuille de code, positionner le cursuer dessus & appuyer sur F1)

Cela pourrait par exemple aider à valider une boîte de dialogue : Application.SendKeys ("{ENTER}") : dialogsheets("Dialog1").show

Regarder aussi ma page xl_raccourci.htm

Onkey

Cette méthode sert à affecter une macro à un raccourci ou à ne rien lui affecter : application.onkey "^c", ""
Pour restaurer ce raccourci qui correspond à la commande copier, il suffit d'écrire : application.onkey "^c"

Beep

Il existe sur certaines (ou toutes ?) versions de VBA une commande Beep :

dim I
for I = 1 to 3 ' Fait 3 boucles.
beep ' émet une note.
next I

Sinon, il faut faire appel à une librairie de fonctions windows :

declare function MessageBeep Lib "user32" (byval wType As long) As long

sub Beep2()
MessageBeep &H40&
end sub

Création de compteurs

(1) Dans une procédure, vous pouvez utiliser des variables et en incrémenter ou en décrémenter leur valeur manuellement ou à l'aide de boucle.

Exemple 1

sub demo_compteur_incrementation_simple
'initialiser à 1 la variable integer_Cpt, puis en inscrire la valeur dans la colonne A à la ligne n° int_Cpt
int_Cpt=1 : activesheet.range("a" & int_Cpt).value=int_Cpt

'incrémentation de int_Cpt, et inscription de sa nouvelle valeur à la ligne n° int_Cpt, soit à présent à la ligne 6
int_Cpt=int_Cpt+5:activesheet.range("a" & int_Cpt).value=int_Cpt
end sub

Exemple 2

sub demo_compteur_incrementation_boucle
'initialisation de la boucle qui va incrémenter la variable int_Cpt de 2 à chaque tour de boucle, en partant de 1 jusqu'à 15
for int_Cpt=1 to 15 step 2

'ainsi l'instruction ci-dessous inscrira 1 dans A1, 3 dans A2, 5 dans A3, 7 dans A3 .....
activesheet.range("a" & int_Cpt).value=int_Cpt

'une autre variable int_autCpt autre variable sera incrémenté de 1 à chaque boucle pour calculer en fait le nombre de tours
int_autCpt=int_autCpt+1 : activesheet.range("b1").value="Il y a eu " & int_autCpt & " tours jusqu'à présent"

next int_Cpt
end sub

(2) Comment créer un compteur : dans un fichier : news, dans un fichier ini : news

(3) Comment installer un compteur qui attribuerait un unique numero à chaque ouverture d'une feuille un peu comme le modèle de facture Microsoft Excel :
         - un truc ne répondant pas à 100% mais intéressant car appelant les modules de classe : news
         - un exemple incrémentant un numéro de facture stocké dans un nom caché à chaque ouverture d'un classeur modèle (par L.L.) : news
         - Facture pour les nuls ;-))) : news
         - un autre exemple intéressant par Frédéric S. : news

         - un tel compteur le plus simple possible

Private Sub Workbook_Open()
Range("numFact") = Range("Numéro") + 1
End Sub

(Autre sujet) : le dénombrement dans des feuilles de calcul, voir là

Gestion d'erreurs

Si vous vous intéressez à :
- la gestion d'erreurs dans les formules, voyez là : xl_fonction.htm
- la personnalisation des erreurs à travers la commande Validation du menu Données, voyez là : donnees.html

- la gestion des erreurs pouvant survenir lors de l'exécution d'un programme VBA alors lisez ce qui suit.

Gérer les erreurs c'est les intercepter dans la mesure du possible et réagir en conséquence.
L'instruction On Error GoTo permet cette gestion.

Explication provenant de http://altern.org/peltierm/welcome.htm (ce site n'existe plus)

Sub essai()
On Error GoTo <étiquette>
...
<étiquette>:
'traitement de l'erreur
End Sub

L'instruction On Error GoTo 0 permet à tout moment d'invalider le contrôle des erreurs par la séquence On Error GoTo. Pour traiter correctement l'erreur, il est possible d'utiliser la propriété Number de l'objet Err.
Celle-ci renvoie le numéro d'erreur . Enfin, l'instruction Resume permet de reprendre le cours normal du programme en ignorant la cause de l'erreur et la commande On Error Resume Next permet d'ignorer systématiquement les erreurs, en sautant les instructions correspondantes.
Voici une illustration du mécanisme de traitement des erreurs :

Sub essai()
Dim i As Integer

On Error GoTo erreur
'ouverture du fichier 'classeur1.xls'
Workbooks.Open ("classeur1.xls")
...
erreur:
'message d'information
i = MsgBox("Impossible d'ouvrir le fichier 'classeur1.xls'!", vbCritical)
'reprise du programme
Resume
End Sub

Quand s'en servir ?
    - news

Comment l'éviter ?
    - on peut gérer les cas d'erreurs en les empêchant d'advenir par des contrôles supplémentaires sur des variables, sur l'existence d'objets,
==> un exemple par Frédéric S. : news.
==> Voir d'ailleurs plus haut d'autres exemples de l'emploi du mot clé Nothing et des exemples de boucles sur des collections d'objet

Où s'est-elle produit, à quelle ligne ? : news ( et la numérotation des lignes : news)

Une erreur, oui laquelle précisément ? :
    - vous pouvez afficher le numéro de l'erreur et la description qui lui est attribuée.
    sub demo_erreur
    on error goto gest_erreur
    ...
    if err then
      msgbox "Numero de l'erreur " & err.number & vbcr & "Description" & vbcr & err.description
    end if
    end sub

Une gestion d'erreur passe, l'autre plante, pourquoi donc : news.
En voyant la complexité de ce dernier message, je me rends que mes explications sont vraiment insuffisantes, j'espère que ce message vous aura éclairer.

Contrôle de saisie d'une date dans un inputbox : news

Article(s) à consulter :

An Explanation of Trappable Errors in Visual Basic for Apps
http://support.microsoft.com/support/kb/articles/Q142/1/38.asp

Q186063 - INFO: Translating Automation Errors for VB/VBA (Long)
Cherchez cet article pour trouver comment obtenir des descriptions de messages d'erreur dignes de ce nom lorsque vous pilotez d'autres applications.

Je n'ai pas voulu aborder ici la gestion des erreurs de formules, mais il existe tout de même des fonctions pour les détecter en VBA.
Alors voilà tout de même une procédure qui remplace toutes les cellules contenant #REF par zéro.

Sub Replace()
For Each oCell In Selection
If IsError(oCell) Then
If oCell = CVErr(xlErrRef) Then
oCell.Value = 0
End If
End If
Next oCell
End
'ps : notez l'utlilisation de cverr ; cf aussi iserror

• d'arrêter définitivement l'exécution : bouton Fin
• de passer en mode de déboguage : bouton Déboguage
• d'afficher l'aide : bouton Aide

Notez que si vous entrez en déboguage, la macro fait une pause, vous pourrez demander à Excel de poursuivrel'exéuction, mais avant cela des points d'arrêts, des espions peuvent être posés. Vous pouvez aussi arrêter ici l'exécution (bouton arrêt).

Il existe une propriété de l'objet Application appelée EnableCancelKey qui définit cette possibilité d'arrêt de la macro.
Par défaut, cette possibilité d'arrêt est active pour chaque instance d'Excel.

Si vous désirez la désactiver, utilisez l'instruction suivante : application.enablecancelkey = xldisabled
Pour rétablir son fonctionnement, donner la valeur xlinterrupt à la propriété EnableCancelKey

Arrêt de procédure par VBA

Il est possible aussi d'arrêter l'exécution d'une procédure en VBA. Il existe deux instructions pour cela : Exit Sub & End.
Sachant cela vous pouvez très bien programmer l'arrêt en cas d'erreur ou sous certaines conditions.

Je ne vous mets pas d'exemple pour l'instant, je pense qu'avec les explications sur l'instruction if et sur la gestion d'erreurs figurant sur cette page, vous pourrez de vous-même arriver à programmer cela.

Différence entre End & Exit Sub

Pour la comprendre ayez en-tête qu'une procédure peut en appeler d'autres au cours de son exécution.
Si vous voulez arrêter l'exécution de l'une d'entre elles, vous devrez utiliser l'instruction Exit Sub, elle permet de sortir de la procédure en cours.
Si vous voulez arrêter définitivement les procédures en cours d'exécution et éviter que d'autres appels procédures se déclenchent, utiliser l'instruction End.

En d'autres termes, pour arrêter toutes les macros d'un coup utilisesz End plutôt que Exit Sub. Exit Sub, arrête la procédure appelée mais renvoie la main à la procédure appelante. (explication reprise d'un message de Thomas C. dans la conversation "arret d'une macro" le mercredi 20 septembre 2000 à 10:53 dans le groupe de discussion microsoft.public.fr.excel)

Si vous utilisez End dans une procédure au sein d'un formulaire de type Userform, l'userform sera fermé.
(Ce n'est pas vrai pour les boîtes de dialogue de type Excel 5/95.)

Arrêt de procédures évènementielles

Comment arrêter une procédure évènementielle une fois qu'elle est lancée ?
Une procédure évènementielle vous permet d'exécuter un code à un moment précis comme avant un double-clic ou avant une sauvegarde.

Lorsqu'une procédure évènementielle est activée, le code qui y est inscrit s'exécute puis l'évènement se produit.
Heureusement, il est possible avec certains évènements d'empêcher leur avènement (après un contrôle éventuel).
C'est possible avec la variable booléenne "système" Cancel, il suffit pour cela de fixer cette variable à True.

Désactivation de procédures évènementielles : à la fin de la partie suivante

Arrêt de procédures planifiées : voir plus haut la partie sur la temporisation avec la méthode OnTime

Procédures évènementielles

Une procédure évènementielle est une macro qui va s'éxécuter à la suite d'un évènement qui se produit eu égard à un objet d'Excel (l'application Excel, un classeur, une feuille, un formulaire, etc.). Ces évènements peuvent correspondre par exemple à l'ouverture d'un fichier excel, sa sauvegarde, l'impression d'une de ses feuilles, la sélection d'une de ses feuilles, la fermeture d'un de ses formulaires, la sélection d'une cellule, etc.

Pour commencer, vous pouvez lire les deux pages web suivantes :

Ci-dessous, je liste les évènements associés à certains objets et montre quelques exemples.

Autres points abordés :

• l'ordre de déclenchement de ces procédures évènementielles
• les évènements sur Excel 5/95 (vous trouverez un message de news)
• la désactivation des évènements

Evènements de l'objet WORKBOOK

Les évènements associés au classeur (Workbook_) : Activate, AddinInstall, AddinUninstall, BeforeClose, BeforePrint, BeforeSave, Deactivate , NewSheet, Open, SheetActivate, SheetBeforedoubleClick, SheetBeforeRightClick, SheetCalculate, SheetDeactivate, SheetFollowHyperlink, sheetselectionChange, WindowActivate, WindowDeactivate, WindowResize.

Evènement Open
    effectuer des actions à chaque ouverture d'un classeur.

La macro ci-dessous cache toutes les barres d'outils sauf la barre d'outils "Feuille de calcul" ("Worksheet Menu Bar").
private sub Workbook_Open()
Dim I As Integer
With Application.CommandBars
For I = 2 To .Count
.Item(I).Enabled = false
Next I
End With
End Sub

Informations sur la macro Auto_Close des versions antérieures à Excel 97 : news

Evènement NewSheet
    effectuer des actions à chaque création de feuille.

La macro ci-dessous montre comment déplacer de nouvelles feuilles à la fin du classeur.
private sub workbook_newsheet(byval Sh as object)
sh.move after:= sheets(sheets.count)
msgbox "La feuille nouvellement insérée appellée " & Sh.name & "," & vbcr & "a été déplacée en dernière position." & vbcr & vbcr & "Pour info, elle est de type : " & typename(Sh)
end sub

Evènement BeforePrint
     effectuer des actions avant toute impression effectuée dans le classeur.

Cet exemple appelle la procédure EstablishFooter qui inscrira la date et le chemin complet du fichier dans le pied de page.
private sub workbook_beforeprint(Cancel as boolean)
EstablishFooter
end sub

sub EstablishFooter()
dim strFilePath as string
dim strDateStamp as string
strFilePath = activeworkbook.fullname
strDateStamp = format(now(), "d-mmm-yyyy, h:mm")
activesheet.pagesetup.leftfooter = " &Page &P of &N " & strFilePath & ", " & strDateStamp
end sub

Evènement Workbook_SheetFollowHyperlink
    Effectuer des actions lors du clic sur un lien hypertexte d'une des feuilles du classeur.

La macro ci-dessous contrôle si le lien cliqué pointe sur une plage de cellule, si c'est le cas elle se substitue à Excel pour effectuer elle-même le déplacement vers cette plage de cellule l'aide de l'instruction "Goto".

La méthode Goto accepte notamment le paramètre scroll, une fois ce paramètre fixé à true, Excel va se déplacer sur la cellule en faisant défiler l'affichage.
Pour tester que le lien hypertexte pointe sur une cellule, on teste la présence du symbole "!" dans la sous-adresse (subaddress), puisque les liens hypertextes internes sont construits avec ce symbole.

private sub workbook_sheetfollowhyperlink(byval Sh as object, byval Target as hyperlink)
if instr(target.subaddress, "!") then
application.goto activecell, true
end if
end sub

Evènements de l'objet Worksheet

Les évènements associés à une feuille (Sheet_) : Activate, BeforedoubleClick, BeforeRightClick, Calculate, Change, Deactivate, FollowHyperlink, SelectionChange

Evènement Worksheet_Change
     effectuer des actions à chaque changement des données de la feuille.

La macro ci-dessous vérifie si la cellule A1 faisait partie des cellules qui ont pu être modifiées.
private sub Worksheet_Change(byval Target As Excel.range)
If Intersect(Target, range("A1")) Is Nothing Then Exit Sub
' ... ton code ici
End Sub

La macro ci-dessous met en majuscules la plage de cellule modifiée.
private sub Worksheet_Change(byval Target as excel.range)
application.enableevents = False
target = ucase(target)
application.enableevents = True
end Sub


Evènement Worksheet_SelectionChange
     effectuer des actions sur les cellules qui viennent d'être sélectionnées.

La macro ci-dessous colorie en rouge les cellules B1 à B15 si la cellule A1 est égale à "oui"
private sub worksheet_selectionchange(byval Target as excel.range)
if range("A1").value = "oui" then range("B1:B15").interior.color = vbred
end Sub

Evènement Worksheet_BeforedoubleClick
    effectuer des actions suite à un double-clic sur les cellules d'une feuille de calcul

La macro ci-dessous permet de grouper/dégrouper un plan par double-clic
private sub worksheet_beforedoubleclick(byval target as excel.range, Cancel as boolean)
target.entirerow.showdetail = not target.entirerow.showdetail
cancel=true
End Sub

Remarques sur la variable "système" cancel
    - elle est booléenne, elle peut prendre deux valeurs : vrai ou faux, soit en anglais "true" (ou bien 1) et "false" (ou bien 0).
    - la fixer à true permet d'indiquer à Excel d'annuler l'opération normalement prévue,
    - suite à un double-clic, Excel devrait passer en mode d'édition de la cellule, et permettre donc d'en changer le contenu, mais comme nous avons fixé la variable cancel à true, cela ne va pas arriver.

Evènements de l'objet Userform

Les évènements associés à un formulaire (Userform_) : AddControl , BeforeDragOver, BeforeDropOrPaste, Click, DblClick, Deactivate, Error, Initialize, KeyDown, KeyPress, KeyUp, LayOut, MouseDown, MouseMove, QueryClose, RemoveControl, Resize, Scroll, Terminate, Zoom

Evènement Initialize
   effectuer des actions au chargement du formulaire (son initialisation)

La macro ci-dessous affiche dans une liste déroulante ListBox1 les derniers classeurs auxquels Excel a accédé.
private sub UserForm_Initialize()
for each oFile in recentfiles
ListBox1.additem oFile.path
next oFile
end Sub

Remarques sur la liste des fichiers récents
     - le nombre de fichiers derniers ouverts apparaissant dans le menu fichier a une valeur par défaut que vous pouvez changer à travers la boîte de dialogue Options du menu Outils (onglet Général, Liste des derniers fichiers utilisés).

Evènement Mousedown
    effectuer des actions au moindre clic de souris

La macro ci-dessous indique quel bouton a été cliqué en contrôlant la valeur de la variable "système" Button.
private sub UserForm_Mousedown(byval Button As Integer, byval Shift As Integer, byval X As Single, byval Y As Single)
if Button = 1 Then
msgbox "You clicked the left button"
elseif Button = 2 Then
msgbox "You clicked the right button"
end if
end sub

Evènement QueryClose
    effectuer des actions à la fermeture du formulaire

La macro ci-dessous exemple empêche la fermeture du formulaire par le biais de la croix.
private sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer)
If CloseMode = vbFormControlMenu Then Cancel = True
End Sub

Pour d'autres exemples de manipulation du formulaire lui-même et de procédures évènementielles avec quelques objets de formulaire (ou de barres d'outils Contrôles / Commandes), regardez la page userform.htm.

événements de l'objet Application

Les événements d'application surviennent à la création ou à l'ouverture d'un classeur ou lors de la modification d'une feuille d'un classeur ouvert. Pour écrire des procédures d'événements pour l'objet Application, vous devez créer un objet à l'aide du mot clé WithEvents dans un module de classe.

Vous trouverez ci-dessous la liste des évènements possibles et un ou deux exemples.

Evènement WorkbookOpen
     effectuer des actions à chaque ouverture de classeur

Ci-dessous, un premier exemple de L.L., ci-contre le message de news d'où extrait l'exemple : new s

Dans ton classeur Perso.xls :
1) Menu Insertion -> Module de classe, puis copie le code suivant :
Public WithEvents App As Application
private sub App_WorkbookOpen(byval Wb As Excel.Workbook)
MsgBox "Ouverture du classeur '" & Wb.FullName & "'"
End Sub

2) Dans le module thisworkbook :
Dim App As New Class1
private sub Workbook_Open()
Set App.App = Application
End Sub

Ci-contre, un autre exemple du genre : news

La macro ci-dessous arrange toutes les fenêtres quand un classeur est ouvert
private sub App_WorkbookOpen(byval Wb As Workbook)
Application.Windows.Arrange xlArrangeStyleTiled
End Sub

Exemple de la commande onentry : news, pour comparer la valeur d'une saisie à d'autres valeurs
Exemple app_windowresize : news

Ordre de l'avènement de ces procédures au sein d'Excel (extrait de la page de Chip Pearson)

Lorsqu'un évènement est traité par plusieurs objet, la procédure de plus bas niveau est d'abord exécutée, puis l'exécution se poursuit en montant de niveau. Par exemple, changer les valeurs d'une cellule déclenche en premier l'évènement Worksheet_change, puis l'évènement Workbook_SheetChange et enfin l'évènement App_Workbook_Change. ( Excusez moi pour ce français, je suis rouillé niveau traduction)

( When an event is processed by more than one object, the procedure in the "lowest level" object is executed first, and then execution proceeds "up the chain". For example, changing the value of a cell triggers the Worksheet_Change event first, then the Workbook_SheetChange event, and finally the App_WorkbookChange event.)

Voici l'ordre des évènements à l'ouverture d'un classeur

Workbook_Open
App_WorkbookOpen
Workbook_WindowDeactivate (of previous workbook)
App_WindowDeactivate (of previous workbook)
Workbook_Deactivate (of previous workbook)
Workbook_Activate
App_WorkbookActivate
Workbook_WindowActivate
App_WindowActivate
Auto_Open

Voici l'ordre des évènements à la fermeture d'un classeur

Workbook_BeforeClose
App_WorkbookBeforeClose
Auto_Close
Workbook_BeforeSave
App_WorkbookBeforeSave
Workbook_WindowDeactivate
App_WindowDeactivate
Workbook_Deactivate
App_WorkbookDeactivate

Voici l'ordre des évènements quand vous passez d'un classeur à un autre classeur

Workbook_WindowDeactivate
App_WindowDeactivate
Workbook_Deactivate
App_WorkbookDeactivate
Workbook_Activate
App_WorkbookActivate
Workbook_WindowActivate
App_WindowActivate

Voici l'ordre des évènements quand vous passez d'une feuille à l'autre au sein d'un classeur

Worksheet_Deactivate
Workbook_SheetDeactivate
App_SheetDeactivate
Worksheet_Activate
Workbook_SheetActivate
App_SheetActivate

Les évènements sous Excel 5/95

Un petit message de news rédigé par LL sur les évènements Excel 5/95 (code vba anglais) : news
Pour d'autres infos sur Excel 4, Excel 5/95, regardez la page suivante : xl_all.htm

Désactivation des évènements

Par défaut, lorsque vous lancez une instance d'Excel, la gestion des évènements est active.
Pour savoir son état, il faut manipuler la propriété EnableEvents de l'objet Application :
    msgbox application.enableevents

Pour désactiver ou réactiver, il suffit de faire basculer cette propriété de False à True
    application.enableevents=false/true

Mais attention à ce qui suit : Application.EnableEvents s'applique uniquement aux événements du tableur, pas des UserForms.
Pour en savoir plus, voici ci-après une explication reprise d'une réponse de LL suite à une demande de Philippe E. : news
(réponse donnée dans la conversation "Comment désactiver une procédure évènement ?" du groupe de discussion microsoft.public.excel le le lundi 4 octobre 1999 à 18:25)

MANIPULATION DE VARIABLES TABLEAUX

Un très bon article pour débuter de microsoft : http://support.microsoft.com/support/excel/content/vba101/vbc5-3.asp

Et quelques exemples en prime

Lire les valeurs d'une variable tableau

Manipulation de variables contenant des plages de cellules : voir xl_cellule.htm

La procédure ci-dessous stocke la plage sélectionnée par l'utilisateur dans la variable range "toRng" et aussi dans la variable tableau "arr".
Elle est rapide à l'exécution puisqu'on n'utilise pas de boucles (loop) pour lire la variable tableau et copier ses valeurs dans les cellules.

Public Sub calcCopy()
Dim arr
Dim r As Integer, c As Integer
Dim toRng As range
arr = Selection
For r = 1 To UBound(arr, 1)
For c = 1 To UBound(arr, 2)
arr(r, c) = arr(r, c) + 15
Next
Next
With Selection
Set toRng = Cells(.Row, .Column + .Columns.Count)
toRng.Resize(.rows.Count, .Columns.Count) = arr
End With
End Sub

S'agit-il d'une vrai variable tableau ou plutôt d'un objet, comme un objet range ?
Fonction de Laurent L. en réponse au sujet "Re: Bug report", le mercredi 20 septembre 2000 16:24, sur microsoft.public.excel.programming

Function IsRealArray(Variable) As Boolean
IsRealArray = IsArray(Variable) And Not IsObject(Variable)
End Function

Ceci est un sujet controversé auquel je n'ai pas tout compris, voici le message à l'origine : news

Récupérer dans une liste de valeurs la valeur correspondant à l'index d'une valeur dans une première liste
Exemple de Laurent L., sujet "Re: parrallèle de 2 listes en VBA", le lundi 10 juillet 2000 18:50

Dim Liste1, Liste2
Dim MaVar1 As Integer, MaVar2 As Integer

Liste1 = Array(90, 110, 125, 140, 160, 180, 200, 225, 250)
Liste2 = Array(140, 170, 170, 182, 195, 205, 210, 170, 224)

With Application
MaVar1 = .InputBox("Elément de Liste1 ?", Type:=1)
MaVar2 = Liste2(.Match(MaVar1, Liste1) + (LBound(Liste2) = 0))
End With

MsgBox "Elément correspondant dans Liste2 = " & MaVar2

Dimension d'une variable tableau : la fonction NbDims(Arr) suivante renvoie le nombre de dimensions d'une variable tableau:

Chercher si une valeur existe dans une variable tableau
Voici deux exemples issus du forum microsoft.public.fr.excel. Il cherche tous deux la présence de trois valeurs textes dans les cellules de la feuille active, et efface le contenu des cellules qui les contiennent.

Créer une fonction qui retourne une variable tableau

Premier exemple : copier la fonction dans un module, puis sélectionner quatre cellules (en une ligne ou une seule colonne), faites F2, collez la formule suivante : =ReturnArray("Bonjour"; 40000;3; FAUX) et valider par Ctrl + MAJ + Entrée. (C'est une formule matricielle).

Function ReturnArray(InName As String, Price As Long, Ask As Integer, Offer As Boolean) As Variant

Dim ReturnVals As Variant
bdRows = Application.Caller.Rows.Count

ReturnVals = Array(InName, Price, Ask, Offer)

If bdRows = 1 Then
ReturnArray = ReturnVals
Else: ReturnArray = Application.Transpose(ReturnVals)
End If
End Function

!!! Beware with versions from french, you'll have to replace commas and boolean values to the right values.

Second exemple

private sub toto() Dim montab(5) As Long montab(0) = 10 montab(1) = 20 montab(2) = 30 montab(3) = 40 montab(4) = 50 AfficheToto montab End Sub

Private Function AfficheToto(mt As Variant) For i = LBound(mt) To UBound(mt) Debug.Print "mt(" & i & ")=" & mt(i) Next i End Function

Tri de variables tableaux

Cette réponse de laurent fait suite à ma question "j'aimerais récupérer les valeurs de la colonne A et de la colonne B et les mettre dans une listbox mais comment faire lorsque mon nombre de lignes est variable (que je calcule avec un application.counta), puisque dans la déclaration de la dimension de la table, excel attend une constante. (Réponse faite sur microsoft.public.fr.excel, Samedi 31 Juillet 1999 à 13:13, sujet "Re: ListBox & Tableau")

UserForm1.ListBox1.List = Feuil1.Range("A1").Resize(Application.CountA(Feuil1.Range("A:A")), 2).Value

Ou alors tu peux aussi utiliser RowSource, comme il s'agit d'une plage. L'intérêt, c'est qu'avec une plage tu peux mettre des entêtes de colonnes (avec la propriété ColumnHeads = True). Dans ce cas, le RowSource doit démarrer une ligne en-dessous des étiquettes de colonne dans la plage spécifiée:

UserForm1.ListBox1.RowSource = Feuil1.Range("A2").Resize(Application.CountA(Feuil1.Range("A:A")) - 1, 2).Address(external:=True)

Dim City() as String

Dim i as long, j as long
j = 0
For i = 0 To ListBox1.ListCount - 1
If ListBox1.Selected(i) Then
Redim Preserve City(0 to j)
city(j) = ListBox1.List(i)
j = j + 1
end if
Next i

for j = 0 to ubound(city)
msgbox city(j)
next

Liens VBA français

Il y a plusieurs types de ressources sur excel : les sites web, les newsgroups, les livres ....
Vous trouverez ci-dessous une liste de sites web orientées VBA Excel, d'autres figurent sur ma page Ressources, ainsi qu'une liste de newsgroups et de livres, qui vous satisferont. Je fréquente pour ma part le forum news://msnews.microsoft.com/microsoft.public.fr.excel sérieusement depuis Mai 1999, même si j'y passe moins de temps depuis début 2000. D'ores et déjà, sachez qu'il existe des moteurs de recherche dédiées à la recherche de messages dans ces newsgroups : http://groups.google.com (anciennement http://www.deja.com), http://www.remarq.com, http://www.foorum.fr

Quelques autres sites français dédiés à VBA
Site sur la programmation en VBA : le site (hébergé par altern.org) !!! il n'existe plus mais je chercherais des traces, j'ai notamment son fichier VBA.doc
Site de Frédéric Sigonneau : le site
Site de Pascal Bazzea : le site
Site de Jean Sanguinetti : le site
Site de Christian Herbé : le site
Accélérer le code VBA : le site, page de L. longre, pour améliorer la rapidité de son code
Une des pages de Microsoft : Excel for Windows Visual Basic for Applications 101 (pour grand débutant sous VBA)

Conclusion :

Au fait, j'en ai jamais lu un, de livre!! je suis certes très rouillé, le meilleur apprentissage c'est l'enregistreur de macros, la bidouille puis les questions-réponses des forums, qui permettent de cogiter et d'apprendre énormément.
Il faut aussi avoir quelques besoins pour progresser !