Résultats d'un calcul faisant varier les paramètres

Le titre est difficile à trouver, j'aurais pu l'appeler "montecarlo", mais cela n'aurait pas été parlant pour beaucoup...

C'est un truc que je souhaite faire pratiquement depuis que je connais Excel :

Excel a 2 dimensions, (largeur/longueur), que faire si j'ai besoin de davantage ?

Plus précisément, si j'ai un calcul qui demande une table complète pour obtenir un seul résultat (genre, la moyenne de f(z) pour z variant de 0 à 100), et que mon calcul prend en compte quelques paramètres, comment faire pour obtenir le résultat lorsque les paramètres changent ?

La plupart du temps, je fais varier mes paramètres à la main, et je trouve ce que je cherchais, mais je me suis décidé à faire une petite macro pour automatiser cela :

un tableau prend pour entête la position des paramètres à changer et des résultats à récupérer

et pour valeurs la valeur des paramètres, et des endroits où l'on va copier les résultats.

Pour l'instant, je fais la différence entre paramètre et résultat selon que c'est un calcul ou non. (les paramètres sont donnés par une formule, la plupart du temps un modulo de la ligne)

Ma macro marche en l'état, mais elle est moche, et néanmoins, je compte bien la réutiliser (et la partager, ca peut être utile je pense), donc je serais preneur d'un certain nettoyage pour parcourir le tableau plus proprement. (par exemple, j'utilise un shift moche parce que je lis le numéro de colonne dans la feuille pour l'entête, qui diffère du numéro de colonne dans le tableau)

Si on pouvait faire un truc plus court et plus propre, on pourrait le partager dans la catégorie applications.

Au début, je voulais faire la macro en définissant le tableau comme étant la zone sélectionnée à la souris, mais je pense que faire un tableau est quand même plus propre.

    Dim Message
    Dim DataTable
    Dim Cellules
    Dim rw As Range
    Dim cel As Range
    Dim shift

    Message = "Message : "
    Table = "Montecarlo"
    Cellules = "Montecarlo[#Headers]"
    shift = Range(Cellules).Cells.Column - 1

    Application.ScreenUpdating = False

    For Each rw In Range(Table).Rows
        For Each cel In Range(Cellules)
            If Left(rw.Cells(1, cel.Column - shift).Formula, 1) = "=" Then
                Range(cel.Value) = rw.Cells(1, cel.Column - shift)
            Else
                rw.Cells(1, cel.Column - shift) = Range(cel.Value)
            End If

        Next cel

    Next rw

    Application.ScreenUpdating = True

Je vous joins un fichier en exemple, le calcul initial est tout con, (aucun intérêt ici) c'est ce calculer la position relative des 2 sommets du triangle pour que le périmètre du triangle soit le plus stable possible quand le troisième sommet parcours un arc de cercle, voir schéma :

Feuil1 calcule le périmètre du triangle selon les différents angles (gros calcul donc), et montecarlo teste les différentes position des sommets. Synthèse trace un joli graphique à la fin.

Sinon, autrement qu'un tableau, il y a peut-être quelque chose encore plus propre :

une simple fonction d'arité variable (c'est possible ?), avec en entrée :

• l'adresse de la cellule à copier
• des couples de :

* valeur à y mettre

... Du coup, on copie cette fonction dans les colonnes "résultat" du tableau.

Mais je ne suis pas sûr du tout que ce soit possible .. A vrai dire, je n'ai jamais fait de formule en vba.

edit : bon, ben, après test, ce n'est clairement pas possible avec des formules personnelles.

Cela me fait penser que ma macro a oublié un truc tout bête : sauvegarder les valeurs initiales des cellules pour les remettre à la fin.

Bon, puisque je sais qu'une autre solution n'est pas possible, et que je vois que mon message part un peu dans tous les sens, je peux simplifier la question :

-> comment parcourir proprement un tableau en VBA, ligne par ligne, puis colonne par colonne ?

Bonjour,

Pour te faire une idée, tu peux faire ce test :

Sub test()
Dim C As Range
Dim i As Integer
    For Each C In Range("Montecarlo")
        i = i + 1
        Reponse = MsgBox("Dans la boucle, la cellule n° " & i & " est positionnée en " & C.Address(0, 0), vbInformation + vbRetryCancel)
        If Reponse = vbCancel Then Exit For
    Next C
End Sub

A+

Wow, déjà, à chaud, j'adore cette fonction de message pour du debug, je crois que je vais la réutiliser souvent ! Merci !

Par contre, je ne vais pas parcourir le tableau cellule par cellule, ce n'est pas une façon très propre de parcourir un tableau à 2 dimensions.

Par contre, l'integer permet de parcourir la ligne d'entête sans utiliser mon shift moche, meme je ne trouve pas cela super joli non plus, hélas.

Edit : nouvelle version du code :

--> effectivement, c'est déjà plus simple, car je cesse de rechercher à chaque fois la position dans la colonne.

Sub Montecarlo()

    Dim rw As Range
    Dim cel As Range
    Dim i As Integer

    Application.ScreenUpdating = False

    For Each rw In Range("Montecarlo").Rows
        i = 0
        For Each cel In Range("Montecarlo[#Headers]")
            i = i + 1
            If Left(rw.Cells(1, i).Formula, 1) = "=" Then
                Range(cel.Value) = rw.Cells(1, i)
            Else
                rw.Cells(1, i) = Range(cel.Value)
            End If
        Next cel
    Next rw

    Application.ScreenUpdating = True

End Sub

Bon, maintenant, je m'aperçois que pour être vraiment propre, il faudrait que je boucle d'abords sur les paramètres, et que je boucle ensuite sur les valeurs à enregistrer ...

Je crois que la solution idéale passe donc nécessairement par des tableaux vba ... Je vais regarder comment ils fonctionnent.