[VBA Excel] WorksheetFunction, passage d'arg Résolu/Fermé

Question

Bonjour à tous ^^,
voilà mon souci :
je cherche à calculer l'écart type des données en colonne F qd la valeur en colonne B est égae à celle en colonne C....
j'ai exploré la piste des matrices, mais le fait de tester B=C me renvoie des 0 qd c'est faux, logique mais ça fausse mon écart type => pas de fonction matricielle :(
du coup jme suis lancé en vba, mais j'ai un pb pour passer les arguments à WorksheetFunction.STDEV(), voilà mon code : 
Function stdevBequalsC(plage As Range) '

chaine = ""
For Each c In plage
    If Range("B" & c.Row) = Range("C" & c.Row) Then
        cpt = cpt + 1
        If cpt = 1 Then
            chaine = Range("F" & c.Row)
        Else
            chaine = chaine & "," & Range("F" & c.Row)
        End If
    End If
Next c
'MsgBox chaine
stdevBequalsC = WorksheetFunction.StDev(chaine) '=> c'est là que ça coince !

End Function

si vous avez une idée...
merci :)

Polux31 · Answer

Bonjour,

WorksheetFunction.StDev() doit recevoir un objet Range en argument et non pas un String.

Si tu fais : stdevBequalsC = Application.WorksheetFunction.StDev(plage) tu dois avoir un résultat.

;o)

tompols · Answer

hello,
merci pr la réponse, en fait, ce n'est pas si catégorique que ça,WorksheetFunction.StDev(1,2,3) revoie bien l'ecart type, d'où mon idée de passer par une chaine.....
j'ai testé avec un range mais mon pb à ce moment là était de construire mon range ds ma boucle :
Function stdevBequalsC(plage As Range) '
Dim c As Range, myrange As Range, cpt As Integer
For Each c In plage
        cpt = cpt + 1
        If cpt = 1 Then
            myrange = Range(c.Address) ' ça coince ici
        Else
            myrange = Union(myrange, Range(c.Address)) 'et là
        End If
Next c

stdevBequalsC = WorksheetFunction.StDev(myrange) 
End Function Range(c.Address) ne fonctionne pas, j'ai testé également myrange = c et myrange = range(c).....
jpense bien que c'est la bonne piste, mais je n'arrive juste pas encore à créer cette plage (en meme tps jdois avouer que c'est la premier fois que jme retrouve à manipuler un objet range de cette façon)

garion28 · Answer

i=1 while (range("B" & i).value<>"" and range("C" & i).value<>"") 'parcours toute les cellule jusqu'a ce qu'il n'y ai plus de valeur dans B et C, necessite qu'il n'y ai pas de ligne vide if (range("B" & i).value = range("C" & i).value then range("B" & i).value = calculecarttype(range("B" & i).value,range("C" & i).value) end if i=i+1 wend et tu écrit ta fonction calculecarttype(param1,param2) toi même ou bien tu cherche si ca existe sur excel (perso je n'utilise jamais ces truc de plage, union, for each etc... quand il sagit de cellule après c'est sur que ca nécessite des modification, notamment pour la plage mise en paramètre et probablement dans le code utilisant cette fonction mais selon moi c'est plus simple à manipuler

tompols · Answer

re,
merci mais aucun pb du coté de ma boucle, au contraire, jprefere une fonction avec un argument de type range pour que l'utilisateur choissise la plage à traiter....ensuite tu n'as pas tout suivi là, le truc c'est de calculer l'écart type pour la population de données en colonne F seulement quand B et C sont égaux (ds ton code, tu calcules l'écart type entre B et C,  qui d'ailleurs sont égaux => pas besoin calcul ecart type = 0; et en plus tu écrases la valeur en B !)
pour ce qui est de recréer un fonction ecarttype, franchement je prefererais m'en passer (j'y avais pensé, en passant une variable tableau contenant mes valeurs, ce sera la solution de "dernier recours"....)

garion28 · Answer

pardon ce n'était pas dans la colonne B a écrire mais F ^^'

je pense que ton probleme vien du fait que tu écrit range(c.adress) au lieu de range(c.adress).value

tompols · Answer

non non, le probleme n'est pas là, le probleme est de creer un objet range contenant mes cellules F qd ma condition est respectée : comment creer un objet Range et y ajouter des plages/cellules.... 
sinon j'ai testé la piste des Range.values en stockant ces valeurs dans une chaine mais WorksheetFunction.StDev() ne smeble pas aimer qu'on lui passe une variable chaine (d'ou l'idée de passer un range)....
merci qd meme :)

garion28 · Answer

arf et as tu essayé c.value au lieu de range(c.adress)
car pour moi c c'est l'ensemble d'une cellule, donc il y a son adresse mais aussi sa valeur (enfin normalement)

tompols · Answer

bon alors 
propriété value : la valeur seulement
propriété addess : l'adresse seulement
je repete, mon probleme si situe au niveau de WorksheetFunction.StDev et du passage d'arguments, recuperer les valeurs de cellules dans une chaine ou un tableau, no pb....

Polux31 · Answer

Re,

Il doit y avoir une solution plus simple, mais je te propose de construire ta plage sur la feuille Excel avec les éléments utiles, de selectionner cette plage pour ensuite la passer en argument.

Sub selectPlage()
Dim lig As Long
Dim ligTest As Long
Dim derlig As Long
Dim mTarget As Range

    lig = 1
    ligTest = 1
    While Sheets(1).Range("B" & ligTest).Value <> ""
        If Sheets(1).Range("B" & ligTest).Value = Sheets(1).Range("C" & ligTest) Then
            Sheets(1).Range("J" & lig).Value = Sheets(1).Range("F" & ligTest).Value
            lig = lig + 1
        End If
    ligTest = ligTest + 1
    Wend
    
    Set mTarget = Sheets(1).Range("J1: J" & lig - 1)
    
    MsgBox stdevBequalsC(mTarget)
    

End Sub

Function stdevBequalsC(plage As Range)
   stdevBequalsC = WorksheetFunction.StDev(plage)
End Function

;o)

tompols · Answer

Re Polux,
jvois qu'on est sur la meme longueur d'onde, j'ai pensé aussi à créer un feuille pour le calcul puis la supprimer.....mais quand meme, j'aurai bien aimé pouvoir passer mes arguments à la worksheetfunction dans le code (ouais jsuis un peu tétu mais j'aime pas qd j'y arrive pas ! :D ), ça serait plus "joli" ....^^
merci :)

Polux31 · Answer

As-tu essayé comme ça ?

Function stdevBequalsC(plage As Range) '
Dim c
Dim chaine As String
Dim cpt As Integer

For Each c In plage
    If Range("B" & c.Row) = Range("C" & c.Row) Then
        cpt = cpt + 1
        If cpt = 1 Then
            chaine = chaine & "F" & c.Row
        Else
            chaine = chaine & ":F" & c.Row
            cpt = 0
            chaine = chaine & ","
        End If
        
    End If
    
Next c

chaine = Mid(chaine, 1, Len(chaine) - 1)

'    MsgBox WorksheetFunction.StDev(Range(chaine))

stdevBequalsC = WorksheetFunction.StDev(Range(chaine))

End Function

Ca fonctionne chez moi.

Je suis également têtu ...

;o)

tompols · Answer

Yes, on y est, j'avais pas pensé à reconstruire l'adresse ds une string !
j'ai juste modifié le séparateur ":" en virgule sinon ça pose pb pour les cellules non contiguës.....reste à tester sur le classeur avec les données à analyser, j'ai peur de dépasser les 30arguments pour la fonction (30arg max pour stdev selon l'aide) mais au pire je gérerai ça dans le code en passant le plage contigües sous la forme Fx:Fy (genre "F1,F2,F12:F18" qui doit etre compris comme 3 arguments)....
encore merci Polux, comme quoi la ténacité paye (et c'est la que ma signature prend tout son sens qd on voit les autres pistes évoquées) :)
jmets en résolu, mais jdonnerai ds nouvelles pour les 30arguments, ça peut toujours servir ;)