[O365] Passer du VBA vers les nouvelles fonctions dynamiques

Bonjour à tous,

Après pas mal de temps, j'ai finalement réussi à écrire un petit guide d'introduction aux nouvelles fonctions dynamiques dans Excel. Comme indiqué dans le titre, ce guide est destiné aux utilisateurs du VBA. J'ai essayé de faire une passerelle entre les concepts de programmation VBA et le fonctionnement de ces nouvelles formules, qui en réalité sont vraiment très proches.

En plus du guide (PDF) vous trouverez 4 fichiers joints :

• Le principal "introduction-fonctions-dynamiques.xlsx" contient des exemples simples pour les fonctions présentées dans le guide
• 3 autres fichiers d'exemples "complexes" dans lesquels j'ai repris quelques études de cas intéressantes du forum, pour lesquelles j'ai essayé d'expliciter le processus de résolution via formules. Vous pourrez observer que la partie cruciale, le raisonnement, est identique qu'on soit en VBA ou en formules.

Je suis bien entendu dans l'attente de vos retours, corrections et propositions. A vrai dire je n'ai pas pris le temps d'écrire les macros équivalentes aux exemples donnés. Si c'est un point que vous trouvez important, n'hésitez pas à me le dire et je les écrirai.

Pour finir, je vous conseille de lire le PDF en parallèle du fichier Excel principal pour tester et comprendre les formules.

Je tiens à remercier @JFL qui m'a bien aidé pour les traductions de fonctions anglais/français et @LooReeD pour avoir accepté mon spam en MP, même si au final je pense que le fichier PDF est plus simple à utiliser.

Salut saboh12617

Joli boulot

En revanche, outre le titre un peu pompeux et trompeur à mes yeux
les fonctions dynamiques ne pourront jamais remplacer VBA, je ne vois pas comment !

Salut @JExcel2fr,

Eh… il faut bien attirer les lecteurs

Non plus sérieusement, à part l'aspect mise en forme et tout le côté "interactions externes" (OLE/UserForms/Shell) qu'offre le VBA, si on parle juste de traitement des données il y a peu d'exemples pour lesquels le VBA "marche" là où une fonction ne marche pas.

Mais au-delà de ça, je pense que tu as mal interprété le titre : ce post a juste pour objectif d'accompagner ceux qui connaissent le VBA mais pataugent un peu avec ces fonctions et voudraient les utiliser davantage. Il n'y a pas lieu de parler de remplacement ou de ce qui est mieux/moins bien, chaque outil a ses avantages et ses limites.

Bonjour,

Merci d'avoir pris le temps de créer et partager ça

De mon côté, j'apprécie beaucoup ces fonctions MAIS tant qu'Excel ne gèrera pas les tableaux imbriqués, ça restera malheureusement insuffisant pour travailler d'une manière satisfaisante avec ces fonctions (à mon sens).

Par exemple une simple formule comme celle-ci (avec des données séparées par des virgules en A1) ne fonctionne pas avec Excel (alors que ça fonctionne très bien avec Google Sheets qui lui gère les tableaux imbriqués) :

=MAP(A1:A5;LAMBDA(i;FRACTIONNER.TEXTE(i;",")))

Si Excel gérait les tableaux imbriqués on pourrait alors aller beaucoup plus loin et créer des formules bien plus intéressantes comme celle-ci, et alors là effectivement ça pourrait remplacer le VBA dans certains cas :

=MAP(SORT(TOCOL(SPLIT(REDUCE(0;TOCOL(BYROW(FILTER(A3:F7;B3:B7<>"");LAMBDA(lig;LET(asso;INDEX(lig;1;1);adh;FILTER(lig;lig<>"";lig<>asso);MAP(adh;LAMBDA(ad;asso&"|"&ad))))));LAMBDA(tab;val;SI(val="";tab;SI(tab<>0;tab&"¦"&val;val))));"¦")));LAMBDA(lig;SPLIT(lig;"|")))

Bonjour Sébastien, merci pour ton retour !

Oui effectivement les calculs en dimension > 2 sont vraiment ce que je trouve le plus limitant moi aussi. Personnellement, sur GoogleSheets j'ai un peu de mal à comprendre comment fonctionne la diffusion car parfois j'ai besoin d'utiliser ARRAYFORMULA, parfois non… Je trouve ça un moins constant que sur Excel.

Juste pour info, pour les plus "fanatiques" d'entre nous, j'ai vu qu'il existait une méthode de contournement, les "THUNK"via LAMBDA pour stocker en mémoire des arrays dans des arrays. Je mets le fil ici pour les curieux What is a thunk in an Excel lambda function? - FLEX YOUR DATA pour ma part ça dépasse mes capacités de compréhension, mais je salue l'effort.

Après comme je disais, chaque outil a ses avantages/limites, l'idée du post c'est de reprendre le raisonnement utilisé en VBA pour l'appliquer à l'écriture d'une formule, pas forcément remplacer VBA.

Bonne journée à tous

Bonsoir,

bravo pour l'effort de synthèse et d'explication ! Ca a le mérite d'exister ! Certains disent comment faire d'autres le font !

Pour ma part avec mon "2021" beaucoup de nouvelles fonctions me sont inaccessibles...
Une remarque sur les liens du document : le Nota indiquant que la fonction n'est utilisable que sur Excel365 est trop bas, je l'aurais presque mis juste sous le titre / nom de la fonction.

Après, je parle pour moi, je suis resté bloqué en 2014 au niveau des fonctions des feuilles, et encore j'en apprends encore, même en VBA d'ailleurs !
Et comme le disait ThauThème dans sa signature : "VBA m'éclate, les formules m'ennuient ! Je n'y peux rien c'est comme ça..." je dirais que je ne suis pas loin de pensait la même chose !

Après je me doute qu'avec les avancées technologiques VBA va perdre de plus en plus de terrain, ne serait-ce qu'avec Power Querry, natif depuis 2016 et en AddIn depuis 2010 (), on le voit sur le forum Excel - VBA, la part de PQ est de plus en plus grande ainsi que les nouvelles formules à base de nouvelles fonctions. De là à dire qu'il va disparaitre... Vu l'utilisation actuel d'Excel au vu des demandes, je ne sais pas si cela va être la politique de MS.

Déjà que l'évolution d'Excel a été plus ou moins orienté par les utilisateurs : d'un tableur utilisé comme base de données, il s'est enrichit au fil des années d'options (terme général) de gestions de données afin d'être plus efficace dans ce domaine. Les petits boutons permettant de "jouer" avec avec Excel à base de VBA, les USF sont également appréciés pour la création d'un interface "machine/base de données/utilisateur". Alors... VBA, je pense, devrait exister encore quelque années, non ?

Bravo encore pour ce travail, merci pour la référence à mon Pseudo, bien que je n'ai rien fait pour cela...

@ bientôt

LouReeD

Salut LooReeD, merci pour ton retour.

Pour info si tu veux voir/étudier les formules 365, si tu as un OneDrive il suffit d'y uploader le fichier et de l'ouvrir sur le Web. Je fais souvent ça quand j'ai pas 365 de dispo.

Je n'ai pas ajouté les numéros de version car c'est vraiment galère de trouver avec certitude quelle version a ou n'a pas telle ou telle fonction. J'ai trouvé que le site ExcelJet était assez correct sur ce point, mais la documentation officielle de MS est complètement à la ramasse. La moitié ne sont pas encore traduites c'est assez honteux… Donc dans le doute => j'ai tout mis en 365.

Et oui encore une fois sur le débat formules/PQ/VBA, chaque outil a ses avantages. L'idée là c'était surtout de montrer que la plupart des concepts de base de programmation (boucles/gestion de tableaux/dictionnaires/récursivité...) se retranscrivent directement via des formules. L'objectif étant de donner quelques lignes directrices pour se lancer dans cette "gymnastique".

Bonjour à tous,

Après je me doute qu'avec les avancées technologiques VBA va perdre de plus en plus de terrain

A mon sens le gros problème est que Microsoft a complètement abandonné le VBA depuis bien longtemps ... Alors certes ils le maintiennent et l'adaptent pour le rendre compatible avec les nouvelles fonctionnalités d'Excel, mais le langage VBA en lui-même a 20 ans de retard par rapport aux langages existants actuellement et les options/limites dans l'éditeur n'ont pas évolué non plus.

Ce n'est pas par hasard si je n'ai plus créé de gros projet VBA depuis plus de 4 ans (après avoir atteint en plus les limites de l'éditeur VBA avec un très gros projet) ... Ce n'est pas par hasard non plus si j'ai fini par m'intéresser aux macros de Google Sheets qui utilisent le JavaScript (renommé Apps Script) au lieu du VBA, qui est un langage moderne et efficace.

Pour vous donner un exemple concret, voici comment trier simplement un tableau en JavaScript/Apps Script :

const tabNonTrie = [1, 10, 2, 20, 0];
const tabTrie = tabNonTrie.sort((a, b) => b - a);
console.log(tabTrie);

Que j'aurais aussi pu écrire sur une ligne :

console.log([1, 10, 2, 20, 0].sort((a, b) => b - a));

Maintenant pour faire le même travail en VBA il faut quelque chose comme ça :

Dim tabNonTrie As Variant
Dim i As Long, j As Long
Dim temp As Variant

tabNonTrie = Array(1, 10, 2, 20, 0)

For i = LBound(tabNonTrie) To UBound(tabNonTrie) - 1
    For j = i + 1 To UBound(tabNonTrie)
        If tabNonTrie(i) < tabNonTrie(j) Then
            temp = tabNonTrie(i)
            tabNonTrie(i) = tabNonTrie(j)
            tabNonTrie(j) = temp
        End If
    Next j
Next i

For i = LBound(tabNonTrie) To UBound(tabNonTrie)
    Debug.Print tabNonTrie(i)
Next i

Alors forcément quand on sait à quel point il est simple de trier un tableau (et bien d'autres opérations courantes) avec un langage moderne, devoir passer par la complexité inutile du VBA parce que Microsoft a abandonné le VBA depuis bien longtemps, c'est assez agaçant.

C'est aussi pour ça que j'ai tendance à encourager ceux qui aiment le VBA (LouReeD ?) à tester Apps Script, car une fois que vous avez des bases en Apps Script vous allez pouvoir écrire des codes bien plus courts et efficaces

Accessoirement c'est aussi grâce au JavaScript que vous avez depuis un moment les notifications automatiques sur le forum sans recharger la page

L'idée là c'était surtout de montrer que la plupart des concepts de base de programmation (boucles/gestion de tableaux/dictionnaires/récursivité...) se retranscrivent directement via des formules. L'objectif étant de donner quelques lignes directrices pour se lancer dans cette "gymnastique".

C'est une bonne idée et à titre informatif c'est encore d'autant plus vrai pour Google Sheets, car il y a par exemple la fonction MAP et il existe la méthode map, il y a la fonction REDUCE et il existe la méthode reduce, etc. J'avais d'ailleurs créé une page pour faire le lien entre les formules de calcul et Apps Script.

Bonne journée

Bonjour,

Javascript j'en ai fait aux alentours des années 2000, avec un mélange de php. À l'époque j'avais même un code intégré au mail d'outlook, mais je crois bien que ce n'est plus possible où je perds la tête et je ne sais plus comment on l'intègre. En effet, mail format HTML on pouvait y adjointe des code javascript.

Mais c'est comme beaucoup de chose avec moi, je n'arrive pas à évoluer... Le Python par exemple...

Bref, je reste actuellement sur du VBA en sortant du tableur classique...

@ bientôt

LouReeD

Bonjour Saboh12617

Merci pour ton travail.