IdentifiantMot de passe
Loading...
Mot de passe oublié ?Je m'inscris ! (gratuit)
Voir le flux RSS

Pierre Fauconnier

VBA Excel: Vérifier qu'une feuille existe dans un classeur, trouver une feuille par son nom dans un classeur

Note : 2 votes pour une moyenne de 5,00.
par , 17/10/2019 à 09h00 (2837 Affichages)
Salut

Il arrive souvent que l'on doive manipuler une feuille dans Excel, et se pose la question de savoir si elle existe. Sur les forums, on voit alors des "solutions" telles que l'utilisation de On Error... censée gérer le fait que la feuille n'est pas trouvée, ou encore une boucle For Each... avec un sale Exit For dès qu'on a trouvé la feuille... Beerkkk

La gestion des exeptions (ce que fait On Error) n'a pas sa place ici. La "programmation par l'erreur" n'est à mon sens pas une bonne programmation. Quant à l'utilisation de Exit dans du code, j'ai déjà dit ce que j'en pensais dans ce billet.

Je vous propose deux codes, l'un pour trouver la feuille par son nom, l'autre par son codename. Ces fonctions reçoivent le nom (ou le codename) en premier argument obligatoire, et en second argument optionnel, le classeur (ouvert) dans lequel il faut chercher ladite feuille. Si ce classeur n'est pas précisé, le code cherchera la feuille dans le classeur actif.

Snippet pour rechercher une feuille par son nom (le nom de l'onglet)
Code vba : Sélectionner tout - Visualiser dans une fenêtre à part
1
2
3
4
5
6
7
8
9
10
11
Function getSheetByName(Name As String, Optional wb As Workbook) As Object
  Dim sh As Object
  Dim Counter As Long
 
  If wb Is Nothing Then Set wb = ActiveWorkbook
  Counter = 1
  Do While Counter <= wb.Sheets.Count And getSheetByName Is Nothing
    If StrComp(wb.Sheets(Counter).Name, Name, vbTextCompare) = 0 Then Set getSheetByName = wb.Sheets(Counter)
    Counter = Counter + 1
  Loop
End Function


Snippet pour rechercher une feuille par son CodeName (le nom VBA de la feuille).
Code vba : Sélectionner tout - Visualiser dans une fenêtre à part
1
2
3
4
5
6
7
8
9
10
11
Function getSheetByCodeName(CodeName As String, Optional wb As Workbook) As Object
  Dim sh As Object
  Dim Counter As Long
 
  If wb Is Nothing Then Set wb = ActiveWorkbook
  Counter = 1
  Do While Counter <= wb.Sheets.Count And getSheetByCodeName Is Nothing
    If StrComp(wb.Sheets(Counter).CodeName, CodeName, vbTextCompare) = 0 Then Set getSheetByCodeName = wb.Sheets(Counter)
    Counter = Counter + 1
  Loop
End Function


Ces codes permettent de manipuler directement la feuille ou de l'affecter à une variable. Pour déterminer qu'une feuille existe sans vouloir l'affecter à une variable, il suffira de déterminer que la fonction renvoie autre chose que Nothing. Fidèle à ma volonté de me simplifier la vie, j'ai donc créé les fonctions qui permettent uniquement de savoir si une feuille existe.

Fonction qui vérifie qu'une feuille existe avec le nom d'onglet souhaité
Code vba : Sélectionner tout - Visualiser dans une fenêtre à part
1
2
3
Function SheetByNameExists(Name As String, Optional wb As Workbook) As Boolean
  SheetByNameExists = Not getSheetByName(Name, wb) Is Nothing
End Function

Fonction qui vérifie qu'une feuille existe avec le CodeName souhaité
Code vba : Sélectionner tout - Visualiser dans une fenêtre à part
1
2
3
Function SheetByCodeNameExists(CodeName As String, Optional wb As Workbook) As Boolean
  SheetByCodeNameExists = Not getSheetByCodeName(CodeName, wb) Is Nothing
End Function

Il pourrait être plus intéressant, si l'on ne veut que savoir si la feuille existe, de créer une fonction qui le dit explicitement (j'aime bien travailler sur l'intention du code) et qui renvoie VRAI ou FAUX en fonction de l'(in)existence de la feuille, sans passer par la création de l'objet.
Code : Sélectionner tout - Visualiser dans une fenêtre à part
1
2
3
4
5
6
7
8
9
Function SheetExists(Name As String, Optional wb As Workbook) As Boolean
  Dim Counter As Long: Counter = 1
  
  If wb Is Nothing Then Set wb = ActiveWorkbook
  Do While Counter <= wb.Sheets.Count And Not SheetExists
    SheetExists = StrComp(Name, wb.Sheets(Counter).Name, vbTextCompare) = 0
    Counter = Counter + 1
  Loop
End Function
Bien entendu, ces fonctions trouveront leur place dans le module générique xlTools que j'embarque dans tous mes développements.

NB: Mes snippets sont toujours indépendants de Option Compare et Option Base. C'est pourquoi j'utilise la fonction StrCompare et pas la simple égalité, car par défaut (pas de Option Compare ou Option Compare Binary), VBA réalise la comparaison en différenciant les majuscules des minuscules.

Envoyer le billet « VBA Excel: Vérifier qu'une feuille existe dans un classeur, trouver une feuille par son nom dans un classeur » dans le blog Viadeo Envoyer le billet « VBA Excel: Vérifier qu'une feuille existe dans un classeur, trouver une feuille par son nom dans un classeur » dans le blog Twitter Envoyer le billet « VBA Excel: Vérifier qu'une feuille existe dans un classeur, trouver une feuille par son nom dans un classeur » dans le blog Google Envoyer le billet « VBA Excel: Vérifier qu'une feuille existe dans un classeur, trouver une feuille par son nom dans un classeur » dans le blog Facebook Envoyer le billet « VBA Excel: Vérifier qu'une feuille existe dans un classeur, trouver une feuille par son nom dans un classeur » dans le blog Digg Envoyer le billet « VBA Excel: Vérifier qu'une feuille existe dans un classeur, trouver une feuille par son nom dans un classeur » dans le blog Delicious Envoyer le billet « VBA Excel: Vérifier qu'une feuille existe dans un classeur, trouver une feuille par son nom dans un classeur » dans le blog MySpace Envoyer le billet « VBA Excel: Vérifier qu'une feuille existe dans un classeur, trouver une feuille par son nom dans un classeur » dans le blog Yahoo

Mis à jour 21/06/2020 à 08h52 par Pierre Fauconnier

Catégories
VBA , Excel , MS Office , Snippet

Commentaires

  1. Avatar de MarcelG
    • |
    • permalink
    Salut Pierre,

    Sans flagornerie aucune.
    Intéressant

    A retenir en règle générale. Pas de Exit mais plutôt Do..While (ou Until sans doute)
    Si l'on m'y reprend (car je suis souvent tombé), une bière pour Marcel!

    Je reporte ci-dessous un extrait de mes aides-mémoires.
    (dont quelques liens de Philippe)
    Peut-être un avis? (les On Error, que tu mentionnes dans ton billet, mis à part)

    A plus tard.

    http://www.developpez.net/forums/d13...xcel-avec-vba/
    Function ExistSheet(Ws$, Optional Wb As Workbook) As Boolean
    If Wb Is Nothing Then Set Wb = ActiveWorkbook
    On Error Resume Next
    ExistSheet = IsObject(Wb.Sheets(Ws))
    End Function

    Voir les liens de Philippe Thulliez

    https://www.developpez.net/forums/d1.../#post10992616

    2 premières fonction = Philippe Thulliez

    Function IsSheetExist(SheetName As String, Optional Wkb As Workbook) As Boolean
    ' http://philippe.tulliez.be
    ' Renvoie True ou False
    ' Arguments
    ' SheetName (String) Nom de la feuille dont on teste l'existence
    ' [wkb] (Workbook) ThisWorkbook est le classeur par défaut
    Dim Sht As Worksheet
    If Wkb Is Nothing Then Set Wkb = ThisWorkbook
    On Error Resume Next
    Set Sht = Wkb.Sheets(SheetName)
    With Err
    If .Number Then .Clear Else IsSheetExist = True
    End With
    ‘--------------------------------

    http://philippe.tulliez.be/fonction-...s-un-classeur/

    Function IsSheetExist(Name As String) As Boolean
    ' Cette fonction renvoie TRUE si la feuille existe et FALSE dans le cas contraire
    ' Argument
    ' Name (String) - Nom de la feuille dont on teste l'existence
    On Error Resume Next
    IsSheetExist = Len(Sheets(Name).Name) > 0
    On Error GoTo 0
    End Function

    Function FeuilleExiste(NomFeuille) As Boolean
    Dim f As Object
    On Error Resume Next
    Set f = Sheets(NomFeuille)
    If Err = 0 Then FeuilleExiste = True
    Set f = Nothing
    End Function
    ‘--------------------------------
    http://www.mdf-xlpages.com/modules/s...q.php?faqid=40
    Function FeuilExiste(F As String) As Boolean
    On Error Resume Next
    FeuilExiste = Not Sheets(F) Is Nothing
    End Function
    ‘--------------------------------
    https://www.developpez.net/forums/d1...feuille-excel/
    Public Function WorkSheetExist(Sheetname As String) As Boolean

    On Error Resume Next
    WorkSheetExist = Sheets(Sheetname).Index
    End Function
    ‘--------------------------------
    Function sheetExists(Feuille) As Boolean ' Code Fait par Marc-L
    sheetExists = Evaluate("ISREF('" & Feuille & "'!A1)")
    End Function
    ‘‘--------------------------------
    Mis à jour 19/11/2019 à 22h01 par MarcelG
  2. Avatar de MarcelG
    • |
    • permalink
    Dans les solutions précédentes, beaucoup - trop à mon goût - de gestions d'erreurs. Ce dont il faut user, certes, mais à bon escient. Personnellement, j'évite tant faire se peut.
    Cela dit, pas de sortie de bloc anticipée.

    Je mets donc ce billet dans mes priorités

    Ce tout en sachant que l'on peut, sauf erreur, obtenir, comme demandé la plupart du temps, une fonction booléenne sur getSheetByName = Nothing ou non.

    Du type:

    Code : Sélectionner tout - Visualiser dans une fenêtre à part
    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16
    17
    18
    19
    20
    21
    Function ExisteFeuille(Name As String, Optional wb As Workbook) As Boolean
      
    Dim getSheetByName As Object
    Dim sh As Object
    Dim Counter As Long
    
    If wb Is Nothing Then Set wb = ActiveWorkbook
            Counter = 1
            Do While Counter <= wb.Sheets.Count And getSheetByName Is Nothing
            If StrComp(wb.Sheets(Counter).Name, Name, vbTextCompare) = 0 Then Set getSheetByName = wb.Sheets(Counter)
            Counter = Counter + 1
    Loop
    
    If getSheetByName Is Nothing Then
            ExisteFeuille = False
    Else
            ExisteFeuille = True
            Set getSheetByName = Nothing
    End If
    
    End Function
    Maintenant, je pose la question, Pierre.

    J'ai codé cette fonction en déclarant sh en Worksheet.
    La fonction reste bien effective. Une feuille étant un objet.

    Y a-t-il une raison à la déclaration initiale en Object?
    Il y a sans doute là des notions qui me sont étrangères.

    A plus tard.

    Bonne soirée.
    Mis à jour 20/11/2019 à 11h12 par MarcelG
  3. Avatar de Pierre Fauconnier
    • |
    • permalink
    Salut Marcel.

    Merci pour tes commentaires et questions. Je vais les prendre à l'envers, si tu veux bien, et d'abord répondre à ta dernière question: Pourquoi As Object et pas As Worksheet?

    Pour que la fonction puisse aussi être utilisée pour une feuille de graphique (Objet Chart) et éviter ainsi d'avoir deux fonctions, une qui traite la feuille de calcul et l'autre qui traite la feuille de graphique.

    Pour ce qui est des codes de Philippe, je ne peux pas les commenter en faisant abstraction de On Error puisque sans les On Error, les codes proposés ne sont pas fonctionnels et que perso, je ne veux pas utiliser les On Error dans ces cas.

    La dernière proposée, qui n'utilise pas de boucle et pas de gestion d'erreurs pourrait être intéressante, mais elle est limitée aux feuilles de calcul. Dès lors, chaud partisan de la systématisation dans mes codes, je préfère en utiliser une seule qui permet de tester une feuille quel que soit son type, de manière à ne pas encombrer mon module xlTools avec des fonctions presqu'identiques*, et de ne pas devoir me poser la question du type de la feuille.

    Je peux juste proposer une "fusion" de mes deux fonctions (celle qui renvoie la feuille et celle qui teste qu'elle existe), bien que je n'aime pas trop cette solution qui oblige à passer un objet même si l'on souhaite seulement tester l'existence de la feuille.

    Code vba : Sélectionner tout - Visualiser dans une fenêtre à part
    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    Function SetSheetByName(Name As String, ByRef sh As Object, Optional wb As Workbook) As Boolean
      Dim Counter As Long
     
      Set sh = Nothing
      If wb Is Nothing Then Set wb = ActiveWorkbook
      Counter = 1
      Do While Counter <= wb.Sheets.Count And sh Is Nothing
        If StrComp(wb.Sheets(Counter).Name, Name, vbTextCompare) = 0 Then Set sh = wb.Sheets(Counter)
        Counter = Counter + 1
      Loop
      SetSheetByName = Not sh Is Nothing
    End Function

    Code vba : Sélectionner tout - Visualiser dans une fenêtre à part
    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
     
    Sub testSheet()
      Dim sh As Object
     
      If SetSheetByName("Feuil1", sh) Then
        Debug.Print sh.Name
      Else
        MsgBox "La feuille n'existe pas"
      End If
    End Sub




    * En son temps, j'avais aussi créé une fonction qui permettait de rechercher une feuille selon son name ou son codename. Elle traîne toujours dans mon module Tools (jétais moins organisé à l'époque... )

    Code vba : Sélectionner tout - Visualiser dans une fenêtre à part
    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16
    17
    Function getSheetByName(Optional Name As String, Optional CodeName As String, Optional wb As Workbook) As Object
      Dim sh As Object
      Dim Counter As Long
     
      If Name <> "" Or CodeName <> "" Then
        If wb Is Nothing Then Set wb = ActiveWorkbook
        Counter = 1
        Do While Counter <= wb.Sheets.Count And getSheetByName Is Nothing
          If Name <> "" Then
            If StrComp(wb.Sheets(Counter).Name, Name, vbTextCompare) = 0 Then Set getSheetByName = wb.Sheets(Counter)
          Else
            If StrComp(wb.Sheets(Counter).CodeName, CodeName, vbTextCompare) = 0 Then Set getSheetByName = wb.Sheets(Counter)
          End If
          Counter = Counter + 1
        Loop
      End If
    End Function

    Elle s'appelle facilement avec les arguments nommés (que j'utilise très souvent dans mes codes, par ailleurs).

    Code vba : Sélectionner tout - Visualiser dans une fenêtre à part
    1
    2
    3
    4
    Sub TestSheets()
      Debug.Print getSheetByName(Name:="Feuil2").CodeName
      Debug.Print getSheetByName(CodeName:="shTest").Name
    End Sub
  4. Avatar de MarcelG
    • |
    • permalink
    Très bien.

    Je prends note, en ayant mis à jour mon aide-mémoire (créé en 2009! ) dans sa partie "Feuilles".

    Merci et à bientôt.

    Marcel