Discussion :

[sebkem]

Bonjour à tous les développeurs,
Je suis tout nouveau sur ce site que je consulte de temps en temps pour mes propres macros Excel.

Ma question concerne un problème que j'ai dans une fonction simple que j'ai créée. Le but de la fonction est de retourner des valeurs qui sont contenues dans une feuille (qui s'appelle TAF) suivant la valeur de la cellule de la feuille courante où j'inscris la fonction. En gros, la fonction est une fonction de recherche conditionnelle qui concatène des valeurs contenues dans un tableau. La fonction s'appelle Functions_associées:


Mon problème est le suivant. Si j'appelle la fonction dans une macro de test, la fonction s'exécute très bien c'est à dire qu'elle concatène bien les valeurs contenues dans le tableau de l'onglet "TAF" qui comporte un "X" pour la valeur LCN.
Néanmoins, quand je mets directement dans la cellule G9 la fonction avec" =Functions_associées(C9)", ça ne me retourne aucune valeur (Vide, pas de message d'erreur #NOM ou #VALEUR ...). En recherchant des sources d'erreur, je pense que lorsque la fonction est appelée directement dans la cellule, elle n'arrive pas à gérer le sheets.select.
Ma question est donc de savoir s'il est possible d'utiliser le sheet.select dans une fonction qui n'est pas lancée par une macro. Et si c'est possible, comment puis-je le faire?

Nota: Si la fonction que je fais est similaire à une fonction RECHERCHE, IF intégrées de Excel, je recherche le moyen de le faire de façon perso. Le but étant de pouvoir adapter le code à différentes fonctions (base de données gérant plusieurs onglets) et toujours sans passer par une macro Sub ... End Sub. Je veux que la formule se mette à jour immédiatement sans avoir à relancer de macro.

En espérant avoir été à peu près clair et avoir une solution, je vous souhaite à tous un bon week-end de pâques (Yessss, Week-end à rallonge )


Le code de la fonction est le suivant:

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
Function Fonctions_associées(Name_LCN As String) As String
 
Application.Volatile True
 
Dim Ligne, Colonne As Integer
Dim Fin_Ligne, Fin_Colonne As Integer
Dim Deb_Ligne, Deb_Colonne As Integer
Dim Ws As Worksheet
 
Deb_Ligne = 6
Deb_Colonne = 7
Fin_Ligne = 126
Fin_Colonne = 20
 
Fonctions_associées = ""
ThisWorkbook.Worksheets("TAF").Select   '<= A Priori, c'est là qu'est le problème quand on place la formule directement dans la cellule
 
For Ligne = Deb_Ligne To Fin_Ligne
 
    If Cells(Ligne, 1).Value = Name_LCN Then
        For Colonne = Deb_Colonne To Fin_Colonne
            If Cells(Ligne, Colonne).Value = "X" Or Cells(Ligne, Colonne).Value = "x" Then
                Fonctions_associées = Fonctions_associées & Cells(5, Colonne).Value & ": " & Cells(4, Colonne).Value & Chr(13) & Chr(10)
            End If
        Next Colonne
        Exit Function
    End If
Next Ligne
End Function

[gnain]

Je veux que la formule se mette à jour immédiatement sans avoir à relancer de macro.

je ne sais pas si j'ai bien compris mais pour chaque changement il faut utiliser l'évenement Worksheet_SelectionChange
à mettre dans le module de ta feuille

Code :

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

1
2
3
4
5
6
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    MsgBox Target.Address
    If Target.Address = "$Ta_colonne$Taligne" Then
        'appel de ta fonction
    End If
End Sub

[joe.levrai]

Bonjour,

j'interviens uniquement pour la question de selection de feuille
pas besoin de selectionner la feuille pour ta fonction je pense

remplace ça

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
 
ThisWorkbook.Worksheets("TAF").Select
 
For Ligne = Deb_Ligne To Fin_Ligne
 
    If Cells(Ligne, 1).Value = Name_LCN Then
 
        For Colonne = Deb_Colonne To Fin_Colonne
 
            If Cells(Ligne, Colonne).Value = "X" Or Cells(Ligne, Colonne).Value = "x" Then
 
                Fonctions_associées = Fonctions_associées & Cells(5, Colonne).Value & ": " & Cells(4, Colonne).Value & Chr(13) & Chr(10)
 
            End If
 
        Next Colonne
 
        Exit Function
 
    End If
 
 Next Ligne

par

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
 
With ThisWorkbook.Worksheets("TAF")
 
    For Ligne = Deb_Ligne To Fin_Ligne
 
        If .Cells(Ligne, 1).Value = Name_LCN Then
 
            For Colonne = Deb_Colonne To Fin_Colonne
 
                If .Cells(Ligne, Colonne).Value = "X" Or .Cells(Ligne, Colonne).Value = "x" Then
 
                    Fonctions_associées = Fonctions_associées & .Cells(5, Colonne).Value & ": " & .Cells(4, Colonne).Value & Chr(13) & Chr(10)
 
                End If
 
            Next Colonne
 
            Exit Function
 
        End If
 
     Next Ligne
 
End With

j'ai pas testé, dis nous si c'est ok

[mercatog]

Pas besoin de sélectionner une feuille pour récupérer une valeur de l'une de ses cellules

Exemple pour ta fonction (regarde aussi les déclarations)

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
Function Fonctions_associées(Name_LCN As String) As String
Dim Ligne As Long, Colonne As Integer
Dim Fin_Ligne As Long, Fin_Colonne As Integer
Dim Deb_Ligne As Long, Deb_Colonne As Integer
 
Application.Volatile True
Deb_Ligne = 6
Deb_Colonne = 7
Fin_Ligne = 126
Fin_Colonne = 20
 
With ThisWorkbook.Worksheets("TAF")    '' <= A Priori, c'est là qu'est le problème quand on place la formule directement dans la cellule
    For Ligne = Deb_Ligne To Fin_Ligne
        If .Cells(Ligne, 1).Value = Name_LCN Then
            For Colonne = Deb_Colonne To Fin_Colonne
                If .Cells(Ligne, Colonne).Value = "X" Or .Cells(Ligne, Colonne).Value = "x" Then
                    Fonctions_associées = Fonctions_associées & .Cells(5, Colonne).Value & ": " & .Cells(4, Colonne).Value & Chr(13) & Chr(10)
                End If
            Next Colonne
            Exit Function
        End If
    Next Ligne
End With
End Function

Edit:
joe.levrai +1

[SfJ5Rpw8]

bonsoir,

et pour ce qui est de la fonction personnalisé voir :

http://silkyroad.developpez.com/vba/fonctions/#LI

et relire la dernière phrase de l'introduction

[sebkem]

Bonjour et merci à vous 2 Mercatog et joe.levrai.

Le With ThisWorkbook.Worksheets("TAF") ... End with fonctionne parfaitement. C'est trop cool, je suis trop content

C'est bien cette syntaxe que je ne connaissais pas.

Merci encore et bonne fin de journée à tous

[joe.levrai]

Bonjour,

ravi que tu ai trouvé ton bonheur !

en relisant ton code, je m'aperçois que tu testes une condition avec un "x" minuscule et un "X" majuscule.

Code :

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

1
2
 
If .Cells(Ligne, Colonne).Value = "X" Or .Cells(Ligne, Colonne).Value = "x" Then

il existe une option qui permet de ne pas tenir compte de la casse, et de détecter indifféremment une chaine en minuscule ou en majuscule

tu as juste à mettre tout en haut du module

Code :

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

1
2
 
Option Compare Text