Liste nommée dont la longueur peut varier mais dont le nom est toujours le même

[Magic_Doctor]

Bonsoir,

J'ai une liste de noms en colonne.
Supposons qu'il y ait 100 noms.
J'attribue un nom à cette liste, par exemple "ZAZA".
La longueur de cette liste peut varier (>10 noms ou <100 noms), mais je voudrais qu'elle s'appelle toujours "ZAZA".

J'ai essayé ainsi :

Sub NomListe()

Dim listeselect As String

'la liste est fluctuante, par ce biais, au moyen de cellules nommées, je retrouve les coordonnées de la liste
'quelle que soit sa longueur
listeselect = [FirstCellColonneTransfert2].Address & ":" & [FirstCellColonneTransfert2].Offset([QtNoms] - 1, 0).Address

Range(listeselect).Select 'jusque là ça marche

ActiveWorkbook.Names.Add Name:="BIBI" 'là ça plante

End Sub

Merci d'avance pour toute aide.

 

[Etienne2323]

Re : Liste nommée dont la longueur peut varier mais dont le nom est toujours le même

Salut Magic,
En fait, il faut faire 2 opérations. La première est pour vérifier si le nom que tu veux donner à ta plage est déjà présent dans ton fichier. La seconde va être de nommer ta plage.

Voici un exemple :

Option Explicit

Sub Test()
Dim i As Long, Plage As Range
Dim Le_Nom As String, Noms
Dim Numero_Nom As Integer

Set Noms = ActiveWorkbook.Names

Numero_Nom = 0
For i = 1 To Noms.Count
    If Noms(i).Name = "Ma_Plage" Then
        Numero_Nom = i
        Exit For
    End If
Next i

If Numero_Nom = 0 Then
    Set Plage = Range("A1:A" & Cells.SpecialCells(xlCellTypeLastCell).Row)
    Plage.Name = "Ma_Plage"
Else
    ActiveWorkbook.Names(Numero_Nom).Delete
    Set Plage = Range("A1:A" & Cells.SpecialCells(xlCellTypeLastCell).Row)
    Plage.Name = "Ma_Plage"
End If

Set Plage = Nothing
Set Noms = Nothing

End Sub

Cordialement,

Étienne

 

[Magic_Doctor]

Re : Liste nommée dont la longueur peut varier mais dont le nom est toujours le même

Bonsoir Étienne,

Merci pour ces bons tuyaux qui m'ont permis de régler le problème.

Dans le cas présent, il n'est pas nécessaire de vérifier si le nom de la plage existe, puisqu'il existe.
M'inspirant de ce que tu m'as adressé, j'ai rédigé ceci qui, ma foi, semble très bien marcher.

Sub NomListe()

Dim listeselect As String, plage As Range

ActiveWorkbook.Names("BIBI").Delete

listeselect = [FirstCellColonneTransfert2].Address & ":" & [FirstCellColonneTransfert2].Offset([QtNoms] - 1, 0).Address

Set plage = Range(listeselect)

plage.Name = "BIBI"

End Sub

 

[Magic_Doctor]

Re : Liste nommée dont la longueur peut varier mais dont le nom est toujours le même

N'étant pas un virtuose de VBA, j'en profite pour poser une question à Étienne.

Pourquoi achèves-tu ta routine par :
Set Plage = Nothing
Set Noms = Nothing

Merci.

 

[Compte Supprimé 979]

Re : Liste nommée dont la longueur peut varier mais dont le nom est toujours le même

Bonjour Magic_Doctor

Pourquoi achèves-tu ta routine par :
Set Plage = Nothing
Set Noms = Nothing

C'est pour vider les variables objet et donc libérer l'espace mémoire

Ceci est très important, quand tu as un "gros" programme avec beaucoup de variables
ça évite le plantage d'Excel pour mémoire saturée

A+

 

[Magic_Doctor]

Re : Liste nommée dont la longueur peut varier mais dont le nom est toujours le même

Merci Bruno.

Mais qu'entends-tu exactement par variable objet ?

 

[Compte Supprimé 979]

Re : Liste nommée dont la longueur peut varier mais dont le nom est toujours le même

Re,

Ou la ... la programmation objet est le béaba de la programmation sous VBA et autres langages
Une variable objet est un "conteneur"

Utiliser les variables en VBA Excel
"Type de données représentant toute référence Objet.
Les variables Object sont stockées sous forme d'adresses codées sur 32 bits (4 octets).
L'instruction Set permet d'attribuer une référence d'objet à la variable.
Dans Excel, un objet peut être un classeur, les feuilles de calcul, un graphique...etc..."

Pour essayer de mettre cela en image, un petit exemple :
Dans ma maison, j'ai ma salle à manger
Dans ma salle à manger, j'ai des spots que je veux allumer

On peut dire :
J'entre dans ma maison

Workbooks("Maison.xls").Activate

J'entre dans ma salle à manger

Sheets("SalleAManger").Activate

J'allume mes spots

Range("A1:D1").Interior.ColorIndex = 6

Tu peux donc voir que cela fait quelques instructions

Ta variable objet est là pour t'aider et mémoriser le conteneur, que tu n'auras plus qu'à rappeler

Avec les nouvelles technologies, je peux dire :
J'allume, les spots de ma salle à manger qui se trouve dans ma maison

Sub VariableObjet()
  Dim MonObj As Object
  ' Définir l'objet conteneur
  Set MonObj = Workbooks("Maison.xls").Sheets("SalleAManger").Range("A1:D1")
  ' Lui appliquer une propriété
  MonObj.Interior.ColorIndex = 6
  ' Vider cette variable objet (si nécessaire)
  Set MonObj = Nothing
End Sub

J'espère que comme ça ce sera plus explicite 😉

A+

 

[Magic_Doctor]

Re : Liste nommée dont la longueur peut varier mais dont le nom est toujours le même

Merci Bruno,

Didactique et un bon sujet de réflexion pour le profane que je suis.

 

[Misange]

Re : Liste nommée dont la longueur peut varier mais dont le nom est toujours le même

Bonjour

un peu de lecture sur le sujet :
Les macros Excel | www.excelabo.net

pour revenir à ta question, si la colonne de destination est toujours la même, inutile de passer par VBA pour définir le nom.
Pourquoi ne pas définir le nom dans le classeur (formules/définir un nom).
Dans le cas présent, tu le définis de façon dynamique, c'est à dire de telle sorte qu'il s'ajuste tout seul au contenu.
Si tes données sont dans la colonne A et qu'en A1 tu as l'étiquette de données et tes valeurs commençant en A2
fans formules/définir un nom, en haut tu écris le nom de ton choix
en bas dans la zone de formule tu écris
=decaler($A$1;1;0;nbval($A:$A)-1;1)

explication :
décaler définit une plage de cellules à partir d'une cellule de référence, ici $A$1. Cette cellule est la seule qui soit fixe dans l'histoire.
2° argument : je descends de 1 cellule (je me retrouve donc en A2)
3° argument : je me décale de 0 cellules vers la droite (je reste donc en A2)
4° argument = quelle est la hauteur de ma plage ? Elle est définie par nbval($A:$A)-1 : nombre de cellules contenant une valeur dans la colonne A (c'est pour ça que cette formule est dynamique : si tu ajoutes ou enlève des valeurs, ce nombre varie). On enlève 1 car la cellule A1 contient l'étiquette de colonne. Si tu as 100 valeurs dans la colonne + l'étiquette, ça renvoie 100.
5° argument : quelle est la largeur de la plage ? ici 1 donc seulement la colonne A

Donc on part de A1 et on définit une plage qui part de 1 cellule plus bas et qui fait 100 cellules de haut. Donc A2:A101

Seule contrainte: pas de cellule vide dans la colonne de données (pas de trous) et rien d'autre que l'étiquette et les valeurs (ou alors il faut ajuster la formule.

Autre solution avec excel 2007; transformer ta colonne en table avec menu accueil/mettre sous forme de tableau.
Excel grée alors AUTOMATIQUEMENT les noms de façon dynamique et quand tu ajoutes des valeurs dans ta colonne ou que tu en supprimes, pif pouf ça s'adapte tout seul. Avantage, tu peux mettre ce que tu veux d'autre dans la colonne, excel sait si ça n'appartient pas à la table et n'en tient pas compte.

 

[Etienne2323]

Re : Liste nommée dont la longueur peut varier mais dont le nom est toujours le même

Salut Magic, Bruno 🙂, Misange 🙂, le forum,

@ Bruno et Misange : voila d'excellentes explications !

@ Magic : essaie d'utiliser les balises de codes (mode avancé et le petit #) lorsque tu mentionnes du code VBA. C'est beaucoup plus lisible. mromain nous a également doté de balises permettant d'afficher la couleur dans le code. Regarde ma signature, cela te montrera comment les utiliser.

Bonne journée,

Étienne