[Tutoriel] Excel/Macro VBA : Trucs et astuces

[zeb]

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 :

Vous devez être connecté pour voir les liens.

)

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.)

Dim element As TypeDeLaCollection
For Each element In Collection
  ..
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 :

WorkBooks(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 :

WorkSheets(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.

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 :

Range("A1")
Range("A1:B2")
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 :

WorkBooks(1).WorkSheets(2).Cells(3, 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)

Rows(12)
Rows("1:2")
Columns("B")
Columns(3)
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 :

Range("B2").Value = Range("B1").Value
Range("B2").Value = Range("B1").Value + 1
Range("B1:B12").Copy Destination:=Range("D1")
WorkSheets("Feuil2").Range("B1:B12").Copy Destination:=WorkSheets(1).Range("D1")
WorkBooks(2).WorkSheets("Feuil2").Range("B1:B12").Copy Destination:=WorkBooks("Classeur1").WorkSheets(1).Range("D1")

....
à suivre

 

[zeb]

Base de données sous Excel

Vous prétendez utiliser Excel comme BDD ?
Exécutez ceci avant tout, il le dit mieux que moi.

Sub ExcelAsDB()
    Dim t As Balloon
    Assistant.On = True
    Set t = Assistant.NewBalloon
    t.Animation = 11
    t.Button = 1
    t.Heading = StrReverse(".ruelbat nu tse lecxE")
    t.Text = StrReverse(".seénnod ed esab ed eriannoitseg nu sap tse'n eC")
    t.Show
    Assistant.Visible = False
    Set t = Nothing
    Assistant.On = False
End Sub

 

[Freeman23]

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

Edit : Je tacherai de rajouter des trucs.

 

[zeb]

Blague à part, ce topic est aussi le tien. 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.

 

[zeb]

La dernière ligne

Comment déterminer la dernière ligne d'un bloc de cellules.

' // Hypothèse : La cellule A1 fait partie de ce bloc de cellules
Range("A1").CurrentRegion.SpecialCells(xlCellTypeLastCell).Row

Comment déterminer la dernière ligne non vide pour une colonne donnée :

' // <c> est le numéro de la colonne.
Cells(Rows.Count, c).End(xlUp).Row

Comment déterminer la prochaine ligne vide par rapport à une cellule donnée :

' // Soit A1, la cellule considérée.
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) :

Range("A1").CurrentRegion.SpecialCells(xlCellTypeLastCell).EntireRow
Cells(Rows.Count, c).End(xlUp).EntireRow
Range("A1").End(xlDown).Offset(1).EntireRow

-------------------​

Une autre façon de faire, consiste à rechercher quelque chose avec la méthode Find() :

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é :

Dim der As Range
    
der = Columns(1).Find("*", searchdirection:=xlPrevious)
If Not der Is Nothing Then
    ...

(Remerciement à Djorge84 pour m'avoir rappelé cette façon de faire.)

 

[zeb]

Supprimer des lignes dans un tableau

Soit le tableau suivant :
[fixed]1. AAAA
2. BBBB
3. CCCC[/fixed]

L'algorithme suivant est faux :

Pour i de 1 à 3
    Si ConditionSurLigne(i) Alors
        SupprimerLigne(i)
Suivant

En effet, supposons que la condition nous fasse vouloir supprimer les lignes AAAA et BBBB.
Déroulons le code:
■ i = 1
[fixed][1] AAAA <-- i
[2] BBBB
[3] CCCC[/fixed]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 :[fixed][1] BBBB <-- i
[2] CCCC[/fixed]
■ i = 2
[fixed][1] BBBB
[2] CCCC <-- i[/fixed]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
[fixed][1] BBBB
[2] CCCC
<-- i[/fixed]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

Pour i de 3 à 1
    Si ConditionSurLigne(i) Alors
        SupprimerLigne(i)
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.

For i = 3 To 1 Step -1
    If ConditionSurLigne(i) Then Rows(i).Delete
Next

 

[zeb]

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 :

Function FileExists(sFileName As String) As Boolean
    Dim FSO
    Set FSO = CreateObject("Scripting.FileSystemObject")
    FileExists = FSO.FileExists(sFileName)
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 :

Function FileExists(sFileName As String) As Boolean
    With New Scripting.FileSystemObject
        FileExists = .FileExists(sFileName)
    End With
End Function

 

[zeb]

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

Ce n'est pas moi qui le dit, c'est Microsoft :

Vous devez être connecté pour voir les liens.

 

[zeb]

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 :

Function WorksheetExists(ByVal Name As String, Optional wb As Workbook) As Boolean
    Dim ws As Worksheet
        
    WorksheetExists = False
    
    If wb Is Nothing Then Set wb = ActiveWorkbook
    
    For Each ws In wb.Worksheets
        If ws.Name = Name Then
            WorksheetExists = True
            Exit For
        End If
    Next
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 :

Enum seCollection
    se_Sheets
    se_Worksheets
    se_Charts
End Enum

Function SheetExists(ByVal name As String, Optional wb As Workbook, Optional collection As seCollection) As Boolean
    Dim s As Object
    Dim sCol As Sheets
    
    SheetExists = False
    
    If wb Is Nothing Then Set wb = ActiveWorkbook
    
    If collection = se_Worksheets Then
        Set sCol = wb.Worksheets
    ElseIf collection = se_Charts Then
        Set sCol = wb.Charts
    Else
        Set sCol = wb.Sheets
    End If
    
    For Each s In sCol
        If s.name = name Then
            SheetExists = True
            Exit For
        End If
    Next
End Function

 

[zeb]

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.

Public Sub AddReference(class As String, file As String)
    Dim ref As Reference
    Dim app As Object
    Dim fic As String

    ' // Efface les références non valides
    For Each ref In VBProject.References
        If ref.IsBroken Then
            VBProject.References.Remove ref
        End If
    Next

    ' // On recherche une appli.
    On Error Resume Next
    Set app = CreateObject(class)
    On Error GoTo 0
    If app Is Nothing Then Exit Sub

    fic = app.Path & "\" & Dir(app.Path & "\" & file)
    Debug.Print "Ref: " & fic
    
    ' // Vérification ultime
    For Each ref In VBProject.References
        If UCase(ref.fullpath) = UCase(fic) Then
            Debug.Print "Ref. déjà présente."
            Exit Sub
        End If
    Next
    
    ' // Ajout de la Référence
    Debug.Print "Ajout de la Référence."
    VBProject.References.AddFromFile fic

    ' // MrProper
    Set ref = Nothing
    Set app = Nothing
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 :

AddReference "MSWord.Application", "MSWORD*.OLB"