Discussion :

[Invité]

Bonjours,

Je n'arrive pas à me servir de la fonction Sumproduct en VBA qui pourtant marche bien en Excel. J'aurai aimer savoir comment on peut introduire des constante dans une fonction Sumproduct.

Imaginons les valeurs suivantes:
A_________B____C_____D_______ Résultat (Excel)____Résultat (VBA)
2.5_______ 2____25____32_______8.967539539______
1_________3____32____12_______-1.727402497______
2_________7____66____24_______-2.173019259______
3_________9____55____78_______1.029013732_______
4_____________________________14.18208467_______
5_____________________________-4.731036159______
6_____________________________-7.243988707______


En Excel, le Résultat (Excel) s'écrit:

Code :

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

=SOMMEPROD($C$2:$C$5; SIN(($D$2:$D$5 * $A2) + RADIANS($E$2:$E$5)))

Cela fonctionne bien. Le résultat est bon. La constante introduit (A2) dans la formule ne pose pas de problème. Excel la prends comme valeur unique. En faisant glisser la formule dans la feuille, la valeur A2 change et passe en A3, puis A4 etc...

En VBA, cela ne marche pas dutout, pourquoi ? Même en utilisant une boucle for pour la constante.

Quel code puis-je appliquer pour avoir le même résultat qu'en Excel ?

Merci.

[SMBeccaria]

Bonjour,

Essayez avec cette procédure:

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
30
31
32
33
34
35
 
Sub MyCalculous()
    Dim mywb As Workbook
    Dim myws As Worksheet
    Dim myConst_B As Range, myConst_C As Range, myConst_D As Range
    Dim myDriver As Range, outPutrange As Range
    Dim myFormuleMagic As String
 
    Set mywb = ThisWorkbook
    Set myws = mywb.Worksheets(1)
    myws.Range("H2:H8").ClearContents
    Set myDriver = myws.Range("A2")
    Set outPutrange = myws.Range("H2")
    Set myConst_B = myws.Range("C2:C5")
    Set myConst_C = myws.Range("D2:D5")
    Set myConst_D = myws.Range("E2:E5")
 
    Do Until myDriver = ""
        myFormuleMagic = "=SUMPRODUCT(" & CStr(myConst_B.AddressLocal) & "," & "SIN((" & CStr(myConst_C.AddressLocal) & "*" & _
        CStr(myDriver.AddressLocal) & ")" & "+RADIANS(" & CStr(myConst_D.AddressLocal) & ")))"
        Debug.Print myFormuleMagic
        myws.Range(outPutrange.AddressLocal).Formula = CStr(myFormuleMagic)
        Set myDriver = myDriver.Offset(1)
        Set outPutrange = outPutrange.Offset(1)
    Loop
 
    Set mywb = Nothing
    Set myws = Nothing
    Set myDriver = Nothing
    Set outPutrange = Nothing
    Set myConst_B = Nothing
    Set myConst_C = Nothing
    Set myConst_D = Nothing
 
End Sub

[Invité]

Bonjour, et merci beaucoup pour cette réponse.

Aïe, aïe, aïe, j'ai failli dire (vu la beauté de la Macro) que c'était impressionnant et que vous déteniez " la " solution ! ... mais non, fausse joie que j'ai pas eu en voyant qu'il s'agissait d'une formule !!!


Je banie les formule en VBA pour ne laisser place qu'au .Value

J'aurai enfaite des milliers et des milliers de formule (en .value enfaite) à utiliser parceque ça rame de partout avec des formules.

Enfaite, je voudrai utiliser des matrices avec beaucoup de cellules et ça prends trop de mémoire avec les formula, alors je suis limité.
Au chargement, ça peux me prendre 5 Go de RAM ou plus:

Avec des .value, j'aurai un plus grand contrôle et je pourrai aller plus loin dans les calculs.

Les formules sont bien pour le calcul en continue ou pour la mise à jour auto de données en direct mais ne permettent pas ou permettent très mal les calculs de longues durées.

Avec des .Formula, je peux y arriver simplement (on peut se débrouiller avec les . formula de la forme (= "="):

Code :

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

1
2
3
4
5
6
7
8
Sub form()
Dim atext As String
 
atext = "$A2"
[I2].Formula = "=" & "SUMPRODUCT($C$2:$C$5" & "," & " SIN(($D$2:$D$5 * " & atext & ") + RADIANS($E$2:$E$5)))"
[I2].AutoFill Destination:=[I2:I8], Type:=xlFillCopy
 
End Sub

La même en .Value ?


P.S.: En tout cas, c'est sympa d'avoir d'essayé. Ça a dû prendre du temps en plus vu la longueur de la Macro .

[SMBeccaria]

Peut-être que cette forme te conviendra.

La premiere valeur attendue est un scalaire, les trois autres sont des vecteurs (de même longueur):

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
 
 
Public Function MyCalculous2(myDriver As Range, myConst_B As Range, myConst_C As Range, myConst_D As Range) As Double
    Dim i As Integer
    Dim Dbl_outPutrange As Double
    Dim pi As Double
 
    pi = 3.1416
    Dbl_outPutrange = 0
    For i = 1 To myConst_B.Count
        Dbl_outPutrange = Dbl_outPutrange + myConst_B(i) * Math.Sin(myConst_C(i) * myDriver.Value + myConst_D(i) * pi / 180)
    Next i
    MyCalculous2 = Dbl_outPutrange
End Function

[Invité]

Petite mise à jour, j'ai trouvé une méthode, certainement peu efficace, mais je verrai par la suite si ça vaut le coup de continuer ou pas. Si c'est trop lent, l'intérêt de la fonction SOMMEPROD se perd.

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
30
31
Option Explicit
Sub form()
 
Dim atext As String
Dim ctext As String, dtext As String, etext As String
Dim itext1 As String, itext2 As String
Dim ModeCalc As Integer
 
ModeCalc = Application.Calculation
Application.Calculation = xlCalculationManual
 
atext = "$A2" ' A écrire sur la Feuille Excel en $K$20 :=> $K$20 = $A2  'Sans les guillemets
ctext = "$C$2:$C$5"  ' A écrire sur la Feuille Excel en $K$21 :=> $K$21 = $C$2:$C$5  'Sans les guillemets  'ADRESSE par calcul dans feuille
dtext = "$D$2:$D$5"  'idem
etext = "$E$2:$E$5"  'idem
itext = "$I$2:$I$8"  'idem
itext1 = "$I$2"      'idem
itext2 = "$I$8"      'idem
 
With Range(itext1)
     .Formula = "=" & "SUMPRODUCT(" & ctext & "," & " SIN((" & dtext & " * " & atext & ") + RADIANS(" & etext & ")))"
     .AutoFill Destination:=Range(itext), Type:=xlFillDefault
End With
ActiveSheet.Range(itext).Calculate  'Obligatoire : Pour les cas où le calcule auto n'est pas actif, avec Nom classeur actif
With Range(itext)
     .Value = .Value  'Transformation de Formule en Valeur
End With
 
Application.Calculation = ModeCalc
 
End Sub

Nos chemins se croisent !

Merci, je vais essayer ta solution pour voir.

EDIT: Ah, elle n'utilise pas de SOMMEPROD hélas.
C'est sympa de ta part de m'avoir fait une petite fonction, mais je ne pense pas que j'utiliserai de fonction perso pour l'instant. Mon but est surtout d'essayer d'apprendre le VBA enfaite et bien sûr pouvoir faire des trucs avec comme ici. Mais je commence à connaitre de mieux en mieux le VBA (création de boucle avec pause/marche/arrêt, création de tableau, et même un tout petit peu d'OpenGL pour les graphiques etc...) et j'arrive bien à utiliser la plupart des fonctions VBA sauf justement les fonctions interne à Excel.

[gFZT82]

Bonjour,

La même en .Value ?

Code :

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

1
2
3
4
5
6
7
Sub form()
Dim atext As String
    atext = "$A2"
    [I2].Formula = "=" & "SUMPRODUCT($C$2:$C$5" & "," & " SIN(($D$2:$D$5 * " & atext & ") + RADIANS($E$2:$E$5)))"
    [I2].AutoFill Destination:=[I2:I8], Type:=xlFillCopy
    [I2:I8] = [I2:I8].Value
End Sub

Cordialement.

[Invité]

Ah oué, carrément , pourtant j'avais essayé il me semble et ça marchait pas au tout début
Puis je suis parti dans des modife pour tout gérer hors VBA (plage de cellule avec le texte en cellule pour éviter d'ouvrir l'éditeur VBA histoire d'être totalement tranquille) et puis ça à marché !

EDIT: Mais attention, il manque quand même la petite sécurité au niveau de l'option de calcul qu'il faut insérer entre :

Code :

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

[I2].AutoFill Destination:=[I2:I8], Type:=xlFillDefault

et

Code :

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

[I2:I8] = [I2:I8].Value

Sinon, ça pourrai ne pas marcher

En tout cas, merci bien

A+ et encore merci !

C'est assez incroyable, mais je n'ai vu aucune formule sur le net utilisant des constantes à l'intérieur des fonctions sumprod, ni même de fonction autre que des plages avec un signe égale après les plages. Tous les sites que j'ai vu parlaient toujours de plages 100% identiques en dimensions et sans fonctions spécifique à l'intérieur des plages.

Ça mériterai bien une contribution sur l'utilisation des sumproduct qui ont l'air difficile en VBA. Mais je ne l'a ferai pas, si quelqu'un veux en faire une, ça pourrai aider je pense, parce que ce genre de fonction est super galère en VBA. Les fonctions sinus par exemple ne prennent pas en charge les plages de données en VBA alors qu'il n'y a aucun problème en Excel.



EDIT:

Peut-être que cette forme te conviendra.

La premiere valeur attendue est un scalaire, les trois autres sont des vecteurs (de même longueur):

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
 
 
Public Function MyCalculous2(myDriver As Range, myConst_B As Range, myConst_C As Range, myConst_D As Range) As Double
    Dim i As Integer
    Dim Dbl_outPutrange As Double
    Dim pi As Double
 
    pi = 3.1416
    Dbl_outPutrange = 0
    For i = 1 To myConst_B.Count
        Dbl_outPutrange = Dbl_outPutrange + myConst_B(i) * Math.Sin(myConst_C(i) * myDriver.Value + myConst_D(i) * pi / 180)
    Next i
    MyCalculous2 = Dbl_outPutrange
End Function

Je viens de tester la fonction, et nickel, elle fonction bien. Mais elle est forcément limité avec un Sin et un radians, ce qui empêcherai de l'utiliser avec d'autres fonctions (Signe, Cos ou sans Radians par exemple). Mais elle fonctionne rapidement en tout cas. A mettre de coté donc.



RePS:
J'ai testé la Fonction SOMMEPROD avec le timer:

Test n°1
2000 valeurs de A
1000 valeurs de C,D,E
Timer = 1,08s ; 1,23 s ; 1,77 s ; 1,47 s pour les 4 essais


Test n°2
400 000 valeurs de A
8000 valeurs de C, D, E
Timer = 33,125 s

Questions:
Ça nous donne combien de multiplications et combien d'additions tous ça en excluant les fonctions RAD et SIN ?

(1+2) x 8000 x 400 000 = 9 600 000 000 Multiplications en 33s ? Ou je me trompe complètement ? Ça parait gros.

[Invité]

Bonjours,

Le sujet est résolu, j'ajoute simplement ici une comparaison du temps de calcul entre la fonction perso de SMBeccaria et la fonction Excel SOMMEPROD pour montrer le gain qu'il y a entre des boucles et les fonctions en Excel.

Test n°1: Fonction SOMMPROD // Fonction perso
A = 320 // 320

B = 8000 // 8000
C = 8000 // 8000
D = 8000 // 8000

Temps de calcul: 1,15 s à 1,8s // 33 s

------------------------------------------------

Test n°2: Fonction SOMMPROD
A = 6400 // 6400

B = 8000 // 8000
C = 8000 // 8000
D = 8000 // 8000

Temps de calcul: 24 s en .Value et 33 s en .Formula // 672 s

-----------------------------------------------

Le temps de calcul est divisé par 28 en utilisant un SOMMEPROD plutôt qu'en utilisant des boucles avec la fonction perso. Utiliser des tableau (travaille en mémoire vive) ne semble pas changer la donne pour la fonction perso.
Le temps de calcul en supprimant les lignes du code:

Code :

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

1
2
3
With Range(itext)
     .Value = .Value  'Transformation de Formule en Valeur
End With

présant dans ce code:

Code :

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

1
2
3
4
5
6
7
8
With Range(itext1)
     .Formula = "=" & "SUMPRODUCT(" & ctext & "," & " SIN((" & dtext & " * " & atext & ") + RADIANS(" & etext & ")))"
     .AutoFill Destination:=Range(itext), Type:=xlFillDefault
End With
ActiveSheet.Range(itext).Calculate  'Obligatoire : Pour les cas où le calcule auto n'est pas actif, avec Nom classeur actif
With Range(itext)
     .Value = .Value  'Transformation de Formule en Valeur
End With

augmente très bizarrement le temps de calcul !!!!
C'est très bizarre, et le " ActiveSheet.Range(itext).Calculate " doit y être pour quelquechose. Pourtant, il est placé avant le " Range(itext).Value = .Value "

Donc, je pense qu'on peut dire que la fonction "AutoFill" associé à un ".Value= .Value" est très efficace.

Voilà.

-------------------------------- Mise à jour --------------------------------------

EDIT: J'arrive enfaite à 11,8 s en remplaçant:

Code :

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

ActiveSheet.Range(itext).Calculate

par:

Code :

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

Calculate

Donc, le ratio passe à 672/11,8 = 57

L'application du "Autofill" couplé à "Calculate" et à ".Value = .Value" est 57 fois plus rapide !!! Et maintenant, la différence entre .Formula et .Formula + .Value est négligeable. Le .Value = .Value ne prends pas de temps de calcul.