[Optimisation] code VBA transposition lignes vers colonnes

[Staple1600]

Bonsoir,

Est-ce que vous voyez un moyen d'optimiser le temps de traitement de la macro de transposition?
(ou un autre type de syntaxe)
J'ai ajouté une seconde macro pour créer les conditions de tests
(Le classeur de test doit contenir deux feuilles)

Sub TransposeLIG_COL()
Dim a As Variant, b As Variant
Dim i&, j&, k&
Dim t0 As Double
'Heure départ
t0 = Timer
Application.ScreenUpdating = False
' passage en calcul sur ordre
Application.Calculation = xlCalculationManual
'si error, on saute à FIN: pour remettre le calcul en automatique
On Error GoTo FIN
a = ActiveSheet.Cells(1).CurrentRegion.Value
ReDim b(1 To UBound(a, 1) * (UBound(a, 2) - 2), 1 To 4)
For i = 2 To UBound(a, 1)
  For j = 3 To UBound(a, 2)
    k = k + 1
    b(k, 1) = a(i, 1): b(k, 2) = a(i, 2): b(k, 3) = a(1, j): b(k, 4) = a(i, j)
  Next j
Next i
Sheets(2).Cells(1).Resize(UBound(b, 1), UBound(b, 2)).Value = b
FIN:
If Err.Number > 0 Then MsgBox "Erreur n° " & Err.Number & vbLf & Err.Description
Application.Calculation = xlCalculationAutomatic
MsgBox Format(Timer - t0, "0.0 \ sec."), vbInformation, "Temps éxécution macro"
'crédits code: Peter_SSs, ma pomme
End Sub
Sub CreationDonnees()
'macro pour générer des données de test
Application.ScreenUpdating = False
[C1] = 1: [A2:B2] = Array(100002, "DATA2")
[C1:N1].DataSeries Rowcol:=xlRows, Type:=xlLinear, Date:=xlDay, Step:=1, Trend:=False
[B2:B30000].DataSeries Rowcol:=xlColumns, Type:=xlAutoFill, Date:=xlDay, Trend:=False
[A2:A30000].DataSeries Rowcol:=xlColumns, Type:=xlLinear, Date:=xlDay, Step:=1, Trend:=False
[C2:N30000] = "=RANDBETWEEN(1,500)": [C2:N30000] = [C2:N30000].Value
End Sub

PS: la macro de transposition est issue de mes archives (glanée sur le web anglophone)

Sur mon PC de test, le MsgBox affiche entre 9 à 10 secondes
(version Excel pour le test: 2013)
NB: Dans la réalité, le nombre de ligne peut aller jusqu'à plus ou moins 60 000 lignes.

Merci à ceux qui prendront le temps de s’intéresser à la question 😉

 

[Staple1600]

Bonjour Efgé 😉

Bah alors pas envie de passer à la 1809 ? 😉

PS: Avec quel outil tu as eu ces infos détaillées sur ton PC?
(J'ai point ceci dans Informations Système de W10)

 

[Efgé]

Salut Staple 1600 😉
Fastoche;

Cordialement

 

[ThomasR]

Sinon pour les vieux il y a toujours : SiSoftware SANDRA

 

[mapomme]

Re,

PS1: Il y avait un petit souci pour la restitution (que j'ai corrigé ci-dessous)

Merci pour ce signalement de coquille (c'est le jour des œufs n'est ce pas ?); j'ai modifié le fichier posté.

 

[Staple1600]

Re, Bonjour ThomasR

J'ai du jadis utiliser la chose (sur 98SE 😉)

Sinon pour revenir à Excel: Dubitatif je suis, perplexe j'étais 😉
J'ai "corrigé" le code* de Laurent, fermer Excel et refait les tests
staple
Temps
0,6 sec. Données stocké Variable tableau b()
4,1 sec. Transfert dans feuilles excel
laurent950
0,7 sec. Données stocké Variable tableau b()
4,4 sec. Transfert dans feuilles excel
formule
3,4 sec. Transfert dans feuilles excel
par paquet
2 500 Nombre de lignes par paquet
4,3 sec. Transfert dans feuilles excel
Donc le Timer est-il si fiable que cela?

*: c'était pas grand chose 😉
ReDim b(1 To UBound(a, 1) * (UBound(a, 2) - 2), 1 To 4) 'ReDim b(LBound(a, 2) To (UBound(a, 1) * UBound(a, 2) - 2), 1 To 4)

 

[ThomasR]

Est-ce toujours les mêmes données ?

 

[chris]

Bonjour à tous

...
Est-ce qu'Excel préfère les processeurs Intel à ceux d'AMD?

Fut un temps où c'était clairement l'inverse car AMD avait un processeur arithmétique et pas Intel et j'avais de gros écarts entre mon PC en AMD et ceux de clients qui avaient pourtant un Proc INTEL plus rapide que moi

 

[laurent950]

J'ai "corrigé" le code* de Laurent, fermer Excel et refait les tests
staple
Temps
0,6 sec. Données stocké Variable tableau b()
4,1 sec. Transfert dans feuilles excel
laurent950
0,7 sec. Données stocké Variable tableau b()
4,4 sec. Transfert dans feuilles excel
formule
3,4 sec. Transfert dans feuilles excel
par paquet
2 500 Nombre de lignes par paquet

Bonjour staple1600, le Forum.
Tous d'abord je vous souhaite a toutes et a tous un très bon dimanche de Pâques.
Aussi j'aurais aimé connaître cette erreur liés à mon code que vous avez résolus et corrigé Staple1600.
J'aime apprendre et je serais vraiment content de voir cette correction au j'ai buté et impossible à résoudre par moi même.
Si vous pouvez me joindre le fichier Excel avec le code pour que je le décortique et que je comprennes pour avancé aussi dans mon apprentissage.
Je vous remercie Staple1600 pour votre aide et se poste qui m'a permis d'avancer car très riche de vôtre savoir ainsi que tous les participants à se poste.
Grand merci
Cdt
Laurent

 

[mapomme]

Re @laurent950 😉,

Staple1600 indique dans le message #65 l'erreur détectée et corrigée (voir l’astérisque en fin de message)

 

[Staple1600]

Re

Merci ma pomme d'avoir prété tes lunettes à Laurent950 😉
Alors j'ai pu accéder à un PC (qui n'est point le mien)
W10+Office 2019 PRO/ Intel I5-2320-CPU/3.Ghz/6Go
résultats du fichier v2 de ma pomme
staple
0,3 sec.
3,2 sec.
formule (ma pomme)
5,6 sec
Laurent950
0,2 sec
3,1 sec
paquets (ma pomme)
100 000 lignes
3,2 secondes

PS: Faudrait essayer avec un timer plus fiable, non?
Style celui-ci

 

[laurent950]

Merci Staple1600 et mapomme,
Je viens de relire le poste #65 (avec mes lunettes cette fois) 🙂 🙂 et compris super merci a vous deux.

Liens limites transpose :

VBA : Limite de la méthode Transpose

ou comment éviter l'erreur 1004 et/ou l'erreur 13 lorsque l'on Transpose des données... Que fait cette méthode? Extrait de l'aide en ligne msdn : source Renvoie une plage de cellules verticales sous forme de plage horizontale, ou...

www.commentcamarche.net

Liens : solution diverses variables tableaux
https://www.commentcamarche.net/faq/46680-vba-variables-tableaux-fonctions-utiles#transposition

Laurent