Discussion :

[Gordon72]

Bonsoir tout le monde,

Je sollicite votre aide car je peine à trouver une solution à mon problème. J'ai dans mes calculs besoin de faire des sommes répétitives. Pour donner un exemple au hasard, disons somme(2*i²) pour i variant de 1 à 50.
Pour le moment je me contente de faire des boucles for avant mon calcul dont j'implémente le résultat. Seulement je trouve que ce n'est pas une solution à long terme car cela me fait du code très gros avec pleins de variables à créer pour les boucles.

1) Je me demande donc si il y aurait une fonction somme dans VBA ? Bien évidement appropriée au calcul mathématiques et non pour sommer des cellules.
2) N'ayant pas trouvé de réponse à ma question 1 ci-dessus, j'ai commencé à coder un morceau mais je bloque sur le passage de l'expression mathématiques en guise de variable fonctionnelle. Ci-joint mon début de code :

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
Function somme(expression As Double, min as Integer, max as Integer)
 
Dim total As Double
 
total = 0
For i = min To max
 
    total = total + expression
 
Next
 
somme = total
 
End Function
 
Sub test()
Dim i As Integer
 
MsgBox somme(2 * i^2, 1, 50)
 
End Sub

Je sollicite donc votre aide en gros pour savoir si la fonction Sum de VBA permet de faire ce que je souhaite et au cas échéant si quelqu’un pourrait bien m'aider à compléter mon bout de code. Je pense à une piste mais je n'arrive pas à la mettre en pratique : pourquoi ne pas passer l'expression en string puis la convertir en autre chose dans la fonction ? Mais pas sûr...


En tout cas merci par avance pour votre aide future.

Cdlt,
Gordon.

[OBO29]

Salut,

J'ai déjà au à réfléchir sur ce type d'application et voici ce que j'avais mis en oeuvre :

Le programme écrit les données d'entrées de "Expression" dans 2 (ou+) cellules du classeur.
Une cellule du classeur contient le calcul à réaliser (Formula).
Récupération du résultat par le programme

Exemple :

somme(2*i²) pour i variant de 1 à 50.

Le programme (boucle) écrit 1 en A1.
Sur la feuille, une formule en A2 dit : = 2*A1*A1
Le programme récupère le résultat en A2 puis somme...

A+

[joe.levrai]

Bonjour,

difficile d'avoir un avis tranché et une proposition adéquate, puisqu'on ne connait pas toutes les formules répétitives que tu vas vouloir simplifier
dans ce genre d'objectif, et pour éviter les usines à gaz impossibles à maintenir ou faire évoluer, il faut dans un premier temps :

- lister les combinaisons de formules possibles. J'entend par combinaisons les "motifs" récurrents qui apparaissent dans tes formules
- transformer les progressions en leur correspondance globale, c'est à dire casser la boucle de progression (tes boucle For/Next) en utilisant la formule qui agrège tout en une fois

c'est à dire que tu dois impérativement travailler la partie purement mathématique avant d'attaquer la programme

si on prend ton exemple : calculer "2 * i^2" pour i allant de 1 à 4

on constate qu'on va devoir se farcir : (2 * 1^2) + (2 * 2^2) + (2 * 3^2) + (2 * 4^2)

on va déjà extraire le multiplicateur et on obtient : 2* (1^2 + 2^2 + 3^2 + 4^2)

on arrive donc à une banale multiplication entre une constante (2) et une somme de carrés consécutifs en partant de 1

Ca tombe bien, il y a une formule pour ça : 1² + 2² + 3² + .... + n² = (1/3 * n^3) + (1/2 * n²) + (1/6*n)

Ce pattern de formule est donc agrégable, comme tel :

Code :

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

1
2
3
4
5
6
7
8
Function sommeCarreConsecutifs(max)
    sommeCarreConsecutifs = 2 * (((1 / 3) * max ^ 3) + ((1 / 2) * max ^ 2) + ((1 / 6) * max))
End Function
 
Sub test()
Dim i As Double
    MsgBox sommeCarreConsecutifs(4)
End Sub

Comme tu commences à le deviner, ton souhait demande beaucoup d'études préalables

[Jouad]

Bonsoir,
Il me semble que cela peut se résoudre ainsi:

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
Sub CalculeDoubleCarré()
    MsgBox SommeDoubleCarré(1, 50)
End Sub
 
Private Function SommeDoubleCarré(iMin As Integer, iMax As Integer) As Double
    Dim dTotal As Double
    Dim i As Integer
    For i = iMin To iMax
        dTotal = dTotal + DoubleCarré(i)
    Next
    SommeDoubleCarré = dTotal
End Function
 
Private Function DoubleCarré(i As Integer) As Double
    DoubleCarré = CDbl(2 * i ^ 2)
End Function

[Gordon72]

Bonsoir à vous et merci pour vos réponses.

Alors justement je veux créer une vraie fonction somme qui soit capable de me faire les sommes de toute fonction de i. La partie mathématiques n'est pas un soucis, je donnais l'exemple de 2*i^2 comme ça pour que l'on parle de la même somme...

Coder une fonction somme pour une formule de i connue ça je sais faire, mais coder une fonction somme capable de sommer n'importe quelle fonction de i pour n'importe quel intervalle de i là je sèche...

Après si vous voulez je vous mets un screen de ce que je cherche à sommer exactement mais c'est plutôt chaud à digérer.

Merci de votre aide en tout cas.

[unparia]

Bonjour
Je vais personnellement me contenter de répondre ainsi : ce n'est pas la variable expression, qu'il convient de passer en paramètre de la fonction, mais son résultat.

J'ajoute à l'intention du seul demander que la variable tremplin total est inutile. Il suffit de travailler directement avec la variable somme, puisque la fonction somme est un numérique. Je déplore à ce propos que cette fonction n'ait pas été typée !

[Gordon72]

ce n'est pas la variable expression, qu'il convient de passer en paramètre de la fonction, mais son résultat.

Bonsoir Unparia et merci de votre intérêt. Par contre désolé mais je ne vois pas bien où vous voulez en venir. Comment la fonction peut-elle sommer une expression si on ne lui indique pas au minimum f(i) et [iMin;iMax]?

[joe.levrai]

Si le but n'était pas de modifier la structure des formules et/ou éviter les boucles, et qu'en plus il ne s'agit que de sommer progressivement une même expression composé d'une (ou deux) parties "dures" et une partie "variable".

voici un exemple appliqué lui-même sur ton exemple :

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
13
Function Somme(Expression As String, Debut As Long, Fin As Long) As Double
Dim ExpressionEnCours As String
    For i = Debut To Fin
        ExpressionEnCours = Replace(Expression, "#i", i)
        Somme = Somme + Evaluate(ExpressionEnCours)
    Next i
End Function
 
Sub test()
Dim MaFormule As String
    MaFormule = "(4/2^#i) - Sin(45)"
Debug.Print Somme("2 * " & MaFormule & " ^ 2", 1, 10)
End Sub

On peut éviter bien sûr la variable MaFormule

prise en compte d'une seule partie "ajustable" tant dans l'appel (donc les paramètres de la fonction ... Variant et éventuellement Paramarray) que dans le traitement qui obligera à un peu plus de rigueur et de structure dans la méthode d'identification de la position pour faire le remplacement. Sans oublier les éventuelles boucles multiples (et potentiellement des croisements récursifs possibles)


Bien sûr, car nous n'avons toujours pas un aperçu assez large du nombre et du type d'expressions que tu veux "passer", ainsi que de l'aspect général des formules (c'est à dire l'expression "globale" que tu vas envoyer à ta formule)

[Gordon72]

Ah ben Joe.levrai j'en étais à peu près à ton stade mais je cherchais le moyen de convertir du texte en expression... Voici où j'en étais (bien que ça ne marche pas ^^) :

Code :

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

1
2
3
4
5
6
7
8
9
Function somme(maFonction As String, iMin As Integer, iMax As Integer)
 
For i = iMin To iMax
 
    somme = somme + Replace(maFonction, """", "")
 
Next
 
End Function

J'aurais peut-être dû formuler ma demande par quelque chose du genre "Comment dire à VBA que l'on veut passer du texte en formule ?"

Sinon voici ce que je veux calculer (bon j'avais prévenu que c'était lourd hein ^^)

[joe.levrai]

La complexité de la formule n'est pas le principal obstacle, mais est-ce que Excel est le bon outil pour celles-là ?
D'où VBA va-t-il puiser ces formules rédigées de la façon qu'elles lui soient intelligibles ? Tu dois toutes les écrire à la main ?

Passé ce frein potentiel, il faut ensuite calculer le nombre maximal de "variables" que tu peux rencontrer sur une seule expression de somme progressive.
Ca ne sert à rien de transmettre la formule complète ... étant décomposée en sous-parties matérialisées par des sommes progressives, tu additionnes simplement les appels de la fonction somme

Et ensuite ... tout est dans l'avant dernier paragraphe de mon message précédant ... les informations entre parenthèses ne sont pas les plus superficielles à ce titre.

[Jouad]

Bonsoir Joe.Levrai,
Il y a un petit souci avec votre code au niveau de la ligne 5:

Code :

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

Somme = Somme + Evaluate(ExpressionEnCours)

Somme désigne ici la fonction (ou plus exactement la valeur renvoyée par la fonction) et en même temps une variable qui s'incrémente dans la boucle. D'où le message d’ambiguïté à l'exécution de la fonction.
Par ailleurs, on ne peut mettre la fonction Somme à droite du signe "=" qu'accompagnée de ses paramètres, ce qui va poser des problèmes de récursivité. On est donc obligés d'utiliser dans la boucle For... Next une variable de type double précision dont la valeur sera affectée en fin de fonction à Somme.
Pour le reste, vos réponses et celles des autres membres du forum sont très instructives.

[joe.levrai]

Bonjour,

je suis très surpris de tes retour Jouad.

je n'ai aucun message d'erreur à l'exécution de mon côté, tu parles de l'erreur de compilation "nom ambigu" ?
as-tu testé la procédure, et arrêté quelques pas pour voir comment évolue la valeur de Somme ?
c'est la première fois que je lis cet impératif de passer par une variable intermédiaire, qui transmettra "à la fin" le résultat à la fonction. Je l'ai donc toujours conceptualisé comme d'ordre esthétique/ergonomique pour le développeur.

attention si tu as testé avec d'autres combinaisons "variables", y'a aucune sécurité dans la formule pour garantir que Evaluate va renvoyer un numérique, j'ai vraiment été à l'essentiel.

[Gordon72]

Bon ben voilà Joe.levrai tu as pallié à mon problème avec ton "Evaluate" que je ne connaissais pas. Mais c'est vraiment un truc surpuissant !!! Si j'avais su j'aurais demandé directement si il existait une fonction de ce genre.

Donc pour reprendre voici le bout de code final qui marche du tonnerre :

Code :

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

1
2
3
4
5
Function sumFunc(Expression As String, Debut As Integer, Fin As Integer) As Double
    For i = Debut To Fin
        sumFunc = sumFunc + Evaluate(Replace(Expression, "i", i))
    Next i
End Function

J'ai juste remplacé "somme" par "sumFunc" car la fonction "SOMME" existe déjà dans Excel. J'ai également repassé les "Debut" et "Fin" en integer. Comme ça je peux finalement virer toutes ces boucles et faire mes calculs directement en implémentant ma super fonction "sumFunc" directement dans mes égalités xD

Merci beaucoup.

[Jouad]

joe.levrai
Désolé pour mon message intempestif.
L'erreur est mienne.
J'avais copié votre code dans un module où il y a avait déjà une fonction Somme et j'ai récolté un message d’ambiguïté.
Après copie de votre code dans un module vierge, cela a fonctionné parfaitement.
Toutes mes excuses pour le désagrément.

[unparia]

Par ailleurs, on ne peut mettre la fonction Somme à droite du signe "=" qu'accompagnée de ses paramètres

est inexact au sein d'une fonction non récursive (la tienne n'en est pas une. Elle procède à des itérations et n'est pas récursive)

[Gordon72]

est-ce que Excel est le bon outil pour celles-là ?

- C'est justement dans le but de coder un.xla pour accélérer mes macros plutôt que d'utiliser les outils du boulot que je fais ça donc oui Excel est le parfait outils.

Tu dois toutes les écrire à la main ?

- Oui j'ai déjà tout tapé à la main avec des boucles For que je vais remplacer par la super fonction !!!

tu additionnes simplement les appels de la fonction somme

- C'était exactement le but recherché quand j'ai posté ma demande d'aide ; remplacer toutes ces boucles For par une seule et unique fonction directement implantable dans mes égalités

Bon ben je passe en "résolu" et merci encore.

[joe.levrai]

La boucle est bouclée

tout est dans l'avant dernier paragraphe de mon message précédant ... les informations entre parenthèses ne sont pas les plus superficielles à ce titre

prise en compte d'une seule partie "ajustable" tant dans l'appel (donc les paramètres de la fonction ... Variant et éventuellement Paramarray) que dans le traitement qui obligera à un peu plus de rigueur et de structure dans la méthode d'identification de la position pour faire le remplacement. Sans oublier les éventuelles boucles multiples (et potentiellement des croisements récursifs possibles)

Heureusement, je pensais à tort que c'était le cas, il n'a pas eu besoin d'incréments distincts pour chaque variables de sa formule, et ça simplifie grandement les choses

[Invité]

Bonjour,

j'ai un erreur ma j'ai plus le temps pour l'instant, alors si ça vous intéresse!

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
Private Function MySomme(Expression As String, Debut As Long, Fin As Long) As Variant   
 MySomme = Replace("$" & Replace(Replace(String(Fin - Debut + 1, "$"), "$", "+(" & Expression & ")"), "#i", "(Evaluate(g))"), "$+", "")
    Debug.Print MySomme
End Function
Function g(Optional X As Boolean = False) As Integer
Static v As Integer
If X = True Then v = 1: Exit Function
v = v + 1
 g = v
End Function
Private Sub test()
Dim MaFormule As String
g True
    MaFormule = "(4/2^#i) - Sin(45)"
Debug.Print Evaluate(MySomme("2 * " & MaFormule & " ^ 2", 1, 2))
End Sub

Edite:

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
Function g(Optional X As Boolean = False) As Integer
Static v As Integer
If X = True Then v = 1: Exit Function
v = v + 1
 g = v
End Function
Private Sub test()
Dim MaFormule As String, Debut As Long, Fin As Long
g True: Debut = 1: Fin = 3
    MaFormule = "(4/2^#i) - Sin(45)"
Debug.Print Evaluate(Replace("$" & Replace(Replace(String(Fin - Debut + 1, "$"), "$", "+(2 * " & MaFormule & ")"), "#i", "g()"), "$+", ""))
End Sub