nom de variable dans formule

[grodep]

bonjour à tous, je me tourne vers vous pour résoudre un petit souci : je cherche à créer par vba des feuilles et à y inclure du code lors de leur création. Pour se faire, j'ai donc écrit le code que je vous joins ci-dessous. Malheureusement le programme bloque sur une ligne(celle surlignée en rouge) que je n'arrive pas à écrire correctement.
si qqun peut m'aider dans mes travaux d'écriture...
merci d'avance

With ThisWorkbook
vry = Array("prévi", "vols", "sols", "recap")
    For i = 1 To 4
        .Sheets.Add After:=.Sheets(.Sheets.Count)
        .Sheets(.Sheets.Count).Name = vry(i)
        .Sheets.Select
       [COLOR="Red"] With ActiveWorkbook.VBProject.VBComponents(vry(i)).CodeModule[/COLOR]
            .InsertLines x + 1, "Private Sub Worksheet_Activate() 'à l'activation de l'onglet"
            .InsertLines x + 2, "Dim cel As Range 'déclare la variable cel (CELlule)"
            .InsertLines x + 3, "For Each cel In Range(""A2:"" & Range(""IV2"").End(xlToLeft).Address)" 'boucle sur toutes les cellules éditées cel de la ligne 2
            .InsertLines x + 4, "If cel.Value = Date Then" 'condition : si la valeur de la cellule est égale à la date du jour
            .InsertLines x + 5, "cel.Select" 'sélectionne la cellule
            .InsertLines x + 6, "Exit For" 'sort de la boucle
            .InsertLines x + 7, "End If" 'fin de la condition
            .InsertLines x + 8, "Next cel" 'prochaime cellule de la boucle
            .InsertLines x + 9, "End Sub"
        End With
    Next i
End With

 

[Cousinhub]

Re : nom de variable dans formule

Bonsoir,

tout d'abord, en VBA, un tableau commence par l'indice 0, à moins que tu ne définisses en déclaration "Option Base 1"

donc, i doit aller de 0 à 3....

ensuite, essaie ce code :

With ThisWorkbook
vry = Array("prévi", "vols", "sols", "recap")
    For i = 0 To 3
        .Sheets.Add After:=.Sheets(.Sheets.Count)
        .Sheets(.Sheets.Count).Name = vry(i)
        With .VBProject.VBComponents(Sheets(vry(i)).CodeName).CodeModule
            .InsertLines x + 1, "Private Sub Worksheet_Activate() 'à l'activation de l'onglet"
            .InsertLines x + 2, "Dim cel As Range 'déclare la variable cel (CELlule)"
            .InsertLines x + 3, "For Each cel In Range(""A2:"" & Range(""IV2"").End(xlToLeft).Address)" 'boucle sur toutes les cellules éditées cel de la ligne 2
            .InsertLines x + 4, "If cel.Value = Date Then" 'condition : si la valeur de la cellule est égale à la date du jour
            .InsertLines x + 5, "cel.Select" 'sélectionne la cellule
            .InsertLines x + 6, "Exit For" 'sort de la boucle
            .InsertLines x + 7, "End If" 'fin de la condition
            .InsertLines x + 8, "Next cel" 'prochaime cellule de la boucle
            .InsertLines x + 9, "End Sub"
        End With
    Next i
End With

Bonne soirée

 

[grodep]

Re : nom de variable dans formule

effectivement bhbh, mon classeur est en option base 1, j'ai omis d'inclure la déclaration dans le code partiel fourni.
En revanche, ta modification ne trouve pas grâce aux yeux d'Excel: soit excel me renvoie la même erreur qu'avant modification, soit, et c'est bien pire, excel crashe carrément!

 

[Cousinhub]

Re : nom de variable dans formule

Re-,

Ah bon?

je n'ai pas d'erreur, avec ce code :

Sub grodep()
With ThisWorkbook
vry = Array("prévi", "vols", "sols", "recap")
    For i = 0 To 3
        .Sheets.Add After:=.Sheets(.Sheets.Count)
        .Sheets(.Sheets.Count).Name = vry(i)
        With .VBProject.VBComponents(Sheets(vry(i)).CodeName).CodeModule
            .InsertLines x + 1, "Private Sub Worksheet_Activate() 'à l'activation de l'onglet"
            .InsertLines x + 2, "Dim cel As Range 'déclare la variable cel (CELlule)"
            .InsertLines x + 3, "For Each cel In Range(""A2:"" & Range(""IV2"").End(xlToLeft).Address)" 'boucle sur toutes les cellules éditées cel de la ligne 2
            .InsertLines x + 4, "If cel.Value = Date Then" 'condition : si la valeur de la cellule est égale à la date du jour
            .InsertLines x + 5, "cel.Select" 'sélectionne la cellule
            .InsertLines x + 6, "Exit For" 'sort de la boucle
            .InsertLines x + 7, "End If" 'fin de la condition
            .InsertLines x + 8, "Next cel" 'prochaime cellule de la boucle
            .InsertLines x + 9, "End Sub"
        End With
    Next i
End With
End Sub

Tu as bien "TOUT" recopié?

....

Edit : pour copier des lignes de code dans un module, ou une feuille, il faut bien sûr avoir autorisé le projet...
Comme tu ne daignes pas donner la version Excel que tu utilises, sous 2003, il faut faire :

"Outils/Macros/Sécurité"

et dans l'onglet "Editeurs Approuvés", cocher la case "Faire confiance au projet Visual Basic"

 

[grodep]

Re : nom de variable dans formule

Version d'excel : 2007. J'avais vainement cherché à le faire apparaitre dans mon profil, mais sans jamais trouver où se situe le changement à faire.(promis je vais fouiller de nouveau).
sinon oui, j'ai bien tout recopié intégralement le code, allant meme jusqu'à créer un nouveau classeur afin d'être sur que rien ne viendrait perturber celui que tu m'as donné.
et j'ai l'apparition du message :
"erreur d'exécution '9' : l'indice n'appartient pas à la sélection", le code souligné en jaune est celui ci "With .VBProject.VBComponents(Sheets(vry(i)).CodeName).CodeModule" .

Concernant le problème des autorisations, j'avais déjà été confronté à ce souci, et ai donc déjà coché la case correspondante sous excel 2007.


Edit: profil mis à jour, j'ai trouvé 🙂 je me demande même comment j'ai pu passer à coté la première fois 😛

 

[Cousinhub]

Re : nom de variable dans formule

re,

pour mettre ta version, tu cliques sur "Tab. de bord", dans le bandeau bleu

puis "modifier vos information", et tu descends....

pour ton souci, regarde le fichier excel joint (désolé pour les autres, mais c'est un fichier 2007...)

le code est le même que proposé plus haut...

 

[grodep]

Re : nom de variable dans formule

bon alors le souci est sans doute ailleurs :1er essai avec ton classeur ==> crash d'excel
2ème essai : réapparition de l'erreur d'exécution '9' !! Et ensuite tous les autres essais se soldent invariablement par le même code erreur.
Je crains d'avoir affaire à une panne excel plus qu'à une mauvaise écriture du code...

Edit: je viens d'essayer sur mon autre pc équipé lui aussi d'Excel 2007, et il se passe presque la même chose : je n'ai pas de crash excel, mais d'emblée le même code d'erreur. Bizarre, isn't it?

 

[Guest]

Re : nom de variable dans formule

Bonjour GroDep,
Re Bh²😀😀😀

Pas de problème non plus à part que puisque les options VBE que j'ai choisies rajoute automatiquement 'Option Explicite' à chaque module, j'ai du rajouter

[COLOR=black]With .VBProject.VBComponents(Sheets(vry(i)).CodeName).CodeModule[/COLOR]
[COLOR=black]          x = .CountOfLines[/COLOR]

Sinon pour le reste tu peux essayer:

        Set sh = .Sheets.Add(After:=.Sheets(.Sheets.Count))
        sh.Name = vry(i)
        With .VBProject.VBComponents(sh.CodeName).CodeModule

qui fonctionne parfaitement avec Excel 2002 et 2007

A+
A+++Hb²

 

[grodep]

Re : nom de variable dans formule

de pire en pire :
que je rajoute

x = .CountOfLines

ou que je remplace

.Sheets.Add After:=.Sheets(.Sheets.Count)
        .Sheets(.Sheets.Count).Name = vry(i)
        With .VBProject.VBComponents(Sheets(vry(i)).CodeName).CodeModule

par

Set sh = .Sheets.Add(After:=.Sheets(.Sheets.Count))
        sh.Name = vry(i)
        With .VBProject.VBComponents(sh.CodeName).CodeModule

j'ai le même résultat : Excel plante illico et sur mes deux pc!
j'ai tenté hier de faire toutes les mises à jour d'office 2007 sur l'un des deux pour voir si cela résolvait mon souci, mais rien à faire...
Si d'aventure, quelqu'un savait m'aiguiller,
merci d'avance

 

[grodep]

Re : nom de variable dans formule

En attendant de regler ce souci, je cherche à contourner le problème : si je fais une simple macro qui introduit du code dans des feuilles déjà existantes, cela fonctionne sans problème. Du coup, ma question est : comment écrire du code qui me retrouve les codename des différentes feuilles existantes, afin qu'ensuite je puisse écrire la suite du code de cette manière là :

With ThisWorkbook
With .VBProject.VBComponents([COLOR="Red"]"Feuil1"[/COLOR]).CodeModule

où "Feuil1" serait remplacé par le codename des feuilles présentes dans mon classeur.
La question peut paraitre saugrenue, mais lors de la création de mon classeur par macro, il supprime certaines feuilles pour en créer d'autres, et donc je ne sais pas à l'avance si les nouvelles feuilles générées vont s'appeler Feuil4, Feuil10, Feuil11 ou autres.
merci d'avance

Edit: on laisse tomber, ce que je cherche à faire revient exactement au même, à savoir introduire une variable dans la formule

With .VBProject.VBComponents([COLOR="Red"]"Feuil1"[/COLOR]).CodeModule

or c'est justement ce qui fait planter excel(chez moi), donc c'est le serpent qui se mord la queue!
Ne me reste plus qu'à comprendre pourquoi Excel plante et, visiblement, c'est pas gagné...

 

[job75]

Re : nom de variable dans formule

Bonjour grodep, bh2, Hasco,

J'ai essayé chez moi : Excel plante, le fichier récupéré montre que seule la 1ère feuille ("prévi") a été créée (avec son code).

A+

 

[MJ13]

Re : nom de variable dans formule

Bonjour Grodep, bh2, Ges, Job

Sinon, pour ne pas trop se prendre la tête, vous pouvez adapter cette macro (voir pièce jointe) pour copier des codes VBA dans d'autres fichiers.

C'était un code, que Pierrot93 que je salue, m'avait donné.

 

[Lii]

Re : nom de variable dans formule

Bonsoir,

C'est le "Private" qui pose problème dans la ligne

   .InsertLines x + 1, "[B]Private[/B] Sub   Worksheet_Activate() 'à l'activation de l'onglet"

Essaie :

  Dim vry, i As Byte, x As Byte
    Dim Sh
    vry = Array("prévi", "vols", "sols", "recap")
    For i = 0 To 3
      Sheets.Add After:=Sheets(Sheets.Count)
      Sheets(Sheets.Count).Name = vry(i)
      [I]ActiveSheet.[D2] = Date ' ici pour contrôle[/I]
      Set Sh = ActiveWorkbook.VBProject.VBComponents([B]ActiveSheet.CodeName[/B])
      With Sh.CodeModule
        .InsertLines x + 1, "[B]Sub[/B] Worksheet_Activate() 'à l'activation de l'onglet"
        .InsertLines x + 2, "  Dim cel As Range 'déclare la variable cel (CELlule)"
        .InsertLines x + 3, "  For Each cel In Range(""A2:[B]G[/B]"" & Range(""IV2"").End(xlToLeft).Column) 'boucle sur toutes les cellules éditées cel de la ligne 2"
        .InsertLines x + 4, "    If cel.Value = Date Then 'condition : si la valeur de la cellule est égale à la date du jour"
        .InsertLines x + 5, "      cel.Select 'sélectionne la cellule"
        .InsertLines x + 6, "      Exit For 'sort de la boucle"
        .InsertLines x + 7, "    End If 'fin de la condition"
        .InsertLines x + 8, "  Next cel 'prochaime cellule de la boucle"
        .InsertLines x + 9, "End Sub"
      End With
    Next i

Est-il utile de recopier plusieurs fois la même routine ?
Voilà un exemple avec une macro dans un module quelconque et une dans ThisWorkbook (avec ce que j'ai compris).

 

[grodep]

Re : nom de variable dans formule

Déjà merci à tous pour vos contributions et efforts afin de résoudre ce problème.
J'ai installé le SP2 office 2007 histoire de voir si cela changeait quelquechose, et l'espace d'un instant, j'ai cru enfin voir la lumière ! En lançant la macro telle qu'elle est écrite dans le fichier de bhbh, tout a fonctionné parfaitement. Youpi! Seul problème, quand j'ai tenté de répéter l'opération, j'ai eu de nouveau droit au même message d'erreur qu'avant!!
Idem avec la modification proposée par Lii, cela a fonctionné une fois, puis plus jamais. C'est vraiment à n'y rien comprendre, ceux chez qui ça fonctionne ont-ils tenté de répéter l'opération plus d'une fois(of course, j'ai supprimé les feuilles créées auparavant) ?
Bref, c'est à n'y rien comprendre : une macro, c'est comme une opération mathématique, si ça fonctionne une fois, ça doit fonctionner tout le temps!

 

[grodep]

Re : nom de variable dans formule

Bon et bien, après moults essais, ce code, qui est une sorte de compilation de celui de bhbh et de Lii fonctionne enfin, et à chaque fois ! Maintenant, ne me demandez pas pourquoi... 😀

Si d'aucuns veulent continuer à phosphorer sur le pourquoi du comment, je serais ravi de participer afin de déméler l'écheveau, car j'avoue qu'une macro qui fonctionne une fois puis, plus jamais, ça me laisse perplexe 🙂

Encore mille merci aux contributeurs !

Sub grodep()
Dim vry, i As Byte, x As Byte
    Dim Sh
    vry = Array("prévi", "vols", "sols", "recap")
    For i = 0 To 3
      Sheets.Add After:=Sheets(Sheets.Count)
      Sheets(Sheets.Count).Name = vry(i)
      ActiveSheet.[AB2] = Date ' ici pour contrôle
      Set Sh = ActiveWorkbook.VBProject.VBComponents(ActiveSheet.CodeName)
      With Sh.CodeModule
            .InsertLines x + 1, "Private Sub Worksheet_Activate() 'à l'activation de l'onglet"
            .InsertLines x + 2, "Dim cel As Range 'déclare la variable cel (CELlule)"
            .InsertLines x + 3, "For Each cel In Range(""A2:"" & Range(""IV2"").End(xlToLeft).Address)" 'boucle sur toutes les cellules éditées cel de la ligne 2
            .InsertLines x + 4, "If cel.Value = Date Then" 'condition : si la valeur de la cellule est égale à la date du jour
            .InsertLines x + 5, "cel.Select" 'sélectionne la cellule
            .InsertLines x + 6, "Exit For" 'sort de la boucle
            .InsertLines x + 7, "End If" 'fin de la condition
            .InsertLines x + 8, "Next cel" 'prochaime cellule de la boucle
            .InsertLines x + 9, "End Sub"
        End With
    Next i
End Sub

Edit: histoire d'en rajouter un peu : sur un autre pc sans l'update SP2, cette macro ne fonctionne toujours pas : "erreur d'exécution '9' : l'indice n'appartient pas à la sélection" .... décidément 😛