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) LIES A WORKBOOK

activate : Quand le classeur ou une feuille est activé

beforeclose : Avant que le classeur ne soit fermé

 beforeprint : Avant l'impression du classeur

beforesave : Avant 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) LIES A 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) 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) 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) 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) 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
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 "pdf" et "effacer" créées ci-avant



5. CONDITIONS

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

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 CONDITIONNELLE

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

Modifié le: mercredi 24 avril 2019, 10:30