Fonction VB personnalisée avec SOMMEPROD

[Djorge84]

Bonjour,

je cherche à passer en VB l'utilisation de la fonction SOMMEPROD afin de réaliser cette opération sur 3 plages de données.

Deux d'entre elles sont figées (à savoir Quantité et M_Unit dans le code qui suit) et la troisième peut varier en fonction de ma variable RN que je recherche dans la plage "I10:AZ10" de la feuille "Données".

Function A_Panier(RN As String) As Double
  
Dim j As Integer
Dim Cel As Range
Dim Plage As Range
Dim Quantité As Range
Dim M_Unit As Range

j = 0

With Sheets("Données")

Set Quantité = .Range("C11:C27")
Set M_Unit = .Range("D11:D27")

For Each Cel In .Range("I10:AZ10")
    
    If Cel.Value = RN Then
                j = Cel.Column

            Plage = .Range(Cells(11, j), Cells(27, j))

            Exit For
    End If
Next Cel

End With

A_Panier = SumProduct(Quantité * M_Unit * Plage)

End Function

et bien sûr cela ne fonctionne pas. Je ne sais pas où peut se situer l'erreur... Lorsque je nomme mes plages peut-être?

Si quelqu'un peut m'aider à régler ce problème, je lui en serait reconnaissant.

Cordialement

 

[pierrejean]

Re : Fonction VB personnalisée avec SOMMEPROD

bonjour Djorge84

A tester:

A_Panier = Application.WorksheetFunction.SumProduct(Quantité * M_Unit * Plage)

 

[Dranreb]

Re : Fonction VB personnalisée avec SOMMEPROD

Bonjour pierrejean
J'aurais plutôt dit

A_Panier = WorksheetFunction.SumProduct(Quantité, M_Unit, Plage)

Et surtout, un peu plus haut:

Set Plage = .Range(Cells(11, j), Cells(27, j))

car là, Plage n'est pas initialisé.
À+

 

[Djorge84]

Re : Fonction VB personnalisée avec SOMMEPROD

Bonjour,

merci à vous 2 pour vos tentatives. J'ai pris en compte vos remarques et j'ai donc le code suivant :

Function A_Panier(RN As String) As Double
  
Dim j As Integer
Dim Cel As Range
Dim Plage As Range
Dim Quantité As Range
Dim M_Unit As Range

j = 1

With Sheets("Données")

Set Quantité = .Range("C11:C27")
Set M_Unit = .Range("D11:D27")
Set Plage = .Range(Cells(11, j), Cells(27, j))

For Each Cel In .Range("I10:AZ10")
    
    If Cel.Value = RN Then
                j = Cel.Column

            Plage = .Range(Cells(11, j), Cells(27, j))

            Exit For
    End If
Next Cel

End With

A_Panier = Application.WorksheetFunction.SumProduct(Quantité, M_Unit, Plage)

End Function

qui malheureusement ne fonctionne toujours pas...

 

[Dranreb]

Re : Fonction VB personnalisée avec SOMMEPROD

Bonjour
Deux remarque:
1) - Plage.Value = .Range(Cells(11, j), Cells(27, j)).Value est incorrect dans une fonction
2) - WorksheetFunction.SumProduct rend un tableau dans un variant qui ne peut être affecté à un Double.
Remarque: Une fonction peut rendre un tableau dan un variant et être alors invoquée dans une formule matricielle.

Vous devriez vraiment joindre un fichier comportant tous les tenants et aboutissants du truc que vous cherchez à faire

 

[Djorge84]

Re : Fonction VB personnalisée avec SOMMEPROD

Re,

voilà donc un fichier pour exemple.

Sur l'onglet "Bilan", la ligne 15 donne le résultat d'un calcul utilisant la fonction SommeProd sur 3 plages de données (renseignées au niveau de l'onglet "Données"), dont l'une d'elle varie (cf. formule indiquée dans le fichier).

ça marche bien, pas de souci.

Mais j'aimerai faire passer cette commande Excel sous VB pour alléger mes feuilles Excel.

D'où les tentatives de code précédentes...

 

[Dranreb]

Re : Fonction VB personnalisée avec SOMMEPROD

Il vaudrait mieux écrire un fonction:

Function OuRien(V as variant) As Variant
If V=0 Then OuRien="" Else OuRien = V
End Function

Et lui passer l'expression avec le SommeProd plutôt que de le refaire dans VBA
Il est aussi possible de ne pas afficher les valeurs 0 tout simplement.
Les formules sont bien plus lisibles si on donne des noms aux plages impliquées.
Bonsoir.

 

[Djorge84]

Re : Fonction VB personnalisée avec SOMMEPROD

Re,

je suis d'accord avec le fait que de nommer les plages qu'on appelle permet d'aérer les formules. Je le fais d'habitude effectivement. Cependant dans le cas qui m'intéresse vu que l'une des plages utilisées dans le SommeProd varie, je préfère passer par le VB pour avoir quelquechose de plus robuste.

Sinon je ne comprends absolument pas ce que vous m'avez répondu avec le passage :

Il vaudrait mieux écrire un fonction:
Function OuRien(V as variant) As Variant
If V=0 Then OuRien="" Else OuRien = V
End Function
Et lui passer l'expression avec le SommeProd plutôt que de le refaire dans VBA

Des précisions?

 

[Dranreb]

Re : Fonction VB personnalisée avec SOMMEPROD

Bonjour.
Je voulais dire que s'il était idiot de faire calculer deux fois une expression comme dans

=SI($F$8=0;"";SI(SOMMEPROD(Données!$C$10:$C$27;Données!$D$10:$D$27;Données!I10:I27)/Données!$D$8=0;"";SOMMEPROD(Données!$C$10:$C$27;Données!$D$10:$D$27;Données!I10:I27)/Données!$D$8))

ce n'est pas pour autant qu'il faut la calculer une seule fois dans une fonction VBA: on peut très bien passer l'expression calculée une seule fois par Excel à une fonction personnalisée qui rend autre chose si ça n'a pas la gueule souhaitée.
À+

P.S. J'ai dit une ânerie dans un post précédent: Il n'y a pas de raison que Sumproduct rende un tableau. J'essaie toujours en vain de coomprendre ce que vous avez voulu faire dans votre fonction. Vous êtes toujours sûr que ce n'est pas un Set qu'il faut dans la boucle ? dans ce cas ça ne peut pas marcher: Excel ne change pas les Value de plages pendant une évaluation de formule même si c'est demandé dans VBA.

Est-ce normal que la ligne 10 contenant des titres soit inclus dans vos formules ?
Bon j'en ai marre. Sans noms de plages je n'y voit rien ! Je vais en mettre.

Un autre problème: Vous ne passez que le nom de l'isotope à votre fonction. Si vous l'utilisez dans une formule, Excel ne sera pas fondé à devoir en demander souvent l'évaluation, puisque celle ci ne dépend apparemment que de cela. On va donc y passer les Range en paramètres.

 

[Dranreb]

Re : Fonction VB personnalisée avec SOMMEPROD

Au final, voici plusieurs solutions.
Cordialement

 

[Djorge84]

Re : Fonction VB personnalisée avec SOMMEPROD

Merci de votre aide. Je vais regarder tout ça de près!

En tout cas c'est sympa de proposer plusieurs solutions, ça me permet d'apprendre...

Je comprends mieux du coup votre fonction OuRien! ;-)

Une question encore : dans votre code pour la fonction A_Panier = Application.WorksheetFunction.SumProduct(FDonn.[Qté], FDonn.[MUnit], LaColonne) / MassTot, que signifie la syntaxe FDonn.[Qté] et autres notations similaires?

Cdlt

 

[Dranreb]

Re : Fonction VB personnalisée avec SOMMEPROD

FDonn est le nouveau nom VBA (ou CodeName) que j'ai donné à votre première feuille.
[Réfcel] est une manière plus courte d'écrire Range("RéfCel") lorsque l'expression RéfCel est une simple constante.
Cordialement