Problème pour traduire une fonction Excel en VBA [Résolu]

[Magic_Doctor]

Bonjour,

Dans ma feuille j'ai rédigé cette fonction (intrication de fonctions) qui marche :

=MIN(DECALER($B$5;1;2;EQUIV($C$2;$B$6:$B$38;0)))

J'ai tenté de la traduire pour une macro :

Sub zaza()

Dim myRange As Range, pos, b
Dim mini As Double


Set myRange = Worksheets("Tableaux").Range("B6:B38")
pos = Application.WorksheetFunction.Match([C2], myRange, 0) 'OK
b = Application.WorksheetFunction.Offset([B5], 1, 2, pos) 'ça coince

mini = Application.WorksheetFunction.Min(b)

End Sub

Comment s'y prendre ?

 

[Staple1600]

Re

Ma question est:
Pourquoi le fichier n'est plus le même?
Les références des cellules ne sont plus les mêmes!

Ma question est:
Pourquoi ma macro (celle de message#11) se retrouve dans une procédure événementielle?
Quand elle est testée dans un module standard, elle fonctionne.
Je le sais, je l'ai testé sur la PJ du message#3

Ma réponse est:
Je passe donc la main à mes petits camarades de jeux pour la suite

 

[Magic_Doctor]

Re,

La réponse est fort simple. On teste d'abord avec un seul tableau. Si ça marche, on teste avec plusieurs tableaux. Et ça ne marche plus très bien avec plusieurs tableaux.
La 1ère PJ était ni plus ni moins qu'un gros résumé de mon projet.

Si vous parvenez à lier les 4 cellules en dehors d'une macro événementielle, dites-moi alors comment.
Que les coordonnées aient changé, ma foi, ce n'est pas trop grave. Le principe reste le même.
Enfin, loger une macro dans une procédure événementielle ou l'appeler à partir de la même procédure, je ne vois pas trop la différence.

Je résume le problème : une cellule prend la main, comment faire en sorte que les autres en fassent autant.
Je choisis une donnée dans la cellule mère (celle qui prend la main) : ça marche.
Je choisis une donnée dans l'une quelconque des 3 autres cellules : ça marche à condition de choisir 2 fois de suite la même donnée.

 

[Magic_Doctor]

Re,

J'ai fini par régler le problème.
La fonction Evaluate (que j'avais totalement oubliée) est certes puissante mais sujette à surprises, du moins pour ceux qui ne jonglent pas avec.
Elle marchait mais, disons, capricieusement.
La pierre angulaire du problème est la concentration que l'on choisit. Sans elle, pas de troncage des colonnes.
Je regarde de près la routine de Staple (post #11) et nulle part je vois cette variable, alors que dans le post#4 il l'introduit dans la fonction Evaluate.
Voilà donc pourquoi ça ne pouvait pas marcher.
J'ai donc pris le taureau par les cornes et bidouillé une fonction :

Function MuchasCol(num As Double, plage As Range, col As Byte, x As Byte) As Double
'Renvoie la valeur minimale ou maximale ou la moyenne d'un ensemble de colonnes qui peut être tronqué
'Magic_Doctor
'- num : un nombre appartenant obligatoirement à la colonne des "abscisses"
'- plage : l'ensemble de cellules constituant la colonne des "abscisses"
'- col : le nombre de colonnes (contiguës à la colonne des "abscisses") que l'on veut analyser
'- x : si = 1 --> valeur minimale | si = 2 --> valeur maximale | si = 3 --> moyenne

Dim haut As Integer

haut = Application.WorksheetFunction.Match(num, plage, 0) 'hauteur des colonnes
Set plage = plage.Offset(, 1).Resize(haut, col) 'redimensionnement de la plage suivant la concentration choisie

If x = 1 Then
    MuchasCol = Application.WorksheetFunction.Min(plage) 'valeur minimale de l'ensemble de cette plage de cellules
ElseIf x = 2 Then
    MuchasCol = Application.WorksheetFunction.Max(plage) 'valeur maximale de l'ensemble de cette plage de cellules
Else
    MuchasCol = Application.WorksheetFunction.Average(plage) 'moyenne de l'ensemble de cette plage de cellules
End If

End Function

qui marche apparemment bien et résoud définitivement (du moins je l'espère) mon problème.

 

[Magic_Doctor]

Bonjour,

Pour clore ce fil passionnant, je ferai quelques commentaires concernant la traduction des fonctions Excel en VBA.

Dans mon 1er post je demandais quelle pouvait être la traduction de cette intrication de formules Excel en VBA :

=MIN(DECALER($B$5;1;2;EQUIV($C$2;$B$6:$B$38;0)))

Je signale, au passage, qu'en raison du forum francophone, j'avais déjà traduit ces fonctions de l'espagnol (ma version d'Excel étant en espagnol) au français. Donc les traduire en anglais n'est pas bien difficile.
MIN (Excel français) = MIN (Excel anglais)
EQUIV (Excel français) = MATCH (Excel anglais)
DECALER (Excel français) = OFFSET (Excel anglais)

Seulement, en VBA, il faut faire quelques modifications :
MIN ---> Application.WorksheetFunction.Min(plage de cellules)
MATCH ---> Application.WorksheetFunction.Match($C$2, $B$6:$B$38, 0)

J'avais testé Application.WorksheetFunction.Match($C$2, $B$6:$B$38, 0), ça marchait parfaitement.
Pour Application.WorksheetFunction.Min(plage de cellules quelconques), ça marchait aussi sans problème.
En revanche, pour OFFSET il en va tout autrement.
Logiquement on devrait écrire :
OFFSET ---> Application.WorksheetFunction.Offset($B$5,1,2,résultat de Match)
Et bien, ça ne marche pas du tout !
Il faut combiner Offset avec Resize.
Il n'y a donc pas d'équivalence "directe" entre OFFSET (Excel anglais) et Application.WorksheetFunction.Offset

Toute PJ était donc cette fois inutile pour résoudre ce problème.
Ma fonction montre très bien comment contourner ce problème, même s'il existe d'autres méthodes plus élégantes.

 

[Staple1600]

Bonsoir le fil, le forum

La macro du message#11 renvoyait 1.49 comme mini si on avait 60% en C2
C'est bien que qu'affichait la formule, non ???

Toute PJ était donc cette fois inutile pour résoudre ce problème.

Cet assertion n'engage que toi.
Et en quoi est-ce un problème pour le demandeur d'ajouter un fichier exemple pour aider à la résolution de sa question?

 

[Magic_Doctor]

Staple, je vous ai compris !

 

[Staple1600]

Re

Peut-être mais toujours pas de réponse quant à la macro du post#11
(voir question dans le précédent message)

 

[Magic_Doctor]

Et quand on adresse poliment une 2ème PJ montrant le problème, on se fait rabrouer...

Staple, je vous ai compris !

PS : les forums, quels qu'ils soient, sont faits pour le fun et rien que le fun !

 

[Staple1600]

Re

Je ne t'ai pas rabrouer.

Je te demande seulement si oui ou non la macro du message#11 affiche 1.49 quand C2=60%
C'est ce qui se passe sur mon PC quand je teste avec ta pièce jointe.

 

[Staple1600]

Re

Pour continuer les tests

Sub a()
MsgBox Application.WorksheetFunction.Min(Range("B5").Offset(, 2).Resize(Application.Match(Range("C2"), Range("$B$6:$B$38"), 0)))
End Sub

 

[Staple1600]

Re

Suite des investigations
Une version plus courte

Sub b()
Set f = Application.WorksheetFunction
MsgBox f.Min([B5].Offset(, 2).Resize(f.Match([C2], [$B$6:$B$38], 0)))
End Sub

 

[Staple1600]

Re

Suite...
Pour l’étrangeté de la chose

Sub c()
Set f = Application.WorksheetFunction: Set r = [C2]
With r
MsgBox f.Min(.Offset(3, -1)(1, 3).Resize(f.Match(.Value, [$B$6:$B$38], 0)))
End With
End Sub

Sans les endives, comme c'est bizarre (on doit mettre 4 ??)

Sub d()
Set f = Application.WorksheetFunction: Set r = [C2]
MsgBox f.Min(r(3, -1)(1, 4).Resize(f.Match(r, [$B$6:$B$38], 0)))
End Sub

 

[Staple1600]

Suite

@Magic_Doctor

Re,
J'ai donc pris le taureau par les cornes et bidouillé une fonction :

Function MuchasCol(num As Double, plage As Range, col As Byte, x As Byte) As Double
'...
End Function

qui marche apparemment bien et résoud définitivement (du moins je l'espère) mon problème.

J'ai téléchargé ton fichier Courbes4
J'ai beau regardé (j'ai affiché les formules), je ne trouve pas où tu utilises ta fonction MuchasCol
Tu peux m'indiquer où elle est utilisée, stp ?
(ou à défaut un exemple de syntaxe d'utilisation, car quand je la teste elle renvoie #VALEUR!)

 

[Magic_Doctor]

Re,

J'ai mis une dernière mouture avec quelques modifications mineures.
Ma foi, ça peut toujours intéresser un passionné du peroxyde d'hydrogène...
Dans ma fonction, je me suis pour l'instant inspiré de "Set f = Application.WorksheetFunction". Je ne savais pas qu'on pouvait faire ça. Ça allège visuellement.

J'utilise cette fonction une seule fois (pour cette maudite courbe N) dans le module de feuille (là où il y a une flèche en REM).

Private Sub Worksheet_Change(ByVal Target As Range)
    Application.ScreenUpdating = False

    If Not Intersect(Target, [O9,AD9,AN9,AX9,BI9]) Is Nothing Then
        With Application: .Calculation = xlManual: .EnableEvents = False: End With  'sinon c'est la cata !!!!
            [O9,AD9,AN9,AX9,BI9] = Target.Value
        With Application: .Calculation = xlAutomatic: .EnableEvents = True: End With
    
        Worksheets(1).Shapes("Graphique_N").Chart.Axes(xlValue).MinimumScale = MuchasCol([O9], Range("AM15:AM47"), 2, 1) '<---
        Worksheets(1).Shapes("Graphique_MV").Chart.Axes(xlValue).MinimumScale = [AX15]
    End If

    If Not Intersect(Target, [O8,P56,P57,P59,P60]) Is Nothing Then
        Target.Select
    End If

    Application.ScreenUpdating = True
End Sub

 

[Staple1600]

Bonsoir le fil, le forum

@Magic_Doctor
Merci pour la PJ qui a éclairé ma lanterne

Au cas où, et par curiosité, est-ce ta fonction ainsi modifiée fonctionne chez toi?

Public Function MuchasCol(ByRef n As Range, p As Range, c As Long, x As Byte) As Double
'Renvoie la valeur minimale ou maximale ou la moyenne d'un ensemble de colonnes qui peut être tronqué
'Function de Magic_Doctor, recoloriée par Staple1600 ;-)
Dim haut&, f: Set f = Application.WorksheetFunction
haut = f.Match(n, p, 0): Set p = p.Offset(, 1).Resize(haut, c)
MuchasCol = Switch(x = 1, f.Min(p), x = 2, f.Max(p), x = 3, f.Average(p))
End Function

PS: Je ne sais toujours pas ta réponse relative à la question de la macro du message#11 qui chez moi fonctionne.