référence à la cellule appelante dans une fonction ?

[sergio_bzh]

Bonjour,
le titre ne doit pas être forcément très clair !

Je n'ai pas trop de pb avec le VBA mais je bute de temps en temps sur des problèmes vraiment basiques.

J'écris une fonction MyFct() pour l'utiliser dans une cellule et dans cette fonction, je voudrais savoir comment avoir la référence de la cellule d'où est appelé la fonction (je ne sais pas si c'est très clair).
J'utilise ActiveCell mais ce n'est visiblement pas la bonne option.

Un peu de concret : dans un module j'écris

Public Function MyFct()
    MyFct = ActiveCell.Address
End Function

et dans la cellule A1 , je rentre :

=MyFct()

La cellule affiche bien "$A$1"

mais si je selectionne ensuite "A1:A3" et je fais "recopier vers le bas" , toutes les cellules affichent "$A$1" car c'est bien la cellule active.

Quand MyFct() est appelé en A2 je voudrais pouvoir récupérer dans ma function l'adresse "A2" tout simplement !

Ce doit être basique mais je n'ai pas trouvé !

merci d'avance
Sergio

 

[Papou-net]

Re : référence à la cellule appelante dans une fonction ?

Bonjour sergio,

Effectivement, la fonction étant rattachée à la cellule active, ça ne fonctionne pas comme tu le voudrais.
Une solution est de faire appel à l'événement SelectionChange de ta feuille, ex :

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
ActiveCell = MyFct()

End Sub

Mais il faut penser à préciser ta plage de cellule concernée, sinon tu afficheras l'adresse sur chaque cellule qui aura le focus.

PS : je ne vois pas bien l'utilité de ta démarche, mais je pense y avoir répondu.

Cordialement.

 

[sergio_bzh]

Re : référence à la cellule appelante dans une fonction ?

Merci papou pour ta réponse rapide.

Bien sûr que ce n'est pas ce que je cherche à faire mais j'ai juste isolé le problème. Je ne vais pas mettre les dizaines de lignes de code inutile en ligne.

Ceci dit, ce n'est pas tout à fait ce que je cherche. C'est bien l'adresse de la cellule appelante que je veux récupérer dans MyFct.

En fait avoir le comportement d'une fonction standard d'une fonction excel : copier / coller, recopier vers le bas, recalcul automatique, etc. Est-ce possible ?

Merci.

 

[Papou-net]

Re : référence à la cellule appelante dans une fonction ?

Je ne sais pas, j'avoue que pour l'instant je sèche.
Peut-être un "XLBarbatruc" pourra-t-il te venir en aide ?

Cordialement.

 

[MJ13]

Re : référence à la cellule appelante dans une fonction ?

Bonjour sergio_bzh,Papou-net

Sinon a tester: en B1 pour avoir l'adresse de A1:

=CELLULE("adresse";A1)

 

[Pierrot93]

Re : référence à la cellule appelante dans une fonction ?

Bonjour Sergio, Papou, Michel

peut être ainsi :

Public Function MyFct()
    MyFct = Application.Caller.Address
End Function

bonne fin d'après midi
@+

 

[sergio_bzh]

Re : référence à la cellule appelante dans une fonction ?

les 2 solutions fonctionnent 🙂

Soit un appel :

=MyFct(CELLULE("adresse";A1))

Mais je préfère celle-ci qui évite un argument:

Public Function MyFct()
    MyFct = Application.Caller.Address
End Function

Merci à tous !
Sergio