copier un tableau de plus de 65536 éléments dans un range Résolu

Question

Bonjour, 
je désire afficher le résultat d'un tableau sur une feuille excel.
pour éviter une boucle for ... next (lourde et gourmande en temps - d'autant plus que sera utilisée souvent) 
donc j'ai pensé utiliser : resize et transpose

Sub essaiAffiche()
Dim TabEssai()

Application.ScreenUpdating = True
Worksheets("Essai").Activate

RienL = 65536 'erreur si > 65536!
ReDim TabEssai(RienL)

For RienL1 = 1 To RienL
TabEssai(RienL1) = "azertyuio" ' pour l'exemple
Next

[D:D].ClearContents
[D1].Resize(RienL) = Application.Transpose(TabEssai)
End Sub

[D1].Resize(RienL) = Application.Transpose(TabEssai)  :
Cela marche bien tant que RienL est <= à 65536 qui doit être une limite de Resize ou Transpose...
puis au delà : erreur (erreur d'exécution 13,  "incompatibilité de type")
merci de votre aide pour contourner cette limite
très cordialement

ucfoutu · Answer

Bonjour,
1) la méthode Transpose n'a en effet pas été programmée par l'équipe de Microsoft pour transposer au-delà de 65535 (et non 65536) lignes
 D'autres fonctionnalités de Excel sont dans le même cas, notamment celles s'appuyant sur la méthode SpecialCells.
L'équipe Microsoft est la seule à pouvoir te répondre sur les raisons pour lesquelles elle n'a pu ou pas voulu appliquer ces méthodes à un nombre plus grand de lignes.

2) Si tu tiens vraiment à utiliser cette méthode pour donner à une plage de cellules les valeurs d'un tableau, rien ne t'empêche de procéder par tronçons

3) d'autres manières existent (sans transpose) pour faire ce que tu veux faire ===>> utilisation d'un tableau dynamique à 2 dimensions (et non une seule)

je vais rechercher sur la toile une discussion qu'il me semble avoir lue à ce même propos il y a 1 à 2 ans (???). Je reviens si je la retrouve.
________________________
Réponse exacte ? => "REPONSE ACCEPTEE" facilitera les recherches.
Pas d'aide en ligne installée ? => ne comptez pas sur moi pour simplement répéter son contenu. Je n'interviend

ucfoutu · Answer

Finalement assez vite retrouvée, cette discussion-là ===>>
 http://www.mrexcel.com/forum/excel-questions/555073-limitation-range-size-transpose-function.html
tout y est dit, tant en ce qui concerne le "tronçonnage" (si transpose) que l'utilisation directe d'un tableau dynamique à 2 dimensions.
Bonne lecture
 
________________________
Réponse exacte ? => "REPONSE ACCEPTEE" facilitera les recherches.
Pas d'aide en ligne installée ? => ne comptez pas sur moi pour simplement répéter son contenu. Je n'interviend

ucfoutu · Answer

Ta difficulté, par contre, va être de ne traiter qu'une colonne..
Il va alors falloir faire un petit coup de "zouzgef" pour feinter tout cela.
J'ai choisi d'insérer une colonne, puis de la supprimer ===>> regarde ce que fait ceci :

Private Sub CommandButton8_Click()  Application.ScreenUpdating = False  RienL = 100000 'erreur si > 65536!  ReDim TabEssai(1 To RienL, 1)  colonne = 1 '===>> ici le numéro de la colonne où écrire tes valeurs  For rienl1 = 1 To RienL    TabEssai(rienl1, 1) = rienl1 & "a" ' pour l'exemple  Next  With Worksheets("Essai")    .Columns(colonne).ClearContents    .Columns(colonne).Insert Shift:=xlShiftleft    .Range(.Cells(1, colonne), .Cells(UBound(TabEssai), colonne + 1)).Value = TabEssai    .Columns(colonne).Delete  End With  Application.ScreenUpdating = TrueEnd Sub
ouais, ouais ... MDR

ClaudeDordogne · Answer

tout d'abord : MERCI
car, encore une fois, Grace à toi, j'ai trouvé la solution :)
je te joins le code ok pour si cela intéresse d'autres :)

Sub TransposeBis()
Dim arr(), N As Long

[D:D].ClearContents
Application.Calculation = xlCalculationManual
N = Application.InputBox("How many trials?", Type:=1)
ReDim arr(1 To N, 1 To 1)
    For i = 1 To N
        arr(i, 1) = Int(Rnd * 10) + 1
    Next i

Application.ScreenUpdating = False
Range("E1") = N
With Range("D2")
    .Resize(N, 1).Value = arr   'Application.Transpose(arr)
End With
Application.Calculation = xlCalculationAutomatic
End Sub

à noter que :
Application.ScreenUpdating = False
est optionnel mais permet d'accélerer les choses.

Ta remarque sur les "65535 (et non 65536) lignes" me surprend, en effet le code :
Option Base 1
Sub essaiAffiche()
Dim TabEssai()

Application.ScreenUpdating = True
Worksheets(2).Activate

RienL = 65536 'erreur si > 65536!
ReDim TabEssai(RienL)

For RienL1 = 1 To RienL
TabEssai(RienL1) = "azertyuio" ' pour l'exemple
Next

[D:D].ClearContents
[D1].Resize(RienL) = Application.Transpose(TabEssai)
End Sub

fonctionne

en effet si option base est à 0 seul 65535 est possible, mais cela commence à la ligne 2 car TabEssai(0) existe et donc on a bien 65536 elts avec redim TabEssai(65535).
et si option base est à 1 cela fonctionne avec  redim TabEssai(65536)

ClaudeDordogne · Answer

cela devrait également fonctionner pour les dictionnaires.

j'en profite pour te remercier aussi pour m'avoir orienté sur les dictionnaires (dont j'ignorais l'existence") qui sont tellement plus rapides que les tableaux
très cordialement
ps : ton surnom "ucfoutu" amuse vraiment ma fille de 6 ans.

ClaudeDordogne · Answer

j'ai comparé les vitesses des 2 codes
je te laisse le soin de regarder...

j'ai fait une boucle supplémentaire (For rienN = 1 To 10) pour avoir un temps affichable

je ne sais pas comment afficher efficacement le temps écoulé en B2 (ou E2), peux tu me conseiller? merci d'avance

dans le mien, Application.ScreenUpdating = True à la fin n'est pas necessaire pour l'affichage... pourquoi??

Private Sub LeTien()  Application.ScreenUpdating = False  rienl = 100000 'erreur si > 65536!  ReDim TabEssai(1 To rienl, 1)  colonne = 1 '===>> ici le numéro de la colonne où écrire tes valeurs  For rienl1 = 1 To rienl    TabEssai(rienl1, 1) = rienl1 & "a" ' pour l'exemple  Next  maintenant = Now  For rienN = 1 To 10  With Worksheets("Essai")    .Columns(colonne).ClearContents    .Columns(colonne).Insert Shift:=xlShiftleft    .Range(.Cells(1, colonne), .Cells(UBound(TabEssai), colonne + 1)).Value = TabEssai    .Columns(colonne).Delete    .Range("B1") = "Toi"    .Range("B2") = Now - maintenant    .Range("C1") = rienl  End With  Next rienN  Application.ScreenUpdating = TrueEnd SubSub LeMien()Dim TabEssai()Dim N As Long[D:D].ClearContentsApplication.Calculation = xlCalculationManual'N = Application.InputBox("How many trials?", Type:=1)N = 100000ReDim TabEssai(1 To N, 1 To 1)  For rienl1 = 1 To N    TabEssai(rienl1, 1) = rienl1 & "a" ' pour l'exemple  NextApplication.ScreenUpdating = Falsemaintenant = Now  For rienN = 1 To 10With Worksheets("Essai")    .Range("D2").Resize(N, 1).Value = TabEssai   'Application.Transpose(TabEssai)    .Range("E1") = "Moi"    .Range("E2") = Now - maintenant    .Range("F1") = NEnd WithNext rienNApplication.Calculation = xlCalculationAutomaticEnd Sub

ucfoutu · Answer

- Mon pseudo n'est dû qu'à mon âge très avancé (je n'y peux rien. Je subis)
- simplement, en ce qui concerne screenUpdating : il revient spontanément à True à la fin d'une procédure. Mais utilise-le systématiquement. Cela te permettra dans certains cas de "voir" en réel ce qui se passe, notamment lorsque tu ne changes pas de fenêtre/feuille.
- en ce qui concerne la mesure du temps d'affichage : les "puristes te diront qu'il est "bon" d'utiliser la fonction GetTickCount de la librairie kernel32 de l'Api de Windows. Ce n'est vrai (et encore ! ...) que pour des traitements très courts, sur un "benchmark". 
Pour des traitements aussi longs que le tien, il est superfétatoire de faire une telle "dépense". Pour éviter quoi, finalement ? une durée supplémentaire d'un millième de seconde sur une boucle de 10 000 000 ? ===>> ridicule.
Il te suffit d'utiliser la fonction Timer. Regarde ===>>>
 Dim debut As Doubledebut = TimerFor i = 0 To 1000000 toto = toto + 2NextMsgBox "cette boucle a duré " & Timer - debut & " secondes"
________________________
Réponse exacte ? => "REPONSE ACCEPTEE" facilitera les recherches.
Pas d'aide en ligne installée ? => ne comptez pas sur moi pour simplement répéter son contenu. Je n'interviend

ClaudeDordogne · Answer

" Mon pseudo n'est dû qu'à mon âge très avancé (je n'y peux rien. Je subis)"
elle pensait que ton pseudo signifiait que quand quelqu'un te demandait de l'aide, c'est qu'il pensait que "ouh c'est foutu!" .....  et que tu le sauves de sa misère .... :))
en tout cas ton cerveau fonctionne mieux que celui de nombreux "jeunes"..... 
en tout cas merci pour tout et pour ta disponibilité à aider les autres.
voilà, maintenant GRACE à toi je peux mettre: 
 Marquer comme résolu
Merci!

ClaudeDordogne · Answer

bonjour,
encore une question....
sais tu comment sauvegarder mon dictionnaire? (put ne fonctionne pas (:   )
merci

ucfoutu · Answer

Va voir ma réponse dans ton autre discussion à ce sujet (dans 2 minutes environ)

Copier un tableau de plus de 65536 éléments dans un range

10 réponses

Votre réponse

Discussions similaires