Fonction personalisée en vba : cellule courante

Bonjour,

il est possible de créer des fonction personalisée en vba directement utilisable dans le classeur, par exemple :
=MAFONCTIONPERSO(A1)

Dans cette fonction, j'aimerai pouvoir déterminer l'adresse de la cellule où elle est demandée.
Quand je recherche sur google, je rentre des mauvais mots clés car à chaque fois, ça me ramène sur cellule courante, mais pas la cellule où est écrite la fonction.

Pour l'instant, j'ai réussi à contourner le problème en donnant en paramètre l'adresse de la cellule où est mis la fonction, mais outre que ce n'est pas facile à utiliser dans le classeur, je me demandais s'il n'y avait pas quelque chose de plus élégant à faire.

L'idée finale est de créer une somme automatique qui ferait l'addition de toutes les cellules situées en dessous de la fonction jusqu'à la première ligne vide rencontrée.
Puis de refaire une autre fonction sensiblement identique, mais en faisant la somme de produits : cellule à gauche x cellule courante (un =SOMMEPROD(xx;yy) qui additionnerait jusqu'à la prochaine ligne vide).

Merci,

JM

salut,

le problème de passer en paramètre la cellule en cours réside principalement dans le fait de faire une boucle sans fin ;)

D'autre part, si tu modifies les valeurs d'une cellule dans ton code, alors que la fonction n'a toujours pas retourné de valeur, la fonction va stopper son code en plein milieu :calim2:

reste qu'on ignore ce que ta fonction perso fait, et cette information pourrait potentiellement nous être utile :)

Merci pour ta rapide réponse !

En fait, je cherche à calculer des barycentres, voilà une fonction qui fonctionne :)

Code:

---

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32

Function BARYCENTRE(Cel As Range, Prod As Boolean) As Double Application.Volatile Application.ScreenUpdating = False   Dim Lig As Integer Dim Col As Integer Dim i As Integer Dim Total As Double   'Initialisation des données Lig = Cel.Row Col = Cel.Column Total = 0 i = Lig + 1 'On démarre à la ligne en dessous   'Boucle par ligne jusqu'à une cellule vide Do Until Cells(i, Col) = "" If Prod = False Then Total = Total + Cells(i, Col) Else Total = Total + Cells(i, Col) * Cells(i, Col - 1) End If i = i + 1 Loop   If Prod = False Then BARYCENTRE = Total Else BARYCENTRE = Total / Cells(Lig, Col - 1) End If Application.ScreenUpdating = True End Function

---

Actuellement la fonction BARYCENTRE demande 2 paramètres :
"Cel" qui est la cellule où est inscrite la fonction
"Prod" qui indique si c'est une somme ou une somme produit à faire.

Pour calculer une somme on entre dans n'importe quelle cellule, par exemple ici la cellule A1
=BARYCENTRE(A1;0)
et ça fait la somme de tous les chiffres situés sous la cellule A1
Puis ans la cellule B1 on entre
=BARYCENTRE(B1;1)
et ça fait l'addition de la multiplication de chaque cellules situées sous la ligne 1 (colonne A x colonne B) avant de diviser le total par le résultat trouvé en A1

Je voudrais que lorqu'on rentre la fonction dans Excel on indique seulement le paramètre Prod (indiquant si c'est une somme ou un produit à calculer) et que la fonction détermine seule où renvoyer le résultat.

JM

Ok,

c'est pas idéal cette façon de coder je pense.

Au lieu de pointer une cellule, tu pointes la plage de cellules qui t'interesse, c'est comme ca qu'est sensé fonctionner une fonction Excel :D

Dans le code tu fera un for Each du range passé en paramètre :ccool:

Oui, mais dans ce cas là, ça revient à utiliser les fonctions =SOMME() et =SOMMEPROD()/Cellule somme.

Et ça, je ne veux pas le faire.

L'exemple que je donne est un exemple simplifié du tableau que je veux obtenir. Mon tableau final contient une série de sommes et barycentres structurés d'une façon hiérarchique : il y a des titres, chapitres, paragraphe et des listes simples (donc sous-somme et barycentres pour chacun de ces élements).

Voilà un schéma du tableau idéal (sans la colonne qui calcule les barycentres) :
http://nsa22.casimages.com/img/2012/...4343561410.png

On voit que dans cet exemple, si on doit faire la somme des plages puis ensuite des sommes de cellules individuelles, sachant qu'on peut venir insérer des chapitres, paragraphes et sous-sommes à volonté, ça complique singulièrement le tableur (s'il n'y avait que des sommes ça pourrait encore aller, mais il y a les barycentres à calculer en plus).

Donc mon idée est de faire une "formule magique" qui dès qu'on l'insère dans une cellule, elle reconnait quelle hiérarchie doit être calculée :

Calcul des Titres :
Formule insérée en E2 :
Cellule A2 renseignée : c'est donc un calcul sur les Titres à faire donc faire la somme de tous les chapitres rencontrés jusqu'à ce que la cellule de la colonne A soit non vide (= changement de Titre).

Calcul des chapitres :
Formule insérée en E3 :
Cellule B3 renseignée : c'est donc un calcul sir les chapitres à faire donc faire la somme de tous les paragraphes rencontrés jusqu'à ce que la cellule de la conne B soit non vide (changement de chapitre)

Calcul des paragraphe :
Idem que pour les titres et chapitres, sauf que la somme est faite sur chaque articles rencontrés jusqu'à ce que la cellule de la colonne C est non vide (= changement de paragraphe).

et enfin, calcul des sous-sommes :
Même logique la formule est insérée en E5, il n'y a rien dans les colonnes A, B et C, donc on fait la somme de tous les articles jusqu'à ce qu'une ligne vide soit rencontrée.


Avec cette "formule magique", ça limite les risques de se tromper en fabriquant le tableur, d'autant plus qu'on peut aussi imaginer la mise en page automatique, en fonction de ce qu'il va calculer (notamment le groupage/dégroupage des parties hiérarchisées).



J'espère avoir été clair, c'est pas facile à expliquer... Il y a peut être un moyen plus simple de faire ça, si oui, merci de me mettre sur la piste.
Parce que pour l'instant, ce que j'ai fait ça fonctionne, mais ça ralenti pas mal l'ordinateur et si un autre fichier Excel est ouvert, la fonction personnalisée se calcule également dès qu'une cellule de l'autre classeur est changée... donc l'autre classeur est lui aussi ralenti.

JM

euh, mais ca ressemble vraiment énormément à un tableau croisé dynamique ton histoire :calim2:

Je ne suis pas très calé en TDC, mais oui, peut être qu'on peut le faire comme ça. Cependant, je crains que les limites soient :
- pour chaque ligne, il faudra renseigner un identifiant pour savoir dans quelle case du TDC le rentrer.
- les formules, tout le monde sait les utiliser, un TCD, c'est déjà plus rare. Or ce tableau devra pouvoir être modifié facilement par n'importe qui.
- Si des "accidents" de logique doivent être insérés dans le tableau, le TCD pourrait ne plus fonctionner.

Mais je rate peut être quelque chose... Peux-tu en dire plus ?

Merci,

JM