Philippe Tulliez

Préambule
On n'est jamais à l'abri du fait qu'un utilisateur renomme la feuille du classeur qu'il utilise et donc lorsque l'on distribue une application écrite avec VBA pour Excel, il est hautement conseillé d'utiliser la propriété CodeName de la feuille et ce afin de ne pas voir l'application renvoyer une erreur 9 (L'indice n'appartient pas à la sélection)

Pour en savoir plus sur la propriété CodeName d’une feuille Excel

Si l'usage du CodeName de la feuille où se trouve le code VBA est simple, il n'en est pas de même si l'on souhaite utiliser le CodeName de la feuille d'un autre classeur

Voici une fonction nommée GetSheetByCodeName qui parcoure la collection des feuilles présentes dans le classeur définit dans le second argument de cette fonction

Code de la fonction
Cette fonction renvoie un objet Sheet ou Worksheet en fonction des arguments qui lui sont passés, soit le CodeName de la feuille et l'objet Workbook où se trouve cette feuille. Ce dernier argument est facultatif.

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
18
19
Function GetSheetByCodeName(CodeName As String, _
                            Optional oWorkbook As Workbook) As Object
  ' Renvoie un objet Sheet ou Worksheet si trouvé ou Nothing dans le cas contraire
  ' Philippe Tulliez www.magicoffice.be
  ' Arguments
  '  CodeName    (String)   - Nom du CodeName de la feuille à trouver
  '  [oWorkbook] (Workbook) - Objet Classeur (ThisWorkbook par défaut)
  Dim Index As Integer
  If oWorkbook Is Nothing Then Set oWorkbook = ActiveWorkbook
 
  With oWorkbook
    Do
      Index = Index + 1
      If StrComp(.Sheets(Index).CodeName, CodeName, vbTextCompare) = 0 Then
         Set GetSheetByCodeName = .Sheets(Index)
      End If
    Loop While Index < .Sheets.Count And GetSheetByCodeName Is Nothing
  End With
End Function

Code d'une procédure invoquant la fonction
La procédure ci-dessous utilise l'objet Worksheet du classeur nommé TestCodeName.xlsx se trouvant dans le sous-répertoire nommé Data en utilisant son CodeName

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
18
19
20
21
22
23
24
25
Sub T()
  ' Déclaration
  Const SubFolder As String = "\Data\"
  Const FileName As String = "TestCodeName.xlsx"
  Const shtCodeName As String = "shtHome"
  Dim Wkb As Workbook
  Dim sht As Worksheet
  Dim CurrentFolder As String
  Dim FullName As String
  Dim msg As String
  ' Affectation
  CurrentFolder = ThisWorkbook.Path
  FullName = CurrentFolder & SubFolder & FileName
  Set Wkb = GetWorkbook(FullName)
  Set sht = GetSheetByCodeName(shtCodeName, Wkb)
  ' Ici code de traitement sur la feuille trouvée ou si Nothing
  If Not sht Is Nothing Then
     msg = "Le nom de la feuille du CodeName (" & shtCodeName & ") est :" & vbCrLf & "[" & sht.Name & "] dans le classeur " & wkb.Name
   Else
     msg = "Pas trouvé " & shtCodeName & " dans le classeur " & Wkb.Name
  End If
  MsgBox msg
  Wkb.Close
  Set Wkb = Nothing: Set sht = Nothing
End Sub

Autre possibilité d'accéder au CodeName d'une feuille
Il est possible également d'accéder au CodeName de la feuille d'un autre classeur à l'aide de VBProject.VBComponents

Cependant l'utilisation de la collection VBComponents nous oblige à autoriser l'application à accéder au modèle objet voir plus bas dans ce billet
Pour ma part, j'évite d'utiliser ce qui n'est pas standard

Comment autoriser l'accès par VBA au projet
Pour ce faire il y a lieu de cocher l'option Accès approuvé au modèle d'objet du projet VBA que l'on accède entre autres en cliquant sur la commande Sécurité des macros du groupe Code de l'onglet [Développeur]