Discussion :

[OtObOx]

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

[Jean-Philippe André]

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

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

[OtObOx]

Merci pour ta rapide réponse !

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

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

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

[Jean-Philippe André]

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

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

[OtObOx]

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) :


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

[Jean-Philippe André]

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

[OtObOx]

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