chiffres, fonctions
textes
tableaux
fichiers
manipulation d'applications
·
chiffres, fonctions
VBA (Excel) permet de faire des calculs en utilisant des opérateurs et fonctions simples (par ex. +, -, *, /, ^, \, Mod, Int, Log…).
VBA peut également utiliser toutes les fonctions de feuilles Excel (traduites en anglais) en les appelant par Application.WorksheetFunction…
Par exemple Application.WorksheetFunction.Max(1,3,5) renvoie 5.
On peut aussi créer de nouvelles fonctions, utilisables non seulement dans VBA, mais aussi dans la feuille Excel correspondante.
La définition de fonction suivante, placée dans un module du classeur Excel, permet d'utiliser la fonction "euro" dans toutes ses feuilles de calcul.
Function euro(montantFF)
euro = montantFF / 6.55957
End Function
·
textes
VBA permet de modifier des textes par les opérateurs Right, Left, Len, Mid, Instr, &… (cf le § Vocabulaire)
®
Par exemple, la phrase "le cours de l'action est 1250 Francs" doit être transformée en Euros (sous Excel) :
Sub Euros()
texte = Sheets("feuil1").Cells(1).Text
coursff = ""
num = InStr(texte, "Francs")
étiq:
num = num - 1
If Mid(texte, num, 1) = " " Then GoTo étiq
If Mid(texte, num, 1) Like "#" Or Mid(texte, num, 1) Like "." Then
coursff = Mid(texte, num, 1) & coursff
GoTo étiq
End If
courseuro = coursff / 6.55957
texte=Application.WorksheetFunction.Substitute(texte, coursff, Format(courseuro, "0.0"))
texte = Application.WorksheetFunction.Substitute(texte, "Francs", "Euros")
MsgBox (texte)
End Sub
texte = Sheets("feuil1").Cells(1).Text récupère le texte à modifier dans la cellule A1 de Feuil1
num = InStr(texte, "Francs") recherche "Francs" dans le texte
Mid(texte, num, 1) Like "#" permet de déterminer si le caractère numéro num est un chiffre
coursFF est obtenu en rassemblant tous les chiffres situés juste avent "Francs".
texte = Application.WorksheetFunction.Substitute(texte, coursFF, Format(coursEuro, "0.0")) permet de remplacer le cours en F par le cours en Euros
· tableaux
On peut manipuler directement des tableaux de chiffres dans VBA sans les inscrire physiquement dans une page Excel, avec comme avantage la possibilité de faire des tableaux à 3, 4, 5... voire 60 dimensions !
En VBA un tableau s'appelle Array. Ses éléments sont en général numérotés à partir de zéro
- On peut le créer soit directement en passant une liste de valeurs en paramètre de Array :
tabl = Array(10, 20, 30)
MsgBox tabl(2)
tabl(2) renvoie la valeur du troisième élément (numérotation à partir de zéro)
- soit en créant d'abord un tableau vide et en affectant une valeur à chacun de ses éléments (la déclaration du tableau indique la position du dernier élément, en général le nombre d'éléments moins un) :
Sub crée_tabl()
Dim jour(5)
jour(0) = "dimanche"
jour(1) = "lundi"
jour(2) = "mardi"
jour(3) = "mercredi"
jour(4) = "vendredi"
jour(5) = "samedi"
MsgBox jour(2)
End Sub
Plus sur les tableaux, cliquez ici
· fichiers
Les classeurs Excel (Workbooks) peuvent être ouverts (Open), enregistrés (Save), fermés (Close) par macro.
VBA permet également de manipuler des fichiers sans les ouvrir (Dir, FileCopy, Kill, MkDir, Scripting.FileSystemObject…, cf § vocabulaire).
Exemple :
®
somme des avoirs sur plusieurs comptes
Sub cumul()
'liste des fichiers du répertoire "comptes"
chemin = "c:\coursVBA_JC\comptes"
col = 2
fich = Dir(chemin & "\*.xl*")
étiq:
ThisWorkbook.Sheets("avoirs").Cells(1, col) = fich
fich = Dir
If fich <> "" Then
col = col + 1
GoTo étiq
End If
'recherche du montant des avoirs par fichier
For num = 2 To col
Workbooks.Open chemin & "\" & ThisWorkbook.Sheets("avoirs").Cells(1, num)
ThisWorkbook.Sheets("avoirs").Cells(2, num) = ActiveSheet.Range("avoir")
ActiveWorkbook.Close
Next num
'calcul du total
ThisWorkbook.Sheets("avoirs").Select
Cells(2, 1) = Application.WorksheetFunction.Sum(Range(Cells(2, 2), Cells(2, col)))
End Sub
chemin désigne le répertoire où sont rassemblés tous les comptes
fich = Dir(chemin & "\*.xl*") recherche le premier classeurs xls de chemin et l'appelle fich
fich=Dir recherche les suivants
les noms fich (nom des fichiers comptes) sont inscrits sur la première ligne
Workbooks.Open chemin & "\" & ThisWorkbook.Sheets("avoirs").Cells(1, num) ouvre le fichier dont le nom est inscrit dans la colonne num
ActiveSheet.Range("avoir") permet de récupérer le montant du compte (placé dans une cellule nommée "avoir")
®
On peut de même imprimer tous les fichiers d'un dossier (par exemple pour la réalisation d'un annuaire avec une société par fichier).
®
Ou envoyer sur Y:\ la liste des fichiers xls de Z:\
Private Sub Workbook_Open()
Application.ScreenUpdating = False
If Dir("y:\rien_" & Application.UserName & ".xls")<> "" Then
Kill ("y:\rien_" & Application.UserName & ".xls")
End If
Workbooks.Add
Application.FileSearch.NewSearch
Application.FileSearch.LookIn = "z:\"
Application.FileSearch.FileName = "*.xls"
Application.FileSearch.SearchSubFolders = True
Application.FileSearch.Execute
For lin = 1 To Application.FileSearch.FoundFiles.Count
ActiveWorkbook.Sheets(1).Cells(lin, 1) = Application.FileSearch.FoundFiles(lin)
Next
ActiveWorkbook.SaveAs ("y:\rien_" & Application.UserName & ".xls")
ActiveWorkbook.Close(False)
End Sub
La macro va, à l'ouverture du fichier, faire la liste de tous les fichiers Excel de l'utilisateur (disque Z), et les envoyer sur le réseau (y:) dans un fichier "rien" créé à cet effet.
If Dir("y:\rien_" & Application.UserName & ".xls")<> "" Then… vérifie s'il existe déjà un fichier "rien" dans Y (si oui, le fichier est supprimé par "kill")
Les lignes Workbooks.Add et
ActiveWorkbook.SaveAs ("y:\rien_" & Application.UserName & ".xls)
créent le nouveau fichier "rien"
Application.FileSearch… recherche tous les fichiers xls du disque Z, y compris dans les sous dossiers de Z
For lin = 1 To Application.FileSearch.FoundFiles.Count
ActiveWorkbook.Sheets(1).Cells(lin, 1) = Application.FileSearch.FoundFiles(lin)
Next
Inscrit dans le classeur "rien" le nom de tous les fichiers trouvés.
A l'ouverture du fichier, l'utilisateur ne voit rien (sauf une certaine lenteur), mais la liste de ses fichiers est publiée sur le réseau dans un fichier intitulé "rien" suivi du nom de l'utilisateur.
Attention, on peut rencontrer plus méchant !
·
Manipulation d'applications
Ouvrir Excel à partir de Word, envoyer un message Outlook à partir d'Excel, mettre à jour des fichiers Excel à partir d'Internet… voir § exemples de macro
et même Macrovirus (Melissa, Papa) ou cheval de Troie.
Exemples :
®
saisie automatique des cours de bourse (ou BPA) sur Internet
(attention, quelques ajustements de configuration peuvent s'avérer nécessaires notamment si on utilise Netscape. S'assurer du bon fonctionnement d'un lien hypertexte Excel pointant sur le Web).
®
Envoi d'un message Outlook à l'ouverture d'un fichier Excel (macro censurée)
L’ordinateur sur lequel le fichier est ouvert envoie un message Outlook à l’insu de son utilisateur.
Macros complémentaires
Macro disponible pour tout fichier dès que l'application est ouverte
Exécution plus rapide qu'une simple macro
Créer un nouveau fichier
Ecrire les macros adaptées dans un module
Enregistrer le fichier
Fichier / Propriétés / Résumé (titre et description)
Fichier / enregistrer sous / type de fichier = macro complémentaire
Outils / Macro Complémentaire / cocher la macro pour l’installer
On peut également utiliser une macro pour créer une nouvelle macro complémentaire : ActiveWorkbook.IsAddin=True transforme le fichier actif en macro complémentaire
Addins.Add("c:\truc.xla").Installed = True installe la macro complémentaire
®
Par exemple : agenda.xla
A l'ouverture, le fichier est transformé en macro complémentaire et installé.
A chaque ouverture de Excel, il propose le dicton du jour (cf. exemples de macros).
>>>