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]

Bonsoir @p'tit vieux
a ma connaissance nous sommes au moins deux à avoir fait ça
a savoir une fonction transpose pouvant recevoir n'importe quelle tableau 1D ou 2D base 0 ou base 1
si tu cherche bien tu trouvera avec les pseudos dudu2 et patricktoulon
pour ma part je l'ai même affublé du détecteur de dim

 

[p'tit vieux]

Bonjour Patriiick
La rade est toujours là. Bonne ?
J'ai regardé et n'ai pas trouvé sinon pourquoi refaire …
Conversion en Base 1 to 0 et inversement incluse?

Je vais à nouveau rechercher vos versions.
Idem pour moi avec le détecteur de DIm. Je l'ai fait aussi.
Je l'ai mis dans le module de classe pour faciliter l'usage.

Là, j'ai mis ma fonction dans "Utilitaire". Elle est en attente de validation depuis hier.

Sinon vu que je suis dedans aurais-tu et/ou as tu relevé des idées qui pourraient être intéressantes?

 

[p'tit vieux]

Bonsoir @p'tit vieux
a ma connaissance nous sommes au moins deux à avoir fait ça
a savoir une fonction transpose pouvant recevoir n'importe quelle tableau 1D ou 2D base 0 ou base 1
si tu cherche bien tu trouvera avec les pseudos dudu2 et patricktoulon
pour ma part je l'ai même affublé du détecteur de dim

J'ai trouvé la version de Dudu2 mais pas la tienne.
Celle de Dudu2 ne convertit pas la possibilité de convertir d'une base à l'autre ni de détection de Dim
.

 

[patricktoulon]

re
il faut chercher c'est dans une discussion pas dans les sources

 

[patricktoulon]

tiens j'ai retrouvé une des premières version
la fonction transpose les array ou tableau 2D sans distinction
tu verra le code est d'une simplicité infantile

Sub testarray()
    tbl = Array(1, 2, 3, 4, 5)
     texte = "Avant" & vbCrLf & "Dim 1 : " & UBound(tbl) & vbCrLf & "Dim 2 : 0" & vbCrLf
     
  tbl = TransposeX(tbl)
    MsgBox tbl(0, 1)
      MsgBox texte & "Apres" & vbCrLf & "ligne : " & UBound(tbl) & vbCrLf & "Dim 2 : " & UBound(tbl, 2)
End Sub

Sub test2D()
    Dim tbl(1 To 5, 1 To 10), tbl2
    texte = "Avant" & vbCrLf & "Dim 1 : " & UBound(tbl) & vbCrLf & "Dim 2 : " & UBound(tbl, 2) & vbCrLf
    tbl2 = TransposeX(tbl)
    MsgBox texte & "Apres" & vbCrLf & "Dim 1 : " & UBound(tbl2) & vbCrLf & "Dim 2 : " & UBound(tbl2, 2)
End Sub

Sub testrange1()
    Dim tbl, tbl2
    tbl = Range("A1:A20")
    texte = "Avant" & vbCrLf & "Dim 1 : " & UBound(tbl) & vbCrLf & "Dim 2 : " & UBound(tbl, 2) & vbCrLf
    tbl = TransposeX(tbl)
    MsgBox texte & "Apres" & vbCrLf & "Dim 1 : " & UBound(tbl) & vbCrLf & "Dim 2 : " & UBound(tbl, 2)
End Sub

Sub testrange2()
    Dim tbl, tbl2
    tbl = Range("A1:t1")
    texte = "Avant" & vbCrLf & "Dim 1 : " & UBound(tbl) & vbCrLf & "Dim 2 : " & UBound(tbl, 2) & vbCrLf
    tbl = TransposeX(tbl)
    MsgBox texte & "Apres" & vbCrLf & "Dim 1 : " & UBound(tbl) & vbCrLf & "Dim 2 : " & UBound(tbl, 2)
End Sub


Function TransposeX(t)
    'patricktoulon    V 1.2 -- 08/07/2021
    Dim y&, i&, c&, t2
    On Error Resume Next
    y = UBound(t, 2)
    On Error GoTo 0
    If y = 0 Then
        ReDim t2(LBound(t) To UBound(t), 1)
    Else
        ReDim t2(LBound(t, 2) To UBound(t, 2), LBound(t) To UBound(t))
    End If
    For i = LBound(t) To UBound(t)
        If y = 0 Then
            t2(i, 1) = t(i)
        Else
            For c = LBound(t, 2) To UBound(t, 2): t2(c, i) = t(i, c): Next
        End If
    Next
    TransposeX = t2
End Function

 

[patricktoulon]

Bonjour Patrick
OK
Oui pour le 1D vers le 2D. Mais là tu chipotes
Et puis ... C’est du bonus qui m’est souvent utile.
J’attends la sanction

Bon j'ai testé

positif:
visiblement le test transpose fonctionne

négatif:
j'ai pas de mot pour décrire ce que j'ai vu dans les codes
tout d'abords il est inintelligible punaise je vais mettre longtemps a le décortiquer
ensuite vu la masse de code je me demande par ou passe t il pour transformer des lignes en colonnes et vise et versa

pour le 1D to 2D
Oui pour le 1D vers le 2D. Mais là tu chipotes
Et puis ... C’est du bonus qui m’est souvent utile.

ben non justement je ne chipote pas puisque c'est ton sujet
imagine un moins averti qui se dirait chouette j'ai une fonction transpose a disposition
et qu'il travaille sur une plage de réception le pauvre il va tourner longtemps avant de comprendre que ta fonction ne transpose pas un array mais le transforme simplement en 2D

alors je ne sais pas trop pourquoi tout ça dans une classe (tu nous le diras)
mais pour ma part je pense que justement puisque nous somme sensé travailler hors limite excel
il faut économiser de l’énergie et là sans vouloir t'offenser c'est tout le contraire

je sais pas si tu a vu la petite fonction que je t'ai donné mais elle fait exactement ce que worksheetfunction.transpose fait et rien de plus sauf que l'on a pas de limite si ce n'est que la puissance de ton pc et je fait ça en moins de 15 lignes

je pense qu'il y a eu beaucoup de travail ça c'est sur
mais tu aurais du venir nous voir a fin que le moteur de base soit fait


2° la conversion base 0 ou 1
là encore il y aurait encore à dire
une fonction transpose transpose et c'est tout elle n'est pas à pour faire la vaisselle et la lessive
chacun son rôle
je serais toi je séparerais ces deux aspects de la chose
d'autant plus que diviser pour mieux régner est le maitre mot en vba

juste pour m'amuser car je suis un joueur
j'ai affublé ma petite fonction de la conversion base 0 to base 1 l'inverse est très rare(le besoin n'existe pas)
et là encore je fait avec 2 trois lignes de plus et ça fonctionne très bien
mais je ne la donnerais pas puisque c'est contraire a ce que je viens de dire un peu plus haut
mais le jeu fut intéressant

voilà ce n'est pas une sanction du tout( je ne sanctionne pas ce qui bossent)

juste un éclairage sur ce qui est raisonnable

et pour t'éclairer
le moteur de transposition (raisonnement )

1° tester avec gestion d'erreur du ubound(tableau,2)dans une variable long(un array déclenche l'erreur)

2° si variable= 0 variable =1(et pas 1 to 1 !!!!!!!!!!!!!)
car dimensionner avec un nombre tout court implique systématiquement un base 0 même si c'est 1

3°reconstruction du tableau

• si variable=0 redim tableau(lbound to ubound,1)(et pas 1 to 1 !!!!!!!!!!!!!)
• sinon redim avec le lbound et ubound dim 1 inversé avec la dim2
• boucle sur le ubound
• si variable=0 alors c'est nouveau tableau(index boucle,1)=le tableau original(index boucle)
• sinon
• boucle sur dim 2
• nouveau tableau(index boucle dim2,index boucle dim 1)=tableau(index boucle dim1,index boucle dim2)

et voila c'est terminé c'est pas la peine d'en faire plus
et ça prend moins de 15 lignes dans une petite fonction dans un module standard
libre à toi de faire une petite fonction à coté de conversion 2D pour un array 4/5 lignes
et voir même de t'en servir en pré traitement dans la fonction transpose pour l'array

voila j’espère ne pas être trop dur

 

[p'tit vieux]

Re,
Merci de t'attarder sur mon code.

…. alors je ne sais pas trop pourquoi tout ça dans une classe (tu nous le diras) ...

Simplement parce que j'ai mis tous mes "outils" dans une boite (à outils) UDF à part (xlam).
Cela me permet d'utiliser mes fonctions à l'envie sans avoir à faire des copier/coller dans les autres applications.
De plus lorsque je fais des corrections celles-ci sont appliquées dans toutes les applications utilisant la boite à outils. Plus pratique, logique non?
Bon mais ça c'est un détail.. C'est vrai que ce serait plus clair pour un lecteur Lambda de déplacer le code dans le module. Tu as raison, je vais faire çà dans la nouvelle version.

une fonction transpose transpose et c'est tout elle n'est pas à pour faire la vaisselle et la lessive
chacun son rôle

En résumé tu me déconseilles de décomposer en plusieurs "mini" fonctions.
A savoir:
1° Une fonction 'basique" qui fait Transpose()
2° Une fonction pour changer de base
etc.

Ok pourquoi pas. Ca se défend niveau facilité de maintenance et compréhension du code des fonctions.
Mais sur ce principe on peut vite arriver à du code "Spaghetti" avec des bouts de fonctions dans tout les sens.
Par contre, pour l'utilisateur (développeur) … Hum!
Suivant les besoins cela obligera le développeur à appeler moulte fonctions différentes pour avoir un résultat final voulu.
Alors je suis en parti d'accord avec toi. Le sujet (le souci) est de trouver l'équilibre entre la maintenance des fonctions et la facilité d'utilisation par les développeurs.

1D vers 2D
ben non justement je ne chipote pas ….

Oui dans un sens tu as raison (Mais dans un sens seulement)
Comment interprètes tu la demande d'une "transposition" d'un tableau 1D?

Pour moi soit tu ne fais rien soit tu convertis le 1D en un tableau 2D. Logique n'est ce pas.
"WorkSheetFunctions.Transpose" fait ceci; (Trans_Excel(1 to n,1)
Moi je fais 1, 1 to n. Bon si ca gène je peux le modifier pour être à l'identique d'Excel.

2éme point: La "transposition" d'une simple chaine texte (ou nombre)?

Excel ne fait rien. En me référant à la remarque de Dudu2, si tu demandes de transposer un texte, quelque soit la raison, c'est que tu veux un tableau en retour. Et je suis d'accord avec lui.

 

[job75]

Bonjour p'tit vieux, Patrick,

Je ne comprends pas, il s'agit bien de recréer une fonction Transpose ?

Alors utilisez :

Function TransposeX(x)
If Not IsArray(x) Then TransposeX = x: Exit Function
Dim a, Lb1, Ub1, Lb2, Ub2, b(), i, j
a = x
Lb1 = LBound(a): Ub1 = UBound(a)
Lb2 = LBound(a, 2): Ub2 = UBound(a, 2)
ReDim b(Lb2 To Ub2, Lb1 To Ub1)
For i = Lb2 To Ub2
    For j = Lb1 To Ub1
        b(i, j) = a(j, i)
Next j, i
TransposeX = b 'matrice
End Function

On entrera ensuite la fonction dans la plage adéquate en validant matriciellement par Ctrl+Maj+Entrée.

A+

 

[p'tit vieux]

Bonjour p'tit vieux, Patrick,

Je ne comprends pas, il s'agit bien de recréer une fonction Transpose ?

Alors utilisez :

Function TransposeX(x)
If Not IsArray(x) Then TransposeX = x: Exit Function
Dim a, Lb1, Ub1, Lb2, Ub2, b(), i, j
a = x
Lb1 = LBound(a): Ub1 = UBound(a)
Lb2 = LBound(a, 2): Ub2 = UBound(a, 2)
ReDim b(Lb2 To Ub2, Lb1 To Ub1)
For i = Lb2 To Ub2
    For j = Lb1 To Ub1
        b(i, j) = a(j, i)
Next j, i
TransposeX = b 'matrice
End Function

On entrera ensuite la fonction dans la plage adéquate en validant matriciellement par Ctrl+Maj+Entrée.

A+

En fait ce n’est une fonction pour l’utiliser dans une cellule.
C’est une fonction que l’on utilise dans son code VBA à la place de la fonction Excel WorkSheetFunction.Transpose qui est, enre autre, limitée à 655536 lignes

 

[patricktoulon]

En fait ce n’est une fonction pour l’utiliser dans une cellule.
C’est une fonction que l’on utilise dans son code VBA à la place de la fonction Excel WorkSheetFunction.Transpose qui est, enre autre, limitée à 655536 lignes

et ben c'est justement ce que te dit @job75 et moi même
tu est parti dans un truc complétement fantasmagorique
2boucle suffisent a transposer
j'en dit un peu plus dans cette video

 

[p'tit vieux]

Bon OK.
Passons sur la granularité du code. (C'est à dire le découpage de la fonction en plusieurs sous-fonctions)
Le code réalisant la transposition en elle même ce n'est que cela:

' ====================
'  Swap Dim 1 / Dim 2
' ====================
 ReDim Temp1(LBound(Tablo_arr, 2) To UBound(Tablo_arr, 2), LBound(Tablo_arr, 1) To UBound(Tablo_arr, 1))
  For I = LBound(Tablo_arr, 1) To UBound(Tablo_arr, 1)
    For J = LBound(Tablo_arr, 2) To UBound(Tablo_arr, 2)
      If IsObject(Tablo_arr(I, J)) Then
        Set Temp1(J, I) = Tablo_arr(I, J)
      Else
        If IsDate(Tablo_arr(I, J)) Then
          Temp1(J, I) = Tablo_arr(I, J)
        Else
          Temp1(J, I) = Tablo_arr(I, J)
        End If
      End If
    Next J
  Next I

Le code QDim = CountArrayDim(Tablo_arr) :
Appelle la fonction CountArrayDim() (se trouve dans le module de classe) sert à connaitre le nombre de dimensions du tableau

Je pense que le problème n°1 est que je me suis mal expliqué.
A quoi sert l'option Base1?
Il permet de demander soit:
1- la conversion le tableau d'une base 0 vers une base 1 ou l'inverse.
et/ou
2- Dans un tableau 2D ayant des bases différentes (comme dans ta video) la fonction permet d'uniformiser les 2 dimensions.
OK, OK! C'est rare …. pour Excel.

Tout le code entre QDim = …. et le code de Swap Dim1 Dim2 ne sert que pour traiter l'option Base1. A rien d'autre.
Est-ce un peu plus clair.

Mais c'est de ma faute … Code mal documenté … PAS BIEN!

 

[patricktoulon]

quand tu met option base 1 en haut de module c'est pour travailler en base 1 c'est tout même si ton array est en base 0

exemple

Option Base 1
Sub test()
    Dim tbl
    tbl = Array("toto", "titi", "riri", "fifi")
    MsgBox tbl(1)
    MsgBox LBound(tbl)
End Sub

les message te dirons que tu es en base 1 alors que c'est faux
dans ce genre d'intention j'entends par là que quand on veut faire des fonction de redimensionnement
variables tableau on évite de les utiliser sinon ça n'a pas de sens

pour te la faire courte c'est un truc de fainéant quand oveut pas se casser la tête avec des fonctions pour savoir si l'on doit partir de zero ou pas

dans cet exercice donc à proscrire bien évidemment

 

[job75]

Ah mais pardon j'ai oublié de traiter le cas où le tableau est de dimension 1 alors utilisez :

Function TransposeX(x)
If Not IsArray(x) Then TransposeX = x: Exit Function
Dim a, Lb1, Ub1, Lb2, Ub2, e As Boolean, b(), i, j
a = x
Lb1 = LBound(a): Ub1 = UBound(a)
On Error Resume Next 'car erreur si a est de dimension 1
Lb2 = LBound(a, 2): Ub2 = UBound(a, 2)
If Err Then
    ReDim b(Lb1 To Ub1, 0 To 0)
    For i = Lb1 To Ub1
        b(i, 0) = a(i)
    Next i
Else
    ReDim b(Lb2 To Ub2, Lb1 To Ub1)
    For i = Lb2 To Ub2
        For j = Lb1 To Ub1
            b(i, j) = a(j, i)
    Next j, i
End If
TransposeX = b 'matrice
End Function

 

[patricktoulon]

@job75
c'est ce que je propose sans variabiliser