C. MACROS COMPLEXES - VBA
C. MACROS COMPLEXES
VISUAL BASIC FOR APPLICATIONS
Il est aussi possible, pour les utilisateurs avertis, de programmer des routines plus complexes et même d'utiliser Excel comme base d'une application automatisée. L'écriture du code s'effectue à l'aide de Visual Basic pour Applications.
ONGLET "DÉVELOPPEUR"
groupe "code" (1er bloc)
<clic g> sur 
la macro-commande s'affiche dans une fenêtre Visual basic
<alt> f11 ouvre une fenêtre Visual Basic
Il faut ici prendre garde de ne pas faire avec Excel des choses que sa structure ne lui permet pas de bien faire. Souvent, Access sera un bien meilleur outil de programmation et de structuration des données alors qu'Excel donnera toute sa puissance dans la synthèse des données sous forme de tableaux ou de graphiques
1. OBJETS
Un objet représente un élément de l'application Excel :
un classeur
une feuille de calcul
une cellule
un bouton …
Pour faire référence à la feuil2
Worksheets(2)
ou
Worksheets("Feuil2")
Tous les objets de même type forment une collection (toutes les feuilles de calcul d'un classeur). Chaque élément de la collection est alors identifié par son nom ou par un index.
Chaque objet peut avoir ses propres objets. Excel est constitué de classeurs eux-mêmes constitués de feuilles elles-mêmes constituées de cellules.
Pour faire référence à la cellule a3 de la feuille 2 du classeur 1
Application.Workbooks(1).Worksheets("Feuil2").Range("A3")
2. PROPRIÉTÉS
Une propriété est propre à un objet. Ce peut être :
La valeur d'une cellule
La couleur d'une cellule
La taille d'une cellule
Les objets sont séparés de leurs propriétés par un point : Cellule.Propriété=valeur :
Pour mettre la valeur 1 dans la cellule A3 :
Range("A3").Value = 1
Une propriété peut faire référence à un état de l'objet.
Pour masquer la feuille de calcul "Feuil2" :
Worksheets("Feuil3").Visible = False
3. MÉTHODES
Une méthode est une opération que réalise un objet :
Ouvrir
Fermer
Sélectionner
Enregistrer
Imprimer
Effacer…
Les objets sont séparés de leurs méthodes par un point.
Pour sélectionner la feuille de calcul nommé "feuil2"
Worksheets("Feuil2").Select
Lorsque l'on fait appel à plusieurs propriétés ou méthodes d'un même objet, on fait appel à l'instruction With Objet Instructions End With.
Pour mettre la valeur 1 dans la cellule A3, la police en gras et en italique et copier la cellule
With Worksheets("Feuil2").Range("A1")
.Value = 1
.Font.Bold = True
.Font.Italic = True
.Copy
End With
4. ÉVÈNEMENTS
Pour qu'une macro se déclenche, il faut qu'un se produise évènement. Les principaux objets auxquels un événement peut être attaché sont :
Un classeur
Une feuille de travail
Une boite de dialogue
Pour lancer la procédure "essai" à l'ouverture du classeur :
Private Sub Workbook_Open()
essai
End Sub
a) LISTE DES ÉVÈNEMENTS LIES A L'OBJET WORKBOOK
activate Quand le classeur ou une feuille est activé
beforecloseAvant que le classeur ne soit fermé
beforeprint Avant l'impression du classeur
beforesaveAvant l'enregistrement du classeur
deactivate Quand le classeur ou une feuille est désactivé
newsheet Lorsqu'une nouvelle feuille est créée
open À l'ouverture du classeur
sheetactivate Lorsqu'une feuille est activée
sheetbeforedoubleclick Lors d'un double-clic
sheetbeforerightclick Lors d'un clic avec le bouton droit de la souris
sheetcalculate Après le recalcul d'une feuille de calcul
sheetchange Lors de la modification d'une cellule
sheetdeactivate Lorsqu'une feuille est désactivée
sheetfollowhyperlink Lors d'un clic sur un lien hypertexte
sheetselectionchange Lors d'un changement de sélection sur une feuille de calcul
windowactivate Lorsqu'un classeur est activé
windowdeactivate Lorsqu'un classeur est désactivé
La création d'une procédure évènementielle liée à une feuille de calcul est identique.
b) LISTE DES ÉVÈNEMENTS LIES A L'OBJET WORKSHEET
activate Quand une feuille est activée
beforedoubleclick Lors d'un double-clic
beforerightclick Lors d'un clic avec le bouton droit de la souris
calculate Après le recalcul de la feuille de calcul
change Lors de la modification d'une cellule
deactivate Quand une feuille est désactivée
followhyperlink Lors d'un clic sur un lien hypertexte
selectionchange Lors d'un changement de sélection
le paramètre "Cancel", peut annuler la procédure
Pour empêcher l'impression d'un classeur :
Private Sub Workbook_BeforePrint(Cancel As Boolean)
Cancel = True
End Sub
Pour récupérer la valeur d'une cellule modifiée :
Private Sub Worksheet_Change(ByVal Target As Range)
MsgBox Target.Value
End Sub
c) LES CLASSEURS
Un classeur est désigné par "Workbook". Les classeurs peuvent être ouverts, fermés, enregistrés, activés, masqués, supprimés ....
Ajouter un nouveau classeur
workbooks.add
Fermer un classeur
workbooks("nomduclasseur.xls").close
Fermer le classeur actif
activeworkbook.close
Ouvrir un classeur
workbooks.open "c:\chemin\nomdufichier.xls"
Activer un classeur
workbooks("nomduclasseur.xls").activate
d) LES FEUILLES DE CALCUL
Une feuille de calcul est désignée par "Worksheet".
Sélectionner une feuille
Worksheets("feuil1").select
Récupérer le nom de la feuille active dans une variable
Mafeuille = activesheet.name
Masquer une feuille
Worksheets("feuil1").visible = false
Supprimer une Feuille.
Worksheets("feuil1").delete
Copier la Feuil2 de Classeur.xls dans un nouveau classeur
Workbooks("classeur.xls").worsheets
("feuil2").copy
e) LES CELLULES
Une plage de cellules est désignée par "Range".
Pour faire référence à la plage de cellule "A2:C10" :
Range("A2:C10")
Pour sélectionner les plages de cellule " A2:C10" et "D3:G20" :
Range("A2:C10, D3:G20").Select
Pour effacer les données et la mise en forme de la plage de cellule "A1:B10" :
Range("A2:C10").Clear
Ouvrir le classeur "dates.xlsx"
Enregistrer comme macro sous le nom "macro1" les actions suivantes :
Effacer les données et la mise en forme de la plage de cellule "A1:D7"
Pour faire référence à une seule cellule, on utilisera l'objet Range(référence de la cellule) ou Cells(Numéro de ligne, Numéro de colonne).
Pour écrire 3 dans la cellule "A6" :
Range("A6").Value = 3
ou
Cells(6, 1).Value = 3
Dans le classeur "dates.xlsx",
Enregistrer comme macro sous le nom "macro2" les actions suivantes :
Écrire 9 dans les cellules "A1 à D7"
f) LE CONTRÔLE
Une macro peut également être lancée en cliquant sur un bouton mais aussi sur un élément graphique (image, zone de texte, objet WordArt, rectangle ...).
ONGLET "DÉVELOPPEUR"
groupe " contrôles " (3ème bloc)
<clic g> sur 
<clic g> sur 
et dessiner le bouton
associer une macro
mettre éventuellement en forme le bouton (très peu d'options)
la macro-commande s'affiche dans une fenêtre Visual basic
Dans le classeur "dates.xlsx"
Créer 2 boutons, un pour chacune des macros "format" et "effacer tout" créées ci-avant
5. CONDITIONS : IF THEN END IF
Les conditions déterminent la valeur que prennent les variables :
condition If condition Then valeur vrai
arrêter une procédure
appeler une procédure
quitter une boucle
atteindre une étiquette
Si la valeur vraie possède plusieurs lignes d'instructions, la syntaxe devient :
If Condition Then Valeur vraie End If
6. BOUCLES : FOR NEXT
Les boucles permettent de répéter un nombre de fois défini un bloc d'instructions. Elles utilisent une variable qui est incrémentée ou décrémentée à chaque répétition.
À l'intérieur d'un bloc d'instruction For Next, l'instruction Exit For peut quitter la boucle avant que la variable n'est atteint sa dernière valeur
7. BOUCLES CONDITIONNELLES : DO LOOP WHILE
La boucle Do While condition Loop exécute un bloc d'instruction tout pendant que la condition est vraie. Dans la boucle Do Loop While condition, le bloc d'instruction est exécuté une fois avant que la condition soit testée.
Pour sortir d'une boucle, on utilise l'instruction Exit Do
Ouvrir le classeur "base.xlsx"
Créer une macro-commande permettant de mensualiser par dixième le total de chaque ligne
Créer une macro-commande permettant de mensualiser par douzième le total de chaque ligne
Associer chaque macro-commande à un bouton
Enregistrer le classeur comme classeur de macros (.xlsm)
Comparer avec l'un des classeurs "ville" du dossier "macros"
Exemple de fonctionnement :
saisir une valeur en janvier pour les ventes de la 1ère ligne
le total de la ligne se met à jour
sélectionner ce total
Cliquer sur un bouton pour qu'il soit mensualisé :
Sub diziéme()
Set c = ActiveCell
If ActiveCell.Column = 14 And ActiveCell.Value <> 0 Then
Selection.Copy
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
For i = 1 To 4
ActiveCell.Offset(0, -1).Select
ActiveCell.Formula = c.Value / 10
Next i
ActiveCell.Offset(0, -1).Select
ActiveCell.Formula = 0
ActiveCell.Offset(0, -1).Select
ActiveCell.Formula = 0
For i = 1 To 6
ActiveCell.Offset(0, -1).Select
ActiveCell.Formula = c.Value / 10
Next i
ActiveCell.Offset(0, 12).Select
ActiveCell.FormulaR1C1 = "=SUM(RC[-1]:RC[-12])"
Else
MsgBox "sélectionnez la cellule de total à répartir"
End If
End Sub
À partir du classeur "base.xlsm", enregistrer un classeur "conso.xlsm"
Créer une macro-commande permettant de consolider les données de tous les tableaux créés à partir du classeur "base" en en faisant simplement la liste dans la feuille "liste" de ce classeur
Sub conso()
'
' conso Macro
' Macro enregistrée le 27/09/2005 par joel GREEN
'mise à zéro des valeurs
Sheets("conso").Select
ActiveSheet.Unprotect
'supression des alertes
Application.DisplayAlerts = False
'affichage des éventuelles lignes et colonnes cachées
Selection.EntireRow.Hidden = False
Selection.EntireColumn.Hidden = False
'volets de fenêtre non figé
ActiveWindow.FreezePanes = False
Range("B3:M12,B13:M20,B22:M25,B27:M37,B39:M50").Select
Range("B39").Activate
'Mise à zéro des valeurs du tableau
Range("B3:M12,B13:M20,B22:M25,B27:M37,B39:M50").Value = 0
'consolidation
Sheets("conso").Select
Sheets("liste").Select
Range("A1").Select
'boucle
For i = 1 To 100
'test pour arrêter à la fin de la liste
If Workbooks(1).Worksheets(2).Cells(i, 1).Value <> 0 Then
'memorisation de la valeur de la cellule dans la liste
setC1 = Workbooks(1).Worksheets(2).Cells(i, 1).Value
'mémorisation du chemin d'accés
setchemin = Workbooks(1).Path
setchemin = setchemin & "\" & setC1
Workbooks.Open (setchemin)
'copie dans le presse papier des valeurs de la première zone à consolider
Range("B3:M12").Select
Selection.Copy
'changement de classeur
Workbooks(1).Worksheets(1).Activate
'addition dans le classeur de conso des valeurs des cellules copiées à celles qui existent déja
Range("B3").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlAdd, SkipBlanks:=False, Transpose:=False
'idem pour autres plages de données
Workbooks(2).Worksheets(1).Activate
Range("B13:M20").Select
Selection.Copy
Workbooks(1).Worksheets(1).Activate
Range("B13").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlAdd, SkipBlanks:=False, Transpose:=False
Workbooks(2).Worksheets(1).Activate
Range("B22:M25").Select
Selection.Copy
Workbooks(1).Worksheets(1).Activate
Range("B22").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlAdd, SkipBlanks:=False, Transpose:=False
Workbooks(2).Worksheets(1).Activate
Range("B27:M37").Select
Selection.Copy
Workbooks(1).Worksheets(1).Activate
Range("B27").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlAdd, SkipBlanks:=False, Transpose:=False
Workbooks(2).Worksheets(1).Activate
Range("B39:M50").Select
Selection.Copy
Workbooks(1).Worksheets(1).Activate
Range("B39").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlAdd, SkipBlanks:=False, Transpose:=False
Workbooks(2).Close SaveChanges:=False
Else 'sortir
End If
Next i
'sortir
'affichage des alertes
ActiveSheet.Protect
Application.DisplayAlerts = True
End Sub
Le code des macros ci-dessus peut être copié/collé de Word dans Visual Basic