Aide Redim Tableau

**BoorBox** · 26/12/2018, 17h41

Bonjour à tous.

Je ne connais pas très bien les fonctionnalités VBA et je souhaiterais avoir une information pas très importante.
Je cherche à compter le nombre de données dans un tableur et les enregistrer dans un tableau dynamique.
Est ce qu'il vaut mieux niveau efficacité et temps :
-réaliser 2 boucle identiques avec la première qui compte le nombre d'éléments et qui à la fin Redim mon tableau dynamique sur le nombre de caractère trouvé et ensuite on enregistre les données dans le tableau
-réaliser une boucle ou pour chaque élément trouvé on l'enregistre et on Redim Preserve mon tableau dynamique

**patricktoulon** · 26/12/2018, 17h53

bonsoir
c'est pas tres clair
d'autant plus que selon les données il y a plus simple qu'une boucle les filtre par exemple et le .speciacells des cells visibles
bref plus de detail sur ton contexte serait utile

**Menhir** · 26/12/2018, 17h59

Vu que transformer une solution en l'autre (donc faire un second code à partir du premier) doit prendre à peu près 5 min, le plus simple pour obtenir une réponses fiable ne serait-il pas de faire le test par toi-même ?

**boisgontierjacques** · 26/12/2018, 18h07

Bonjour,

La manipulation de tableau est très rapide.
Redim Preserve ne pénalise pas.
Son inconvénient, c'est que c'est la dernière dimension du tableau qui peut être modifiée, ce qui amène parfois à utiliser Application.Transpose qui est limité à 65.000 lignes.
Utilisé pour alimenter un ListBox e.g., il suffit d'utiliser la propriété .Column au lieu de .List

Boisgontier

**BoorBox** · 26/12/2018, 18h08

Je pense pas que tu as compris le sens de ma question.
Je souhaite remplir un tableau dynamique, temporaire qui n'est pas une feuille excel, juste un tableau.
VBA semble avoir le même fonctionnement que le language C avec les MALLOC (allocation dynamique de mémoire) avec
l'utilisation de la fonction Redim, et VBA possède l'option Preserve qui permet de conserver les données du tableau.

Du coup pour être un peu plus concis sur ma question:
Est ce que l'utilisation répétée de Redim Preserve en VBA peut prolonger de manière significative la durée d’exécution?
Et je voulais par la même occasion avoir des informations sur le fonctionnement de Redim.

**BoorBox** · 26/12/2018, 18h13

Merci boisgontierjacques.

Menhir pour tester moi même je ne peux pas voir de changement significatifs étant donné que je travail sur des échantillons et que par la suite dans mon développement faire la modification aurait pu changer énormément mon code.

**patricktoulon** · 26/12/2018, 18h16

re
je pense c'est plutot toi qui n'a pas compris ma remarque interrogative

Je cherche à compter le nombre de données dans un tableur et les enregistrer dans un tableau dynamique.

pour quoi veux tu compter et remplir une variable tableau !!!!!!!!???????????
tu peux tres bien faire ton tableau directement

Code :

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

1
2
3
4
5
dim montableau
mon tableau=range("A1:f5000").value
'et pour compter
msgbox "montableau fait " & ubound(montableau) & " lignes"
msgbox "montableau fait " & ubound(montableau,2) & " colonnes"

**boisgontierjacques** · 26/12/2018, 19h15

Essai avec tableau entrée 10.000 lignes/10 colonnes - Tableau sortie 5.000 lignes:
0,015 s dimension fixe et 0,04 s avec Redim Preserve
Essai avec tableau entrée 20.000 lignes/10 colonnes - Tableau sortie 10.000 lignes:
0,04 s dimension fixe et 0,25 s avec Redim Preserve

Boisgontier

**Menhir** · 28/12/2018, 10h12

Envoyé par BoorBox

Est ce qu'il vaut mieux niveau efficacité et temps :
-réaliser 2 boucle identiques avec la première qui compte le nombre d'éléments et qui à la fin Redim mon tableau dynamique sur le nombre de caractère trouvé et ensuite on enregistre les données dans le tableau
-réaliser une boucle ou pour chaque élément trouvé on l'enregistre et on Redim Preserve mon tableau dynamique

Autre solution qui combine les avantages des deux : initialement, tu dimensionnes ton tableau à une taille nettement supérieure au nombre de valeur à stocker, tu le remplies en comptant les valeurs, tu fais un Redim final pour l'ajuster.

**boisgontierjacques** · 28/12/2018, 11h21

Bonjour,

Dimension fixe en calculant la taille avant:

1 ligne sur 2 est sélectionnée (20.000 lignes x 10 colonnes dans le tableau TblRes() : 0,09 secondes

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
Sub essaiDimFixe()
   t = Timer
   Set Rng = [J2:J40000]
   n = Application.CountIf(Rng, "X")
   ReDim TblRes(1 To n, 1 To 10)
   TblBD = [A2:J40000].Value
   n = 0
   For i = 1 To UBound(TblBD)
     If TblBD(i, 10) = "X" Then
       n = n + 1
       For k = 1 To 10: TblRes(n, k) = TblBD(i, k): Next k
     End If
   Next i
  '[M2].Resize(UBound(TblRes), 10) = TblRes
  MsgBox Timer - t
End Sub

Redim final

-Pour faire un Redim à la fin,oblige à travailler sur un tableau transposé TblRes() -10 colonnes x 40.000 lignes-
-Si on transpose pour obtenir le tableau 'normal', 0,20 sec=0,09 sec+0,11 sec

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
 
Sub essaiRedimFinal()
   t = Timer
   TblBD = [A2:J40000].Value
   Dim TblRes(): ReDim TblRes(1 To 10, 1 To UBound(TblBD))
   For i = 1 To UBound(TblBD)
     If TblBD(i, 10) = "X" Then
       n = n + 1
       For k = 1 To 10: TblRes(k, n) = TblBD(i, k): Next k
     End If
   Next i
   ReDim Preserve TblRes(1 To 10, 1 To n)
   TblRes2 = Application.Transpose(TblRes)    ' 
   '[M2].Resize(n, 10) = TblRes2
    MsgBox Timer - t
End Sub

Boisgontier

**BoorBox** · 28/12/2018, 12h00

Je vous remercie de vos réponses, la solution pour laquelle j'ai opté:

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
 
Private Sub fill_nd_tbl(ByRef nd_tbl() As need)
    nd_row = first_nd_row
 
 
    While (Cells(nd_row, ref_col) <> "")
        If (Cells(nd_row, desc_col) <> "") Then
            nd_col = first_nd_col
            While (Cells(date_row, nd_col) <> "")
                If (Cells(nd_row, nd_col) <> "" And Cells(nd_row, nd_col) <> 0) Then
                    ReDim Preserve nd_tbl(v_row)
                    nd_tbl(v_row).ref = Cells(nd_row, ref_col)
                    nd_tbl(v_row).desc = Cells(nd_row, desc_col)
                    nd_tbl(v_row).qty = Cells(nd_row, nd_col)
                    nd_tbl(v_row).date = Cells(date_row, nd_col)
                    v_row = v_row + 1
                End If
                nd_col = nd_col + 1
            Wend
        End If
        nd_row = nd_row + 1
    Wend
 
End Sub

J'ai cherché à gagner du temps sur mon code de différentes façons en pensant que c'était mes fonctions qui prenaient du temps alors que le soucis
était que mon code doit ouvrir d'autres classeurs et que ceux ci ont une quantité de données énorme et qui pour les 4/5 ne me servent pas, et j'ai
découvert que les tableurs excels utilisent la RAM du PC pour afficher les données ou quelque chose du genre .

D'ailleurs si vous avez une solution pour utiliser les données de tableurs sans les ouvrir ou juste ouvrir une feuille de calcul sans charger les autres
je vous serais très reconnaissant.

Néanmoins je vous remercie de vos réponses qui m'ont apportées différents points de vue sur le dimensionnement des variables tableau VBA.

**boisgontierjacques** · 28/12/2018, 12h20

Travailler directement sur des cellules est très pénalisant en temps d'exécution (comme travailler sur les objets d'un Userform aussi)
Il faut:
-transférer les champs du tableur dans des Arrays
-travailler sur ces Arrays
-renvoyer des Arrays dans le tableur.

Dans certains cas, indexer les Arrays par des dictionnaires pour éviter de rechercher séquentielement une clé dans un Array

Boisgontier

**BoorBox** · 28/12/2018, 16h05

Ok je ferai l'effort d'exploiter mes tableurs par des arrays.
Cependant est ce qu'il est possible d'extraire les données d'un tableur directement de façon structuré?

Du style:

Code :

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

1
2
3
4
5
6
7
8
9
 
type document
     nom as string
     date as date
     taille as long
     type as string
end type
 
dim tbl_doc() as document

ou on aurait une extraction du genre

Code :

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

1
2
 
tbl_doc=[A2:J40000].Value

Avec les données déja rangées.
Ou est il possible d'extraire directement un tableau structuré tout en gardant sa structure?

**boisgontierjacques** · 28/12/2018, 16h56

Voila un exemple de tri d'un Array() de structures.
La programmation est plus compliquée et ne doit pas être plus performant que le tri d'un Array() classique (0,1 sec pour 10.000 lignes et 3 colonnes)

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
Option Compare Text
Type Personne
  Nom As String
  Dte As Long
  Ville As String
End Type
 
Sub triSimpleStructures()
  t = Timer
  Set Rng = Range("A2:C" & [A65000].End(xlUp).Row)
  TblE = Rng.Value
  n = UBound(TblE)
  Dim a() As Personne: ReDim a(1 To n)
  For i = 1 To UBound(TblE)
    a(i).Nom = TblE(i, 1): a(i).Dte = TblE(i, 2): a(i).Ville = TblE(i, 3)
  Next i
  '--- tri  par nom
  Tri a(), LBound(a), UBound(a)
  '-- transfert feuille pour test
  Dim TblS(): ReDim TblS(1 To n, 1 To UBound(TblE, 2))
  For i = 1 To n
    TblS(i, 1) = a(i).Nom
    TblS(i, 2) = a(i).Dte
    TblS(i, 3) = a(i).Ville
  Next i
  [A2].Resize(n, UBound(TblS, 2)).Value = TblS
  MsgBox Timer - t
End Sub
 
Sub Tri(a() As Personne, gauc, droi)  ' Quick sort
  Dim tmp As Personne
  pref = (gauc + droi) \ 2
  ref = a(pref).Nom
  g = gauc: d = droi
  Do
    Do While a(g).Nom < ref: g = g + 1: Loop
    Do While a(d).Nom > ref: d = d - 1: Loop
    If g <= d Then
      tmp = a(g): a(g) = a(d): a(d) = tmp
      g = g + 1: d = d - 1
    End If
  Loop While g <= d
  If g < droi Then Call Tri(a, g, droi)
  If gauc < d Then Call Tri(a, gauc, d)
End Sub

Egalement en PJ un module de classe pour gérer des enregistrements.

Boisgontier

**Mat.M** · 28/12/2018, 17h17

Envoyé par BoorBox

Je ne connais pas très bien les fonctionnalités VBA et je souhaiterais avoir une information pas très importante.
Je cherche à compter le nombre de données dans un tableur et les enregistrer dans un tableau dynamique.

en 2019 on ne fait plus de "redim preserve"

Maintenant tout le code source que je fais utilise abondamment des "conteneurs" ( en C++)
Donc en VBA il faut créer des classes ou des structures ( "types") et utiliser des collections d'objets ainsi on n'a pas à se soucier du redimensionnement du tableau

**BoorBox** · 28/12/2018, 17h48

D'accord dommage qu'il n'y ait pas de fonction qui traite directement les tableau structurés d'Excel et qu'il soit nécessaire de traiter nous même les données.
Merci.

Je fais un peu d'objet et tout ça mais en C et VBA il est nécessaire d'allouer de la mémoire pour créer des tableaux, non? ou ma mémoire est périmée?

Je viens de regarder comment ça fonctionne.
.
.
.
c'est tricher, c'est du python

Aide Redim Tableau

Macros et VBA Excel

Discussions similaires

Partager

Partager