Macro Excel - mémento

Détails: Catégorie : w.com; Publié le mercredi 9 novembre 2016 16:11; Écrit par jean-marc stoeffler; Affichages : 5987

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

ActiveSheet.Previous.Select

fichier suivant

ActiveWindow.ActivateNext

fichier précédent

ActiveWindow.ActivatePrevious

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) = "," xVals = Mid(xVals, 2) Loop 'Attach a label to each data point in the chart. For Counter = 1 To Range(xVals).Cells.Count ActiveChart.SeriesCollection(Série).Points(Counter).HasDataLabel = _ True ActiveChart.SeriesCollection(Série).Points(Counter).DataLabel.Text = _ Range(xVals).Cells(Counter, 1).Offset(0, -1).Value Next Counter Next Série 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" Else 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.

W.COM

Navigation de recherche

Navigation

Recherche

Ce site

Macro Excel - mémento

Memento macros VBA EXCEL