Insérer un tableau d'une feuille dans une variable tableau

Version imprimable

1 pièce(s) jointe(s)

Bonjour,

Je travaille dans un Foyer d'Hébergement (FH) accueillant des personnes handicapées,
ces personnes ont un studio au FH et travaillent sur le même site (soit à l'ATELIER soit à la PENSION POUR ANIMAUX)
et J'ai créé un fichier excel pour gérer les présences des résidents au FH et aux repas.
J'ai besoin d'aide pour améliorer mon code qui n'est pas satisfaisant au niveau de la vitesse d'exécution.

Ci joint un petit échantillon de ce fichier => 3 feuilles :
- "2021" : Planning de travail des personnes handicapés (en sachant que dans la feuille 2021 certaines personnes sont des externes et ne vivent pas au fh, donc n'apparaissent pas dans la feuille "stats repas")
- "Fiche_Resident" : Liste des résidents avec le numéro de ligne de chaque résident dans la feuille "2021" et le numéro de ligne dans la feuille "Stats Repas"
- "Stats Repas" : Planning des présences des résidents au FH et aux repas (Midi et Soir)

Si les personnes travaillent (feuille "2021"), ils sont présents au FH, si ils ne travaillent pas ils sont présent au FH si ils sont renseignés présents au FH dans la ligne+2 de la feuille "Stats Repas".

J'ai mon code ci-dessous qui fonctionne et qui me permet de reporter les plannings de travail des résidents ("2021") sur le planning des présences FH ("Stats Repas")
et avec ces données et les données de la ligne+2 de la feuille "Stats repas" je peux calculer si ils sont présent ou pas au FH et remplir la ligne+1 de la feuille "Stats repas" : soit avec "FH" si ils ne travaillent pas, soit avec la valeur du code travail (1 pour Atelier en orange OU 2 pour la Pension en vert).
L'exécution du code est trop lente, j'ai donc réduit ce code pour les colonnes du 10 au 25 Aout.
Ce code s'exécute en cliquant sur la forme de ligne 54 ("Copie Planning ESAT ...").
Code:

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 Sub TestCopieEsatVersFH() Application.Calculation = xlCalculationManual: Application.ScreenUpdating = False: ActiveSheet.DisplayPageBreaks = False: Application.DisplayAlerts = False: Application.EnableEvents = False Dim col As Integer: Dim ligneR As Integer: Dim LigneESAT As Integer: Dim ligne As Integer: Dim numres As Integer: Dim LL As Integer For ligne = 4 To 39 'Parcours les lignes de la feuille Fiche_Resident numres = ligne - 3 'calcul le numéro du résident selon le numéro de ligne car la liste des résidents commence à la ligne 4 de la feuille Fiche_Resident If Sheets("Fiche_Resident").Cells(ligne, 4) <> "" Then 'si la ligne de Fiche_Resident n'est pas vide With Sheets("Fiche_Resident") 'Alors affectation de la var LigneR(numéro de ligne du résident 'dans la feuille Stats Repas) ET la var LigneESAT (numéro de ligne du résident dans la feuille 2021) ligneR = .Cells(numres + 3, 4): LigneESAT = .Cells(numres + 3, 8) End With With Sheets("Stats repas") ' Pour parcourir le tableau feuille Stats Repas, normalement de la colonne 12 à 375 mais trop long ' donc je fais un echantillon de la colonne 230 à 245 (Du 10 au 25 Aout) For col = 230 To 245 'Parcoues des colonnes de la feuille Stats Repas (parcours les dates de l'année) For LL = 13 To 390 'Parcoures les colonnes de la feuille 2021 (parcours les dates de l'année) If CDate(Sheets("2021").Cells(5, LL)) = CDate(Sheets("Stats repas").Cells(55, col)) Then 'si les 2 dates correspondent Sheets("Stats repas").Cells(ligneR, col) = Sheets("2021").Cells(LigneESAT, LL) 'affectation de la valeur de la feuille 2021 dans lka feuille Stats Repas If Sheets("2021").Cells(LigneESAT, LL) = 0 And (Sheets("Stats repas").Cells(ligneR + 2, col) = "x" Or Sheets("Stats repas").Cells(ligneR + 2, col) = "x>" Or Sheets("Stats repas").Cells(ligneR + 2, col) = "x<") Then ' Verifie si le resident est présent au FH ' si la cellule de 2021 = 0 et si la cellule de la ligne +2 de la feuille Stats Repas = x ou x> ou x< Sheets("Stats repas").Cells(ligneR + 1, col) = "FH" 'si oui noté "FH" dans la ligne du dessous (LigneR+1) ElseIf Sheets("2021").Cells(LigneESAT, LL) > 2 And (Sheets("Stats repas").Cells(ligneR + 2, col) = "x" Or Sheets("Stats repas").Cells(ligneR + 2, col) = "x>" Or Sheets("Stats repas").Cells(ligneR + 2, col) = "x<") Then ' idem si valeur > 2 (0=absent : >2=codes congés ou vacances : 1 et 2 = codes pour signaler que le resident travaille et est donc present au FOYER Sheets("Stats repas").Cells(ligneR + 1, col) = "FH" Else ' Sinon affectation de la valeur de la feuille 2021 dans la ligne +1 (LigneR+1) de la feuille Stats Repas Sheets("Stats repas").Cells(ligneR + 1, col) = Sheets("2021").Cells(LigneESAT, LL) End If End If Next Next col End With End If Next ligne Application.Calculation = xlCalculationAutomatic: Application.ScreenUpdating = True: ActiveSheet.DisplayPageBreaks = True: Application.DisplayAlerts = True: Application.EnableEvents = True End Sub
Je pense qu'il faudrait pour accélérer l'exécution du code créer une variable tableau de la feuille "2021" et utiliser cette variable pour remplir la feuille "Stats repas".

Malheureusement je ne maitrise pas du tout les variables tableaux et après quelques essais je n'arrive à rien.

Pourriez vous svp m'aider à améliorer ce code et utiliser si besoin une ou plusieurs variables Tableau?

Je vous en serais très reconnaissant

Je vous remercie de votre aide

Cordialement

Cordialement

Bonjour,

Sans passer par des variables tableau, ceci prend 50" sur mon pc:

Code:

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
Sub TestCopieEsatVersFH()
    Dim col As Integer
    Dim ligneR As Integer
    Dim LigneESAT As Integer
    Dim ligne As Integer
    Dim numres As Integer
    Dim LL As Integer
    Application.Calculation = xlCalculationManual
    Application.ScreenUpdating = False
    ActiveSheet.DisplayPageBreaks = False
    Application.DisplayAlerts = False
    Application.EnableEvents = False
    Debug.Print Timer
    For ligne = 4 To 39             'lignes feuille Fiche_Resident
        numres = ligne - 3          'calcul n° résident selon n° ligne car la liste des résidents commence à la ligne 4 de la feuille Fiche_Resident
        If Sheets("Fiche_Resident").Cells(ligne, 4) <> "" Then
            'si la ligne de Fiche_Resident n'est pas vide
            With Sheets("Fiche_Resident")
                'affectation de la var LigneR (n° ligne résident dans feuille Stats Repas)
                'ET la var LigneESAT (n° ligne résident dans feuille 2021)
                ligneR = .Cells(numres + 3, 4)
                LigneESAT = .Cells(numres + 3, 8)
            End With
            With Sheets("Stats repas")
               'parcourir les dates tableau feuille Stats Repas --- 308 = dernière colonne
               For col = 12 To 308         'colonnes feuille Stats Repas
                        LL = col + 8       'colonnes feuille 2021, même date que dans feuille Stats Repas
                        If Sheets("2021").Cells(5, LL) <> .Cells(55, col) Then
                            Debug.Print "pbl date en colonne ";  col
                            Stop
                        End If
                        'si les 2 dates correspondent
                        .Cells(ligneR, col) = Sheets("2021").Cells(LigneESAT, LL) 'affectation valeur en feuille 2021 dans feuille Stats Repas
                        ' Verifie si resident présent au FH si cellule de 2021 = 0
                        ' et si la cellule ligne +2 de la feuille Stats Repas contient un "x"
                        ' idem si valeur > 2 (0=absent : >2=codes congés ou vacances : 1 et 2 = codes pour signaler que le resident travaille et est donc present au FOYER
                        ' Sinon affectation de la valeur de la feuille 2021 dans la ligne +1 (LigneR+1) de la feuille Stats Repas
                        If Sheets("2021").Cells(LigneESAT, LL) = 1 Then
                            .Cells(ligneR + 1, col) = Sheets("2021").Cells(LigneESAT, LL)
                        Else
                            If InStr("x", .Cells(ligneR + 2, col)) = 0 Then
                                .Cells(ligneR + 1, col) = Sheets("2021").Cells(LigneESAT, LL)
                            Else
                                .Cells(ligneR + 1, col) = "FH"
                            End If
                        End If
                Next col
            End With
        End If
    Next ligne
    Debug.Print Timer
    Application.Calculation = xlCalculationAutomatic
    Application.ScreenUpdating = True
    ActiveSheet.DisplayPageBreaks = True
    Application.DisplayAlerts = True
    Application.EnableEvents = True
End Sub

Il n'est vraiment pas utile de reparcourir les dates dans les 2 feuilles alors qu'il n'y a qu'un décalage constant entre les 2.
Par ailleurs, il est possible de simplifier les conditions (mais code proposé à vérifier).
Cordialement.

06/10/2021, 19h38
Pierre Fauconnier

1 pièce(s) jointe(s)

Salut.

Range("...").Value Renvoie un variable Array (un tableau en VBA) à deux dimensions de base 1 (quelle que soit la ligne Option Base du module). On peut l'affecter à une variable déclarée Variant ou non typée.

Pièce jointe 606149

Perso, je travaillerais avec des tableaux structurés, et j'utiliserais au maximum les outils Excel en VBA (voir ma signature... ;)
11/10/2021, 09h13
persjussysylvain

Désolé pour le retard.

Votre code fonctionne bien
Je vous remercie

Ca reste long, je vais peut-être remettre des formules dans la feuille.
Et essaie de comprendre l'utilisation des tableaux

Je vous remercie
Cordialement
11/10/2021, 10h37
Pierre Fauconnier
Dans le fichier donné, il y a des formules interclasseur. C'est un des 7 péchés capitaux d'Excel, voire le premier et le pire de tous. Les formules entre classeurs sont à bannir à tout jamais.

Il faut:
- privilégier une approche par Power Query par exemple pour importer les données du classeur TSH2021.xlsx dans le classeur de travail puis travailler en local.
- utiliser les tableaux structurés pour le stockage des données;
- éviter de mélanger le stockage et l'exploitation des données.
En stockant les données dans des tableaux structurés, on peut alors réaliser le planning avec des formules utilisant des fonctions qui savent travailler facilement sur ces données, et si l'on passe par VBA, on a, là aussi, des moyens plus étendus et des techniques actuelles pour réaliser le planning.
11/10/2021, 10h52
persjussysylvain

Merci pour tous ces conseils,

Ca me semble compliqué pour moi de réaliser tout cela mais je vais me pencher sur vos conseils et vos cours.
Je ne connaissais par PowerQuery, je suis en train de regarder;
J'ai du mal avec les tableaux (excel, stucuturé , vba) Je vais me pencher sérieusement sur votre cours pour comprendre comment l'utiliser dans ma situation.

Je vous remercie

Cordialement
11/10/2021, 11h05
Pierre Fauconnier

Une solution s'appuyant sur une conception correcte du classeur sera toujours moins compliquée qu'une usine à gaz. La complexité d'une solution ne dépend que de la complexité des règles de gestion mises en oeuvre, et non des manipulations d'Excel ;). Si les manipulations sont compliquées, c'est que la conception est foireuse ^^

Quelle est ta version d'Excel?
11/10/2021, 11h47
persjussysylvain

Sur le poste au travail c'est 2007 il me semble.
Mon fichier est beaucoup plus gros et encore plus usine à gaz :?
11/10/2021, 11h55
Pierre Fauconnier

Avec Excel 2007, tu oublies Power Query. Mais depuis "la nuit des temps", Excel propose MS Query qui permet d'importer une table de données d'un autre classeur.

On peut bien sûr toujours aider à maintenir l'usine à gaz, mais bon, elle pète simplement plus fort lorsqu'elle pète. Ca n'a pour moi pas de sens de fonctionner ainsi.