Votre question

[Tutoriel] Excel/Macro VBA : Trucs et astuces

Tags :
  • element
  • Programmation
  • VB
Dernière réponse : dans Programmation
a b L Programmation
5 Juillet 2007 15:24:08

Excel/Macro VBA: Trucs et astuces

Je vous invite à compiler ici les trucs tout bêtes, pour se faciliter la vie lorsqu'on développe de petites macros sous Excel.
(Pour demander de l'aide, prière de créer un autre sujet : [Nouveau Sujet])

Option Explicit

Ne jamais oublier de mettre l'Option Explicit.
(Voir l'aide de VBA/Excel).


Itération dans une collection

Pour parcourir tous les objets d'une collection :
(Voir la définition d'une collection dans l'aide de VBA/Excel.)
  1. Dim element As TypeDeLaCollection
  2. For Each element In Collection
  3. ..
  4. Next

Assez intelligemment, une collection porte comme nom le pluriel du type des objets de la collection. En général...
Exemple, la collection des feuilles de calcul d'un classeurs s'appelle Worksheets, alors que le type feuille de calcul est Worksheet.

Quelques objets Excel

Les objects importants sont :
  • L'application Excel elle-même. L'objet prédéfini est Application

  • Le classeur. De type WorkBook. L'application possède une collection de classeurs notée WorkBooks. Un classeur particulier de cette collection peut être désigné par son nom ou son numéro :
    1. WorkBooks(2)
    2. WorkBooks("Classeur1")


  • La feuille de calcul. De type WorkSheet. Le classeur possède une collection de feuilles de calcul notée WorkSheets. Une feuille de calcul particulière peut être désignée par son nom ou son numéro :
    1. WorkSheets(2)
    2. WorkSheets("Classeur1")


  • Les cellules de la feuille de calcul. De type Range. Attention, Range n'est pas une cellule, mais un ensemble de cellules. Il n'y a pas de classe d'objet définie pour désigner une cellule en VBA/Excel ! Une feuille de calcul possède une collection de cellules notée Cells. Une cellule particulière peut être désignée par ses coordonnées ligne x colonne.
    1. Cells(row, col)
    Il est souvent plus agréable d'accéder à un ensemble de cellules (quitte à ce que cet ensemble ne soit qu'une seule cellule) en donnant son adresse. On utilise alors la méthode Range de l'objet WorkSheet :
    1. Range("A1")
    2. Range("A1:B2")
    3. Range(Cells(1, 1), Cells(4, 5))


    Lorsqu'on ne travaille que dans un seul classeur, une seule feuille à la fois, Excel considère le classeur actif, la feuille active.
    Il n'est alors pas utile de préciser quoi que ce soit.

    Lorsqu'on travaille dans plusieurs classeurs, dans plusieurs feuilles à la fois, on serait tenté d'activer tel classeur, telle feuille pour ne pas avoir à y faire référence. C'est souvent une erreur. Les changements de classeurs, de feuilles, de sélections engendrent énormément de recalculs de la part d'Excel qui ne sont pas justifiés. Il est bien plus judicieux de préciser à quel classeur, à quelle feuille on fait référence. Cela alourdi un peu le code, mais ne le complique absoluement pas, au point de vue de l'exécution. Ainsi, pour accéder à la cellule B3, de la feuille Feuil2 du classeur Classeur1 on écrit :
    1. WorkBooks(1).WorkSheets(2).Cells(3, 2)
    2. WorkBooks("Classeur1").WorkSheets("Feuil2").Range("B3")
    Ces lignes sont équivalentes, et les différentes notations peuvent être panachées.


    Désigner la ligne 12, les deux premières lignes, la colonne B, la troisière colonne (C), les colonnes de A à F :
    (Tous ces exemples renvoient un objet Range)
    1. Rows(12)
    2. Rows("1:2")
    3. Columns("B")
    4. Columns(3)
    5. Columns("A:F")



    Copier une zone (Range) vers une autre, de B1 vers B2, de B1 vers B2 en incrémentant, toute une zone de B1:B12 vers D1:.., d'une feuille à l'autre, d'un classeur à l'autre :
    1. Range("B2").Value = Range("B1").Value
    2. Range("B2").Value = Range("B1").Value + 1
    3. Range("B1:B12").Copy Destination:=Range("D1")
    4. WorkSheets("Feuil2").Range("B1:B12").Copy Destination:=WorkSheets(1).Range("D1")
    5. WorkBooks(2).WorkSheets("Feuil2").Range("B1:B12").Copy Destination:=WorkBooks("Classeur1").WorkSheets(1).Range("D1")



    ....
    à suivre
  • Autres pages sur : tutoriel excel macro vba trucs astuces

    a b L Programmation
    17 Juillet 2007 20:12:52

    Base de données sous Excel

    Vous prétendez utiliser Excel comme BDD ?
    Exécutez ceci avant tout, il le dit mieux que moi.
    1. Sub ExcelAsDB()
    2. Dim t As Balloon
    3. Assistant.On = True
    4. Set t = Assistant.NewBalloon
    5. t.Animation = 11
    6. t.Button = 1
    7. t.Heading = StrReverse(".ruelbat nu tse lecxE")
    8. t.Text = StrReverse(".seénnod ed esab ed eriannoitseg nu sap tse'n eC")
    9. t.Show
    10. Assistant.Visible = False
    11. Set t = Nothing
    12. Assistant.On = False
    13. End Sub
    18 Juillet 2007 09:33:38

    Zeb fait de l'humour. ca devrait être fournit par défaut dans l'assistant :D 

    Edit : Je tacherai de rajouter des trucs.
    Contenus similaires
    a b L Programmation
    18 Juillet 2007 11:04:53

    :D 

    Blague à part, ce topic est aussi le tien. :o  Si tu as des trucs, des astuces, merci de contribuer. Je te demande juste de garder une certaine homogénéité dans la forme : titre explicite en taille 2, baratin court et factuel, exemple simple.
    a b L Programmation
    19 Juillet 2007 17:46:57

    La dernière ligne

    Comment déterminer la dernière ligne d'un bloc de cellules.
    1. ' // Hypothèse : La cellule A1 fait partie de ce bloc de cellules
    2. Range("A1").CurrentRegion.SpecialCells(xlCellTypeLastCell).Row


    Comment déterminer la dernière ligne non vide pour une colonne donnée :
    1. ' // <c> est le numéro de la colonne.
    2. Cells(Rows.Count, c).End(xlUp).Row


    Comment déterminer la prochaine ligne vide par rapport à une cellule donnée :
    1. ' // Soit A1, la cellule considérée.
    2. Range("A1").End(xlDown).Row + 1


    -------------------


    Les exemples précédents renvoient un numéro de ligne. Voici comment renvoyer les lignes au sens Excel (type Range) :
    1. Range("A1").CurrentRegion.SpecialCells(xlCellTypeLastCell).EntireRow
    2. Cells(Rows.Count, c).End(xlUp).EntireRow
    3. Range("A1").End(xlDown).Offset(1).EntireRow


    -------------------


    Une autre façon de faire, consiste à rechercher quelque chose avec la méthode Find() :
    1. Columns(c).Find("*", searchdirection:=xlPrevious)

    Attention, au contraire des exemples précédents, cette méthode peut ne rien renvoyer. Il faut donc vérifier si quelque chose à été trouvé :
    1. Dim der As Range
    2.  
    3. der = Columns(1).Find("*", searchdirection:=xlPrevious)
    4. If Not der Is Nothing Then
    5. ...


    (Remerciement à Djorge84 pour m'avoir rappelé cette façon de faire.)
    a b L Programmation
    25 Juillet 2007 14:18:20

    Supprimer des lignes dans un tableau

    Soit le tableau suivant :
    1. AAAA
    2. BBBB
    3. CCCC


    L'algorithme suivant est faux :
    1. Pour i de 1 à 3
    2. Si ConditionSurLigne(i) Alors
    3. SupprimerLigne(i)
    4. Suivant


    En effet, supposons que la condition nous fasse vouloir supprimer les lignes AAAA et BBBB.
    Déroulons le code:
  • i = 1
    [1] AAAA <-- i
    [2] BBBB
    [3] CCCC
    La condition est vrai. Supprimons la ligne 1. Pour tout, i, la ligne i+1 devient la ligne i.
    Nous avons donc le tableau suivant à la fin du premier tour :
    [1] BBBB <-- i
    [2] CCCC

  • i = 2
    [1] BBBB
    [2] CCCC <-- i
    Et hop, première erreur, on vient de rater la ligne des BBBB ! Cette ligne ne sera pas testée.
    C'est la ligne 2, celle des CCCC qui sera testée. La condition n'est pas remplie, le 2-ième tour est fini.

  • i = 3
    [1] BBBB
    [2] CCCC
    <-- i
    Et hop, encore une erreur, il n'y a plus de ligne 3.
    Nous venons de rencontrer une erreur de débordement.
    Qui sait ce que nous sommes en train de tester, et peut-être de supprimer !?


    La solution : Partir du bas du tableau et remonter
    1. Pour i de 3 à 1
    2. Si ConditionSurLigne(i) Alors
    3. SupprimerLigne(i)
    4. Suivant


    Traduit en VBA/Excel

    Note: Il n'y aura pas, à proprement parler, d'erreur de débordement sous Excel. Même si vous supprimez une ligne ou une colonne, une autre apparaîtra tout en bas ou tout à droite pour qu'il y ait toujours 65536 lignes x 256 colonnes dans la feuille. Mais vous risquez quand même de supprimer des cellules qui n'auraient pas dû l'être.


    1. For i = 3 To 1 Step -1
    2. If ConditionSurLigne(i) Then Rows(i).Delete
    3. Next
    a b L Programmation
    27 Juillet 2007 16:14:01

    Ce fichier existe-t-il ?

  • Une technique ancienne consistait à utiliser la fonction Dir.
    A proscrire ! Dir scanne tout le répertoire pour y trouver le fichier et peut générer une empreinte mémoire importante (i.e. il n'est pas garanti que des descripteurs de fichiers ou autres n'y restent pas alloués) : ce qui est légitime quand on veut parcourir tout un dossier, l'usage normal de cette fonction.

  • Une autre technique consiste à lire les attributs d'un hypothétique fichier avec la fonction Getattr et de vérifier si une erreur a été générée (ce fichier n'existe pas) ou pas (ce fichier existe).

  • Voici une technique moderne, si vous disposez de SCRRUN.DLL :

    Code générique :
    1. Function FileExists(sFileName As String) As Boolean
    2. Dim FSO
    3. Set FSO = CreateObject("Scripting.FileSystemObject")
    4. FileExists = FSO.FileExists(sFileName)
    5. End Function


    Si vous vous donnez la peine d'ajouter Microsoft Scripting Runtime (SCRRUN.DLL) à votre projet, le code peut être encore un peu plus sobre :
    1. Function FileExists(sFileName As String) As Boolean
    2. With New Scripting.FileSystemObject
    3. FileExists = .FileExists(sFileName)
    4. End With
    5. End Function
    a b L Programmation
    30 Juillet 2007 16:04:19

    Comment faire pour automatiser Microsoft Excel à l'aide de Visual Basic

    Ce n'est pas moi qui le dit, c'est Microsoft : 219151
    a b L Programmation
    25 Janvier 2008 14:47:40

    Cet onglet existe-t-il ?

    On peut vouloir savoir si une feuille existe dans un classeur quand on connaît le nom qu'elle devrait avoir.

    Une technique répandue consiste à appeler l'onglet sans plus de précaution, et à gérer l'erreur si celui-ci n'existe pas.

    Je propose ici une technique bien plus élégante, qui consiste à parcourir toutes les feuilles à la recherche de celles qui nous intéresse. Inutile de les activer une par une, pour connaître le nom de celle qui est active. Il suffit de considérer directement la collection Worksheets :
    1. Function WorksheetExists(ByVal Name As String, Optional wb As Workbook) As Boolean
    2. Dim ws As Worksheet
    3.  
    4. WorksheetExists = False
    5.  
    6. If wb Is Nothing Then Set wb = ActiveWorkbook
    7.  
    8. For Each ws In wb.Worksheets
    9. If ws.Name = Name Then
    10. WorksheetExists = True
    11. Exit For
    12. End If
    13. Next
    14. End Function


    Plus génériquement, les onglets sous Excel (Sheets) peuvent être des feuilles de calculs (Worksheet) ou des graphiques (Chart) - (ou autres : DialogSheet et feuilles de macros pour Excel 4.0).
    On peut donc ne pas se restreindre aux feuilles de calcul en créant deux autres fonctions, l'une pour les graphiques, l'autre pour les onglets.

    Encore mieux, une fonction paramétrée qui regroupe les trois :
    1. Enum seCollection
    2. se_Sheets
    3. se_Worksheets
    4. se_Charts
    5. End Enum
    6.  
    7. Function SheetExists(ByVal name As String, Optional wb As Workbook, Optional collection As seCollection) As Boolean
    8. Dim s As Object
    9. Dim sCol As Sheets
    10.  
    11. SheetExists = False
    12.  
    13. If wb Is Nothing Then Set wb = ActiveWorkbook
    14.  
    15. If collection = se_Worksheets Then
    16. Set sCol = wb.Worksheets
    17. ElseIf collection = se_Charts Then
    18. Set sCol = wb.Charts
    19. Else
    20. Set sCol = wb.Sheets
    21. End If
    22.  
    23. For Each s In sCol
    24. If s.name = name Then
    25. SheetExists = True
    26. Exit For
    27. End If
    28. Next
    29. End Function
    a b L Programmation
    5 Novembre 2008 10:51:01

    Gestion des références

    Lorsque l'on utilise Automation pour piloter une application à partir d'une autre, il faut ajouter dans la liste des références du projet VBA (Menu Outils/Références) la référence à l'application concernée.

    Il s'agit d'une DLL, parfois avec une extension exotique : *.dll, *.tlb, *.olb.

    Si vous prenez la peine de le faire sur votre machine, tout fonctionnera à merveille. Mais si votre projet doit être exécuté sur une autre machine, il est fort possible qu'un problème de conflit de version se produise.

    Pour pallier à cet inconvénient, on peut dynamiquement référencer les applications dont on a besoin pour le projet.

    1. Public Sub AddReference(class As String, file As String)
    2. Dim ref As Reference
    3. Dim app As Object
    4. Dim fic As String
    5.  
    6. ' // Efface les références non valides
    7. For Each ref In VBProject.References
    8. If ref.IsBroken Then
    9. VBProject.References.Remove ref
    10. End If
    11. Next
    12.  
    13. ' // On recherche une appli.
    14. On Error Resume Next
    15. Set app = CreateObject(class)
    16. On Error GoTo 0
    17. If app Is Nothing Then Exit Sub
    18.  
    19. fic = app.Path & "\" & Dir(app.Path & "\" & file)
    20. Debug.Print "Ref: " & fic
    21.  
    22. ' // Vérification ultime
    23. For Each ref In VBProject.References
    24. If UCase(ref.fullpath) = UCase(fic) Then
    25. Debug.Print "Ref. déjà présente."
    26. Exit Sub
    27. End If
    28. Next
    29.  
    30. ' // Ajout de la Référence
    31. Debug.Print "Ajout de la Référence."
    32. VBProject.References.AddFromFile fic
    33.  
    34. ' // MrProper
    35. Set ref = Nothing
    36. Set app = Nothing
    37. End Sub
    L'objet VBProject appartient au classeur. Il convient donc de le préciser explicitement ou de déclarer cette fonction dans le code du classeur (ThisWorkbook).
    Le type Reference est défini dans la bibliothèque Microsoft Visual basic for application extensibility (Exemple : %CommonProgramFiles%\Microsoft Shared\VBA\VBA6).

    L'appel est on ne peut plus simple, mais il faut connaître la classe de notre application et le fichier de référence correspondant.

    Pour ajouter l'automation de Word à votre projet :
    1. AddReference "MSWord.Application", "MSWORD*.OLB"