Discussion :

[mimi123456789]

Bonjour à tous,

Savez-vous si il est possible a une fonction vba de modifier la valeur d'une cellule dans une feuille de calcul et en meme temps retourner une valeur?
Je cherche déséspérement un moyen de le faire et ce sans passer par une procédure.

Un tout grand merci pour votre précieuse aide!

[Daniel.C]

Bonjour,

Pas directement, ce pendant, tu peux combiner une fonction et une macro évènementielle pour arriver à tes fins (c'est de la bidouille) :

Code :

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

1
2
3
Function test(c, Optional ValeurAutreCellule)
    test = 2
End Function

et :

Code :

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

1
2
3
4
5
6
7
8
9
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.HasFormula And Left(Target.Formula, 5) = "=test" Then
    txt = Split(Target.Formula, ",")(1)
    txt = Mid(txt, 2, Len(txt) - 3)
    Application.EnableEvents = False
    [J1] = txt
    Application.EnableEvents = True
End If
End Sub

[Daniel.C]

Ou, plus simple, avec une variable publique :

Code :

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

1
2
3
4
5
Public Var As Variant
Function test(c, ValeurAutreCellule)
    Var = ValeurAutreCellule
    test = 2
End Function

et dans le module feuille :

Code :

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

1
2
3
4
5
6
7
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.HasFormula And Left(Target.Formula, 5) = "=test" Then
    Application.EnableEvents = False
    [J1] = Var
    Application.EnableEvents = True
End If
End Sub

[mimi123456789]

Merci beaucoup pour ces réponses, par contre je n'arrive toujours pas à modifier la valeur de la cellule J1.

[gFZT82]

Bonjour,

Savez-vous si il est possible a une fonction vba de modifier la valeur d'une cellule dans une feuille de calcul et en même temps retourner une valeur?

Je ne suis pas sûr d’avoir bien saisi le problème et surtout la finalité de ta demande.
Pourquoi faut-il que ce soit impérativement une fonction ?
Regarde le test ci-dessous.

Code :

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

1
2
3
4
5
6
7
Sub Test()
    Range("K1") = Essai
End Sub
Function Essai()
    Range("J1") = "Bonjour, "
    Essai = "Ca va ?"
End Function

La fonction Essai modifie la valeur de J1 et retourne une valeur.

Cordialement.

[Daniel.C]

Bonjour,
@ gFZT82 :

Ca fonctionne effectivement si la fonction est appelée par VBA. Ca ne fonctionne pas si elle est utilisée comme une fonction perso dans Excel.

@ mimi123456789 :
Regarde le classeur joint.

[gFZT82]

Bonjour Daniel.C,

Ca fonctionne effectivement si la fonction est appelée par VBA. Ca ne fonctionne pas si elle est utilisée comme une fonction perso dans Excel.

Oui, mais mimi123456789 ne fait pas état d'une fonction personnelle.
Attendons ses précisions sur la finalité.

Cordialement.

[mimi123456789]

Merci pour toutes ces réponses. Effectivement j'ai besoin d'une fonction personnelle que "j'appelle" depuis une feuille de calcul excel.
Ma fonction perso doit pouvoir modifier la valeur d'une autre cellule (dans la feuille de calcul) et en même temps renvoyer une valeur.
Le projet sur lequel je travaille m'impose d'utiliser une fonction et non pas une procédure.

Merci pour votre aide.

[Daniel.C]

Est-ce que tu as testé ma solution ?

[mimi123456789]

Oui merci. J'ai touvé ou il y a un petit problème. Ca marche si la fonction et la cellule à modifier se trouvent sur la meme feuille mais dès que la fonction se trouve dans une feuille différente de la cellule à modifier ca ne fonctionne plus.

[Daniel.C]

C'était juste un exemple à adapter à tes besoins. Regarde le classeur joint. La fonction demande en arguments :
1. la valeur à mettre dans la cellule cible;
2. l'adresse de la cellule cible (type string);
3. Le nom de la feuille (type string).
Le code de la fonction est :

Code :

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

1
2
3
4
5
6
7
8
9
10
11
Public Valeur As Variant
Public Feuille As String
Public Cell As String
 
Function test(ValeurCible As Variant, Ou As String, FeuilleCible As String)
    Application.Volatile
    Valeur = ValeurCible
    Cell = Ou
    Feuille = FeuilleCible
    test = 2
End Function

Dans le module "ThisWorkbook" :

Code :

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

1
2
3
4
5
6
7
Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
If Target.HasFormula And LCase(Left(Target.Formula, 5)) = "=test" Then
    Application.EnableEvents = False
    Sheets(Feuille).Range(Cell) = Valeur
    Application.EnableEvents = True
End If
End Sub

Note que c'est un détournement de l'usage normal d'une fonction. Ce n'est qu'un pis-aller.

[mimi123456789]

La fonction ne doit pas demander en argument "l'adresse du stockage".
Merci pour ton aide et ta patience!
voici le code:

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
Public Var As Variant
Function test(c)
 ValeurAutreCellule = 99999
    Var = ValeurAutreCellule
    test = c
End Function
 
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.HasFormula And Left(Target.Formula, 5) = "=test" Then
    Application.EnableEvents = False
    Worksheets("AAA").Range("AEB") = Var
    Application.EnableEvents = True
End If
End Sub

Si je suis dans une autre feuille que AAA la fonction ne modifie rien alors que je lui ai spécifié quoi modifier....

j ai oublié le Application.Volatile ca devrait fonctionner mnt...

[Daniel.C]

Bon. Je t'ai donné une méthode qui fonctionnait. Si tu n'en veux pas...

[mimi123456789]

finalement ca ne marche toujours pas, je vais creuser encore.... merci pour ton aide

[mimi123456789]

Bonjour,
Serait-ce la version de Excel qui jouerai un rôle? Je travaille actuellement sur la dernière version sur PC.
Je ne suis toujours pas en mesure de modifier la cellule de la feuille AAA en appelant la fonction dans une feuille que celle AAA.
Je ne comprends pas mon erreur.
Merci pour votre aide.

[gFZT82]

Bonjour,

J’ai testé le code de Daniel qui fonctionne parfaitement.

1. J’ai placé le code suivant dans un module standard (Modules / Module1)

Code :

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

1
2
3
4
5
6
Public Var As Variant
Function test(c)
 ValeurAutreCellule = 99999
    Var = ValeurAutreCellule
    test = c
End Function

2. J’ai placé le code suivant dans le module ThisWorkbook

Code :

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

1
2
3
4
5
6
7
Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
If Target.HasFormula And Left(Target.Formula, 5) = "=test" Then
    Application.EnableEvents = False
    Worksheets("AAA").Range("AEB1") = Var
    Application.EnableEvents = True
End If
End Sub

Nota : j’ai modifié l’adresse destination AEB1.

Cordialement.

[mimi123456789]

Un tout grand merci a Daniel.C et gFzT82!!!
Merci pour votre patience!