Résult. fonct° instable si utilisée sur+de1feuille

[brokengillou]

Bonjour

J'ai réalisé une fonction qui renvoie le résultat attendu mais qui se comporte bizarrement lorsque on l'insère dans plusieurs feuilles.
En fait le contexte de calcul de la feuille visualisée précédament influe sur la feuille active qui renvoie des résultats abérants tant que l'on ne fait pas F9.
Merci d'avance pour votre aide?

Cordialement,

brokengillou [file name=MOY_PERSO_DEBUG.zip size=46349]http://www.excel-downloads.com/components/com_simpleboard/uploaded/files/MOY_PERSO_DEBUG.zip[/file]

 

[pierrejean]

une solution (pas forcement la meilleure et qui ne resoud pas le probleme à l'origine)

Private Sub Worksheet_Activate()
ActiveSheet.Calculate
End Sub

pour les feuilles ou la moyenne est utilisée

 

[Charly2]

Bonjour brokengillou et pierrejean, bonjour à toutes et à tous 🙂

Le comportement de ta fonction est tout à fait normal compte tenu du codage.

En fait, il est très rare dans une formule personnalisée, de définir des références de cellules. Ce qui est fait dans cette fonction perturbe logiquement son fonctionnement. La concaténation de chaînes pour obtenir des adresses de cellules n'est pas mauvaise en soit, mais lorsqu'ensuite on met une intruction de ce type :

Som_Coe_N = Cells(Lig_Moy_N, Col_Coe_N).Value

La fonction prend la bonne référence mais dans la feuille active ! Ainsi, à chaque recalcul dans une feuille, les résultats sont faussés pour les cellules qui contiennent cette formule mais qui ne sont pas dans la feuille active.

Voici ta fonction (hors commentaires et déclarations de variables) réécrite en tenant compte de ce qui vient d'être dit :

Function MOY_PERSO(Col_Coe_R As Range, Cel_Moy_R As Range)
'
' Déclarations variables
'

  Application.Volatile
  Application.ScreenUpdating = False

  With Sheets(Cel_Moy_R.Parent.Name)
    Cel_Moy_A = Cel_Moy_R.Address(0, 0)
    Col_Moy_A = Left$(Cel_Moy_R.Address(0, 0), (Cel_Moy_R.Column < 27) + 2)
    Lig_Moy_N = Cel_Moy_R.Row

    Col_Coe_A = Left$(Col_Coe_R.Address(0, 0), (Col_Coe_R.Column < 27) + 2)
    Col_Coe_N = Col_Coe_R.Column

    ' référence à la feuille !
    Lig_Vid_N = .Range('A65536').End(xlUp).Row + 1

    Ligv_N = 1
    Colv_N = 0

    Do
      If Cel_Moy_R.Offset(Ligv_N, Colv_N).Interior.PatternColor = 16711935 Then
        Exit Do
      Else
        Ligv_N = Ligv_N + 1
      End If
    Loop While Ligv_N < Lig_Vid_N

    Lig_DebSct_V = Lig_Moy_N + 1
    Lig_FinSct_V = Ligv_N - 1 + Lig_DebSct_V - 1

    ' Construction des références (adresses)
    Pla_Not_A = Col_Moy_A & Lig_DebSct_V & ':' & Col_Moy_A & Lig_FinSct_V
    Pla_Coe_A = Col_Coe_A & Lig_DebSct_V & ':' & Col_Coe_A & Lig_FinSct_V

    ' référence à la feuille !
    Num_N = WorksheetFunction.SumProduct(.Range(Pla_Not_A), .Range(Pla_Coe_A))
    Ssi_AE_N = WorksheetFunction.SumIf(.Range(Pla_Not_A), 'AE', .Range(Pla_Coe_A))
    Ssi_AR_N = WorksheetFunction.SumIf(.Range(Pla_Not_A), 'AR', .Range(Pla_Coe_A))
    Ssi_NU_N = WorksheetFunction.SumIf(.Range(Pla_Not_A), '', .Range(Pla_Coe_A))

    ' référence à la feuille !
    Som_Coe_N = .Cells(Lig_Moy_N, Col_Coe_N).Value

    Den_N = Som_Coe_N - Ssi_AE_N - Ssi_AR_N - Ssi_NU_N

    If Den_N = 0 Then MOY_PERSO = '--,--' Else MOY_PERSO = Num_N / Den_N

  End With

  Application.ScreenUpdating = True

End Function

En espérant avoir été assez clair :unsure:

A+ 😉

 

[brokengillou]

Re:Résult. fonct° instable si utilisée sur+de1feui

Bonsoir pierrejean et charly2

:woohoo:

Bravo , merci à charly2 pour sa correction, qui fonctionne parfaitement, je suis scié!
Encore une question...
'With Sheets(Cel_Moy_R.Parent.Name)
ç'est en rapport avec Application.Caller?
Quelle est la syntaxe pour qualifier les objets avec?

A+

brokengillou

NB je vais mieux dormir... jusqu'à ma prochaine gaffe...

 

[Charly2]

Re:Résult. fonct° instable si utilisée sur+de1feui

re 🙂

Ça a effectivement un lien avec Application.Caller, mais comme il y a une référence de cellule en paramètre, je l'utilise pour identifier la feuille de calcul d'où est appelée la fonction. On peut aussi mettre cette instruction équivalente dans ton cas mais certainement préférable si tu fais référence à des cellules d'autres feuilles :

With Application.Caller.Parent

' Ton code

End With

A+ 😉