XL 2021 Vos avis SVP: Une fonction TRANSPOSE() à améliorer

[p'tit vieux]

Bonjour à tous,
Pour mes codes VBA, j'ai écris une première version de ma fonction Transpose() que je vais mettre à votre disposition dans sa version 1.0.0.
Toutefois, je voudrais votre avis sur l'intérêt de rajouter des options complémentaires à celle-ci.

Dans un premier temps j'ai écris une fonction Transpose() afin de lever la limite des 65536 lignes de la fonction "WorkSheetFunctions.Transpose" d'Excel.
Pour mes besoins j'ai ajouté les possibilités suivantes:
- Choisir la base départ du tableau (Base 0 ou 1)
- Pouvoir convertir un texte/valeur simple dans un tableau 2D T(1,1)

Rien d'exceptionel, mais pratique.

Donc je vous propose de me soumettre vos idées.

Par exemple, j'ai pensé à:
- Si on transmet T(0 to 0, 0 to N) ou T(1 to N, 1 to 1) Transpose() renvoie un tableau 1D T(0 to N) ou T(1 to N) suivant la base choisie
- Transposer ET ne renvoyer que les n premieres lignes
- Transposer ET ne renvoyer de la ligne n° X à n° Y

Voilà pour ces quelques idées.
A votre avis laquelle ou lesquelles de celles-ci ont-elles un intérêt.

Et vous, vos autres idées qui vous paraitraient utiles?
Ensuite suivant vos propositions ….
Je nommerai un 1er Ministre du développement
Mais non! Je publierai une nouvelle version (Pas sérieux ces P'tit Vieux)

Merci pour vos avis et suggestions.

ATTENTION!
Ici il n'est pas question de faire des recherches de texte/valeur ou autre truc du genre.

 

[patricktoulon]

punaise je lui prépare un truc à p'tit vieux

 

[Dudu2]

Bonjour,

Si tu veux un Transpose qui permet de changer la base du Lbound (0 ou 1 ou n'importe quelle valeur), alors voici une fonction (testée):

Option Explicit

Private Const BaseOutByDefault = xlNone

'------------------------------------------------------------------
'Transpose "naturel" qui évite la réduction du nombre de dimensions
'lors de l'utilisation de WorksheetFunction.Transpose().
'Cette fonction conserve les 2 dimensions dans tous les cas et
'lève la limite de 65535 items de WorksheetFunction.Transpose().
'
'- t        : Tableau à 1 ou 2 dimensions
'- BaseOut  : Optionnel - 0 ou 1 ou autre valeur selon le LBound souhaitée
'             xlNone pour utiliser le ou les LBound de t
'------------------------------------------------------------------
Function TransposeNaturel(t As Variant, _
                          Optional BaseOut As Integer = BaseOutByDefault) As Variant
                         
    Dim tt() As Variant
    Dim NbDimensions As Integer
    Dim LB1 As Long, UB1 As Long
    Dim LB2 As Long, UB2 As Long
    Dim it As Long, jt As Long
    Dim itt As Long, jtt As Long
 
    'Vérifie que t est un tableau
    If Not IsArray(t) Then
        MsgBox "Function TransposeNaturel: error ""t"" argument is not an array !"
        Exit Function
    End If

    '-----------------------------
    '0, 1 ou 2 dimensions pour t ?
    '-----------------------------
    On Error Resume Next
    it = UBound(t, 1)
    If Not Err.Number = 0 Then
        NbDimensions = 0
    Else
        it = UBound(t, 2)
        If Not Err.Number = 0 Then
            NbDimensions = 1
        Else
            NbDimensions = 2
        End If
    End If
    On Error GoTo 0

    '------------------------------
    't est un tableau à 1 dimension
    '------------------------------
    If NbDimensions = 1 Then
        If BaseOut = xlNone Then
            LB1 = LBound(t)
            UB1 = UBound(t)
        Else
            LB1 = BaseOut
            UB1 = LB1 + UBound(t) - LBound(t)
        End If
       
        ReDim tt(LB1 To UB1, LB1 To LB1)
     
        itt = LB1 - 1
        For it = LBound(t) To UBound(t)
            itt = itt + 1
            tt(itt, LB1) = t(it)
        Next it

    '-------------------------------
    't est un tableau à 2 dimensions
    '-------------------------------
    ElseIf NbDimensions = 2 Then
        If BaseOut = xlNone Then
            LB1 = LBound(t, 2)
            UB1 = UBound(t, 2)
            LB2 = LBound(t, 1)
            UB2 = UBound(t, 1)
        Else
            LB1 = BaseOut
            UB1 = LB1 + UBound(t, 2) - LBound(t, 2)
            LB2 = BaseOut
            UB2 = LB2 + UBound(t, 1) - LBound(t, 1)
        End If
       
        ReDim tt(LB1 To UB1, LB2 To UB2)
       
        jtt = LB2 - 1
        For it = LBound(t, 1) To UBound(t, 1)
            jtt = jtt + 1
            itt = LB1 - 1
            For jt = LBound(t, 2) To UBound(t, 2)
                itt = itt + 1
                tt(itt, jtt) = t(it, jt)
            Next jt
        Next it
    End If

    '------------
    'Return value
    '------------
    TransposeNaturel = tt
End Function

Que tu peux tester avec ce fichier qui permet toutes les combinaisons.

Edit: si tu veux un défaut de LBound particulier en sortie par défaut (ex = 1), adapte la constante Private Const BaseOutByDefault = xlNone.

 

[patricktoulon]

Bonjour
ca pourra servi a p'tit vieux ,moi je fait ça en 5/6 lignes et je préfère séparer la partie re base
car ça n'a rien a voir avec le transpose
je suis plutôt adepte du couteau suisse en terme de fonction
mais là sur ce sujet tout particulièrement je pense au contraire qu'il faut séparer en fonction
et chaque fonction peut avoir son utilité propre

 

[p'tit vieux]

Bonsoir Dudu2, Patrick
Je suis de retour et je viens de prendre connaissance de vos commentaires et propositions.
Merci pour votre temps et vos participations.

Merci Patrick pour tes conseils.
Un jour peut-être, je reviendrai discuter avec toi sur le sujet de savoir jusqu'où on devrait aller sur le niveau de granularité (factorisation) des applis … Sans tomber dans le code "Spaghetti", c'est à dire que l'on a des dizaines et des dizaines de "bouts" de codes partout. J'ai connu et c'est très pénible lorsque tu as à gérer le code d'environ une dizaine de développeurs. J'ai connu … il y a longtemps (Qui a dit très?). C'est là la difficulté.
Il y a d'autres trucs que je te livre que l'on retrouve quasiment partout.
- Le nommage des variables. Si elles pouvaient être … explicites.
- L'écriture du code au kilomètre. Style: If blabla then blabla Else blabla : Foo1 : Foo2 : Foo3
BOBO la tête! Imagine t'on des phrases d'un kilometre et pourquoi pas sans ponctuation pendant qu'on y est. OK à l'époque actuelle cela ne choquerait plus.
Pour moi le code c'est pareil. Le "Retour chariot" n'est pas là pour rien. Surtout quand on sait que certains développeurs font plus cela pour "faire style" y a pas beaucoup de ligne de code.
Ce ne sont que des exemples (vécus) de … vieux
Mais tout le monde le sait … Faites ce que je dis ne faites pas ce que je fais

Merci Dudu2 pour ton dernier code.
Ca marche très bien. Te concernant ça frôle le pléonasme, non? .
Il sert l'entrée, le plat et … pas le dessert.
Je chipote un peu mais ce n'est vraiment pas méchant. Il manque juste le cas ou l'on passe un simple texte/valeur.
Utile dans certains cas. Ne t'inquiète pas je le rajouterai.
Mais tu as raison si l'on veut rester dans le cadre de ce que fait le Transpose d'Excel.
En résumé ton code me convient nickel et fait même plus que ce qui m'est aujourd'hui utile. Bravo!
Je l'ai testé dans mes applis. Pour l'instant "no problemo".

Maintenant, question philosophique …
Pour moi, je considère qu'une simple chaine est un tableau 1D dont on n'écrirait pas la dimension T(1) (ou T(base)).
DONC la transposition d'un Texte est comme (Si en base 1):

"Foo" => T(1)="Foo"​

=> T(1,1) = "Foo" après transposition​

Un peu comme les fractions 4 = 4/1

Encore merci. à vous … toujours performants

 

[patricktoulon]

bonsoir @p'tit vieux
il ne s’agit pas de granularité mais de bon sens et d'intelligence
chaque fonction doit faire son job et non pas tout mettre en une dans un code incompréhensible
tu veux travailler des array et même en extrapolant du string
très bien
tu dois alors avoir des fonctions intermédiaires spécialisées
exemple
une pour convertir du 1D vers 2D
une autre pour convertir du string en array ou tableau
une autre pour transposer
une autre pour trier
une pour déterminer les dimensions
une pour convertir en base 1 ou 0
etc..
et certaines de ces fonctions peuvent être dépendantes des autres ou être utilisées tout court comme tel
rien a voir avec un code spaghetti

d'autant plus qu'il est plus facile de mettre à jour ou d'apporter des amélioration
plutôt qu'un code mono bloc comme tu a fait ou une chatte n'y retrouverait pas ses petits

 

[p'tit vieux]

bonsoir @p'tit vieux
il ne s’agit pas de granularité mais de bon sens et d'intelligence
chaque fonction doit faire son job et non pas tout mettre en une dans un code incompréhensible
tu veux travailler des array et même en extrapolant du string
très bien
tu dois alors avoir des fonctions intermédiaires spécialisées
exemple
une pour convertir du 1D vers 2D
une autre pour convertir du string en array ou tableau
une autre pour transposer
une autre pour trier
une pour déterminer les dimensions
une pour convertir en base 1 ou 0
etc..
et certaines de ces fonctions peuvent être dépendantes des autres ou être utilisées tout court comme tel
rien a voir avec un code spaghetti

d'autant plus qu'il est plus facile de mettre à jour ou d'apporter des amélioration
plutôt qu'un code mono bloc comme tu a fait ou une chatte n'y retrouverait pas ses petits

Tu n'as pas vraiment tord. Mais ou est la limite?
Et comme dis plus haut ce n'est pas parce que je ne sais pas mais je suis comme tout le monde (ou beaucoup) …
Faites ce que je dis ne faites pas ce que je fais
Donc je n'applique pas toujours ce que je sais ou dis … c'est cela les P'tit vieux. Parfois c'est un peu borné, Certains diront limite xxx.
Mais est ce vraiment une question d'âge? Je pose la question
Mais je l'avoue et le sais.

 

[patricktoulon]

j'ai longtemps coder comme tu le fait
sauf que des mois après quand il faut revenir dessus pour ajouter une option ou améliorer j'ai l'impression d’être un bambin à l’école devant un code comme un débutant qui ne sait pas ou taper

jusqu'au jour ou t a compris que rien ne reste figé et que tu devra CERTAINEMENT revenir dessus
et qu'il vaut mieux factoriser

 

[Dudu2]

Ca marche très bien. Te concernant ça frôle le pléonasme, non? .

Oui, j'atteins la perfection divine la plupart du temps ! En toute modestie bien sûr !

Je chipote un peu mais ce n'est vraiment pas méchant. Il manque juste le cas ou l'on passe un simple texte/valeur.

J'ai ajouté cette fonction puisque tu as l'air d'y tenir.
Je ne sais pas quel retour tu veux alors je l'ai mis dans une table à 2 dimensions unitaires.

 

[Dudu2]

Je suis également un chaud partisan de la modularité et de la spécificité des fonctions / méthodes pour à la fois limiter la complexité, la taille du code et faciliter la maintenance.

En l'occurrence, sur cette fonction, on ne fait pas grand chose de multiple.
- Changer les LBound et UBound ? C'est juste un problème de déclaration
- Traiter une valeur, une table à 1 dimension ou une table à 2 dimensions ? Des séquences courtes et similaires

Mais il y a un avantage majeur:
- On n'alloue et ne valorise le tableau final et ne parcourt le tableau source qu'une seule fois !
Si des fonctions dédiées étaient utilisées, il faudrait le faire autant de fois que de fonctions ce qui, pour de grosses tables, pourrait impacter les temps de traitement.

 

[TooFatBoy]

Oui, j'atteins la perfection divine la plupart du temps !

Chouette, un deuxième dieu du VBA sur ce forum...

 

[p'tit vieux]

Je suis également un chaud partisan de la modularité et de la spécificité des fonctions / méthodes pour à la fois limiter la complexité, la taille du code et faciliter la maintenance.

En l'occurrence, sur cette fonction, on ne fait pas grand chose de multiple.
- Changer les LBound et UBound ? C'est juste un problème de déclaration
- Traiter une valeur, une table à 1 dimension ou une table à 2 dimensions ? Des séquences courtes et similaires

Mais il y a un avantage majeur:
- On n'alloue et ne valorise le tableau final et ne parcourt le tableau source qu'une seule fois !
Si des fonctions dédiées étaient utilisées, il faudrait le faire autant de fois que de fonctions ce qui, pour de grosses tables, pourrait impacter les temps de traitement.

100% d'accord avec toi.
Surtout quand, éventuellement, tu peux avoir a traiter des tableaux de plusieurs millions de datas chacune de tailles inconnues.
Çà fait vite très mal.
Mais bon ce n'est pas tout les jours.
(D'ailleurs très rapide ta fonction.)
Bonne soirée O (Demi)Dieu du VBA

 

[patricktoulon]

re
j'ajouterais après avoir fait le test benchmarck (et oui je vais au fond des choses je suis pas un dieu moi )
que la transpose de substitution est bien plus longue en dessous 65635 lignes que la transpose vba
conclusion
en dessous ou égal à 65635 ça ça s'appelle réinventer la roue

 

[Dudu2]

J'aime bien vérifier les affirmations non basées sur du concret du genre:

la transpose de substitution est bien plus longue en dessous 65635 lignes que la transpose vba

Alors faisons un petit test...

 

[Dudu2]

D'une part, quelles que soient les valeurs de limites que je place dans les UBound, le VBA est, étonnamment je l'avoue, plus rapide.

en dessous ou égal à 65635 ça ça s'appelle réinventer la roue

D'autre part, non ce n'est pas réinventer la roue puisque les fonctionnalités sont différentes:
- Pas de réduction / ajout de dimension (qui perso me perturbe car il faut vérifier à chaque fois)
- Des ajustement de Lbound en sortie paramétrables
- Acceptation d'une simple valeur
- Pas d'acceptation d'un Range comme le fait Application.Transpose (encore que ce serait possible de le coder)

 

[p'tit vieux]

D'une part, quelles que soient les valeurs de limites que je place dans les UBound, le VBA est, étonnamment je l'avoue, plus rapide.


D'autre part, non ce n'est pas réinventer la roue puisque les fonctionnalités sont différentes:
- Pas de réduction / ajout de dimension (qui perso me perturbe car il faut vérifier à chaque fois)
- Des ajustement de Lbound en sortie paramétrables
- Acceptation d'une simple valeur
- Pas d'acceptation d'un Range comme le fait Application.Transpose (encore que ce serait possible de le coder)

J'ai fait un test sur un transpose 2D de 1 milion 47500 lignes sur 2 colonnes.et sans changement de base.
N'étant pas sur mon PC, donc de mémoire, j'ai un temps de moins de 2 secondes.
Plus qu'honorable à mon goût.
Je vous confirme cela demain et ferai un comparatif avec la fonction Excel.