Aide VBA - Première Partie

C'est là que nous pouvons introduire la notion de macro. Une macro est un ensemble d'instructions écrites dans un langage relativement simple appelé VBA, acronyme de Visual Basic For Applications. Une macro sert à automatiser une tâche longue ou répétitive, comme un formatage particulier de cellules.

VBA est un langage de programmation orienté objet comparable à Visual Basic ; il est possible de réaliser des applications complètes qui interagissent avec l'utilisateur ou des applications externes.

Excel dispose d'un enregistreur de macros. Quand vous actionnez l'enregistreur , Excel "enregistre" vos faits & gestes, et produit le code correspondant aux actions que vous avez effectuées. Pour reproduire l'action que avez enregistrée, vous n'avez plus qu'à réexécuter cette macro (bouton play).

Lorsqu'il s'agit de faire certaines actions plus élaborées (incluant conditions & calculs spécifiques), vous devez modifier le code de votre macro avec l'outil d'édition du code VBA qui est Microsoft Visual Basic Editor.

Excel et VBA mettent à votre disposition toutes les ressources et moyens techniques nécessaires à la construction de véritables interfaces applicatives.
VBA est un langage de programmation dédié aux applications office qui découle du langage Microsoft Visual Basic.

Autre Plaidoyer pour Excel & VBA : Extrait de commentaires de Pierre L. (News)

Enregistrement d'une macro

Afin de faire apparaître la boîte de dialogue « Enregistrer une macro », mettez en oeuvre l'une ou l'autre des méthodes suivantes :

par le biais de la boîte de dialogue « Macro »
- suivre le chemin suivant à travers le menu Outils/ Macro/ Macros
- cliquer sur le bouton « Play » de la barre d'outils « Visual Basic »
- cliquer sur le bouton « Modifier » pour y apporter des modifications.
par le biais d'un bouton de la barre d'outils « Formulaires »
- créer un bouton avec la barre d'outils « Formulaires »,
- cliquer à droite puis choisir la commande « Affecter une macro » dans le menu contextuel,
- enfin sélectionner la macro et valider ; à présent, un clic sur le bouton et la macro s'exécute.
par le biais d'un bouton sur une barre d'outils
- cliquer à droite sur une barre d'outils, choisissez « Personnaliser ... »,
- opter pour l'onglet « commandes « de la boîte de dialogue », sélectionner la catégorie « Macros »,
- les options « Elément de menu personnalisé » et « Bouton personnalisé » apparaissent dans le bloc « Commandes »,
- cliquer sur le « bouton personnalisé » et faites le glisser sur une barre d'outils existante (ou créée au préalable grâce à l'onglet « Barres d'outils »),
- cliquer à droite sur le bouton et affecter lui une macro,
- fermer la boîte de dialogue "Personnaliser" et voilà, un clic sur le bouton et la macro s'exécute.
- Vous pouvez bien entendu donner l'image, le nom que vous voulez au bouton.
à partir des feuilles de code de Visual Basic Editor
- afficher Visual Basic Editor grâce au bouton « Visual Basic Editor » de la barre d'outils « Visual Basic » ou en suivant le chemin Outils/ Macro/ Visual Basic Editor ou encore en appuyant sur ALT et la touche de fonction F11
- retrouver à l'aide de l'explorateur de projets (Menu Affichage ou Ctrl + R) la feuille de module concernée
- positionnez le curseur sur la procédure (en la choisissant dans la liste déroulante à droite), puis appuyer sur la touche de fonction F5 pour l'exécuter ou utiliser le menu « Exécution »
par le biais d'une autre macro
- call nomdemamacro
- call nomdemamacro(valeur_du_ou_des_paramètres_éventuels)
- run "mamacro" ou application.run "mamacro", valeur_du_ou_des_paramètres en les séparant par une virgule.

à partir d'un autre classeur
Appel "indirect"

La procédure exemple "appel_exemple_1" donnée ci-dessous peut être copiée dans un module quelconque d'un classeur quelconque. Elle permet d'exécuter la macro "Macro1" d'un autre classeur "Classeuraappeler.xls".

Instructions :

- Donner à la variable WB le nom du classeur (ici "Classeuraappeler.xls") qui contient la macro que vous voulez exécuter,
avec éventuellement son chemin d'accès complet s'il ne se situe pas dans le même répertoire que votre classeur "quelconque".

- Donner à la variable NomMacro le nom de la macro à exécuter (ici "Macro1"), et indiquer ses éventuels paramètres en les séparant par une virgule.

Sub appel_exemple_1()
Dim WB As String
Dim NomMacro As String
Dim Arg1 As String
WB = "Classeuraappeler.xls"
NomMacro = "Macro1"
Arg1 = "Val1"
Application.run WB & "!" & NomMacro, Arg1
End Sub
'voir aussi http://www.deja.com/getdoc.xp?AN=550234882&fmt=text

Appel "direct"

Cet appel direct requiert la création d'une référence au classeur contenant la procédure à appeler

Instructions
- Ouvre ou active le classeur contenant la ou les procédures à appeler ("Toto.xls").
- Renomme le nom du projet du classeur qui est VBAProject par défaut.
Pour cela, sous l'éditeur VBA, fais Outils -> Options de VBAProject,
Remplace "VBAProject" par un autre nom de projet (par exemple "Zaza").
- Enregistre ce classeur
Puis,
- Ouvre ou active le deuxième classeur ("Tata.xls") devant appeler les procédures de Toto.xls.
- Sous l'éditeur VBA, fais Outils -> Références, et coche la ligne "Zaza", puis OK.

Cette commande ajoute une référence vers Toto.xls dans Tata.xls.
Désormais, toutes les procédures et fonctions implicitement ou explicitement publiques de Toto.xls (de même que ses variables publiques) seront désormais accessibles à Tata.xls exactement comme si elles faisaient partie de ce dernier classeur.
Donc à la place d'un appel de procédure comme suit << Run "Toto.xls!MaProc", Arg1, Arg2 >>
Tu peux faire un appel direct à la procédure : << MaProc Arg1, Arg2" >>

'NB : les macros complémentaires marchent sur le même principe.

déclarer les variables que vous employez
public x => rend valable x pour tout le classeur excel
private x => rend valable x pour la feuille de code en cours mais est normalement acccessible aux procédures de cette feuille.
global x => rend valable x pour toute l'instance d'Excel en cours, et elle est donc reconnaissable par les autres classeurs
dim x => ma déclaration n'est valable que le temps de l'exécution de la procédure.

Pour information, on peut contrôler la présence d'une variable passée en paramètre.
--> voir notamment la partie de cette page sur les fonctions et procédures --> voici d'autres explications sur les variables à consulter ensuite : actionnvba2.htm

A noter !
- Sachez que vous pouvez rendre Public ou Private les procédures et fonctions comme décrit ci-dessus pour les variables.
- Option Explicit => Inscrit au début d'un module, cela force à déclarer toutes les variables, toute variable non déclarée sera signalée lors de la compilation du projet ou lors de l'exécution de la procédure. Il existe d'autres options de ce genre.
  
  Ceci peut vous éviter de vous arracher les cheveux, je vous le jure, je vous conseille donc d'utiliser cette instruction et en même temps de suivre le conseil suivant :
donner aux variables des noms parlants
Il est en effet pratique de donner à une variable un nom qui explicite sa nature et son usage, comme montré dans l'exemple ci-dessous.
exemple: public StrNomPers as string . (str=string)

Il existe sur le web un ou deux messages de groupe de discussion dans lesquels sont suggérés quelques codifications. Pour en savoir plus sur les variables, vous pourrez étudier ensuite la page web suivante : xl_vba_2.htm

connaître les objets & les méthodes que vous pourrez manipuler

application	correspond à une instance d'excel
workbooks	correspond aux classeurs ouverts dans une instance d'excel
activeworkbook	correspond au classeur actif dans l'instance d'excel
sheets	correspond à une feuille indépedemment de son type
worksheets	correspond spécifiquementà une feuille de calclul
activesheet	correspond à la feuille active
range	correspond à une plage cellule
cells	correspond à toutes les cellules
activecell	correspond à la cellule active
etc.

utiliser les apostrophes en début de ligne pour inscrire des commentaires
Un commentaire peut être insérer en début de ligne ou après une instruction.
L'apostrophe est la seule possibilité d'insérer un commentaire, on ne peut utiliser les symboles tels que /* & */ ou // ou autres, utilisables dans des langages tels que le java, le C++.

Des fonctions sont disponibles sur la barre d'outils Edition pour passer une ligne d'instruction ou un bloc d'instructions en commentaires ou pour ôter les commentaires. A travers la personnalisation des barres d'outils de Visual Basic Editor, vous trouverez ces commandes pour les mettre sur votre propre barre d'outils.
écrivez en miniscule les instructions
si elles sont justes, les majuscules se mettront automatiquement en majuscule, c'est un moyen d'être plus "sûr" de sa syntaxe et de ses instructions.
une fois une variable déclarée, à chaque fois que celle-ci sera mentionnée, la capitalisation de celle-ci sera mise à jour conformément à celle de la déclaration.

Afin d'être un peu plus sûr de son code, il est possible de compiler le projet Visual Basic à travers le menu Déboguage.
donnez à votre code une structure claire
Pour cela, vous pouvez insérer des tabulations, autrement dit vous procéderez à l'indentation de votre texte.

l'indentation, c'est un décalage des lignes de code les unes par rapport aux autres pour rendre le code de la macro plus lisible.
Elle est possible par le biais de la touche Tabulation (Tab) ou par un bouton de la barre d'outils Edition de l'éditeur (similaire au bouton de retrait de Word ou autre). La combinaison des touches MAJ & Tabulation produit l'effet inverse, un retrait.

Comment faire ?
    - sélectionner votre texte
    - positionner le curseur dans la marge grise mais pointé vers la droite,
    - et appuyez sur la touche tabulation ou sur les boutons de la barre d'outils Edition,
    - > vous verrez le texte se décaler

Les instructions sont alors visuellement alignées par bloc, donc certains blocs sont alignés et d'autres décalés les uns par rapport aux autres.
Pour exemple, les lignes de début de déclaration d'une boucle seront alignés, alors que les instructions qui la constitue seront décalées.
Exemple d'une boucle :
    for i=1 to 10 step 1
         activesheet.cells(i,1).value=i
    next i

Il existe des petits utilitaires pour indenter le code avec une certaine logique, que vous pourrez trouverez sur Internet.

NB : pour votre information (et même si cela n'a rien à voir), ils existent aussi des outils pour l'impression du code.
créer des points d'arrêts & apprenez à utiliser les fonctions de déboguage
Les points d'arrêt servent à arrêter une procédure à un moment donnée pour prendre le temps d'examiner la valeur de certaines variables et de voir le résultat ou les dégâts obtenus jusque là. Cela permet de connaître la valeur d'une variable avant sa modification.

Afin de le créer, positionnez-vous sur une ligne d'instruction (et non pas une ligne vide), ensuite il existe au moins deux méthodes :
- utilisation de la barre d'outils "Déboguage", puis cliquez sur le bouton en forme de main pour basculer le point d'arrêt
- déplacer la souris dans la marge gauche de la feuille de code concernée à hauteur de l'instruction concernée, puis cliquer à l'endroit que vous pointez

Lorsque vous exécutez la macro, Excel exécutera toutes les instructions jusqu'à ce qu'il parvienne à la ligne où figure le point d'arrêt.
Vous pouvez bien sûr créer un point d'arrêt sur les lignes de déclaration d'une boucle.

Voir aussi le débogueur & la commande de compilation du code pour vérifier la syntaxe.
Vous pouvez notamment ajouter des espions, cela vous permet de surveiller la valeur de variables ou d'expressions (comme des expressions issues de la concaténation de variables).
Regardez à ce sujet la partie sur Visual Basic Editor, mentionnée dans cette page là.
désactiver ou activer la mise à jour de l'écran
Par défaut, excel affiche les différentes mises à jour ou les différents rafraîchissements auxquels(les) il procède. Cela produit un effet visuel que l'on peut vouloir masquer parceque le rafraîchissement n'est point beau et qu'il rallonge considérablement le temps nécessaire à l'exécution de la macro

La propriété correspondante est la propriété ScreenUpdating de l'objet Application, il faut ainsi la passer à faux/false pour la désactiver et à vrai/true pour la réactiver.
application.screenupdating=false/true (xL97 & supra)
application.miseajourecran=faux/vrai (xl95 & infra)

Attention, si vous êtes amené à utiliser des inputboxes de type 8 (qui offre la possiblité à l'utilisateur de choisir une cellule), il faudra que cette mise à jour soit active.

Voir un exemple d'application ici : Macros Diverses
NB : il est possible de la désactiver de manière systématique (pour ne pas avoir à le faire au sein de chaque macro) mais ce n'est pas recommandé. Je peux vous procurer le code en question, il doit être également "retrouvable" dans les anciennes conversations du groupe de discussion MPFE.
distinguer procédures et fonctions, c'est la prochaine partie
connaître les composantes d'un projet VBA Excel, voir plus bas
se familiariser avec Visual Basic Editor, voir ici : xl_vbe_menus.htm
en savoir plus sur la programmation vba, voir ici : xl_vba_2.htm
se familiariser avec la gestion d'erreurs, voir ici : xl_vba_2.htm
éventuellement numéroter les lignes d'instruction de vos modules !!! : news

Une fonction exécute une tâche spécifique au sein d'un programme Visual Basic et renvoie une valeur.
Elle commence par une instruction Function et se termine par une instruction End Function.

En l'absence des mentions Public, Private ou Friend devant l'instruction Function, la fonction est publique par défaut.
Vous pouvez par exemple la rendre privée pour qu'elle soit seulement appellable par une procédure d'un même module.

Elle peut accepter des paramètres optionnels ou obligatoires.
Elle peut être appelé à partir d'une autre fonction ou procédure, et elle peut appeler d'autres fonctions ou procédures.
Elle peut être utilisé comme fonction dans une formule classique.
Elle peut être appelé d'une autre macro, voir plus haut, au 5 ième point.
Elle peut retourner ou non un résultat.
On peut définir le type de résultat retourné comme on définit une variable.

Ci-dessous, vous trouverez des manières d'utiliser une fonction.

Premier exemple, la fonction "la_fonction" (je ne suis pas allé chercher bien loin le nom ;-) ).

Cette fonction accepte un paramètre obligatoire l'adresse d'une cellule et un paramètre optionnel, le facteur de multiplication, et surtout elle retourne un résultat.
Si le paramètre optionnel a été renseigné, la valeur de la cellule sera multipliée par le facteur, sinon la valeur sera retournée telle quelle.
Le test de présence d'une variable lors d'un appel à une procédure se fait avec l'instruction IsMissing.

Troisième exemple, Appel dans une formule

Ci-dessous vous verrez comment appeler la fonction la_fonction à partir d'une formule.

Une procédure Sub exécute une tâche déterminée au sein d'un programme, sans renvoyer aucune valeur explicite.
Elle commence par une instruction Sub et se termine par une instruction End Sub.

Elle aussi peut être publique ou privée (rattachée à une feuille de code précise), etc.
Vous pouvez par exemple la rendre privée pour qu'elle soit seulement appellable par une procédure d'un même module.
Si vous la déclarez privée, elle n'apparaît plus dans la liste des macros disponibles, c'est une manière de cacher une macro, cacher une procédure.

Elle peut accepter des paramètres optionnels ou obligatoires.
Elle peut être appelée à partir d'une autre fonction ou procédure, et elle peut appeler d'autres fonctions ou procédures.
Elle peut être appelée d'une autre macro, voir plus haut, au 5ième point.

Tester l'existence d'une macro : existence.htm
D'autres infos sur les fonctions : xl_fonction.htm

Chaque classeur Excel, quand il est créée, comporte un certain nombre de feuilles de calcul et un "Projet VBA" qui n'est rien d'autre qu'une coquille vide prête à recevoir du code VBA.

Pour accéder au projet VBA d'un classeur, on utilise l'éditeur « Visual Basic Editor ». Son ouverture est notamment possible par le menu en suivant ce chemin : Outils/ Macros/ Visual Basic Editor (VBE).

Une fois VBE ouvert, vous devez voir une petite fenêtre nommée "Explorateur de projet" où vous verrez pour chacun des classeurs ouverts le projet VBA qui lui correspond, nommé "VBAProject(LeClasseur)". Si cette fenêtre n'est pas ouverte, passer par le menu Affichage ou utiliser le raccourci clavier Ctrl + R.

Dans cette fenêtre, vous voyez tous les objets qui composent un projet VBA, ce sont :

Vous pouvez ainsi insérer ces objets par le menu « Insérer » ou par le biais de boutons d'une barre d'outils

Les feuilles de code vont contenir les macros à l'aide desquelles vous allez manipuler les feuilles de calcul, les différentes objets d'Excel dont les formulaires.
On pourrait dire qu'il existe deux types de feuilles de code :

En double-cliquant sur l'objet thisworkbook qui représente le classeur Excel ou sur les objets Feuil1, Feuil2, Feuil3 (etc.), vous avez également accès à la feuille de code propre à ces objets. Vous devez y insérer du code dans ces derniers seulement si vous voulez associer votre code à l'avènement de certaines actions produites sur ces objets. Cela revient à créer une procédure dite événementielle. Le cas contraire, le code devra être placé dans une feuille de code standard, un module, ou un module de classe.

Les formulaires sont des objets graphiques dont vous pouvez personnaliser l'aspect, le comportement et le contenu. En effet, il est possible d'avoir un formulaire avec une couleur particulière, qui soit centré à l'écran, et à travers lequel l'utilisateur peut consulter et saisir des données : visualiser un graphique, saisie des fiches produits, etc. Un formulaire peut également contenir des objets comme des boutons radios, des cases à cocher, etc. D'autres informations figurent ci-après.

Au passage, expliquons la nature des objets que sont les boîtes de dialogue modales & non modales, message du 18 Novembre 1999 dans le groupe de discussion microsoft.public.fr.excel :

Avant de vous laisser consulter les pages de Microsoft, je vais vous faire part des quelques réflexions que j'ai pu trouvé ici & là :

Message de Didier L., le jeudi 23 septembre 1999 01:30 sur microsoft.public.fr.excel

Cela sert à créer une classe, qui est la définition formelle d'un objet (méthodes et propriétés).
A l'exécution, une instance de l'objet est créée à partir de ce modèle. Une classe objet possède en outre 2 procédures événementielles - Initialize et Terminate - qui sont déclenchées respectivement lors de la création (Set ... New) et la destruction (Set ...Nothing).

Puisque VBA, comme VB, ne gère pas encore l'héritage (créer un nouvel objet à partir d'une classe existante en ne la modifiant qu'en partie), le principal intérêt est d'encapsuler données et procédures en mémoire (accès protégé). Autre avantage, la réutilisation ou le partage des classes objets.

Un p'tit exemple simpliste pour illustrer:
imaginons un objet dont la seule méthode est d'afficher un message "bonjour". Dans un module de classe, il suffit de déclarer une procédure (on la déclare publique pour qu'elle soit accessible - si on voulait ajouter des propriétés, il faudrait utiliser Property Let et Property Get : voir message de news par Laurent L.

'soit un module de classe "class1" contenant la procédure suivante
Sub AffMsg
MsgBox "Bonjour"
End Sub

'on peut alors écrire dans un module public le code suivant
Sub toto
Dim monObj as Object
Set monObj = New Class1 'initialise une nouvelle instance de la class1, 'ce qui déclenche la procédure Initialize
monObj.AffMsg 'déclenche la procédure AffMsg de l'objet
Set monOgj = Nothing 'libère la mémoire et détruit l'instance '(procédure Terminate)
End Sub

J'ajouterais que les modules de classe sont également nécessaires pour définir des procédures événementielles de niveau 'Application'. Une utilisation intéressante réside aussi AMA dans la création d'objets de type "WorksheetFunction" rassem<blant ses propres fonctions personnalisées VBA.

Message de Chip Pearson à ce sujet, le 26 novembre 1999 sur le groupe microsoft.public.excel.programming : news
Il dit notamment que vous pouvez aussi créer des types de données de ce genre dans un module standard en utilisant l'instruction Type, mais les réaliser dans un module de classe vous donnera plus de contrôles sur celles-ci.

C'est un classeur créé par Excel et stocké dans le répertoire \program files\Microsoft Office\office\xlouvrir lors du premier enregistrement de macros. Ainsi à chaque ouverture d'Excel, ce classeur est ouvert mais attention vous ne le voyez pas car il est masqué par défaut. Vous pouvez écrire dans ce classeur des macros auxquelles vous voulez pouvoir accéder à partir de différents classeurs.

Comment appeler une procédure sub se trouvant dans le classeur "Perso.xls" (13/09/99, by L.L.)

Une macro complémentaire peut être considéré comme un véritable composant pouvant être installé sur votre ordinateur pour ajouter des commandes et des fonctions à Excel. Ces macros sont propres au programme Excel. Cela peut également être ce qu'on appelle des macros COM (Component Object Model, modèle d'objet composant).

C'est en fait est un classeur Excel, il possède lui aussi des objets, des modules et des feuilles de calcul.
Le classeur xla est un classeur de même structure qu'un classeur xls et contient donc obligatoirement des feuilles, même si elles peuvent être vides.

Comment crééer une macro complémentaire ?
1. Enregistrer votre fichier excel au format xla : Fichier -> Enregistrer sous -> Type de fichier = "Macro complémentaire Microsoft Excel (*.xla)"
2. Ouvrir Visual Basic Editor.
3. Dans l'explorateur de projet, sélectionner "thisworkbook" pour le projet concerné.
4. Fixer la propriété IsAddin à VRAI. Ceci masquera toutes les feuilles de votre classeur.
5. Sauvegarder le projet, votre macro complémentaire est prête !

Si vous le stockez dans C:\Program Files\Microsoft Office\Office\Modèles, vous verrez votre fichier en allant dans outils/macros complémentaires
Les macros complémentaires XLA sont masquées par défaut et seul leur code est visible dans l'éditeur VBA.

Que se passe-t-il alors ?

En fait, quand on vient d'enregistrer un classeur standard comme macro complémentaire, on reste dans le classeur standard. Le fichier XLA est créé à part, mais il n'est pas ouvert. Pour ouvrir ensuite le fichier XLA qui vient d'être créé, il faut fermer le classeur qui a servi à le construire (inutile de l'enregistrer, d'ailleurs) et ouvrir ensuite la macro complémentaire, de préférence par Outils -> Macros complémentaires.

Sous Excel 97 et sup, les XLA sont simplement des classeurs "normaux" masqués, dont la propriété 'IsAddIn' est égale à True. Comme .xla est une extension de macro, ces classeurs sont automatiquement affichés dans la boîte de dialogue du gestionnaire de macro complémentaire, au même titre que les XLL. C'est leur seul avantage. Un classeur XLS dont on mettrait la propriété IsAddin sur True se comporterait exactement comme un classeur XLA. Ceux-ci ne sont ni plus rapides, ni plus compacts.

Avec les versions antérieures (Excel 5 et 95), les XLA étaient "cryptés" (bien que très facilement déplombables) à partir de classeurs standard. Il était donc nécessaire de conserver les classeurs XLS d'où étaient tirées les macros XLA pour pouvoir visualiser et modifier le code. Ce n'est plus du tout le cas à partir d'Excel 97.
On peut très bien enregistrer le code dans un classeur séparé des données, le classeur contenant le code peut être enregistré au format XLA puis installé en macro
complémentaire. Pour les versions antérieures à Excel 97, il faut conserver le classeur Excel original, parce qu'on ne peut plus modifier le code une fois compilé en macro complémentaire. Je conseille de désinstaller une macro complémentaire avant d'en réinstaller une version plus récente.

Fermer les classeurs référencés à la fermeture du classeur qui les utilise: news

Pour une procédure	Pour une fonction
sub Ma_Macro() end sub	function Ma_Fonction() end function