Optimisation code VBA pour Excel

Bonjour Charles,
tu as essaye la formule de Stephane ?
Bonjour

Avec une formule.
Attention j'ai changé l'ordre de la colonne B
A B
-----------
115 | 6
101 | 7
203 | 8
210 | 9

en C1:Cx :
=SOMMEPROD($A$1:A1;DECALER($B$1;NB(B:B)-LIGNES($A$1:A1);;LIGNES($A$1:A1)

ça marche ?

Stéphane

Bonjour,

je cherche a optimiser le code VBA suivant. Il s'agit en fait d'une
formule VBA que j'utilise directement dans un tableur Excel. Mon
probleme est que je l'utilise beaucoup et que le modele met pour le
moment pres de 3min sur un gros PC a se calculer. Chaque petite
amelioration peut donc avoir un gros impact.

Le code est le suivant:

Public Function CX_TO_C1(A As Range, B As Range, C As Range,
compartment As Double) As Double
Dim nbrow, i, imax As Integer
nbrow = A.Rows.Count
imax = nbrow
If nbrow > 130 Then
imax = 130
End If
Dim temptotal As Double
temptotal = 0
For i = 1 To imax
If C.Rows(i).Value = compartment Then
temptotal = temptotal + A.Rows(i).Value * B.Rows(nbrow + 1
- i).Value
End If
Next i
CX_TO_C1 = temptotal / 100
End Function

Je ne pense pas qu'il y ait beaucoup a esperer des calculs car ils sont
relativement simple, mais en revanche je ne suis pas tout a fait sur
que ma syntaxe est parfaitement adaptee (ne faudrait il pas "acquerir"
les ranges dans un tableau au debut, ou autre?)

Je m'en remets a votre expertise
Je vous remercie d'avance
Charles

--
Pour m'ecrire directement :
jeanluc.laurent@free.fr
Tomorrow is another day

Bonjour,

A tout hasard, essayez le code suivant:

'***********
Public Function CX_TO_C1 _
(A As Range, B As Range, C As Range, compartment As Double) As Double
'---- Tels que, nbrow et i sont des variants ----
''' Dim nbrow, i, imax As Integer
'-----------------------------------------------
Dim nbrow As Integer
Dim i As Integer
Dim imax As Integer
nbrow = A.Rows.Count
imax = nbrow
If nbrow > 130 Then
imax = 130
End If
Dim temptotal As Double
temptotal = 0
'---- modifié ----
Dim varA As Variant
Dim varB As Variant
Dim varC As Variant
varA = A
varB = B
varC = C
For i = 1 To imax
If varC(i, 1) = compartment Then
temptotal = temptotal + varA(i, 1) * varB(nbrow + 1 - i, 1)
End If
Next i
'---- stop modif ----
CX_TO_C1 = temptotal / 100
End Function
'***********

Cordialement.
--
PMO
Patrick Morange

Bonjour,

je cherche a optimiser le code VBA suivant. Il s'agit en fait d'une
formule VBA que j'utilise directement dans un tableur Excel. Mon
probleme est que je l'utilise beaucoup et que le modele met pour le
moment pres de 3min sur un gros PC a se calculer. Chaque petite
amelioration peut donc avoir un gros impact.

Le code est le suivant:

Public Function CX_TO_C1(A As Range, B As Range, C As Range,
compartment As Double) As Double
Dim nbrow, i, imax As Integer
nbrow = A.Rows.Count
imax = nbrow
If nbrow > 130 Then
imax = 130
End If
Dim temptotal As Double
temptotal = 0
For i = 1 To imax
If C.Rows(i).Value = compartment Then
temptotal = temptotal + A.Rows(i).Value * B.Rows(nbrow + 1
- i).Value
End If
Next i
CX_TO_C1 = temptotal / 100
End Function

Je ne pense pas qu'il y ait beaucoup a esperer des calculs car ils sont
relativement simple, mais en revanche je ne suis pas tout a fait sur
que ma syntaxe est parfaitement adaptee (ne faudrait il pas "acquerir"
les ranges dans un tableau au debut, ou autre?)

Je m'en remets a votre expertise
Je vous remercie d'avance
Charles

Fantastique! vous m'avez quasiment divise le temps total de calcul par
2!

Si d'autre ameliorations vous viennent a l'esprit, n'hesitez pas

Merci encore
Charles

Bonjour,

Je viens de m'apercevoir de l'existence de votre message précédent
et prends donc connaissance de votre problème.
On peut faire beaucoup mieux.
Voici donc une meilleure solution avec la fonction ci-dessous.

'**********
Function PMO(Donnees As Variant)
Dim T
Dim T2
Dim nbLig&
Dim i&
Dim j&
Dim k&
Dim x#
T = Donnees
nbLig& = UBound(T, 1)
ReDim T2(1 To nbLig&, 1 To 1)
For i& = 1 To nbLig&
x# = 0
If i& = 1 Then
x# = T(i&, 1) * T(i&, 2)
Else
k& = i& + 1
For j& = 1 To i&
x# = x# + T(k& - 1, 1) * T(j&, 2)
k& = k& - 1
Next j&
End If
T2(i&, 1) = x#
Next i&
PMO = T2
End Function
'**********

FONCTIONNEMENT:
Supposons la plage A9:B268 renseignée par des données telles que
(je reprends votre exemple)
A9 115 B9 9
A10 101 B10 8
A11 203 B11 7
A12 210 B12 6
etc… A vous de remplir jusqu'à la ligne 268 (par exemple)
Vous voulez obtenir les résultats en C9:C268 (le nombre de lignes
doit être égal au nombre de lignes de la plage de données)

1) Sélectionnez C9:C268
2) Dans la barre de formule, tapez =PMO(A9:B268)
qui fait référence à la plage des données.
3) IMPORTANT entérinez la formule en tant que formule matricielle.
Pour ce faire, MAINTENEZ Ctrl+Majuscule et appuyez sur Entrée
4) En principe les résultats doivent s'afficher en C9:C268 et on doit lire
dans la barre de formule {=PMO(A9:B268)}
Les accolades indiquent que la fonction est matricielle.

Cela marche-t'il chez vous ?

Cordialement.

--
PMO
Patrick Morange

Fantastique! vous m'avez quasiment divise le temps total de calcul par
2!

Si d'autre ameliorations vous viennent a l'esprit, n'hesitez pas

Merci encore
Charles

Effectivement. J'ai un peu modifie la syntaxe pour l'adapter a mon
Mammouth, mais cela ameliore significativement le temps de calcul.

Originellement cela mettait 3 min. En utilisant votre premiere methode,
on descend a 1 min et en utilisant votre dernier code, 10sec...

Merci infiniment pour votre aide... En plus j'ai appris a utiliser de
nouveaux outils!

Petite question, lorsque vous declarez mais ne dimensionez pas une
variable, elle est consideree comme variant par defaut? Et lorsque l'on
met comme variable d'entree de la fonction variant au lieu de range,
cela copie d'un seul coup tt les donnees dans VBA? (c'est important,
car dans ce cas la colonne A et B ne dependent pas du resultat de C
mais cela pourrait arriver (genre C2 depend de A2 et B2, qui eux meme
dependent de C1)

Merci encore
Charles

Bonjour,

Pour être plus explicite
1) Function PMO(Donnees As Variant) As Variant
la valeur de retour est un variant qui est à considérer comme
un tableau bidimensionné.
2) Dim T As Variant et Dim T2 As Variant
sont des variants qui peuvent contenir pratiquement
n'importe quoi. En l'occurrence ils contiennent des
tableaux bidimensionnés.
3) Si le fait de sélectionner plus de 2 colonnes pour
la plage de données ne pose pas de problème à
l'argument Donnes As Variant de la fonction,
il n'en est pas de même pour l'algorithme de calcul.
Il faudra en inventer un autre.
A première vue (???), il faudrait utiliser une première
fonction pour renseigner la colonne C et une autre
qui prendrait en compte les colonnes A B C et
qui afficherait les résultats en D.

Enfin, et pour ma propre gouverne, quelle est l'utilité
de ce genre de calcul ? En clair, vous l'utilisez à
quelle fin ? (si ce n'est pas un secret d'état)

Cordialement.
--
PMO
Patrick Morange

Ce n'est pas un secret d'etat: ce sont des emprunts qui sont origines
chaque mois et qui ensuite s'amortisent. Il faut donc pour calculer
l'etat du portefeuille avoir le nombre d'emprunts origine a telle date
et l'amortisation depuis. Il y a d'autres moyens plus simples de faire
une amortisation, mais disons que je fais apres des calculs assez
complexes et que pour des raisons de simplicite, je prefere jouer avec
une feuille excel qui me dit ce qui arrive au cours de sa vie a un euro
emprunte a t=0 et ensuite le multiplier par le montant effectivement
origine, que de travailler directement sur le montant origine.

Pour en revenir a votre solution, cela marcherait rellement? prenons
l'exemple suivant:
A B C
-------------------------------------
10 1 X
11 Á+B1 X
etc...
on aurait besoin de A et B pour calculer C. Mais B depend lui aussi de
C. Et donc si on utilise un array, et qu'on nourrit la fonction qui
donne C avec les valeurs au debut du calcul des arrays A et B, ne
casse-t-on pas l'ordre des calculs d'Excel? (calculer entierement C a
partir de A et B, au lieu de calculer C1 a partir de A1 et B1 puis B2 a
partir de C1 et B1, puis enfin C2 a partir de A2 et B2)

Cordialement
Charles

Bonjour,

J'ai fait le test suivant
1) A1=1 B1=1
2) Sélection de C1:C5 et formule matricielle {=PMO(A1:B5)}
j'obtiens 1 en C1 et 0 en C2:C5
3) je fais en A2 ¡*2 et je recopie cette formule en A3:A5
j'obtiens en A1:A5 1 2 4 8 16 et la même chose en C1:C5
4) je fais en B2 ¡+B1 et je recopie cette formule en B3:B5
j'obtiens en B1:B5 1 2 4 8 16
5) la fonction PMO recalcule les résultats en fonction des formules
définies en A1:B5

N'est-ce pas la réponse à votre interrogation ?

Cordialement.
--
PMO
Patrick Morange

Ce n'est pas un secret d'etat: ce sont des emprunts qui sont origines
chaque mois et qui ensuite s'amortisent. Il faut donc pour calculer
l'etat du portefeuille avoir le nombre d'emprunts origine a telle date
et l'amortisation depuis. Il y a d'autres moyens plus simples de faire
une amortisation, mais disons que je fais apres des calculs assez
complexes et que pour des raisons de simplicite, je prefere jouer avec
une feuille excel qui me dit ce qui arrive au cours de sa vie a un euro
emprunte a t=0 et ensuite le multiplier par le montant effectivement
origine, que de travailler directement sur le montant origine.

Pour en revenir a votre solution, cela marcherait rellement? prenons
l'exemple suivant:
A B C
-------------------------------------
10 1 X
11 Á+B1 X
etc...
on aurait besoin de A et B pour calculer C. Mais B depend lui aussi de
C. Et donc si on utilise un array, et qu'on nourrit la fonction qui
donne C avec les valeurs au debut du calcul des arrays A et B, ne
casse-t-on pas l'ordre des calculs d'Excel? (calculer entierement C a
partir de A et B, au lieu de calculer C1 a partir de A1 et B1 puis B2 a
partir de C1 et B1, puis enfin C2 a partir de A2 et B2)

Cordialement
Charles

Pas exactement. En fait ce qui serait interessant serait de faire le
meme cas de figure mais quand A ou B depend d'un element de C

Lorsque vous modifiez un element de A ou B et qu'aucun ne depend de C,
et que vous recalculez la spreadsheet, il est normal que C se comporte
normalement.

Ce qui serait interessant, c'est si B2 par exemple depend de C1. Et que
C2 depend de B2. Le calcul de C2 se fera t il sur a partir de la valeur
de C1 du tout debut ou Excel aura-t-il l'intelligence de calculer
d'abord C1, ensuite de mettre a jour B2 et alors seulement de calculer
C2, ou, ce que je pense, va-t-il directement calculer la colonne C d'un
seul bloc a partir des valeurs des colonnes A et B d'un seul bloc, sans
prendre en compte que les colonnes A et B dependent de C1 et donc que
B2 aura pris une nouvelle valeur une fois que l'on aura calcule C1.

Il n'est pas evident d'etre clair en expliquant cela

Charles