Problème avec une variable "Public"

[Magic_Doctor]

Bonsoir,

Dans ma feuille j'ai une variable (nblignes) qui revient dans pratiquement toutes les macros.
Cette variable correspond au nombre de lignes d'un tableau, si vous préférez sa hauteur.
La hauteur peut se modifier.
Plutôt que de sempiternellement la déclarer dans chaque macro où elle apparaît, j'ai tenté de la déclarer en tête de module comme Public.
J'écris donc (sous Option Explicit) : Public nblignes As Integer

La valeur de cette variable est la suivante :
nblignes = Application.WorksheetFunction.CountA(Range(ColLtr([Balise1]) & ":" & ColLtr([Balise1])))
Où ColLtr est une fonction que j'ai trouvée et qui renvoie la lettre qui correspond à la colonne où se trouve une cellule nommée ("Balise1") qui ne sert en fait qu'à repérer, sur la feuille, la position de la colonne au cas où cette feuille serait modifiée et éviter par là de modifier les macros.
À vrai dire, je ne sais pas exactement où je dois donner à nblignes sa valeur afin qu'elle soit par la suite récupérée dans les macros qui utilisent cette variable.

Je ne sais pas si j'ai été clair ?

Quoi qu'il en soit, un grand merci pour toute suggestion.

 

[Dranreb]

Re : Problème avec une variable "Public"

Bonsoir
Alors ma suggestion sera de donner un nom à cette plage et de l'utiser dans les macros. Comme ça son nombre de ligne sera sa propriété .Rows.Count.
Cordialement.

 

[Magic_Doctor]

Re : Problème avec une variable "Public"

Bonsoir Dranreb,

Tant que l'on ne modifie pas le nombre de lignes, celui-ci reste constant.
Sa valeur = Application.WorksheetFunction.CountA(Range(ColLtr([Balise1]) & ":" & ColLtr([Balise1])))
Elle change uniquement si on modifie le tableau (en général en augmentant sa hauteur), ce qui arrive rarement.
Supposons que je modifie le tableau et que nous ayons nblignes = 250 (hauteur du tableau = 250 lignes)
J'actualise cette donnée au moyen d'un bouton que j'appelle "Actualisation" et qui déclenche une macro qui calcule nblignes et assigne cette valeur à la variable Public nblignes, variable qui, par la suite, est récupérée par les autres macros.
Voilà en fait ce que désirerais.

 

[Dranreb]

Re : Problème avec une variable "Public"

Comme vous voudrez. Je vous ai donné mon conseil, je ne vais rien vous conseiller de moins bon.

P.S. Lorsqu'on supprime ou insère une ligne dans une plage nommée, sa référence est corrigée automatiquement. Pour prévenir un ajout manuel derrière on peut utiliser ce genre de code:
Dans un module de feuille:

Rem. —— Garde fou + Ajout nouvel article
Private Sub Worksheet_Change(ByVal Target As Range)
Dim L As Long
If UnVandaleEstPasséParLà(Me) Then Exit Sub
If Target.Column = Me.[Artic].Column And Target.Row = Me.[Tablo].Row + Me.[Tablo].Rows.Count Then
   Application.ScreenUpdating = False
   Application.EnableEvents = False
   AjoutDrnLigTablo L, Me
   Me.[Tablo Artic].Rows(L).Value = Target.Value
   Target.MergeArea.ClearContents ' ce n'est plus fusionné mais ça ne gêne pas
   Me.[Tablo Prix].Rows(L).Select
   Application.EnableEvents = True
   End If
End Sub

Dans un module ordinaire:

Rem. —— Ajout d'une nouvelle dernière ligne à la plage "Tablo" d'une feuille
Sub AjoutDrnLigTablo(LAjté As Long, ByVal F As Worksheet)
Dim RgTab As Range, Cel As Range
Set RgTab = F.[Tablo]
LAjté = RgTab.Rows.Count
RgTab.Rows(LAjté).Copy
RgTab.Rows(LAjté).Insert xlShiftDown
LAjté = LAjté + 1
For Each Cel In Intersect(F.[Tablo].Rows(LAjté), F.UsedRange)
   If Not Cel.HasFormula Then Cel.ClearContents
   Next Cel
End Sub
'

Rem. —— Dispositif de sécurité pour préserver la plage "Tablo" d'une feuille
Function UnVandaleEstPasséParLà(ByVal Feuil As Worksheet) As Boolean
Dim NbL As Long
On Error Resume Next
NbL = Feuil.[Tablo].Rows.Count
If Err Then NbL = 0
On Error GoTo 0
UnVandaleEstPasséParLà = NbL < 2
If UnVandaleEstPasséParLà Then
   MsgBox "La liste doit comporter au moins 2 lignes," & vbLf _
        & "quitte à en vider la part saisissable." & vbLf _
        & "Pour ne saccager aucune référence, formule," & vbLf _
        & "ou format, votre manœuvre va être annulée.", _
      vbExclamation, "Opération dangereuse pour l'intégrité du fichier"
   Application.Undo
   End If
End Function

 

[Magic_Doctor]

Re : Problème avec une variable "Public"

Bonjour Dranreb,

Merci pour vos propositions.
Question. Pourquoi écrire : Sub AjoutDrnLigTablo(LAjté As Long, ByVal F As Worksheet)
et non : Sub AjoutDrnLigTablo(ByVal F As Worksheet), et déclarer : Dim LAjté As Long, RgTab As Range, Cel As Range
puisque par la suite LAjté = RgTab.Rows.Count

 

[Magic_Doctor]

Re : Problème avec une variable "Public"

Le problème semble se résoudre.
1/ je rallonge mon tableau, il passe, par exemple, de 50 lignes à 100 lignes
2/ j'assigne à la variable Public nblignes sa nouvelle valeur, à savoir 100
3/ toutes les macros qui utilisent cette variable fonctionnent
4/ le problème arrive quand je ferme le classeur et que je le rouvre. La variable nblignes a pris comme valeur 0 et c'est la panique.

Question : comment se débrouiller, une fois que l'on a assigné sa valeur à nblignes qu'elle la conserve quoi qu'il arrive jusqu'à son éventuelle nouvelle assignation si l'on décide de modifier la taille du tableau ?

 

[Dranreb]

Re : Problème avec une variable "Public"

Bonjour Magic_Doctor.
Ça se discute. J'ai jugé probable qu'on ait besoin du numéro de la nouvelle dernière ligne dans la procédure appelante aussitôt après l'appel pour garnir cette ligne, aussi ai-je prévu de la récupérer dans un paramètre. Mais s'il y plus de cas où on n'en a pas besoin on peut faire comme vous dites car il peut se retrouver facilement par Feuil1.[Tabo].Rows.Count.
On pourrait aussi rendre plutôt la ligne elle même comme Range car si on a besoin du n°de ligne c'est en général pour y accéder et comme ça l'accès est fait.
Cordialement.

Ah, ben votre nouveau message m'apparaît comme un régression...

 

[MJ13]

Re : Problème avec une variable "Public"

Bonjour à tous

En général Public, c'est pour les variables qu'on pourrait avoir besoin dans toutes les procédures .

Une fois qu'elle a été définie, elle ne change pas.

Sinon Option Explicit, j'ai bien essayé de le mettre un jour dans un programme, mais cela m'a vite saoulé, je ne pense pas le réutiliser .

 

[Magic_Doctor]

Re : Problème avec une variable "Public"

Bonjour Dranreb,

Je ne sais pas, mais vue la présentation de la feuille avec des tableaux dispersés, cela me semblait tenir la route, d'autant plus que ça marche, ma foi, très bien, si ce n'est cette maudite variable qui à chaque réouverture redevient 0.

 

[Compte Supprimé 979]

Re : Problème avec une variable "Public"

Bonjour à tous,

Est-ce que tu redéfinis la valeur de ta variable à l'ouverture du classeur !?
Sinon c'est normal, une variable public n'existe que dans le classeur ou elle a été crée

A+

 

[Magic_Doctor]

Re : Problème avec une variable "Public"

Bonjour MJ13,

Si je mets Option Explicit c'est tout simplement que nombre de "caïds" d'Excel le conseillent afin d'éviter que des variables n'aient pas été oubliées dans les déclarations. Mais je suis d'accord avec vous, cela peut engendrer quelques contraintes.

"En général Public, c'est pour les variables qu'on pourrait avoir besoin dans toutes les procédures"

C'est exactement ce que je recherche, mais pourquoi diable redevient-elle nulle à chaque réouverture ?

 

[MJ13]

Re : Problème avec une variable "Public"

Re

C'est exactement ce que je recherche, mais pourquoi diable redevient-elle nulle à chaque réouverture ?

Mais, il faut bien la définir au démarrage de ton classeur. Sinon fait des tests (c'est ce que je fait quand j'ai un doute) .

 

[Magic_Doctor]

Re : Problème avec une variable "Public"

Hola Bruno,

Non, je ne redéfinis pas cette variable à l'ouverture du classeur. Du reste j'y avais songé en désespoir de cause...

"Sinon c'est normal, une variable public n'existe que dans le classeur ou elle a été crée"
Pourtant je travaille toujours sur le même classeur où a été déclarée cette variable et où une valeur lui a été assignée.

Pourrais-tu, s'il te plaît, me rappeler comment on s'y prend pour déclencher une macro lors de l'ouverture du classeur ?

Merci d'avance.

 

[Magic_Doctor]

Re : Problème avec une variable "Public"

En conséquence, la valeur d'une variable Public serait en quelque sorte volatile.

 

[Dranreb]

Re : Problème avec une variable "Public"

Tandis qu'un nom de plage reste enregistré avec son nombre de lignes