Macro Excel - mémento
- Détails
- Catégorie : w.com
- Publié le mercredi 9 novembre 2016 16:11
- Écrit par jean-marc stoeffler
- Affichages : 5710
Memento macros VBA EXCEL
Voici ma page de référence que je me constitue petit à petit et dans laquelle je puise régulièrement des instructions prêtes à l'emploi :
Télécharger le fichier Excel : mémento VBA Excel
| objet | instruction | commentaire | |||||
| mettre à jour tous les tableaux croisés dynamiques | ActiveWorkbook.RefreshAll | à glisser dans une macro liée à la feuille de calcul de la base de données (bouton droit sur l'onglet de la feuille > visualiser le code | |||||
| poser une question et arrêter le programme si réponse "non" | if msgbox("bla, bla (question) ?", vbInformation + vbYesNo, "titre de la fenêtre") <> vbyes then exit sub | remplacer "blabla" par votre question et "titre de la fenêtre" par votre nom d'appli, par exemple. | |||||
| ne pas mettre à jour l'écran d'Excel | Application.ScreenUpdating = False | pour accélérer le déroulement de la macro | |||||
| vers la cellule du dessous | ActiveCell.Offset(1, 0).Select | ActiveCell.Offset(Y, X).Range("A1").Select Y ligne(s), X colonne(s) |
|||||
| vers la cellule de gauche | ActiveCell.Offset(0,-1).Select | idem | |||||
| numéro de la ligne active | ActiveCell.Row | Row = ligne | |||||
| numéro de la colonne active | ActiveCell.Column | ||||||
| valeur d'une cellule de la ligne ... | Cells(ligne, colonne).Value | noter le S à Cells | |||||
| aller sur cette cellule | Cells(ligne, colonne).Select | ||||||
| exemple : | Cells(ActiveCell.Row, 6).Range("A1").Value = Now() | affecte à la 6ème colonne de la ligne courante la valeur date/heure système | |||||
| dernière ligne de la feuille | ActiveSheet.UsedRange.Rows.Count | ||||||
| dernière colonne de la feuille | ActiveSheet.UsedRange.Columns.Count + 1 | bizarement c'est l'avant dernière alors que pour la colonne c'est bon, d'où le plus 1 | |||||
| adresse dernière cellule | Selection.SpecialCells(xlCellTypeLastCell).Address | avec "A1:" & cette adresse on a toute la zone de recherche | |||||
| déplacer une feuille | Sheets(1).Move After:=Sheets(3) | ici on déplace la feuille 1 après la 3ème... | |||||
| ouvre la boite de dialogue "ouvre fichier" | Application.Dialogs(xlDialogOpen).Show | voir exemple | |||||
| nom de l'utilisateur dans Excel (fichier->option->général) | application.username | ||||||
| valeur d'aujourd'hui | Now() | ||||||
| nom du fichier actif | ActiveWorkbook.Name | ||||||
| nom du fichier qui contient la macro | ThisWorkbook.Name | exemple d'utilisation (pour qu'une macro événementielle ne se déclenche pas intempestivement !) : If ActiveWorkbook.Name <> ThisWorkbook.Name Then End |
|||||
| faire apparaitre un UseForm | nom_du_Useform.Show | ||||||
| Masquer un UserForm | nom_du_Useform.Hide | ||||||
| nombre de feuilles (visibles ou cachées) | worksheets.count | ||||||
| nombre de fichier ouverts (visibles ou cachées) | Workbooks.Count | ||||||
| Date et heure système | Now | ||||||
| Date et heure système | Time | ||||||
| Date de modification du fichier | FileDateTime(Nom complet du fichier) | (même après son ouverture) | |||||
| pas d'alertes | Application.DisplayAlerts = false | désamorce les questions (comme des suppressions de feuilles) | |||||
| avec alertes | Application.DisplayAlerts = True | rétablit les alerte | |||||
| opération sur toutes les feuilles | dim ws as worksheet For Each ws In Worksheets:MsgBox ws.Name:Next ws |
||||||
| opération sur toutes les cellules d'une zone nommée | Dim Cellule as Range For Each Cellule In Range("nom_zone").Cells etc... Next |
||||||
| sortir d'une boucle avant la fin | Exit For | bien approprié avec le "for each cellule... | |||||
| écrire dans la barre d'état | Application.StatusBar = votre_message | ||||||
| sélection le fichier fichier1.xlsx qui doit être ouvert | Windows("fichier1.xlsx").Activate | ||||||
| feuille suivante | ActiveSheet.Next.Select | ||||||
| précédente | ActiveSheet.Previous.Select | ||||||
| fichier suivant | ActiveWindow.ActivateNext | ||||||
| fichier précédent | ActiveWindow.ActivatePrevious | ||||||
| login de conenxion windows | Utilsateur = Environ("username") | ||||||
| demander une valeur | nombre=inputbox("taper votre nombre...") | ||||||
| taille du fichier ! | FileLen(ActiveWorkbook.FullName) | ||||||
| date du fichier enregistré | FileDateTime(nom absolu du fichier) | ||||||
| masquer un classeur | ActiveWindow.Visible = False | (attention -> pour le faire réapparaître affichage-> afficher) | |||||
| pour les macro événementielle comme "calculate" : pour lui dire de ne pas exécuter la macro si le fichier n'a pas le focus | If ActiveWorkbook.Name <> ThisWorkbook.Name Then Exit Sub | ||||||
| effacer le contenu d'un groupe de cellules nommé | Range("nom_zone").ClearContents | ||||||
| exemple : macro compléte pour étiquettes de nuage de points (indispensable !) | Sub NuageEclairé() Dim Counter As Integer, ChartName As String, xVals As String, Série As Integer, Message As String On Error GoTo FIN For Série = 1 To 256 'Store the formula for the first series in "xVals". xVals = ActiveChart.SeriesCollection(Série).Formula 'Extract the range for the data from xVals. xVals = Mid(xVals, InStr(InStr(xVals, ","), xVals, _ Mid(Left(xVals, InStr(xVals, "!") - 1), 9))) xVals = Left(xVals, InStr(InStr(xVals, "!"), xVals, ",") - 1) Do While Left(xVals, 1) = ","<br /> xVals = Mid(xVals, 2)<br /> Loop<br /> 'Attach a label to each data point in the chart.<br /> For Counter = 1 To Range(xVals).Cells.Count<br /> ActiveChart.SeriesCollection(Série).Points(Counter).HasDataLabel = _<br /> True<br /> ActiveChart.SeriesCollection(Série).Points(Counter).DataLabel.Text = _<br /> Range(xVals).Cells(Counter, 1).Offset(0, -1).Value<br /> Next Counter<br /> Next Série<br /> MsgBox "série = " & Série & " Fin" Exit Sub FIN: If Série = 1 Then MsgBox "Sélectionner un Graphique avant de lancer la macro !" Else If Série = 2 Then Message = "Une seule série traitée"<br /> Else<br /> Message = Série - 1 & " séries traitées" End If MsgBox Message End If End Sub |
||||||
| permet de sauvegarder un fichier avec son nom, même s'il est caché | Workbooks(nom du fichier).Save | ||||||
| permet de fermer un fichier avec son nom, même s'il est caché | Workbooks(nom du fichier).close | ||||||
| ferme un fichier sans poser de question (il faut être sûr de soi... car sinon, après, on n'a plus que les yeux pour pleurer) | Windows(nom du fichier).Close SaveChanges:=False | ||||||
| exemple : somme d'une colonne | Sub Somme_Colonne_H() '--- exemple Dim Cellule As Range Dim Somme, valeur As Double Dim Zone As String '--------------------- calcule la limite du nombre de lignes max de la feuille Zone = "H1:H" & ActiveSheet.UsedRange.Rows.Count For Each Cellule In Range(Zone).Cells On Error Resume Next 'pour éviter les erreurs sur des cellules contenant du texte valeur = 0 valeur = Cellule.Value Somme = valeur + Somme Next ActiveCell = Somme End Sub |
|
|||||
| Une astuce que j’utilise surtout pour faire patienter les utilisateurs qui pourraient penser que la macro est plantée ou tourne en rond : utilisation de la fonction Application.StatusBar = " texte" • affichage dans la barre d’état du défilement des étapes du traitement, par exemple « Traitement des doublons 1/4 » puis « Suppressions des colonnes inutiles 2/4 » etc… • affichage dans la barre d’état de la progression du traitement en pourcentage, par exemple « "Suppressions des doublons en cours... " & Format(cptrlig / nbligne, "0.00%") » avec les variables cptrlig = ligne en cours de traitement et nbligne = le nombre de lignes à traiter. • Ou tout autre affichage qui progresse au fur et à mesure du traitement. Cela prend un peu de temps de traitement mais c’est rassurant pour l’utilisateur et également pour le développeur. • NE PAS OUBLIER de terminer par application.StatusBar = False pour rendre la main à Excel. |
|||||||