Nombreuses sont les répétitions de tâches sous Excel : style particulier, mises en
page spécifiques... Excel permet d'automatiser ces travaux, il permet aussi de bâtir de
véritables applications complexes.
1-
Comment éviter les macros
a- Le
menu Options - Onglet général
Nous trouvons ici quelques options qui permettent de paramétrer l'environnement
d'Excel - Attention toutefois, à ne pas trop le personnaliser de telle sorte que seul
vous, pourrez l'utiliser-
- Activez l'alerte
macros : décochée, cela évite les messages d'alerte chaque fois qu'un classeur
contenant des macros est utilisé.
- Liste des
derniers fichiers utilisés : définit le nombre de fichiers apparaissant à la fin
du menuFichier - Nombre de
feuilles par nouveaux classeurs, police et taille de la police utilisées
dans le classeur offrent une personnalisation de base du classeur standard.
- Dossier par
défaut vous permet de définir quel répertoire local ou réseau qui sera ouvert par
la commande Fichier-Ouvrir
- Autre dossier de
démarrage (même rôle que le répertoire XLOuvrir) , tous les
classeurs et surtout les macro-complémentaires de type xla s'y trouvant seront chargées
à chaque démarrage d'Excel.
b-
Personnalisation du classeur
Toutefois, si vous
souhaitez constamment avoir comme Nouveau Classeur, un classeur avec vos propres
caractéristiques (tant de feuilles, avec telle mise en page , tel entête, tel pied de
page, tel police en 1ère feuille... ). Vous formatez un classeur standard
selon vos désirs et vous l'enregistrez sous le nom Classeur en tant que
modèle dans le répertoire XLOuvrir sous le répertoire Office ou Excel (ou dans l'autre
répertoire de démarrage)
Si vous souhaitez que modifier la feuille standard celle de Insertion Feuille de
calcul. Ouvrez un nouveau classeur et gardez une seule feuille, appliquez vos
paramètres ou vos styles et enregistrez dans le répertoire XLOuvrir (ou
autre répertoire de démarrage) sous nom Feuil (et non Feuille comme
indiqué dans l'aide) en tant que modèle.
Pour revenir aux paramètres standards, il suffit de supprimer Classeur.xlt ou
feuil.xlt du répertoire de démarrage.
c -
Classeur modèle
Un classeur modèle correspond à un imprimé type dont vous utilisez une fiche
régulièrement. Une fois, votre classeur formaté (exemple : Be.xls),
vous l'enregistrez sous le dossier XLOuvrir (ou l'autre répertoire de
démarrage) en tant que fichier modèle (*.xlt). Il vaut mieux pour des questions de
commodités lui donner un nom court. Lorsque vous appelez le menu Fichier-Nouveau, il vous est proposé
dans la liste (onglet Général), outre le classeur standard (ou modifié), les
différents modèles que vous avez créés. Pour l'avoir dans l'onglet solutions
tableur, il aurait fallu l'enregistrer sous Microsoft
Office\modèles\solution tableur.
Le document ouvert alors prend le nom du modèle suivi d'un chiffre (ex: Be1.xls)
comme pour les classeurs standards.
d
l'Assistant modèle
La démarche est identique dans un premier temps, mais le but est d'alimenter une base
de données externe à partir des informations saisies sur le formulaire. Exemple : Conge.xls
pour saisir les congés d'un personnel.
Une fois, l'imprimé constitué, nous appelons l'Assistant Modèle dans le menu Donnée
pour transformer ce modèle simple en masque de saisie pour alimenter une base qui sera
créée dans l'opération.
Etape 1 :
l'assistant vous propose d'utiliser le classeur en cours pour en faire un modèle qui se
trouvera systématiquement dans le dossier Modèle sous Office ou Excel.
Le nom du modèle peut être modifié par rapport au classeur de départ. L'extension xlt
sera rajoutée.
Etape 2 : elle
concerne la nouvelle base qui recevra les informations saisies. On définit le type base
(Excel, Access, Foxpro,Dbase..) ce qui permettra une exploitation de ces informations par
un autre progiciel. L'emplacement et le nom de la base peuvent être modifiés dans
l'optique d'une utilisation réseau.
Etape 3 :
C'est l'étape la plus importante : la liaison entre le formulaire et la base. Le contenu
de certaines cellules sera lié à des champs de la base. Dans notre exemple, la feuille
Constante (cste) contient les informations à reporter. Dans le tableau
dans la colonne de gauche nous cliquerons sur les cellules contenant les informations
(exemple Cste!D12 pour le nom de la personne) puis dans la partie droite
du tableau. Comme l'intitulé de l'information se trouve à gauche (cellule C12),
il vous proposera automatiquement cet intitulé (personnel) comme nom de champ. Cependant,
il peut être modifié ou saisi directement. Nous ferons la même chose pour les
informations suivantes.
Les étapes 4 et 5 vous proposent d'inclure les données existantes sur le
classeur en cours dans la base, et il vous indique que ce modèle est modifiable. Ne pas
oublier d'appuyer sur le bouton Fin
La Mise en oeuvre
: Dans le menu Fichier Nouveau, on choisit Conge.xlt, un
classeur Conge1.xls s'ouvre. On sélectionne les informations et on ferme
le classeur. A la première question : enregistrement des modifications, il faut répondre
OUI. La seconde (importante !) vous demande si les informations doivent
être reportées dans la base. Après vous voyez si vous souhaitez garder une copie de ces
informations enregistrant ou non le classeur Conge1.xls
Il ne restera qu'à exploiter cette base soit via Excel (Tableau Croisé Dynamique) soit
via un autre Gestionnaire de base de donnée.
2
L'enregistreur de Macros
L'enregistreur de macros est sur le principe d'un magnétophone mais il s'applique non
à la musique que nous pouvons réécouter, mais l'enchainement de commandes Excel
que nous pouvons répéter. Il est aussi l'instrument idéal de découverte du code Vba.
a
l'enregistrement simple
La 1ère macro : Nous souhaitons que l'entête de notre établissement puisse
s'inscrire de manière automatique (Collège Les papillons dans une
cellule, 4 rue des Piérides dans la cellule en dessous, 49990
Hespérides-sur-Loire encore en dessous) car, nous nous fatiguons d'avoir à le
retaper sans cesse et Excel ne dispose pas de la puissance d'insertion automatique de
Word.
Nota : Néanmoins, pensez à la correction automatique du menu Outils
qui permet des insertions simples
Positionné à un
endroit quelconque d'une feuille vide, nous lançons le Menu OutilsMacros
Nouvelle Macro. La boîte de dialogue qui suit mérite de nous retenir quelques
instants.
Le nom de la macro : Macro1 par défaut, mais ce n'est pas un nom très
explicite, il convient de prendre l'habitude de nommer clairement sa macro (pour pouvoir
la retrouver et la modifier). Pour faciliter la lecture car les espaces sont interdits et
les soulignés peu pratiques, veiller à mettre la première lettre de chaque nom en
Majuscule (EnteteEtablissement). Evitez aussi les accents typiquement français
(le langage VBA est exclusivement en anglais international). Touche de Raccourci : C'est souvent la méthode la plus rapide pour lancer une
macro à condition de ne pas chercher 5 mn la bonne combinaison de touches !. Elle
commence systématiquement par CTRL. Si la lettre que vous avez choisie
est déjà prise par l'application Excel ou par une autre macro, il vous sera proposé
l'association de CTRL+MAJ+ lettre. Bref, limitez le nombre de macros à
lancer par des touches de raccourci. Enregistrer la macro dans : c'est la dernière option choisie qui s'affiche.
- Ce classeur : la macro sera stockée dans un module particulier de ce classeur et
ne fonctionnera qu'à l'intérieur de ce classeur.
- Classeur des macros personnelles. Le principe : un classeur nommé perso.xls,
car se trouvant dans le répertoire XLOuvrir contient toutes les macros
personnelles. Ce fichier est caché à l'utilisateur. Dès que la 1ère macro sera
créée, dans le menu Fenêtre, la ligne Afficher ne sera plus grisée.
L'avantage principal est la disponibilité pour tous les classeurs ouverts des macros
contenues dans ce fichier. Donc, dans ce classeur, doit se trouver les macros les plus
fréquemment utilisées quel que soit le classeur ouvert.
- Nouveau classeur : dans l'immédiat, peu d'intérêt. Description : commentaire sans effet sur la macro si ce n'est d'informer un
utilisateur sur son utilité.
Pour notre exemple : nous inclurons notre macro EnteteEtablissement dans ce
classeur avec la touche de raccourci CTRL+a.
Dès l'appui, sur le bouton OK, une petite barre d'outil apparaît : Arrêt
de l'enregistrement
- 1er bouton : Arrêt Aussitôt, la fin de votre frappe ou de vos
traitements enregistrés, vous appuyez sur le bouton Arrêt. La macro répétera toutes
les actions enregistrées y compris vos erreurs en cours d'enregistrement, il convient
d'être vigilant et d'économiser au maximum vos actions.
- 2ème bouton : référence relative. Les conséquences ne seront pas les mêmes
si ce bouton est enfoncé (relatif) ou absolu. Ce sont les sélections qui sont
différentes : l'une dit sélectionne la cellule A2, l'autre sélectionne la cellule
située même colonne et une ligne en dessous.
Action enregistrée
en Absolu - répétition :
en Relatif - répétition
Je suis en A1 et je tape Collège Les Papillons puis Entrée
Dans la cellule active, s'inscrit Collège Les Papillons
Dans la cellule active, s'inscrit Collège Les Papillons
en A2, je tape 4 rue des Piérides
En A2, s'inscrit 4 rue des Piérides
Dans la cellule juste en dessous, s'inscrit 4 rue des Piérides
Une fois, nos trois frappes effectuées, nous arrêtons l'enregistrement :
Mise en oeuvre de la macro : sélectionnons une autre feuille et appuyons sur CTRL+a ou
par le menu Outils Macros et on choisit sa macro dans la liste (peu pratique). Nous
y reviendrons plus loin.
b- enregistrement dans le classeur
perso.xls
La 2ème Macro : Enregistrement dans le classeur des macros personnelles. Descriptif : nous utilisons fréquemment mais pas toujours , le bas de page suivant
: Edité le : <date>
Page : <P>/<Ps>
Origine : <Fichier>-<Onglet>
Nous voulons donc l'appliquer quel que soit le classeur ouvert, notre enregistrement se
fera donc dans le classeur des macros personnelles (Perso.xls).
Nous lançons : l'enregistrement sur le classeur en cours, le nom de la macro sera
PiedDePagePersonnel avec la touche de raccourci CTRL+b
Le traitement s'effectue par Fichier-Mise en Page - entêtes et pieds de page
Une fois l'opération effectuée, nous arrêtons l'enregistrement :
Test : sur une autre feuille, nous exécutons notre macro par CTRL+b
ou par le menu Macro, MacrosPiedDePagePersonnelExécuter. La macro est un peu longue à s'exécuter. Nous
résoudre ce problème plus loin.
Exercice : Faire une macro permettant le format kwh avec touche de
raccourci CTRL+c et appelée FormatKwh
c - l'ajout d'icône ou de menus de lancement
Très rapidement, vous ne vous souviendrez plus, si c'est CRTL+a +b
... qui lance telle ou telle macro, d'où la nécessité de créer des boutons
(ou icônes) ou d'insérer un nouvel élément de menu.
1- insérer une icône de lancement
Les opérations
méritent toute notre attention : clic droit dans la barre d'outil (et non sur un bouton)
on choisit le menu Personnaliser. Puis, dans l'onglet Commande la
catégorie Macros et on clique sur bouton personnalisé
Ce bouton sera installé quelque part dans les barres d'outils existantes. Et, clic sur ce
bouton vide. Dans la zone Nom, nous donnons un intitulé plus explicite qui
figurera dans l'info-bulle. Puis, nous modifions l'image du bouton et choisissons
le carré vide. Ensuite, nous appelons l'éditeur de bouton et traçons notre
dessin.
Il ne reste qu'à Affecter une macro et choisissons dans la liste FormatKwh.
A vous de tester le résultat. 2 - Créer un menu de lancement
La première
démarche est identique mais, on choisit Elément de menu personnalisé.
Il suffit alors
de lui donner un nom et lui affecter une macro (par exemple : PiedDePagePersonnel)
Si on souhaite créer un nouveau menu, on prendra dans la boîte Personnaliser la
catégorie : Nouveau menu :
Ce nouveau menu sera glissé selon votre convenance, et les éléments de menu
personnalisé ou les autres nouveaux menus seront installés dans cette nouvelle boîte.
Attention de ne pas trop personnaliser votre environnement car seul vous, pourrez
l'utiliser.
d la modification des macros enregistrées
Nous
avons remarqué que la macro PiedDePagePersonnel était un peu longue à
s'exécuter. Nous allons la modifier pour la rendre plus rapide.
Mise en oeuvre : Menu OutilMacro puis MacrosModifier
en sélectionnant PiedDePagePersonnel
Nous arrivons alors dans un module dépendant d'Excel : l'éditeur Visual Basic
Application
Trois parties d'écran :
1- la fenêtre VBA Projet: organisée comme l'explorateur Windows, tous
les classeurs ouverts y figurent. La sélection d'un élément dans cette fenêtre
mettra à jour les 2 autres. Précisions de vocabulaire : Feuille désigne des
feuilles de calcul classique, module, des feuilles contenant des macros
commandes, ThisWorkbook, l'ensemble de ce classeur, VbaProject
(fichier.xls) désigne le classeur mais présenté sous l'angle du langage de
programmation VBA.
2- La fenêtre Propriétés -
Suivant les
éléments sélectionnés, feuille, module, CeClasseur, différentes informations
apparaîtront.
3 - La Fenêtre du code (grande fenêtre), là apparaît le code du
module sélectionné. Il est en Anglais international.
Quelques précisions de langage : Sheets = feuille donc ActiveSheet = feuille active cell = cellule range = plage de cellules Workbooks = classeur
La procédure PiedDePagePersonnel se trouve entre Sub NomDeLamacro et
End Sub (fin de la procédure) sur le principe des bannières ouvertes puis
fermées
Tout ce qui est en vert et précédé du signe ' forme un commentaire sans effet sur la
macro
Dans notre commande, beaucoup de choses ne servent à rien, elles ont été
enregistrées car elles constituent toutes les options du menu Mise En page. Nous
pouvons supprimer les éléments supplémentaires en ne gardant que quelques lignes :
Sub PiedDePagePersonnel()
'
' PiedDePagePersonnel Macro
' Macro enregistrée le 26/11/00
With ActiveSheet.PageSetup
.LeftHeader = ""
.CenterHeader = ""
.RightHeader = ""
.LeftFooter = "Édité le &D"
.CenterFooter = "Page : &P / &N"
.RightFooter = "Origine : &F - &A"
End With
End Sub
En revenant sur Excel et en testant à nouveau ce petit programme, nous constaterons
son accélération.
L'enregistreur de macros vous permettra d'apprendre l'essentiel du code et son
fonctionnement
e l'assemblage de macros enregistrées
Enoncé : un fichier contient le stock actuel de vin suivant la provenance
régionale et la couleur, nous voulons obtenir de manière automatisée dans un classeur
que nous appellerons EtudeVin, un graphique de répartition des vins par
provenance et couleur. Il convient de décortiquer les différentes étapes.
1- La copie du fichier Vins.xls dans notre classeur EtudeVin.xls
2- La création du tableau croisé sans totaux avec en ligne : la
provenance, en colonnes, la couleur et en données le stock
3- La création d'un graphique sous forme de barres empilées.
L'enregistrement de toutes ses procédures peut-être long et donc source d'erreurs de
manipulation. Il convient donc de segmenter l'opération en petites actions enregistrées
et de la réunir en un seul bloc à la fin. Il est nécessaire de surveiller les points de
départ et d'arrivée de chaque mini-procédure.
Etape 1 : paramétrage d'un nouveau classeur. Enregistrer sousEtudeVin.xls Etape 2 : point de départ : Positionné sur la cellule A1 de feuil1,
nous lançons l'enregistreur de macros nom de la macro : ImportationFichierVin
dans ce classeur. Les actions : ouvrir le fichierVins.xls du répertoire Stage,
clic droit sur la feuil1 de Vins.xls - déplacer
ou copier dans le classeur EtudeVin avant la feuille2
en créer une copie. Sélectionner la cellule A1 de
cette feuille et arrêt de l'enregistrement.
Code de la procédure
Sub ImportationFichierVin()
Workbooks.Open FileName:="C:\Stage\IRA\base\VINS.xls"
Sheets("Feuil1").Select
Sheets("Feuil1").Copy Before:=Workbooks("etudevin.xls").Sheets(2)
Range("A1").Select
End Sub
Commentaires : Sub et End Sub ouvre et ferme la procédure Worksbooks (classeur qui constitue un objet Excel) dispose d'une méthode Open
(ouvrir) et le paramètre suivant est le nom du fichier feuill1 en tant Sheets (feuille, autre objet Excel) dispose de la
méthode Select (sélectionner) et de la méthode Copy suivi du
paramètre before (avant). Ici, nous n'avons pas besoin de sélectionner avant de
copier, nous pouvons donc supprimer la ligne Sheets("Feuil1").Select A1 en tant que Range(plage de cellule) dispose aussi de la méthode Select
Avant de tester cette procédure, il faut :
- supprimer la feuille copiée
- fermer le classeur Vin.xls
Etape 3 : Bâtir le tableau croisé dynamique. Nouvelle macro nom TableauVin
dans ce classeur Actions : Faire le TCD sous la forme ci-dessous et en options nom
:TableauVin, ensuite Clic droit dans le tableau options suppression
des Totaux (sinon le graphique ne suivrait pas).Puis on renomme la feuille en TCD
Code de la procédure
Sub TableauVin()
ActiveSheet.PivotTableWizard SourceType:=xlDatabase, SourceData:= _
"'Feuil1 (2)'!R1C1:R21C9", TableDestination:="",
TableName:="TableauVin"
ActiveSheet.PivotTables("TableauVin").AddFields RowFields:="Région",
_
ColumnFields:="Coul."
ActiveSheet.PivotTables("TableauVin").PivotFields("Stock").Orientation
= _
xlDataField
With ActiveSheet.PivotTables("TableauVin")
.ColumnGrand = False
.RowGrand = False
End With
Sheets("Feuil6").Select
Sheets("Feuil6").Name = "Tcd"
End Sub
Commentaires :
ligne 2 : PivotTableWizard : créer un TCD à partir de la feuille active
ligne 4 : Addfields (méthode), ajoute les champs ligne et colonne
ligne 6 : PivotFieds :champ du TCD
ligne 9 et 10 : suppression des
lignes 12 et 13 : Ici, est nommé Feuil6. Cela ne sera pas toujours exact, il est
préférable de remplacer FeuilX par Activesheet (feuille active) de façon à
éviter des disfonctionnement. La ligne12 peut être effacée et la suivante devient ActiveSheet.Name="Tcd"
La nomination de la feuille est importante car elle sera la source de notre graphique à
suivre. Si la feuille du TCD s'appelle une fois Feuil4 , une autre fois Feuil5,
la macro ne pourra s'exécuter sans erreur.
Avant de tester cette procédure, il faut
- supprimer la feuille contenant le TCD
- sélectionner la cellule A1 de Feuil1(2)
Etape 4 : Faire le Graphique : Nouvelle Macro : GraphiqueVin
dans ce classeur
Actions :
Choisir le type de graphique suivant, on passe les autres étapes on choisit sur
une nouvelle feuille qui est appelée GraphiqueVin.
Code de la procédure
Sub GraphiqueVin()
Charts.Add
ActiveChart.ChartType = xlBarStacked
ActiveChart.SetSourceData Source:=Sheets("Tcd").Range("A2:D12"),
PlotBy:= _
xlColumns
ActiveChart.Location Where:=xlLocationAsNewSheet, Name:="GraphiqueVin"
With ActiveChart
.HasTitle = False
.Axes(xlCategory, xlPrimary).HasTitle = False
.Axes(xlValue, xlPrimary).HasTitle = False
End With
End Sub
Commentaires :
ligne 2 : Charts (graphique) Add (ajouter)
ligne 3 : type de graphique
ligne 4 : la source des données et leur orientation
ligne 6 : la destination du graphique et son nom
Entre With (avec) et EndWith (fin avec), se trouvent des informations complémentaires
titres.... Etant tous à False (faux - rien ) nous pouvons les effacer.
Avant de tester cette procédure, il faut
- supprimer la feuille Graphique
- sélectionner une cellule du Tcd
Etape 5 : réunir les macros en une seule qui fera toutes les opérations.
Celle-ci ne sera pas enregistrée mais créée. Outil Macro Macros Créer
Nous arrivons
alors dans l'éditeur VBA avec Sub RecapVin
End Sub
Il ne reste qu'à taper entre ces 2 lignes le nom des autres procédures. Tapez les en
minuscules et s'il n'y pas d'erreur de frappe, elles reprendront leur format original.
Code de la procédure
Sub RecapVin()
ImportationFichierVin
TableauVin
GraphiqueVin
End Sub
Avant de tester cette procédure, il faut
- supprimer la feuille Graphique
- supprimer la feuille TCD
- supprimer la feuille Feuil1(2)
- se positionner sur la feuille Feuil1
Etape 6 : Mettre un bouton de lancement. Dans la barre d'outil formulaire,
on choisit l'icône Bouton et on trace un rectangle sur Feuil1. Immédiatement,
s'ouvre la boîte Affecter une Macro, et on choisit RecapVin. Par un clic
Droit sur le bouton, on modifie le texte : Application Vin
Etape 7 : Fermer le classeur vide .De façon à éviter des messages d'erreur si
les feuilles TCD , GraphiqueVin et feuil1(2)
sont présentes.
A la fermeture du classeur, celui-ci sera considéré comme déjà enregistré et ne sera
pas modifié.
Il convient de rédiger cette macro qui s'exécutera à la fermeture du classeur. Elle
doit porter un nom particulier Sub Auto_Close (à l'inverse une
procédure Auto_open s'exécutera à l'ouverture du classeur).
Code de la procédure
Sub auto_close()
ThisWorkbook.Saved = True
End Sub
Commentaire :
ligne 1 : procédure particulière exécutée à la fermeture du classeur
ligne 2 : L'objet CeClasseur a pour propriété d'avoir sauvegardé
à Vrai
Avant de tester cette procédure, il faut
- supprimer la feuille Graphique
- supprimer la feuille TCD
- supprimer la feuille Feuil1(2)
- se positionner sur la feuille Feuil1
- enregistrer le classeur vide avec ses macros complètes.
- fermer le classeur
Si la procédure s'exécute sans défaut (ou bugs) lorsque vous rouvrez le classeur,
félicitations !
3 le code Vba
a Les principes
Au cours de la 2ème partie, nous avons découvert un peu de code Visual Basic Application
au travers de l'enregistreur de macros. Vba est un langage de programmation orientée
objet. Commun à l'ensemble des applications Office, il se situe cependant, à
l'intérieur d'une application "hôte" quand Excel se ferme, l'éditeur Vba est
fermé également. Dépendant du tableur, ses objets sont particuliers :
Workbooks
classeur
Sheets
feuille
Range
plage de cellules
Chart
graphique
PivotTable
Tableau Croisé
Application
Application Excel...
Par programmation orienté objet, nous entendons que les objets sont le support de
propriétés et de méthodes qui leurs sont attachés. Les objets : Voitures("MaClio") : l'objet appelé MaClio (qui correspond à
mon propre véhicule) appartient à la classe des objets Voitures Workbooks("etudevin.xls") : l'objet appelé etudevin.xls
appartient à la classe des objets classeurs Les objets dépendants : Voitures("MaClio").Phares("MonPhare") : l'objet appelé
MonPhare appartient à la classe des objets Phares qui dépend de classe Voitures Les Propriétés : Voitures("MaClio").Carrosseries("MaCarrosserie").Couleur = Vert
: la propriété Couleur de l'objet MaCarrosserie appartenant à la classe Carrosseries
dépendant de la classe Voitures appliqué à l'objet MaClio a pour valeur Vert Workbooks("etudevin.xls").Sheets("Feuil1").Range("A1").Value
= 15 : la propriété Valeur de l'objet situé en A1 appartenant à la classe
PlageDeCellules dépendant de l'objet Feuil1 appartenant à la classe Feuilles dépendant
de l'objet etudevin de la classe Classeurs égal 15 Les Méthodes : Voitures("MaClio").Accélérer CompteTour :="3000" : L'objet
MaClio appartenant à la classe Voitures a pour méthode Accélérer avec comme paramètre
que le compte tour est égal à 3000. Sheets("Feuil1").Copy Before:=Workbooks("etudevin.xls").Sheets(2).
La méthode Copier est appliquée à l'objet Feuil1 appartenant à la classe Feuilles avec
comme paramètre d'être copié dans l'objet etudevin.xls appartenant à la classe
Classeurs avant la feuille placée en seconde position.
b Les Procédures ou Fonctions
Nous verrons deux moyens de "stocker" du code qui ont des conséquences
différentes :
Les procédures qui accomplissent des actions après le déclenchement d'un
évènement (le clic sur un bouton par exemple). Ces Procédures sont dites
événementielles. Elles commencent par Sub et se terminent par End Sub
Les Fonctions qui renvoient un résultat.
Exemple : le Classeur Macros (module 1)
Pour remplir la cellule D9 avec le multiple par 2 de la cellule D7,
la réalisation de cette procédure se fera lorsque nous appuierons sur le bouton
"Bouton 1".
Procédures Sub
Sub MultiplePar2()
Range("D9").Value =Range("D7").Value * 2
End Sub
La valeur de cellule D9 correspondant à celle de la
cellule D7*2
Sub MultiplePar2()
ActiveCell.Offset(2,0).Value =ActiveCell.Value * 2
End Sub
La valeur de la cellule située 2 lignes en dessous et dans la même
colonne que la cellule active est égale à la valeur de la cellule active multipliée par
2
Sub MultiplePar2()
rep = InputBox("Saisissez un chiffre ")
MsgBox "Le multiple par 2 est " & rep * 2
End Sub
Affichage d'une boîte de dialogue permet de saisir un chiffre et affichage
du résultat dans une autre boîte de dialogue. rep est une variable
Fonctions Function
Function Multi2(nombre)
Multi2 = nombre * 2
End Function
Prend le paramètre "nombre" et le multiplie par
2
Conséquences procédures Sub
Sub MultiplierPar2()
nombre = Range("d7").Value
Range("D9").Value = Multi2(nombre)
End Sub
Sub MultiplePar2()
set nombre=Activecell
nombre.Offset(2,0).Value =Multi2(nombre).Value
End Sub
Sub MultiplePar2()
nombre = InputBox("Saisissez un chiffre ")
MsgBox "Le multiple par 2 est " & Multi2(nombre)
End Sub
Maintenant que nous avons vu les principes, nous découvrirons au travers
de la réalisation d'une petite application quelques éléments du code (cf fichier Eau)
Descriptif : une feuille Saisie comprenant:
- année : liée à la cellule D2 de Constantes (Cst)
- Zone de liste Décade : liée à la zone Cst!A2:A37 et le résultat est
renvoyé dans la cellule Cst!G2
- Zone de liste Compteur : liée à Cst!B2:B14 et le résultat est
renvoyé dans la cellule Cst!G3
- L'ancien relevé : lié à Cst!B2:C14
- Nouveau relevé : à saisir
- un Bouton (sans macro)
La feuille est protégée sans mot de passe une feuille Report
ici, se retrouveront les données introduites dans la feuille Saisie à partir de la ligne
2 une feuille Cst (ou constantes)
Contient les lignes sources des boîtes de dialogues (colonne A à E),
les cellules liées (G2:G3), ainsi que la zone de "transit"
avant le report des informations dans la feuille Report (A40:F40).
c Transmission de valeur de cellules
La 1ère opération : consiste à envoyer les informations saisies
et présentes dans Cst!A40:F40 dans la ligne 2 de la feuille Report.
Avec l'enregistreur de Macros : De la feuille Saisie, on
sélectionne la Feuille Report, on insère une ligne à partir
de la ligne 2, puis on sélectionne la feuille Cst, on copie
les cellules A40:F40, on revient sur la feuille Report,
on fait un collage spécial, on sélectionne la feuille Saisie,
cellule D5.
Enregisteur de Macros
Sub ReportValeur()
Sheets("Report").Select
Rows("2:2").Select
Selection.Insert Shift:=xlDown
Sheets("Cst").Select
Range("A40:F40").Select
Range("F40").Activate
Selection.Copy
Sheets("Report").Select
Range("A2").Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
Application.CutCopyMode = False
Sheets("Saisie").Select
Range("D5").Select
End Sub
De la ligne 2 à 4 , nous pouvons simplifier en une seule ligne en supprimant les Select.
Sheets("Report").Rows("2:2").Insert Shift:=xlDown
Traduction : Feuille(Report).Ligne2.Insert1LigneEnDécalantVersLeBas
Le Copie-CollageSpécial peut être remplacé grâce à la
propriété Value (valeur) de l'objet Range (plage de
cellules)
Sheets("Report").Range("A2:F2").Value =
Sheets("Cst").Range("A40:F40").Value
Traduction : les valeurs contenues dans la plage de cellules A2:F2 de la
feuille Report sont égales au contenu des cellules A40:F40
de la feuille Cst.
Et comme nous n'avons plus changé de feuille (Nous sommes toujours dans Saisie),
seule la sélection de la cellule D5 est nécessaire.
Procédure Corrigée
Sub ReportValeur()
Sheets("Report").Rows("2:2").Insert Shift:=xlDown
Sheets("Report").Range("A2:F2").Value = _
Sheets("Cst").Range("A40:F40").Value
Range("D5").Select
End Sub
Note : espace _
permet de continuer la ligne d'instruction en dessous
La 2ème opération : Remettre à blanc de la cellule Saisie!D5 et
changement de compteur d'eau par la même technique.
Sheets("Saisie").Range("D5").Value = Empty
Sheets("Cst").Range("G3").Value =
Sheets("Cst").Range("G3").Value+1
Nous rajouterons quelques commentaires pour mieux comprendre notre macro
Nous pouvons donc transmettre les valeurs directement aux cellules par cette technique
d Les structures conditionnelles : If...Then
3ème opération : Mettre un contrôle pour éviter de reporter des valeurs
vides ou erronées du type Si dans Saisie!B5, la
valeur est vide ou inférieur à C5, alors on fait
apparaitre un boite de dialogue d'avertissement et on arrête la
procédure.
Cette instruction commence par If... then (si ... alors) et se termine
par Endif (comme nous l'avons vu pour With ... EndWith). Else (autres cas..) peut être rajoutée.
If Sheets("Saisie").Range("D5").Value = 0 _
Or Sheets("Saisie").Range("D5").Value <
Sheets("Saisie").Range("C5").Value Then
Msgbox "Saisie erronnée"
End
Endif
Procédure après If...Then
Sub ReportValeur()
' Contrôle de la saisie instruction If Then
If Sheets("Saisie").Range("D5").Value = 0 _
Or Sheets("Saisie").Range("D5").Value <
Sheets("Saisie").Range("C5").Value Then
' si la condition est remplie, boite d'information
MsgBox "Saisie erronnée"
' fin de la procédure
End
' fin de la condition
End If
' insertion ligne 2
Sheets("Report").Rows("2:2").Insert Shift:=xlDown
'recopie des Valeurs
Sheets("Report").Range("A2:F2").Value = _
Sheets("Cst").Range("A40:F40").Value
' Vidage de Saisie!D5
Sheets("Saisie").Range("D5").Value = Empty
' Incrémentation des compteurs d'eau
Sheets("Cst").Range("G3").Value = _
Sheets("Cst").Range("G3").Value + 1
'selection de la cellule nouveau relevé
Range("D5").Select
End Sub
Rajoutons un autre contrôle : Si valeur de compteur = 0, alors
Message d'information : "Saisie terminée pour cette décade", et retour au
premier compteur d'eau.
Dans les cas à multiples conditions, on utilise Select Case... Case.... End
Select
e Les Boucles Do... Loop
Il nous reste à mettre le nouveau relevé à la place de l'ancien. Nous avons
plusieurs techniques. Pour des fins pédagogiques, nous utiliserons la technique du Do
...Loop. Le principe est de parcourir une zone en fonction d'un caractère
logique soit Tantque (While) ( Tant
que une condition n'est pas remplie par exemple) soit Jusqu'à ce que (Until)
(jusqu'à la cellule active soit vide par exemple).
Ici, nous testerons le contenu de Cst!C40 (le compteur d'eau ) dans la
colonne B.
Boucle
' Début de la boucle DO loop avec selection du
départ de zone
Sheets("Cst").Select
Range("B2").Select
' Faire jusqu'à la 1ere cellule vide
Do While ActiveCell <> Empty
'Test : concordance entre base et report
If ActiveCell.Value = Sheets("Cst").Range("C40").Value Then
' si vrai mis à jour du compteur
ActiveCell.Offset(0, 1).Value = Sheets("Cst").Range("D40").Value
' et sortie de boucle
Exit Do
End If
'sinon, on descend d'une ligne
ActiveCell.Offset(1, 0).Select
'et on recommence
Loop
f Le Parcours For...Next
Les parcours de zone utilisant la procédure Do avec l'objet Range
sont assez lentes. On peut de préférence utiliser les instructions For... Next
plus rapide. Cette instruction effectue une tâche un nombre de fois défini grâce
un compteur (pas d'eau, cette fois-ci !). Ce compteur est en fait une variable
qui permet le stockage d'information dans une zone mémoire. Elle se déclare à
l'intérieur d'une procédure par l'instruction Dim, à l'extérieur, par
les instructions Public, Private suivant la portée de
la variable.
Procédure For... Next
Dim X as Range
For Each X In Sheets("Cst").Range("b2:b39")
If X.Value = Sheets("Cst").Range("C40").Value Then
X.Offset(0, 1).Value = Sheets("Cst").Range("D40").Value
Exit For
End If
If X.Value = Empty Then
Exit For
End If
Next X
Commentaire :
La 1ère ligne : déclaration de la variable X comme objet Range For Each : pour chaque élément de la collection B2:B39 If X.Value : test entre base et report X.offset(0,1) : instruction de report Exit For : sortie de boucle If X.Value (2ème) : test de la cellule vide pour une sortie de boucle Next : passage au prochain élément de la collection
f les variables
Nous souhaiterions avoir à la fin de la saisie une boîte de dialogue nous
annonçant les consommations relevées sur tous les compteurs d'eau pendant la décade
ainsi que le coût.
Les variables sont donc des informations stockées dans des zones mémoires
temporaires; elles peuvent stocker des nombres (Integer...) , elles
peuvent permettre de manipuler des objets (Workbooks, Sheets,
Range...). Ces variables objets recoivent leur affectation grâce à la
commande Set Private Variable : utilisable dans ce module Public Variable : utisable pour tous les classeurs ouverts Dim Variable : utilisable et déclaré pour une procédure
'Déclaration de la variable
Private Conso As Long
'affectation de la valeur avec le contenu des consommations
Conso = Conso + Sheets("Cst").Range("E40").Value
'dans la dernière partie
MsgBox "Saisie Terminée pour cette décade" + Chr(10) + _
" Les consomations de la décade sont : " & Conso & " M3"
Conso = Empty
La procédure est terminée
Private Conso As Long
Sub ReportValeur()
Dim X As Range
' Contrôle de la saisie instruction If Then
If Sheets("Saisie").Range("D5").Value = 0 _
Or Sheets("Saisie").Range("D5").Value <
Sheets("Saisie").Range("C5").Value Then
' si la condition est remplie, boite d'information
MsgBox "Saisie erronnée"
' fin de la procédure
End
' fin de la condition
End If
'variable Consommation
Conso = Conso + Sheets("Cst").Range("E40").Value
' insertion ligne 2
Sheets("Report").Rows("2:2").Insert Shift:=xlDown
'recopie des Valeurs
Sheets("Report").Range("A2:F2").Value = _
Sheets("Cst").Range("A40:F40").Value
' Début de la boucle For Next
For Each X In Sheets("Cst").Range("b2:b39")
If X.Value = Sheets("Cst").Range("C40").Value Then
X.Offset(0, 1).Value = Sheets("Cst").Range("D40").Value
Exit For
End If
If X.Value = Empty Then
Exit For
End If
Next X
' Vidage de Saisie!D5
Sheets("Saisie").Range("D5").Value = Empty
' Incrémentation des compteurs d'eau
Sheets("Cst").Range("G3").Value = _
Sheets("Cst").Range("G3").Value + 1
'selection de la cellule nouveau relevé
Range("D5").Select
' 2 eme condition sur le compteur
If Sheets("Cst").Range("c40").Value = Empty Then
' boite d'information
MsgBox "Saisie Terminée pour cette décade" + Chr(10) + _
" Les consomations de la décade sont : " & Conso & " M3"
'vidage de la variable Conso
Conso = Empty
' remise en position de départ pour les compteurs
Sheets("Cst").Range("G3").Value = 1
End
End If
End Sub
Autres utilisations des variables : pour manipuler les objets (plus rapide)
La procédure est terminée (variables)
Private Conso As Long
Private Saisie, Cste, Report
Private Releve As Range
Sub ReportValeur()
Dim X As Range
Set Saisie = Sheets("Saisie")
Set Cste = Sheets("Cst")
Set Report = Sheets("Report")
Set Releve = Saisie.Range("D5")
If Releve.Value = 0 _
Or Releve.Value < Saisie.Range("C5").Value Then
MsgBox "Saisie erronnée"
End
End If
Conso = Conso + Sheets("Cst").Range("E40").Value
Report.Rows("2:2").Insert Shift:=xlDown
Report.Range("A2:F2").Value = Cste.Range("A40:F40").Value
For Each X In Cste.Range("b2:b39")
If X.Value = Cste.Range("C40").Value Then
X.Offset(0, 1).Value = Cste.Range("D40").Value
Exit For
End If
If X.Value = Empty Then
Exit For
End If
Next X
Releve.Value = Empty
Cste.Range("G3").Value = Cste.Range("G3").Value + 1
Releve.Select
If Cste.Range("c40").Value = Empty Then
MsgBox "Saisie Terminée pour cette décade" + Chr(10) + _
" Les consomations de la décade sont : " & Conso & " M3"
Conso = Empty
Cste.Range("G3").Value = 1
End
End If
End Sub
g l'aide
L'aide sera nécessaire pour réaliser vos applications. L'instrument le plus utile est
l'explorateur d'objet qui vous indiquent
toutes les classes ( ou collection d'objets) possibles ainsi que les
propriétés ou méthodes qui en dépendent. De cet Explorateur, vous pouvez
appeller l'aide accompagnée d'exemples.
Nota : Si votre macro "bogue", après les correction, n'oubliez pas de réinitialiser
(Menu Exécution)
Conclusion : Pour bâtir une application, il faut déterminer au préalable
votre projet, et essayer de le réaliser avec les brides de connaissances. Les
difficultés rencontrées vous feront progressées grâce à l'aide à l'intérieur
d'Excel ou sur internet.