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

[Promethee25]

Bonjour Eric,

Dit autrement, le code de Philippe:

1. copie en un coup tout le tableau Excel dans le tableau VBA (rapide)
2. boucle sur chaque cellule du tableau VBA pour la traiter (rapide)
3. copie en un coup tout le tableau VBA traité dans le tableau Excel (rapide)

Ton code, s'il utilise un tableau VBA, ne fait en aucun cas une copie de masse entre Excel et VBA: il copie le tableau Excel case par case dans le tableau VBA (lent) puis recopie case par case le tableau VBA traité dans le tableau Excel (lent).

[eric4459]

Const TableName As String = "t_Data"

copie en un coup tout le tableau Excel dans le tableau VBA (rapide)

Je ne comprends pas trop comment définir TableName, ma plage de cellule se situe entre D4 et DYA491

Je vous recontacterai début mai après mes vacances, je pars demain matin.
Merci en tous cas
Eric

[Philippe Tulliez]

Bonjour,
La constante TableName que j'utilise dans le code que j'ai publié est le nom du tableau structuré mais cela pourrait tout aussi bien être le nom d'une plage de cellules nommée à l'aide du gestionnaire des noms.

Cependant on peut tout aussi bien charger une variable tableau en lui passant la référence de cette plage

Exemple

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
Sub t()
  ' Déclaration
  Dim rng As Range
  Dim t As Variant
  Dim r As Long
  Set rng = ThisWorkbook.Worksheets("Base 0").Range("B6:L15")
  ' Transfert des données vers la variable tableau
  t = rng.Value
  ' Traitement
  For r = 1 To UBound(t)
    If t(r, 8) > 0 Then t(r, 11) = 100
  Next
  ' Transfert de la variable tableau vers la plage de cellules
  rng.Value = t
  '
  Set rng = Nothing
End Sub

[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