Localiser la cellule où une fonction personnelle est encodée

Bonjour,

Je dois écrire une fonction dont le contenu dépend de sa position. Je vous donne un exemple simple (mais évidemment que la fonction que je dois écrire est plus compliquée).

Si dans une cellule, j'encode "=mafonction()", je voudrais que la valeur retournée soit égale au contenu de la cellule qui se trouve juste au dessus +1.

function mafonction()

mafonction=Worksheets("Feuil1").Cells(mapositionx-1,mapositiony-1).Value+1

end function

Mon problème : comment trouver mapositionx et mapositiony ?

Pour l'instant, j'ai résolu ce problème en ajoutant un paramètre dans ma fonction : ce paramètre pointe sur la cellule elle-même mais ce n'est pas très joli :-(

Si je reprends mon exemple précédent : si je mets par exemple ma fonction dans la cellule G7; j'encode "=mafonction(G7)" et ma fonction s'écrit comme suit :

Function mafonction(pos As Range) As Variant

mafonction = Worksheets("Feuil1").Cells(pos.Row - 1, pos.Column).Value + 1

End Function

Cela fonctionne mais je voudrais éviter d'avoir à définir ce paramètre dans l'appel de la fonction. Est-ce possible ?

Merci d'avance pour votre aide

Bonjour,

c'est faisable, mais pourquoi tu ne veux pas lui passer la cellule du dessus en paramètre tout simplement ?
En relatif tu peux tirer la formule et la référence s'adaptera.
C'est plus orthodoxe... Toute fonction procède ainsi.
Ca ne te ferait gagner qu'un clic puisque les suivantes seront souvent des copies

eric

PS : le fichier

Bonsoir FVancop, Eriiic,

Il est possible de passer par ActiveCell pour éviter le positionnement. Selon ci-dessous.

Function LigM(Optional X As Long)
LigM = ActiveCell.Offset(-1) + WorksheetFunction.Max(X, 1)
End Function

Soit rien n'est inscris entre les parenthèses de la fonction. Et alors la valeur en résultant sera égale à la valeur de la ligne précédente + 1.

Soit on place entre les parenthèses toute valeur numérique supérieure à 1. Et cette valeur s'ajoutera à la valeur de la ligne précédente.

Bonjour à tous.

mais ce n'est pas forcément la cellule active qui a la fonction et qui est concernée par une ré-évaluation X Cellus non ?
D'autant plus qu'il y a un recalcul complet à l'ouverture

Ca me fait penser qu'il vaut mieux laisser la cellule en paramètre de la fonction pour qu'elle soit ré-évaluée automatiquement si changement.
Sinon il faut mettre la fonction Volatile mais ça serait une consommation de ressources inutile.
eric

Bonjour à tous,

On n'est pas obligé de passer un paramètre à une fonction.
Il faut faire un test, car la fonction ne peut être utilisée sur la ligne 1.

Essayez ceci :

Function mafonction()
    Dim cel As Range, x As Integer, y As Integer

    Set cel = ActiveCell
    x = cel.Column
    y = cel.Row
    mafonction = ""
    If y > 1 Then mafonction = Worksheets("Feuil1").Cells(y - 1, x).Value + 1
End Function

bonjour le fil,

on crée une plage nommée "Test" et puis on utilise celle-ci dans une fonction. Cela fonctionne mais ...

Function FVanCop()
     Application.Volatile                    'désolé mais nécessaire, voir Eriiic
     x = [test]                              'utiliser une plage nommée
     x1 = Split(x(1), "-")                   'séparer en ligne et colonne
     FVanCop = Sheets("blad1").Cells(--x1(0) + 1, --x1(1)).Value     'résultat
End Function

Bonjour à tous,

passer une variable à une fonction n'est pas obligatoire mais on se passe du fonctionnement normal et optimisé d'une fonction.
Excel met à jour son arbre des antécédents sur les paramètres passés, et du coup recalcule systématiquement et uniquement ce qui est nécessaire en cas de changement.

C'est donc à privilégier fortement.
Ne pas le faire OBLIGE à la mettre Volatile.

Pour moi utiliser ActiveCell reste une grosse erreur qui va amener des résultats erronés.
La cellule appelante d'une fonction est .ThisCell, pourquoi vouloir s'en passer ?
eric

A nouveau,

Si dans une cellule, j'encode "=mafonction()", je voudrais que la valeur retournée soit égale au contenu de la cellule qui se trouve juste au dessus +1.

Bien que le demandeur ne se soit pas manifesté après son premier message. Il me semble qu'il souhaite dans une cellule (peu importe laquelle) une fonction particulière.

Passer par ActiveCell est suffisant pour cela. Bien sur il ne faut pas multiplier dans ce cas l'utilisation de la fonction à plusieurs cellules. Cela engendrerait des incohérences en fonction de la dernière cellule activée.

Et il serait plus cohérent alors dans le cas d'appel par plusieurs cellules de passer par ThisCell qui limiterait à chaque cellule cible l'application de la fonction.

Mais non, c'est faux. Ca ne peut amener que des erreurs.
Met 5 en A2, ta fonction en A3, tu obtiens 6, c'est ok

Maintenant met 8 en C2 et garde C3 sélectionné.
Fait Ctrl+Alt+F9 pour recalculer toutes les formules. Que lis-tu en A3 ?

re, Eriiic a raison, voir PJ

Surtout qu'il y a une fonction qui marche très bien pour ça (ThisCell), voir le fichier de mon 1er post.
Pas la peine de chercher des solutions bancales.
eric

Suite et Fin,

Mon précédent message était assez explicite sur l'utilisation d'ActiveCell et ses précautions sans que je m'étende encore plus sur ce choix personnel.

Qui, de plus dans le cadre d'une fonction utilise régulièrement Ctrl+Alt+F9. Je le fais pratiquement jamais.

Ci-dessous un fichier avec plusieurs exemples passant par ActiveCell sur une même feuille.

Respecter les notes inscrites en rouge et bien sur, éviter le Ctrl+Alt+F9.

@Eriiic, okay et merci, je ne connaissais pas cet "Application.ThisCell",

et bien sur, éviter le Ctrl+Alt+F9.

le soucis c'est qu'excel ne se gène pas pour le faire tout seul à l'ouverture (voire à d'autres occasion ?) s'il ressent le besoin de tout recalculer.

Dans l'absolu il faudrait aussi forcer le travail dans la feuille d'appel car les fonctions personnalisées se recalcule parfois si on est sur une autre feuille :

    With Application.ThisCell.Parent
       .[A1] = a
           '...
          

Merci à tous pour vos réponses et désolé pour ma réaction tardive (je me suis absenté durant qq jours).

Je vais utiliser ActiveCell :-)

Je rêve....