Copier un tableau de plus de 65536 éléments dans un range [Résolu]

ClaudeDordogne 47 Messages postés mardi 13 janvier 2015Date d'inscription 7 mars 2015 Dernière intervention - 16 févr. 2015 à 22:35 - Dernière réponse : ucfoutu 18039 Messages postés lundi 7 décembre 2009Date d'inscriptionModérateurStatut 11 avril 2018 Dernière intervention
- 18 févr. 2015 à 17:44
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
Afficher la suite 

Votre réponse

10 réponses

ucfoutu 18039 Messages postés lundi 7 décembre 2009Date d'inscriptionModérateurStatut 11 avril 2018 Dernière intervention - Modifié par ucfoutu le 17/02/2015 à 08:04
0
Utile
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
Commenter la réponse de ucfoutu
ucfoutu 18039 Messages postés lundi 7 décembre 2009Date d'inscriptionModérateurStatut 11 avril 2018 Dernière intervention - Modifié par ucfoutu le 17/02/2015 à 08:13
0
Utile
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
Commenter la réponse de ucfoutu
ucfoutu 18039 Messages postés lundi 7 décembre 2009Date d'inscriptionModérateurStatut 11 avril 2018 Dernière intervention - 17 févr. 2015 à 10:41
0
Utile
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 = True
End Sub

ouais, ouais ... MDR
Commenter la réponse de ucfoutu
ClaudeDordogne 47 Messages postés mardi 13 janvier 2015Date d'inscription 7 mars 2015 Dernière intervention - 17 févr. 2015 à 11:19
0
Utile
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)
Commenter la réponse de ClaudeDordogne
ClaudeDordogne 47 Messages postés mardi 13 janvier 2015Date d'inscription 7 mars 2015 Dernière intervention - 17 févr. 2015 à 11:24
0
Utile
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.
Commenter la réponse de ClaudeDordogne
ClaudeDordogne 47 Messages postés mardi 13 janvier 2015Date d'inscription 7 mars 2015 Dernière intervention - 17 févr. 2015 à 12:21
0
Utile
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 = True
End Sub




Sub LeMien()
Dim TabEssai()
Dim N As Long

[D:D].ClearContents
Application.Calculation = xlCalculationManual
'N = Application.InputBox("How many trials?", Type:=1)
N = 100000
ReDim TabEssai(1 To N, 1 To 1)
For rienl1 = 1 To N
TabEssai(rienl1, 1) = rienl1 & "a" ' pour l'exemple
Next

Application.ScreenUpdating = False

maintenant = Now
For rienN = 1 To 10
With Worksheets("Essai")
.Range("D2").Resize(N, 1).Value = TabEssai 'Application.Transpose(TabEssai)
.Range("E1") = "Moi"
.Range("E2") = Now - maintenant
.Range("F1") = N
End With
Next rienN
Application.Calculation = xlCalculationAutomatic

End Sub
Commenter la réponse de ClaudeDordogne
ucfoutu 18039 Messages postés lundi 7 décembre 2009Date d'inscriptionModérateurStatut 11 avril 2018 Dernière intervention - Modifié par ucfoutu le 17/02/2015 à 12:49
0
Utile
- 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 Double
debut = Timer
For i = 0 To 1000000
toto = toto + 2
Next
MsgBox "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
Commenter la réponse de ucfoutu
ClaudeDordogne 47 Messages postés mardi 13 janvier 2015Date d'inscription 7 mars 2015 Dernière intervention - 17 févr. 2015 à 14:48
0
Utile
" 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!
Commenter la réponse de ClaudeDordogne
ClaudeDordogne 47 Messages postés mardi 13 janvier 2015Date d'inscription 7 mars 2015 Dernière intervention - 18 févr. 2015 à 14:47
0
Utile
bonjour,
encore une question....
sais tu comment sauvegarder mon dictionnaire? (put ne fonctionne pas (: )
merci
Commenter la réponse de ClaudeDordogne
ucfoutu 18039 Messages postés lundi 7 décembre 2009Date d'inscriptionModérateurStatut 11 avril 2018 Dernière intervention - 18 févr. 2015 à 17:44
0
Utile
Va voir ma réponse dans ton autre discussion à ce sujet (dans 2 minutes environ)
Commenter la réponse de ucfoutu

Vous n'êtes pas encore membre ?

inscrivez-vous, c'est gratuit et ça prend moins d'une minute !

Les membres obtiennent plus de réponses que les utilisateurs anonymes.

Le fait d'être membre vous permet d'avoir un suivi détaillé de vos demandes et codes sources.

Le fait d'être membre vous permet d'avoir des options supplémentaires.