Discussion :

[eric4459]

Bonjour à tous,
Cela faisait longtemps que je n'avais pas touché aux macros excel et aux tableaux en particulier.
J'ai un fichier qui se présente comme ceci :
Dans les cellules sous les dates je souhaite distribuer le budget journalier pour chaque tâche sous les dates correspondantes.

J'ai un tableau, pour la partie à renseigner, de 491 lignes et 3344 colonnes ( de decembre 2021 à janvier 2031) soit plus de 1,6 millions de cellules. Etant donné le nombre de cellules à traiter j'ai imméditement pensé à utiliser un tableau.
J'ai consulté mes archives et mes anciens messages pour retrouver un peu de référence et voici ce à quoi je suis arrivé

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
Sub Assign_Budget()
Dim Data_1 As Worksheet
Dim Input_Data As Range
Dim Data_Month As Range
Dim Tableau_TCC2() As String
Dim i As Integer
Dim j As Integer
Dim k As Integer
Dim x As Integer
Dim y As Integer
Set Data_1 = ThisWorkbook.Sheets("Data_1")
Set Input_Data = Data_1.Range("D4")
Set Data_Month = Data_1.Range("L3")
Dim NbCol1 As Integer
Dim NbCol2 As Integer
Dim NbLig As Integer
 
With Data_1
NbLig = .Cells(.Rows.Count, 1).End(xlUp).Row
NbCol1 = .Cells(1, .Columns.Count).End(xlToLeft).Column - 11
End With
 
x = NbLig
y = NbCol1
 
ReDim Tableau_TCC2(1 To x, 1 To y)
 
For i = 1 To x
    For j = 1 To y
        If Data_Month.Offset(0, j - 1) >= Input_Data.Offset(i - 1, 0) And Data_Month.Offset(0, j - 1) < Input_Data.Offset(i - 1, 1) And Data_Month.Offset(-1, j - 1) <> "1" Then
            Tableau_TCC2(i, j) = Input_Data.Offset(i - 1, 6)
        Else
            Tableau_TCC2(i, j) = "0"
        End If
    Next j
Next i
 
 For i = 1 To x
        For j = 1 To y
            Data_Month.Offset(i, j - 1) = Tableau_TCC2(i, j)
        Next j
    Next i
 
End Sub

J'ai dû oublier quelquechose car la macro prend un temps fou pour s'executer

Je fais donc appel à vos connaissances pour m'aiguiller sur le bon chemin

Eric

[Franc]

Salut, tu pourrais désactiver les mises à jour d’écran et les calculs automatiques pendant l’exécution de la macro et voir ce que ça donne ?

[eric4459]

Bonjour Franc,
Oui j'ai ajouté Application.ScreenUpdating=False au début et Application.ScreenUpdating=True à la fin mais ça n'arrange rien à priori.
Peut-être la boucle qui charge mon tableau?
J'ai 491 x 3344 cellules ça fait 1 641 904 cellules, ça me semblait réalisable mais j'ai dû louper un truc
Eric

[Philippe Tulliez]

Bonjour,
Pourquoi ne pas charger la table directement en mémoire, effectuer le traitement et après celui-ci, transférer la table vers la plage de cellules et ce sans boucles ?

[EDIT]

Petit exemple
La table de données se nomme t_Data on écrit la valeur 100 dans la colonne 11 (Prime) de la table si le chiffre en colonne 8 (Enfants) est supérieur à 0

Code :

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

1
2
3
4
5
6
7
8
9
10
11
Sub t()
  Const TableName As String = "t_Data"
  Dim t As Variant
  Dim r As Long
  t = Range(TableName).Value      ' Chargement de la table
  ' Traitement en mémoire
  For r = LBound(t) To UBound(t)
    If t(r, 8) > 0 Then t(r, 11) = 100
  Next
  Range(TableName).Value = t      ' Ecriture des données depuis la table
End Sub

Illustration


Pour en savoir plus sur le sujet

• Utiliser les variables tableaux en VBA Excel
• Conceptualisation des variables tableau en VBA et Application à l'optimisation du code sous Excel

[eric4459]

Bonjour Philippe,

Pourquoi ne pas charger la table directement en mémoire

Ce code ne fais pas le job?

Code :

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

1
2
3
4
5
6
7
8
9
For i = 1 To x
    For j = 1 To y
        If Data_Month.Offset(0, j - 1) >= Input_Data.Offset(i - 1, 0) And Data_Month.Offset(0, j - 1) < Input_Data.Offset(i - 1, 1) And Data_Month.Offset(-1, j - 1) <> "1" Then
            Tableau_TCC2(i, j) = Input_Data.Offset(i - 1, 6)
        Else
            Tableau_TCC2(i, j) = "0"
        End If
    Next j
Next i

Eric

[Philippe Tulliez]

Bonjour Eric,

Ce code ne fais pas le job?

Non car dans votre code, vous chargez la variable tableau pendant la lecture du Range ce qui finalement augmente le temps de traitement.

Avez-vous regardé mon code exemple ?

• Etape 1 : Transférer la plage de cellules dans la variable tableau
• Etape 2 : Effectuer le traitement par boucle
• Etape 3 : Transférer le contenu de la variable tableau ainsi traitée vers la plage de cellules

[eric4459]

Merci Philippe,
C'est plus clair pour moi maintenant, je vais essayer à mon retour.
J'avais déjà utilisé les tableaux pour traiter un nombre important de données et c'était mieux que de travailler sur la feuille mais j'ai perdu la main et j'avoue que je n'utilisais pas cette méthode.
Va falloir faire les fonds de tiroirs pour retrouver ce que je faisais 😉.
Bon week-end à tous et à bientôt
Eric