Discussion :

[JohannJo]

Bonjour le forum, bonjour le fil,

Afin d'alléger le code de mes macros, j'essaie de créer des procédures que j'appelle au fur et à mesure. Et la je débute!

Voici mon problème:
j'ai le code suivant:

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
'Boucle de traitement pour les stats/////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////
        i = 2                                                                                                                       'initialisation des variables
        St_Rng_Car = 2
        St_Rng = 2
        L_Ac_Car = 0
        L_Ac = 0
 
        Range(.Cells(2, 1), .Cells(lr, 12)).Sort key1:=.Cells(2, 1), Order1:=xlAscending, key2:=.Cells(2, 2), Order2:=xlAscending
 
        Do
            'traitement par carrier////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////
            If .Cells(i, 2) <> .Cells(i + 1, 2) Or .Cells(i, 1) <> .Cells(i + 1, 1) Then                                                                                   'condition pour initialiser les stats: changement de carrier
                ReDim Preserve Stat_Car(L_Ac_Car)                                                                                  '(Re)Definition du tableau statistique à chaque changement de carrier (ajout d'une ligne/dimension au tableau, qui commence à 0) avec préservation des lignes/dimensions précédents
                Set Rng_Car = Range(.Cells(St_Rng_Car, 12), .Cells(i, 12))                                                         'Definition de la plage pour le calcul des stats
 
                Select Case i - St_Rng_Car                                                                                         'Condition pour calcul des stats: il faut plus de 2 valeurs!
                    Case Is > 0: Stat_Car(L_Ac_Car) = Array( Application.WorksheetFunction.CountA(Rng_Car), Application.WorksheetFunction.Median(Rng_Car), Application.WorksheetFunction.StDev(Rng_Car), Application.WorksheetFunction.Average(Rng_Car), Application.WorksheetFunction.Min(Rng_Car), Application.WorksheetFunction.Max(Rng_Car))      'remplissage de tableau de stats ==> chaque ligne est constitué d'un tableau avec les différents éléments des stats en colonne
                    Case Is = 0: Stat_Car(L_Ac_Car) = Array( "1", .Cells(i, 12), "/", .Cells(i, 12), .Cells(i, 12), .Cells(i, 12))
                End Select
 
                St_Rng_Car = i + 1                                                                                                 'nouvelle initialisation du debut de la plage à i + 1 (ligne ou le carrier change)
                L_Ac_Car = L_Ac_Car + 1                                                                                       'incrémentation de 1 de la variable pour ajout d'une lmension/ligne au tableau de stat en début de boucle if
 
                'Traitement par "triptyque" Paste-Screen-printer: idem que boucle carrier avec condition sur le if différent//////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////
                If .Cells(i, 3) <> .Cells(i + 1, 3) Or .Cells(i, 6) <> .Cells(i + 1, 6) Or .Cells(i, 7) <> .Cells(i + 1, 7) Then
                    ReDim Preserve Stat_PR(L_Ac)
                    Set rng = Range(.Cells(St_Rng, 12), .Cells(i, 12))
 
                        Select Case i - St_Rng
                            Case Is > 0: Stat_PR(L_Ac) = Array( Application.WorksheetFunction.CountA(rng), Application.WorksheetFunction.Median(rng), Application.WorksheetFunction.StDev(rng), Application.WorksheetFunction.Average(rng), Application.WorksheetFunction.Min(rng), Application.WorksheetFunction.Max(rng))
                            Case Is = 0: Stat_PR(L_Ac) = Array( "1", .Cells(i, 12), "/", .Cells(i, 12), .Cells(i, 12), .Cells(i, 12))
                        End Select
 
                        St_Rng = i + 1
                        L_Ac = L_Ac + 1
                End If
                'Fin Traitement par "triptyque" Paste-Screen-printer//////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////
            End If
 
            i = i + 1                                                                                                                   'incrémentation de la variable i de 1
        Loop While i < lr + 1
 
'----insertion des stats
Range(.Cells(1, 1), .Cells(UBound(Stat_Car) + 4, 1)).EntireRow.Insert Shift:=xlDown
For L_Ac = LBound(Stat_Car) To UBound(Stat_Car)                                                                                        'boucle pour copie du premier tableau, la variable court donc de 0 (car base0) à la limite supérieur du tableau
     Range(.Cells(L_Ac_Car + 3, 1), .Cells(L_Ac_Car + 3, 13)) = Stat_Car(L_Ac)                           'copie de la ligne l du tableau dans les cellules de colonne 1 à 10 (on sait qu'uil y a 10 colonnes dans le tableau)
Next L_Ac

comme mentionné précédemment, je souhaite créer une procédure "externe" pour générer mes tableaux de stat (car ce code est utilisé moultes fois dans mes divers macros), donc pour générer les variables Stat_Car ou Stat_PR.
J'ai donc créé la "sous procédure" suivante afin de générer les tableaux de stats:

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
 
Public Sub Statistiques(i As Integer, St_Rng As Integer, L_Ac As Integer, feuille, To_Treat() As Variant, lr)
 
Dim essai As Worksheet: Set essai = ThisWorkbook.Worksheets(feuille)
  Stat = To_Treat
 
    ReDim Preserve Stat(L_Ac)
 
    Set rng = Range(essai.Cells(St_Rng, 10), essai.Cells(i, 10))                                                        'Definition de la plage pour le calcul des stats
        Select Case i - St_Rng                                                                                          'Condition pour calcul des stats: il faut plus de 2 valeurs!
            Case Is > 0
                Nb = Application.WorksheetFunction.CountA(rng)
                med = Application.WorksheetFunction.Median(rng)
                St_Dev = Application.WorksheetFunction.StDev(rng)
                Moy = Application.WorksheetFunction.Average(rng)
                Min = Application.WorksheetFunction.Min(rng)
                Max = Application.WorksheetFunction.Max(rng)
                    Stat(L_Ac) = Array( Nb, med, St_Dev, Moy, Min, Max)
            Case Is = 0
                T_P = essai.Cells(i, 10)
                Stat(L_Ac) = Array( "1", T_P, "/", T_P, T_P, T_P)
        End Select
 
 Stat_Car = Stat
End Su

Et j'appelle cette procédure dans ma procédure d'appel:

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
 'Boucle de traitement pour les stats/////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////
        i = 2                                                                                                                       'initialisation des variables
        St_Rng = 2
        L_Ac = 0
 
        Do
        Dim feuille As String
        feuille = wsh_mass.Name
 
            'traitement par carrier////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////
            If .Cells(i, 3) <> .Cells(i + 1, 3) Or .Cells(i, 6) <> .Cells(i + 1, 6) Then                                                                                   'condition pour initialiser les stats: changement de carrier
                Dim To_Treat() As Variant
                    ReDim Preserve To_Treat(L_Ac)                                                                              
 
                    Call Statistiques(i, St_Rng, L_Ac, feuille, To_Treat(), lr) '==> appel de la procédure
                        Stat_Car = Stat
                        St_Rng = i + 1                                                                                                 
                        L_Ac = L_Ac + 1 
 
'----insertion des stats
Range(.Cells(1, 1), .Cells(UBound(Stat_Car) + 4, 1)).EntireRow.Insert Shift:=xlDown
For L_Ac = LBound(Stat_Car) To UBound(Stat_Car)                                                                                        'boucle pour copie du premier tableau, la variable court donc de 0 (car base0) à la limite supérieur du tableau
          Range(.Cells(L_Ac_Car + 3, 1), .Cells(L_Ac_Car + 3, 13)) = Stat_Car(L_Ac)                           'copie de la ligne l du tableau dans les cellules de colonne 1 à 10 (on sait qu'uil y a 10 colonnes dans le tableau)
Next L_Ac

Lors de l'insertion de ce tableau à l'aide de la procédure principale donc, j'ai une erreur de type 9 (l'indice n'appartient pas à la sélection) qui souligne la ligne:

Code :

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

Range(.Cells(1, 1), .Cells(UBound(Stat_Car) + 4, 1)).EntireRow.Insert Shift:=xlDown

Il semble donc que la procédure principale n'arrive pas à connaitre la valeur de "UBound(Stat_Car)". J'avoue qu'à force de bidouiller, j'avais un code fonctionnel cette après midi (donc une insertion dans ma "procédure principale" du tableau généré dans ma "sous procédure"); hélas, j'ai voulu mettre au propre et ai supprimé plus de lignes qu'il n'aurait fallu et depuis, je bloque sur cette erreur9.

Puis je vous demander conseil SVP pour résoudre ce problème?
Question plus générale, comment utiliser dans une "procédure principale", une variable générée dans une "sous procédure".

En espérant que ma question soit suffisamment compréhensible,
Cdt

[Belga16]

Une piste : soit "Stat_Car" n'est pas déclarée comme array ou qu'elle n'est pas initialisée, soit vous y mettez plus d'éléments que sa dimension permet (comme essayer de mettre 2 litres dans une bouteille d'un litre)

[Philippe Tulliez]

Bonjour,
Lorsque l'on crée une procédure qui doit renvoyer une valeur, on utilise une procédure Function et pas une Sub
Pour que votre variable tableau puisse contenir le résultat calculé dans la procédure nommée Statistiques, il faudrait que celle-ci soit déclarée en tête de module (à condition que les deux procédures soient présentes dans le même module) ou comme variable publique.

[JohannJo]

Bonjour le fil,

Merci de vos retours.

@Belga16:
je déclare ma variable "Stat_Car" dans la procédure principale

Code :

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

1
2
Dim Stat_Car() As Variant
                    ReDim Preserve

et ma variable "Stat" dans la procédure secondaire

Code :

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

1
2
Dim Stat() As Variant
                    ReDim Preserve

et je déclare les 2 comme variants, ce qui est le type de variables préconisés il me semble?

@ Philippe
Merci pour le conseil de passé en fonction plutôt qu'en procédure. Une fonction peut donc retourner un tableau (je pensais que non)?
Concernant la déclaration, mes variables sont déclarées en tête de procédure. Pour ce qui est de la variable publique, je regarde car je ne connais pas.


Pour information, si je place le code

Code :

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

1
2
Stat_Car = Stat
MsgBox (UBound(Stat_Car))

en fin de ma procédure "statistiques", la dimension de "Stat_Car" est bien incrémentée à chaque fois.
Si je place le même code dans la procédure principale juste après l'appel, j'ai une erreur.

[Philippe Tulliez]

Bonjour,

Une fonction peut donc retourner un tableau (je pensais que non)?

Une fonction peut même retourner un objet ou une collection d'objets

Quelques exemples d'une fonction qui renvoie une variable tableau dans les billets ci-dessous

• VBA/Excel - Fonction qui renvoie la liste des fichiers d'un répertoire en fonction d'un critère
• VBA - Excel - Fonction qui renvoie la liste des feuilles d'un classeur suivant critère

Pour ce qui est de la variable publique, je regarde car je ne connais pas

Je ne peux que vous conseiller la lecture de ce tutoriel Utiliser les variables en VBA Excel et tout particulièrement le chapitre V. Les niveaux de variables

[JohannJo]

Un grand merci pour votre aide.
Après une déclaration des variables utilisées comme publique, le code fonctionne.

Philippe, un grand merci pour votre aide et pour la documentation. Je m'empresse d'en prendre connaissance