Macro Excel - mémento

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.