Appliquer une procédure à une feuille

**ch0ubida** · 16/12/2014, 11h09

Bonjour à tous,

J'ai crée une procédure adaptée à une feuille en particulier. Mais j'aimerais que la procédure se répète sur x feuilles.
Je m'explique: suite au remplissage d'un userform par l'utilisateur, une macro crée autant de feuilles que nécessaire.
Je dois donc adapter la macro qui marche pour une feuille à toutes les autres. Pour se faire, je souhaiterai rentrer en paramètre la feuille voulue et effectuer une boucle pour remplir toutes les feuilles. Malheureusement ça ne marche pas, je dois mal écrire le paramètre ou mal appeler ma sub par la suite.

Code :

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
26
27
28
29
Public Sub GenererMatin(Feuille As String)
 
    Dim Calorie As Double
    Dim compteur As Double 'Comparateur de calories
    Dim i As Integer
 
   [...] 
 
 Dim CellNomGeneral As Range, CellNom As Range, CellMasse As Range, CellCalorie As Range
    Set CellNomGeneral = Sheets(Feuille).Range("B5")
    Set CellNom = Sheets(Feuille).Range("D6")
   Set CellMasse = Sheets(Feuille).Range("E6")
   Set CellCalorie = Sheets(Feuille).Range("F6")
 
   With TabPetitDej(m, 7)
    CellNomGeneral.value = .Nom
   End With
 
    For i = 1 To 6
        Dim j As Integer
        j = i - 1
        With TabPetitDej(m, i)
           CellNom.Offset(j, 0).value = .Nom
            CellMasse.Offset(j, 0).value = Masse(i)
            CellCalorie.Offset(j, 0).value = .Calorie * Masse(i) / 100
        End With
    Next
 
End Sub

Ensuite, dans un autre module, je fais appelle à ma sub

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
Public Sub Test()
Call DeclarationPetitDejeuner
Call GenererMatin(Feuil6)
End sub

Je ne sais pas si j'ai bien déclaré ma feuille, normalement c'est un string si j'ai bien compris
Mais ensuite, pour y faire appel, que dois je faire?
Call GenererMatin("Feuil6") ?
Call GenererMatin(Feuil6) ?
Admettons que Feuil6 soit le nom donné automatique par excel mais que la feuille en question a été renommée Toto, dois-je écrire:
Call GenererMatin("Toto") ?
Call GenererMatin(Toto) ?
Je ne comprends pas vraiment comment faire appel à ma procédure

Ce qui m'embête, c'est que je serai obligée d'appliquer une boucle pour remplir toutes mes feuilles, donc je ne vois pas comment faire autrement que ne pas spécifier la feuille lors du code, mais de l'entrer en paramètre. De plus, je ne renommerai pas mes feuilles, donc il me faut utiliser le nom donné par excel "Feuil1", "Feuil2" etc

Merci d'avance,
Bonne journée

**Thautheme** · 16/12/2014, 11h23

Bonjour Ch0ubida, bonjour le forum

Utilise le nom de l'onglet entre guillemets :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
Call GenererMatin("Feuil1")
Call GenererMatin("toto")

**ch0ubida** · 16/12/2014, 12h42

J'ai essayé avec des guillemets mais ça ne marche pas...
Dois-je mettre entre " ... " le nom utilisé par Excel où le nom que j'ai donné à la feuille lorsque je la renomme ?

**Docmarti** · 16/12/2014, 12h52

Bonjour Ch0ubida, bonjour le forum

Pour copier une feuille modèle vers une nouvelle feuille

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
Public Sub Test()
 
Call DeclarationPetitDejeuner
 
'Supposons que Planning est la feuille modèle
Set ws = Sheets.Add(After:=Sheets("Planning"))
 
Sheets("Planning").Cells.Copy ws.Cells(1, 1)
 
MsgBox ws.Name
 
Call GenererMatin(ws)
 
End Sub

**ch0ubida** · 16/12/2014, 12h53

J'ai joint mon fichier pour une meilleure compréhension.
Dans le module 5, je teste les deux types de Sub:
TestGeneral, ou je fais appel à une feuille
et TestLocal qui fait appel à la sub où la feuille est définie.

Ces deux subs se trouvent dans le module 1 sous "GénererMatinGeneral" et "GenererMatinCible".
J'ai pourtant l'impression de correctement faire appel à mes fonctions, mais puisque ça ne marche pas, j'imagine que je le fais mal.

Merci pour votre aide.

ProjetRepas.xlsm

**Docmarti** · 16/12/2014, 14h29

Bonjour ch0ubida

Voici comment créer créer une nouvelle feuille et y afficher le petit-déjeûner.

Pour faire simple, ajoute une feuille et nomme-la PREFS
La feuille PREFS ne servira qu'à conserver en A1 le nom de la dernière feuille créée par la routine CreerFeuille (Voir ci-dessous)

Pour qu'il n'y ait pas deux routines du même nom, supprime ta routine GenererMatinGeneral ou renomme-la.

Copie le code ci-dessous dans un module.

Code :

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
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
 
 
Sub Test
 Call GenererMatinGeneral
End Sub
 
Public Sub GenererMatinGeneral()
 
    Dim Calorie As Double
    Dim compteur As Double 'Comparateur de calories
    Dim i As Integer
    Dim prefs As Worksheet
    Dim Feuille  As String
 
    Call InitialisationPlanning
 
    Call CreerFeuille
 
    Set prefs = ThisWorkbook.Worksheets("Prefs")
 
    Feuille = prefs.Cells(1, 1)
 
    CalorieJour = 2000
 
    Calorie = CalorieJour * 0.3  '30% des calories sont réservées au petit déjeuner, c'est la quantité max à ne pas dépasser
 
    m = NombreAleatoire(1, 26)
 
'temporaire
    Dim Masse(6) As Double
 
    For i = 1 To 6
         With TabPetitDej(m, i)
                 Masse(i) = .Masse * 1.05    'On modifie la masse en lui appliquant un facteur multiplicateur inférieur à 1
         End With
    Next
'Temporaire fin    
 
 
 Dim CellNomGeneral As Range, CellNom As Range, CellMasse As Range, CellCalorie As Range
    Set CellNomGeneral = Sheets(Feuille).Range("B5")
    Set CellNom = Sheets(Feuille).Range("D6")
   Set CellMasse = Sheets(Feuille).Range("E6")
   Set CellCalorie = Sheets(Feuille).Range("F6")
 
   With TabPetitDej(m, 7)
    CellNomGeneral.Value = .Nom
   End With
 
    For i = 1 To 6
        Dim j As Integer
        j = i - 1
        With TabPetitDej(m, i)
 
           CellNom.Offset(j, 0).Value = .Nom
            CellMasse.Offset(j, 0).Value = Masse(i)
            CellCalorie.Offset(j, 0).Value = .Calorie * Masse(i) / 100
        End With
    Next
 
End Sub
 
Public Sub InitialisationPlanning()
 
'Calcul des besoins nutritionnels de l'utilisateur
Call CalculCaloriesParJour
 
'Mise en mémoire des menus
Call DeclarationPetitDejeuner
Call DeclarationEntree
Call DeclarationPlatPrincipal
Call DeclarationDessert
 
End Sub
 
Public Sub CreerFeuille()
 'Création d'une feuille
 
 Dim prefs As Worksheet
 Dim ws As Object
 
'Supposons que Planning est la feuille modèle
Set ws = Sheets.Add(After:=Sheets("Planning"))
 
ThisWorkbook.Sheets("Planning").Cells.Copy ws.Cells(1, 1)
 
'Sauvegarde le nom de la feuille créée 
Set prefs = ThisWorkbook.Worksheets("Prefs")
prefs.Cells(1, 1) = ws.Name
 
End Sub

**ch0ubida** · 16/12/2014, 22h08

J'ai trouvé une méthode pour générer des feuilles, ce n'est pas la tienne et j'ai un peu triché en utilisant le ActiveSheet.

En réalité, je n'ai pas tout compris à ta méthode pour être honnête. Mais en gros, tu as mis les méthodes dans GenererMatin, et j'ai fait l'inverse: j'ai tout mis dans CreationFeuilles.
Je ne sais pas si c'est la marche à suivre ou non, ou même si ma méthode est correcte.
En tout cas, quand j'y fais appel pour un certain nombre de feuilles, elle marche. C'est quand j'utilise l'userform que ça bloque...
Le fait est que lorsque je lance mon programme, une fois que le userform est rempli et que je le valide, ça tourne dans le vide... Je ne sais pas si Excel calcule les données ou si ça ne fait absolument rien!
J'ai pourtant ajouté

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

Application.ScreenUpdating = True

dans CreationFeuilles situé dans le module 6.

Aurais-tu une idée ?

Je désespère, ce fichu projet me donne bien du fil à retordre !!

**Docmarti** · 16/12/2014, 23h01

Envoyé par ch0ubida

J'ai trouvé une méthode pour générer des feuilles, ce n'est pas la tienne et j'ai un peu triché en utilisant le ActiveSheet.

En réalité, je n'ai pas tout compris à ta méthode pour être honnête. Mais en gros, tu as mis les méthodes dans GenererMatin, et j'ai fait l'inverse: j'ai tout mis dans CreationFeuilles.
Je ne sais pas si c'est la marche à suivre ou non, ou même si ma méthode est correcte.
En tout cas, quand j'y fais appel pour un certain nombre de feuilles, elle marche. C'est quand j'utilise l'userform que ça bloque...
Le fait est que lorsque je lance mon programme, une fois que le userform est rempli et que je le valide, ça tourne dans le vide... Je ne sais pas si Excel calcule

Bonjour.

Je n'ai pas encore testé avec l'userform.

Activesheet ne va pas t'aider. Au contraire. Mieux vaut le remplacer par ThisworkBook.Worksheets("NomDeLaFeuille").
Si tu trouves cela trop long à écrire, écris

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

Set f = ThisworkBook.Worksheets("NomDeLaFeuille").

Puis tu n'as plus qu'à écrire f au lieu de Activesheet.

Application.ScreenUpdating = True n'est pas à utiliser durant la phase de programmation.
Il ne sert à rien d'autre que d'accélérer l'exécution du programme. Mais il suspend l'affichage des modifications à l'écran.

Pour voir si Excel calcule, pèse sur les touches CTRL-Arrêt pour en suspendre l'exécution.

J'avais remarqué que ton code While compteur > Calorie
tournait toujours en rond.
Je l'avais donc enlevé dans mon post précédent.
Je l'ai remis dans le présent post, mais corrigé.

Si tu as des questions sur mon code, n'hésite pas à me les poser.

Code :

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
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
Public Sub GenererMatinGeneral()
 
    Dim Calorie As Double
    Dim compteur As Double 'Comparateur de calories
    Dim i As Integer
    Dim prefs As Worksheet
    Dim Feuille  As String
    Dim cal As Double
    Dim j As Integer
    Dim Masse(6) As Double
    Dim CellNomGeneral As Range, CellNom As Range, CellMasse As Range, CellCalorie As Range
 
    Call InitialisationPlanning
 
    Call CreerFeuille
 
    Set prefs = ThisWorkbook.Worksheets("Prefs")
 
    Feuille = prefs.Cells(1, 1)
 
    CalorieJour = 2000
 
    Calorie = CalorieJour * 0.3  '30% des calories sont réservées au petit déjeuner, c'est la quantité max à ne pas dépasser
 
    Set CellNomGeneral = Sheets(Feuille).Range("B5")
    Set CellNom = Sheets(Feuille).Range("D6")
   Set CellMasse = Sheets(Feuille).Range("E6")
   Set CellCalorie = Sheets(Feuille).Range("F6")
 
    m = NombreAleatoire(1, 26)
 
    For i = 1 To 6
         With TabPetitDej(m, i)
              Masse(i) = .Masse '* 1.05    'On modifie la masse en lui appliquant un facteur multiplicateur inférieur à 1
              compteur = compteur + (Masse(i) * .Calorie) / 100   'Compteur de calorie avec les masses de base
         End With
    Next
 
 While compteur < Calorie
 
            compteur = 0
            For i = 1 To 6
               With TabPetitDej(m, i)
                 Masse(i) = Masse(i) * 1.05    'On modifie la masse en lui appliquant un facteur multiplicateur inférieur à 1
                compteur = compteur + (Masse(i) * .Calorie) / 100 'On incrément le compteur avec les nouvelles masses
               End With
           Next
 
Wend
 
While compteur > Calorie
  compteur = 0
 
            cal = 0
            For i = 1 To 6
               With TabPetitDej(m, i)
                 Masse(i) = Masse(i) * 0.999    'On modifie la masse en lui appliquant un facteur multiplicateur inférieur à 1
                cal = cal + (Masse(i) * .Calorie) / 100 'On incrément le compteur avec les nouvelles masses
               End With
           Next
        compteur = cal
 
Wend
 
   With TabPetitDej(m, 7)
    CellNomGeneral.Value = .Nom
   End With
 
    For i = 1 To 6
        j = i - 1
        With TabPetitDej(m, i)
 
           CellNom.Offset(j, 0).Value = .Nom
            CellMasse.Offset(j, 0).Value = Masse(i)
            CellCalorie.Offset(j, 0).Value = .Calorie * Masse(i) / 100
        End With
    Next
 
End Sub

**ch0ubida** · 16/12/2014, 23h12

J'ai oublié de joindre mon fichier excel avec les modifications apportées.

Je ne comprends pas l'utilisation de la feuille "prefs". Comment vais-je faire appel à ma procédure par la suite puisque la feuille n'est pas rentrée en argument ? Je savais comment le faire avec ActiveSheet mais là je suis perdue.

La variable compteur suffit ? J'avais en effet rajouter une seconde variable nommée "cal" mais maintenant que je vois ton code, je me demande bien pourquoi ...

Est-ce que tu crois qu'il est possible, à la fin du code, d'ajouter:

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
For i = 1 To 6
        Dim j As Integer
        j = i - 1
        With TabPetitDej(m, i)
           CellNom.Offset(j, 0).value = .Nom
           If Masse(i) <> 0 Then
                CellMasse.Offset(j, 0).value = Masse(i)
                CellCalorie.Offset(j, 0).value = .Calorie * Masse(i) / 100
            Else
            CellMasse.Offset(j, 0).value = " "
            CellCalorie.Offset(j, 0).value = " "
            End If
        End With
    Next

à la place de:

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
For i = 1 To 6
        j = i - 1
        With TabPetitDej(m, i)
 
           CellNom.Offset(j, 0).Value = .Nom
            CellMasse.Offset(j, 0).Value = Masse(i)
            CellCalorie.Offset(j, 0).Value = .Calorie * Masse(i) / 100
        End With
    Next

Je n'ai pas spécialement envie d'avoir des 0 inutiles lors de l'affichage de mon planning. Je l'ai testé, mais puisque mon code ne marche pas, je ne sais pas si c'est bon ou non.

Je ne dois donc pas utiliser Application.ScreenUpdating = False ? Je pensais que ça allait calculer plus rapidement, du moins c'est ce que j'avais lu sur plusieurs forums.

ProjetRepas.xlsm

**ch0ubida** · 16/12/2014, 23h25

Bon en fait je n'ai vraiment pas tout compris...

Dans le premier While, cal n'existe plus. Mais dans le second, il y apparaît. Quelle est l'utilité de le supprimer dans un cas et pas dans l'autre ?
Je n'avais pas l'impression que ma boucle était infinie puisque lorsque je lançais la procédure, ça marchait. C'est l'userform qui rend le fonctionnement impossible.

Dans la procédure:

Code :

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
Public Sub CreerFeuille()
 'Création d'une feuille
 
 Dim prefs As Worksheet
 Dim ws As Object
 
'Supposons que Planning est la feuille modèle
Set ws = Sheets.Add(After:=Sheets("Planning"))
 
ThisWorkbook.Sheets("Planning").Cells.Copy ws.Cells(1, 1)
 
'Sauvegarde le nom de la feuille créée 
Set prefs = ThisWorkbook.Worksheets("Prefs")
prefs.Cells(1, 1) = ws.Name
 
End Sub

Qu'est-ce que signifie Sheets.Add(After:=Sheets("Planning")) ? ça ajoute une feuille après la feuille planning ? en fait, je ne peux pas avoir de feuille de référence. Donc je ne sais pas à partir de quoi copier. Ou alors je garde une feuille de référence mais je la masque pour l'utilisateur ?
Donc, est-ce qu'il serait possible que je garde ma feuille planning, que je la copie autant de fois que nécessaire (en la gardant masquée) et que je compile dans chacun des nouvelles feuilles les méthodes de génération de repas ? Je ne sais pas si la copie garde la mise en forme (couleur, largeur des colonnes, fusion, etc): j'avais créer une macro pour cela. Mais la copie permet de copier les boutons aussi non ? Ce serait peut-être plus pratique que de créer x feuilles avec une boucle.

Tu es assailli de questions, je suis désolée...

Mais ton aide est tellement utile que je me sens obligée !!

**ch0ubida** · 17/12/2014, 07h30

Tu n'as pas encore répondu, mais j'anticipe.
Mon projet étant à rendre demain, j'aimerais bien y venir à bout vu le temps passé dessus !

Je pense que plusieurs choses ne marcheront pas. J'ai testé la méthode CreerFeuille, et si je la mets dans GenererMatin, je ne vais pas pouvoir lancer GenererMidi et GenererSoir sur les feuilles créées. C'est pourquoi j'appelais les 3 méthodes de générations de repas dans la méthode CreerFeuilles que j'avais réalisé avec ActiveSheet. Etant donné que je n'ai pas tout compris à ton code, notamment avec la feuille Prefs et le fait qu'elle copie la cell(1,1) de Planning. Je n'arrive pas à modifier mon code en conséquent. Néanmoins, ta méthode marche, le problème étant que comme mes 3 repas sont dissociés, la création de feuilles ne marchera pas.

Sais-tu comment je pourrais faire pour avoir une date dans une cellule dans la première feuille, puis la date suivante sur la deuxième feuille. Par exemple, quand je copie Planning pour la mettre dans une autre feuille (ws si j'ai bien compris), et ce que je peux dire que

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

ws.Range("D3") = Worksheet("Planning").Range("D3") + 1

?

Je ne comprends pas comment mes méthodes peuvent parfaitement marcher indépendamment de l'userform, et cesser de fonctionner une fois que j'y fait appel lors du clic sur le bouton valide de l'userform. Tout marche parfaitement, ça devrait marcher ! Si tu prends mon projet (post précédent), et que tu fais un test en écrivant

Code :

Sélectionner tout - Visualiser dans une fenêtre à part