XL 2021 Bugs dans la propagation de cellules

[MATLEA]

Bonjour,

Dans le fichier joint je suis bloqué suite à l'apparition de bugs dans la propagation de la formule suivante qui est de type
=SIERREUR(RECHERCHEV(PETITE.VALEUR(SI(ESTTEXTE(CC$25:CC$56);CB$25:CB$56);LIGNE(CD1));CB:CC;2;FAUX);"") visant à randomiser la liste des valeurs.
Je recherche quelqu'un qui pourrait me dire comment régler ce problème soit par correction des bugs soit par utilisation d'une formule alternative.

Merci d'avance et bonne journée.

 

[MATLEA]

Il n'est pas obligatoire qu'une graine soit portée dans une cellule. Mais si elle ne l'est pas elle doit avoir obligatoirement avoir un nom dans la feuille ou dans le classeur. J'ai vu que vous aviez déjà la macro ChangerGraine dans le module MChgGraine capable de créer si nécessaire ce nom ou changer la valeur de la graine. Mais je m'aperçois qu'elle possède un défaut de taille: si plusieurs graines sont générées dans la même seconde, elles risquent d'avoir la même valeur.
Pour remédier à cela, je propose de la modifier comme ceci :

Sub ChangerGraine(Optional ByVal Cible = "!Graine")
   Static DernH As Double: Dim H As Double
Rem. Change la graine utilisée pour les tirages aléatoire.
'  Argument Cible facultatif. Si omis, "!Graine" est assumé. 2 cas :
'     1) — Un String indiquant le nom de la graine. S'il commence par un point d'exclamation,
'          la suite indique un nom dans la feuille active, sinon le tout est un nom dans le classeur
'          Ce nom peut avoir comme référence une cellule contenant la graine ou directement sa valeur.
'          Si le nom n'existe pas, il est créé comme ayant pour référence la valeur.
'     2) — Un Range représentant la cellule devant contenir la graine
'  Remarque: Pour afficher cette graine, le format de cellule "jjjj hh:mm:ss" est assez pertinent,
'     parce que la graine sera l'heure courante au jour de la semaine seulement. Now aurait en effet
'     trop de bits significatifs pour pouvoir être considérée à la seconde près en le type de donnée
'     Single utilisé par la procédure Randomize et renvoyé par la fonction Rnd.
   H = Date Mod 7 + Time: If H = 0 Then H = 7
   If H = DernH Then H = H + 2 ^ -19
   DernH = H
   If TypeOf Cible Is Range Then Cible.Value = H: Exit Sub
   If VarType(Cible) <> vbString Then MsgBox "Argument de type """ & TypeName(Cible) & """ incorrect.", _
      "Spécifiez une cellule ou un nom pour la feuille ou le classeur.", _
      vbCritical, "ChangerGraine": Exit Sub
   On Error Resume Next
   If Left$(Cible, 1) = "!" Then
      If TypeOf ActiveSheet.Evaluate(Mid$(Cible, 2)) Is Range Then
         ActiveSheet.Evaluate(Mid$(Cible, 2)).Value = H
      Else
         ActiveSheet.Names.Add Mid$(Cible, 2), H
         If Err Then MsgBox "Err " & Err.Number & " lors de :" _
            & vbLf & "ActiveSheet.Names.Add """ & Mid$(Cible, 2) & """, " & H _
            & vbLf & Err.Description, vbExclamation, "ChangerGraine"
         End If
   Else
      If TypeOf Application.Evaluate(Cible) Is Range Then
         Application.Evaluate(Cible).Value = H
      Else
         ActiveWorkbook.Names.Add Cible, H
         If Err Then MsgBox "Err " & Err.Number & " lors de :" _
            & vbLf & "ActiveWorkbook.Names.Add """ & Cible & """, " & H _
            & vbLf & Err.Description, vbExclamation, "ChangerGraine"
         End If
      End If
   End Sub

Pour l'autre système, écrivez ensuite par exemple une autre macro qui utilise cette Sub Mélanger pour plusieurs plages spécifiques, et affectez la à un bouton de formulaire dessiné dans la feuille Excel.

OK
Pour l'option graine j'ai changé comme vous le demandez le programme du module MChgGraine par le votre en collant après "Option Explicit" le texte commençant par

Sub ChangerGraine(Optional ByVal Cible = "!Graine")  jusqu'à End Sub

. C'est ce qu'il fallait faire?
Comment dois-je concrètement me servir de cette graine (que dois-je écrire, dans quelle cellule etc?)

Pour l'autre système, même ce qui apparait basique pour vous reste pour moi du chinois. Comment faire pour écrire une autre macro qui utiliserait cette Sub Mélanger et comment l'affecter à un bouton de formulaire que je ne sais absolument pas comment dessiner dans ma feuille.
C'est la 1ère fois que je me lance dans la création de ces programmes dans excel et comme c'est pour répondre à une demande ponctuelle cela risque fort d'être la dernière. Si vous voulez m'aider et vous avez déjà fait beaucoup il faut aller beaucoup plus en détail dans vos explications. Dès que j'ai une solution qui fonctionne je ne vous ennuierai plus avec mes questions de néophyte.
Bien à vous.


Excusez moi mais

 

[MATLEA]

OK
Pour l'option graine j'ai changé comme vous le demandez le programme du module MChgGraine par le votre en collant après "Option Explicit" le texte commençant par

Sub ChangerGraine(Optional ByVal Cible = "!Graine")  jusqu'à End Sub

. C'est ce qu'il fallait faire?
Comment dois-je concrètement me servir de cette graine (que dois-je écrire, dans quelle cellule etc?)

Pour l'autre système, même ce qui apparait basique pour vous reste pour moi du chinois. Comment faire pour écrire une autre macro qui utiliserait cette Sub Mélanger et comment l'affecter à un bouton de formulaire que je ne sais absolument pas comment dessiner dans ma feuille.
C'est la 1ère fois que je me lance dans la création de ces programmes dans excel et comme c'est pour répondre à une demande ponctuelle cela risque fort d'être la dernière. Si vous voulez m'aider et vous avez déjà fait beaucoup il faut aller beaucoup plus en détail dans vos explications. Dès que j'ai une solution qui fonctionne je ne vous ennuierai plus avec mes questions de néophyte.
Bien à vous.


Excusez moi mais virtual basic m'envoie un message d'erreur pour la graine: Erreur de compilation. Attendu: Expression !!!

 

[Dranreb]

Pour son utilisation, c'est le dernier argument, en 3ème position, de la fonction Hasard. J'avais mis 1,23456789 en attendant, au hasard ou presque, si je puis dire …
Insérez une forme dans une feuille. Ensuite clic droit, affectez une macro que vous aurez écrite sous forme de procédure Sub.
Pour invoquer une autre Sub, il lui suffit d'invoquer son nom suivi des arguments obligatoires nécessaires, séparés par des virgules.

 

[MATLEA]

Pour son utilisation, c'est le dernier argument, en 3ème position, de la fonction Hasard. J'avais mis 1,23456789 en attendant, au hasard ou presque, si je puis dire …
Insérez une forme dans une feuille. Ensuite clic droit, affectez une macro que vous aurez écrite sous forme de procédure Sub.
Pour invoquer une autre Sub, il lui suffit d'invoquer son nom suivi des arguments obligatoires nécessaires, séparés par des virgules.

Désolé mais c'est incompréhensible pour moi.

Je ne sais pas si dans la correction du module MChgGraine je dois laisser ou pas votre remarque et si je dois ou pas saisir autre chose. Je ne sais pas ce qu'est le 3ème argument de la fonction Hasard, où il se trouve, ce que je dois en faire et le rapport avec la graine sans même parler du rapport avec ma colonne de noms que je veux modifier. Impossible pour moi en l'état des infos de m'en sortir.

C'est pareil pour la macro. Je n'ai jamais écrit de macro, procédure Sub ou autre et j'ignore tout des arguments obligatoites. Vous n'avez pas affaire à un professionnel. Je suis incapable de vous suivre.
Cordialement.

 

[Dranreb]

Voir poste 3 :

=Hasard(LIGNE()-3;$AM$4:$AM$67;1,234565789)

Le troisième argument c'est 1,23456789. C'est celui la qu'il faudra remplacer par le nom d'une graine ou par une adresse de cellule contenant une valeur de graine.

 

[MATLEA]

Voir poste 3 :Le troisième argument c'est 1,23456789. C'est celui la qu'il faudra remplacer par le nom d'une graine ou par une adresse de cellule contenant une valeur de graine.

OK merci
et le nom de la graine est bien MChgGraine dans modules et voici ce que j'ai recopié de votre message précédent:

Sub ChangerGraine(Optional ByVal Cible = "!Graine")
Static DernH As Double: Dim H As Double
H = Date Mod 7 + Time: If H = 0 Then H = 7
If H = DernH Then H = H + 2 ^ -19
DernH = H
If TypeOf Cible Is Range Then Cible.Value = H: Exit Sub
If VarType(Cible) <> vbString Then MsgBox "Argument de type """ & TypeName(Cible) & """ incorrect.", _
"Spécifiez une cellule ou un nom pour la feuille ou le classeur.", _
vbCritical, "ChangerGraine": Exit Sub
On Error Resume Next
If Left$(Cible, 1) = "!" Then
If TypeOf ActiveSheet.Evaluate(Mid$(Cible, 2)) Is Range Then
ActiveSheet.Evaluate(Mid$(Cible, 2)).Value = H
Else
ActiveSheet.Names.Add Mid$(Cible, 2), H
If Err Then MsgBox "Err " & Err.Number & " lors de :" _
& vbLf & "ActiveSheet.Names.Add """ & Mid$(Cible, 2) & """, " & H _
& vbLf & Err.Description, vbExclamation, "ChangerGraine"
End If
Else
If TypeOf Application.Evaluate(Cible) Is Range Then
Application.Evaluate(Cible).Value = H
Else
ActiveWorkbook.Names.Add Cible, H
If Err Then MsgBox "Err " & Err.Number & " lors de :" _
& vbLf & "ActiveWorkbook.Names.Add """ & Cible & """, " & H _
& vbLf & Err.Description, vbExclamation, "ChangerGraine"
End If
End If
End Sub

 

[Dranreb]

Il ne vous reste plus qu'à écrire une petite macro, une Sub à affecter à un bouton, qui va fabriquer des noms de graines ou changer leurs valeurs en invoquant ChangerGraine pour autant de noms que vous aurez besoin de graines différentes pour vos utilisation de la fonction Hasard.
Tien, pourquoi dans le code que vous avez reproduit n'avez vous pas gardé les commentaires guides d'utilisation de ma Sub ChangerGraine ?

 

[MATLEA]

OK merci
et le nom de la graine est bien MChgGraine dans modules et voici ce que j'ai recopié de votre message précédent:

Sub ChangerGraine(Optional ByVal Cible = "!Graine")
Static DernH As Double: Dim H As Double
H = Date Mod 7 + Time: If H = 0 Then H = 7
If H = DernH Then H = H + 2 ^ -19
DernH = H
If TypeOf Cible Is Range Then Cible.Value = H: Exit Sub
If VarType(Cible) <> vbString Then MsgBox "Argument de type """ & TypeName(Cible) & """ incorrect.", _
"Spécifiez une cellule ou un nom pour la feuille ou le classeur.", _
vbCritical, "ChangerGraine": Exit Sub
On Error Resume Next
If Left$(Cible, 1) = "!" Then
If TypeOf ActiveSheet.Evaluate(Mid$(Cible, 2)) Is Range Then
ActiveSheet.Evaluate(Mid$(Cible, 2)).Value = H
Else
ActiveSheet.Names.Add Mid$(Cible, 2), H
If Err Then MsgBox "Err " & Err.Number & " lors de :" _
& vbLf & "ActiveSheet.Names.Add """ & Mid$(Cible, 2) & """, " & H _
& vbLf & Err.Description, vbExclamation, "ChangerGraine"
End If
Else
If TypeOf Application.Evaluate(Cible) Is Range Then
Application.Evaluate(Cible).Value = H
Else
ActiveWorkbook.Names.Add Cible, H
If Err Then MsgBox "Err " & Err.Number & " lors de :" _
& vbLf & "ActiveWorkbook.Names.Add """ & Cible & """, " & H _
& vbLf & Err.Description, vbExclamation, "ChangerGraine"
End If
End If
End Sub

Il ne vous reste plus qu'à écrire une petite macro, une Sub à affecter à un bouton, qui va fabriquer des noms de graines ou changer leurs valeurs en invoquant ChangerGraine pour autant de noms que vous aurez besoin de graines différentes pour vos utilisation de la fonction Hasard.
Tien, pourquoi dans le code que vous avez reproduit n'avez vous pas gardé les commentaires guides d'utilisation de ma Sub ChangerGraine ?

J'avais enlevé les commentaires de crainte qu'ils n'interfèrent avec le code. J'ai remis le code d'origine.
Par contre la formule =Hasard(LIGNE()-3;$AH$4:$AH$67;MChgGraine) insérée dans la cellule appropriée ne fonctionne pas.

Pour la petite macro dois-je aller dans Affichage, puis Macro puis enregistrer une macro. C'est ca un Sub?

Merci

 

[Dranreb]

Il faut spécifier comme graine non pas un nom de procédure VBA mais un nom connu d'Excel.
La procédure ChangerGraine est capable de créer un tel nom lui étant spécifié en unique argument, délimité par des doubles apostrophes et d'y faire correspondre l'heure du jour de la semaine de l'exécution.
Une Sub est une procédure commençant par une instruction Sub, écrite dans la fenêtre de code d'un module standard.
Par exemple :

Sub ChangerLesGraines()
   ChangerGraine "Toto"
   ChangerGraine "Tata"
   ChangerGraine "Titi"
   ChangerGraine "Tutu"
   End Sub

Attention. En testant cette procédure je me suis aperçu que la Sub ChangerGraine fabriquait encore de valeurs identiques de graines.
C'est corrigé en changeant le début comme suit :

   Static DernH As Double: Dim H As Double
   H = Date Mod 7 + Time: If H = 0 Then H = 7
   Do While H <= DernH: H = H + 2 ^ -19: Loop
   DernH = H

 

[Dranreb]

Je change encore d'avis concernant le début de ma procédure ChangerGraine, car sinon il risquerait de se poser un problème au passage du dimanche soir très tard au lundi matin très tôt. Alors voici mon nouveau début de procédure :

   Static DernH As Double, Espilon As Double
   Dim H As Double
   H = Date Mod 7 + Time: If H = 0 Then H = 7
   If H = DernH Then
      Espilon = Espilon + 2 ^ -19: H = H + Espilon
   Else
      Espilon = 0: DernH = H
      End If

 

[MATLEA]

Il faut spécifier comme graine non pas un nom de procédure VBA mais un nom connu d'Excel.
La procédure ChangerGraine est capable de créer un tel nom lui étant spécifié en unique argument, délimité par des doubles apostrophes et d'y faire correspondre l'heure du jour de la semaine de l'exécution.
Une Sub est une procédure commençant par une instruction Sub, écrite dans la fenêtre de code d'un module standard.
Par exemple :

Sub ChangerLesGraines()
   ChangerGraine "Toto"
   ChangerGraine "Tata"
   ChangerGraine "Titi"
   ChangerGraine "Tutu"
   End Sub

Attention. En testant cette procédure je me suis aperçu que la Sub ChangerGraine fabriquait encore de valeurs identiques de graines.
C'est corrigé en changeant le début comme suit :

   Static DernH As Double: Dim H As Double
   H = Date Mod 7 + Time: If H = 0 Then H = 7
   Do While H <= DernH: H = H + 2 ^ -19: Loop
   DernH = H

Bonsoir,
votre phrase:

Il faut spécifier comme graine non pas un nom de procédure VBA mais un nom connu d'Excel.
La procédure ChangerGraine est capable de créer un tel nom lui étant spécifié en unique argument, délimité par des doubles apostrophes et d'y faire correspondre l'heure du jour de la semaine de l'exécution.
Avouez que pour un néophyte ce n'est pas simple;
Un nom connu d'excel! mais comment je sais moi quels noms connait excel?
Comment j'interroge la procédure Changergraine? et quand j'y serai je lui demande comment de créer un nom;
est-ce quelque chose comme cela? ""créer un nom de graine"" 17h22 lundi semaine 5.
Merci

 

[Dranreb]

La première procédure du poste #24 crée 4 noms qui, après, seront connus d'Excel : Toto, Titi, Tata et Tutu.
Elle utilise pour cela 4 fois la procédure ChangerGraine. Celle ci ne s'interroge donc pas: on l'invoque pour qu'elle crée des noms connu d'Excel dans le gestionnaire de noms ou pour changer les valeurs auxquelles ils correspondent s'ils existent déjà.
Ces noms pourront ensuite être utilisés comme graines en troisième paramètre de la fonction Hasard.

 

[MATLEA]

La première procédure du poste #24 crée 4 noms qui, après, seront connus d'Excel : Toto, Titi, Tata et Tutu.
Elle utilise pour cela 4 fois la procédure ChangerGraine. Celle ci ne s'interroge donc pas: on l'invoque pour qu'elle crée des noms connu d'Excel dans le gestionnaire de noms ou pour changer les valeurs auxquelles ils correspondent s'ils existent déjà.
Ces noms pourront ensuite être utilisés comme graines en troisième paramètre de la fonction Hasard.

Bonjour,
dans un nouveau module standard j'ai écrit:
Sub
Changer les graines()

Changer graine "Pointeurs"
Changer graine "Milieux"
Changer graine "Tireurs"
End Sub

Si je remplace 1,23456789 en 3ème position de la fonction hasard (exemple =Hasard(LIGNE()-3;$AM$4:$AM$67; pointeurs) j'obtiens un message d'erreurs et ca me renvoie sur mon module avec le message erreur de compilation, attendu: identificateur; Que faire?
Est-ce lié au poste #24 et à ses procédures et où se trouve ce poste #24?
Merci

 

[Dranreb]

Bonjour.
L'avez-vous exécutée, au moins votre procédure ?
Ça m'étonnerait vu que vous montrez un nom incorrect muni d'espaces et qui n'est même pas sur la même ligne que l'instruction Sub. Appelez la ChangerLesGraines en un seul mot, et affectez la à un bouton de formulaire pour pouvoir l'exécuter à tout moment. Pareil, c'est la Sub ChangerGraine qui doit être utilisée, pas Changer graine.

 

[MATLEA]

Bonjour,
dans un nouveau module standard j'ai écrit:
Sub
Changer les graines()

Changer graine "Pointeurs"
Changer graine "Milieux"
Changer graine "Tireurs"
End Sub

Si je remplace 1,23456789 en 3ème position de la fonction hasard (exemple =Hasard(LIGNE()-3;$AM$4:$AM$67; pointeurs) j'obtiens un message d'erreurs et ca me renvoie sur mon module avec le message erreur de compilation, attendu: identificateur; Que faire?
Est-ce lié au poste #24 et à ses procédures et où se trouve ce poste #24?
Merci

Bonjour.
L'avez-vous exécutée, au moins votre procédure ?
Ça m'étonnerait vu que vous montrez un nom incorrect muni d'espaces et qui n'est même pas sur la même ligne que l'instruction Sub. Appelez la ChangerLesGraines en un seul mot, et affectez la à un bouton de formulaire pour pouvoir l'exécuter à tout moment. Pareil, c'est la Sub ChangerGraine qui doit être utilisée, pas Changer graine.

Bonsoir,
On y est presque; je vous joins 2 fichiers pratiquement identiques: dans essai1 la formule avec en 3ème position de la fonction hasard les termes Pointeurs et Tireurs colonnes AI et AN (exemple cellule AN4 =Hasard(LIGNE()-3;$AM$4:$AM$67;Pointeurs) cela fonctionne. dans Essai2 pas le résultat attendu et je ne vois pas pourquoi.
Merci