Discussion :

[Julien S]

Bonjour à tous,

Voilà j'aimerai tout simplement savoir comment utiliser les fameuses accolades pour les formules matricielles d'EXCEL dans du code VBA.

Je souhaiterais changer cette formule qui me permet de calculer l'évolution de la performance cumulée, de façon à remplacer la function "SUM" qui n'est pas mathématiquement bonne pour calculer une perf cumulée en une function PRODUCT :

Code :

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

1
2
3
 
 
Range("F" & h) = Application.WorksheetFunction.Sum(Range("D6", "D" & h )

h est un compteur d'une boucle

le "Range("D6", "D" & h ) parcours donc une colonne en partant de la ligne 6 jusqu'à la ligne courante, et ce qu'il y a dans la colonne D c'est une performance journalière en %

Voilà j'aimerais donc le changer par quelque chose du style :

Code :

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

1
2
3
 
 
Range("F" & h) = application.WorksheetFunction.Product(1+Range("D6", "D" & h)) - 1

et y ajouter les accolades afin de faire fonctionner cette "ARRAY entered function "

Merci beaucoup pour votre aide

[Menhir]

Pourquoi vouloir faire une fonction matricielle quand on peut disposer de boucles qui permettent de contrôler les itérations de manière bien plus efficace et sûre ?

[joe.levrai]

Bonjour,

pour du matriciel, j'évite de calculer en interne ou alors j'utilise des méthodes VBA sans fonction (mode fainéant j'avoue)
sinon j'injecte la formule dans la cellule et ensuite je remplace la formule par sa valeur

Code :

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

1
2
3
4
With Range("F" & h)
    .FormulaArray = ' ta formule en anglais et sans les accolades
    .Value = .Value
End With

[Julien S]

Re-bonjour,

merci pour vos réponses. Alors après test de ma part il s'avère que je n'ai pas la bonne formule Excel pour mon problème. Voici mon souci :

Je souhaite toujours calculer l'évolution d'une performance cumulée, à l'aide d'une série de performance comme suit :

+1.92 %
-0.12%
-1.5%
+2%
+1.3%
.... etc


La colonne où je colle les performances ci-dessus avec mon code est du format pourcentage, et j'aimerai faire un calcul pour avoir la performance cumulée jusqu'a l'observation en cours et la coller dans la colonne d'a côté

Sachant que pour une performance cumulée avec n observations, la formule mathématique ressemble à :

(1+Vo)*(1+V1)*(1+V2) ....*(1+Vn) -1

( Dites moi si cette formule vous parait correcte )

Je suis donc à la recherche de toute solution ergonomique dans le code pour faire ce calcul le plus simplement possible

Merci à vous

[Menhir]

En supposant que te données sont en colonne A et commencent ligne 1

Code :

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

1
2
3
4
5
6
7
8
Dim Lig as Long
Dim Prod as Double
 
Prod = 1
For Lig=1 to Cells(Rows.Count,1).End(xlUp).Row
   Prod = Prod * (1 + Cells(Lig,1).Value)
   Cells(Lig,2).Value = Prod - 1
Next Lig

C'est moins élégant qu'une formule matricielle mais c'est simple et efficace.

[Julien S]

Merci beaucoup menhir

j'adapte à mon cas et marque le sujet comme résolu !

bonne journée à tous

[Pierre Fauconnier]

Salut.

Perso, je préfère les formules matricielles aux boucles en VBA (avant tout, on est utilisateur Excel, pas programmeur connaissant les boucles en VBA )

Pour saisir une formule matricielle dans la cellule A1, tu pourras utiliser range("a1").formulaarray = ...

[Menhir]

Perso, je préfère les formules matricielles aux boucles en VBA (avant tout, on est utilisateur Excel, pas programmeur connaissant les boucles en VBA )

Je parlais pour une utilisation dans du code VBA.
Bien sûr, si une fonction matricielle permet de se passer de macro, c'est toujours bon à prendre.

[Pierre Fauconnier]

Mais même en VBA, je préfère une approche Excel (plus tournée utilisateur) qu'une approche VBA (plus tournée programmeur), et j'utiliserai plus volontiers une matricielle q'une boucle, ne serait-ce que pour la concision du code à écrire, la formule matricielle pouvant être transcrite d'Excel vers VBA très facilement via la fenêtre d'exécution.

Ceci dit, il serait intéressant de tester, sur de grandes plages de calcul, ce qui va le plus vite, et on pourrait avoir trois approches:
matricielle en VBA ;
boucles en VBA ;
boucles sur tableau en VBA.

Je serais curieux de voir ce qui va aller le plus vite, mais là, j'ai pas mal de taf et donc je ne me lancerai pas dans la comparaison aujourd'hui...