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.

 

[jurassic pork]

Sinon tu comptes les manip faites entre-temps.

Hello Dudu2,
pas avec le pause qui gèle le benchmark et le continue qui reprend le benchmark

 

[patricktoulon]

test avec intermediaire

ordre 2

alors certes les résultats sont un peu plus justes mais reste l'impact du précédent sur le suivant
c'est simple 401 pour ma version 1 et 457 pour ma version 2
et dans l'ordre inverse
c'est 398 pour ma version 2 et 451 pour ma version 1

certes avec l'effet de bord du timer il y a un impact mais pas autant
c'est donc bien une erreur dans le benchmark
d'ailleurs pour avoir fait une petite fonction de conversion s/ms/ns/µs pour BenchmarkLIDL
je ne suis pas sur que les échelles de calcul soient bonnes

dommage il est bien foutu ce truc

 

[patricktoulon]

au final le start règle le problème du premier a l'affichage a retrouve un petit count des tics mais dans le calcul des suivants il y est toujours

 

[patricktoulon]

avec mon benchmark de chez LIDL
QUI LUI REDEMARRE A ZERO LE GETTICOUNT FREQ A CHAQUE TEST

 

[Dudu2]

pas avec le pause qui gèle le benchmark et le continue qui reprend le benchmark

Ah oui, ok ! Bonne idée !

 

[Dudu2]

Ce truc de Benchmark, ben je vais simplement l'abandonner au profit du Timer, certes lui aussi peu précis mais simple et malgré tout représentatif.

En fait c'est pas judicieux ce que je dis là.
Y a des raisons et il faut les trouver.

Alors les raisons, elle sont assez communes et s'appellent un GROS BUG d'indice dans le code !

Il n'y a très peu de différence qu'on appelle par Application.Run ou Call Direct.
Il n'y a aucune pénalisation du 1er de cordée.
Le Application.Transpose() est effectivement moins performant
La technique de @jurassic pork est utilisée pour limiter les Names du rapport.

Edit: Désolé pour avoir enduit d'erreur ceux qui se sont intéressé au fichier précédent.

 

[patricktoulon]

re

Alors les raisons, elle sont assez communes et s'appellent un GROS BUG d'indice dans le code !

c'est bien ce que j'ai dit plus haut
j'aurais besoins de votre aide tiens pouvez vous me dire si cette conversion est bonne

Function ConvertTime(tim As Double) As String
    ' Convertit automatiquement le temps en la meilleure unité
    Dim converted As Double
    Dim unitStr As String
    Debug.Print CStr(tim) & "---" & CStr(tim * 1000000)
    Select Case tim
        Case Is >= 1
            converted = Round(tim, 3)
            unitStr = " s"
        Case 0.001 To 0.999999
            converted = Round(tim * 1000, 1)
            unitStr = " ms"
        Case 0.001 To 0.000999
            converted = Round(tim * 1000000, 1)
            unitStr = " µs"
        Case Else
            converted = Round(tim * 1000000000, 1)
            unitStr = " ns"
    End Select

    ConvertTime = converted & unitStr
End Function

j'ai un doute avec les case 2 et 3

 

[patricktoulon]

je pense que là je suis bon

Function ConvertTime(tim As Double) As String
    ' Convertit automatiquement le temps en la meilleure unité
    Dim converted As Double
    Dim unitStr As String
    Debug.Print CStr(tim) & "---" & CStr(tim * 1000000)
    Select Case tim
        Case Is >= 1
            converted = Round(tim, 3)
            unitStr = " s"
        Case 0.001 To 0.999999
            converted = Round(tim * 1000, 1)
            unitStr = " ms"
        Case 0.0009 To 0.000999
            converted = Round(tim * 1000000, 1)
            unitStr = " µs"
        Case Else
            converted = Round(tim * 1000000000, 1)
            unitStr = " ns"
    End Select

    ConvertTime = converted & unitStr
End Function

 

[jurassic pork]

Sinon Hors sujet si vous voulez faire joujou pour faire des comparaisons de performance de formules vous pouvez allez voir ici (je pense qu'il n'y a que le xlsm qui pourra vous intéresser car le reste c'est pour tester des fonctions personnalisées dans des Addin d'autres langages (python, c++, c#) mais aussi en VBA). L'animation suivante présente le test d'une somme de 1 millions de cellules contenant des nombres avec une formule native une UDF en VBA , une UDF en python (avec addin xlwings) et une autre en C# avec addin Excel-DNA. Les tests sont réalisés plusieurs fois pour avoir une moyenne de temps d'exécution (en millisecondes) :

Toutes les formules donnent le même résultat et c'est de loin la formule native (SUM) qui est la plus rapide.
Vous pouvez peut-être amélioré la fonction personnalisée qui fait la somme en VBA :

Public Function SumVba(rng As Range) As Double
    Dim i As Long, j As Long, sum As Double
    Dim arr() As Variant  
    arr = rng.Value
    sum = 0
    For i = 1 To UBound(arr, 1)
        For j = 1 To UBound(arr, 2)
            sum = sum + arr(i, j)
        Next j
    Next i
    SumVba = sum
End Function

 

[patricktoulon]

RE
@jurassic pork
et moi j'ai decouvert un truc inquiétant
en finalisant mon module Benchmark_de_chez_TEMU
je me suis appercu d'un truc
pour info chaque test se fait entre une remise à zero a chaque fois
il n'y a donc pas d'interaction entre un test et un autre
et bien comme le timer il y a un effet de bord sauf que la c'est de l'ordre de 70 à 250%

voila ce que donne mon benchmark de chez temu

je joins le fichier avec le benchmark de chez TEMU
Observez les 3 derniers tests qui sont des boucles 1 à 10000 et 1 à 100 et 1 à 10

et Maintenant observez ces mêmes test mais dans un ordre différent

 

[Dudu2]

@jurassic pork ,

Intéressant.
J'ai extrait la toute petite fonction MicroTimer() et elle donne les mêmes résultats que le gros bazar de la Classe cBenchmark qui, à son crédit, donne plus d'infos.
On doit pouvoir dorénavant alléger les comparatifs si on ne veut que le temps d'exécution, voire aussi le nombre de tics extractible de MicroTimer().

 

[patricktoulon]

@jurassic pork
assez chaotique l'effet de bord

 

[patricktoulon]

re
@Dudu2 a tu essayé le post 160?

 

[patricktoulon]

re
test du post 161 microtimer
là on constate l'effet de bord
maintenant c'est mapomme le plus rapide ce qu'un simple timer contredit bien entendu

 

[Dudu2]

J'ai modifié la fonction MicroTimer() pour retourner le nombre de tics.

Problème:
Les API 32 bits et 64 bits, les variables étaient déclarées en Currency.
J'ai laissé Currency pour le 32 bits et utilisé un LongLong pour le 64 bits.

Pour ne pas avoir à gérer cette différence dans les fonctions utilisateur, le nombre de tics est retourné en Double.
Ce dont je ne suis pas sûr (car 32 bits je n'ai pas), c'est la conversion d'un Currency en Double.
J'ai multiplié par 10.000 car un test en 64 bits nécessite de le faire. Est-ce pareil en 32 bits ?

Voilà la fonction:

Option Explicit

#If VBA7 Then
    Private Declare PtrSafe Function getFrequency Lib "kernel32" Alias "QueryPerformanceFrequency" (Frequency As LongLong) As Long
    Private Declare PtrSafe Function getTickCount Lib "kernel32" Alias "QueryPerformanceCounter" (TickCount As LongLong) As Long
#Else
    Private Declare Function getFrequency Lib "kernel32" Alias "QueryPerformanceFrequency" (Frequency As Currency) As Long
    Private Declare Function getTickCount Lib "kernel32" Alias "QueryPerformanceCounter" (TickCount As Currency) As Long
#End If

Function MicroTimer(Optional ByRef TickCount As Variant) As Double
    ' Returns seconds.
    Dim Ticks1 As Variant
    Static Frequency As Variant

    ' Get frequency.
    If Frequency = 0 Then getFrequency Frequency

    ' Get ticks.
    getTickCount Ticks1
    If Not IsMissing(TickCount) Then
        '32 bits Currency
        If VarType(Ticks1) = vbCurrency Then
            TickCount = CDbl(Ticks1 * 10000)
        '64 bits LongLong
        Else
            TickCount = CDbl(Ticks1)
        End If
    End If
   
    ' Seconds
    If Not Frequency = 0 Then MicroTimer = Ticks1 / Frequency
End Function