Je voudrais utiliser la fonction SOMME.SI.ENS pour faire la somme des 2
colonnes où mon critère s'applique... mais je n'ai pas réussi à additionner
les colonnes...
Dans cet exemple je voulais additionner les données des colonnes F et G :
=SOMME.SI.ENS(Ecritures!F2:G2460;Ecritures!$C$2:$C$2460;"=7*")
mais il pourrait être intéressant également d'additionner des colonnes qui
ne sont pas voisines.
Mais avec Evaluate, tu exécutes la fonction Excel, donc tu nas de gain que dans le cas où tu n'as qu'une partie de la feuille à recalculer.
Du gain par rapport à des formules Excel ? ou du gain par rapport à une macro purement VBA sans utilisation de fonction Excel ? Par rapport à une boucle VBA la différence de rapidité est en tout cas flagrante. Pour ce qui est des formules Excel, je cherche à créer une fonction du style Débit(numéro de cpte) pour mes collègues car ils ne sauront pas utiliser le Somme.Si.Prod. De plus, via un ancien logiciel, nous avons déjà des classeurs avec des formules nommées "Débit" cela me permettra de reprendre ces classeurs et d'y ajouter la nouvelle fonction "Débit" avec les mêmes arguments et là cela pourra refonctionner directement.
@+ HD
Mais avec Evaluate, tu exécutes la fonction Excel, donc tu nas de gain que
dans le cas où tu n'as qu'une partie de la feuille à recalculer.
Du gain par rapport à des formules Excel ? ou du gain par rapport à une
macro purement VBA sans utilisation de fonction Excel ? Par rapport à une
boucle VBA la différence de rapidité est en tout cas flagrante. Pour ce qui
est des formules Excel, je cherche à créer une fonction du style
Débit(numéro de cpte) pour mes collègues car ils ne sauront pas utiliser le
Somme.Si.Prod. De plus, via un ancien logiciel, nous avons déjà des
classeurs avec des formules nommées "Débit" cela me permettra de reprendre
ces classeurs et d'y ajouter la nouvelle fonction "Débit" avec les mêmes
arguments et là cela pourra refonctionner directement.
Mais avec Evaluate, tu exécutes la fonction Excel, donc tu nas de gain que dans le cas où tu n'as qu'une partie de la feuille à recalculer.
Du gain par rapport à des formules Excel ? ou du gain par rapport à une macro purement VBA sans utilisation de fonction Excel ? Par rapport à une boucle VBA la différence de rapidité est en tout cas flagrante. Pour ce qui est des formules Excel, je cherche à créer une fonction du style Débit(numéro de cpte) pour mes collègues car ils ne sauront pas utiliser le Somme.Si.Prod. De plus, via un ancien logiciel, nous avons déjà des classeurs avec des formules nommées "Débit" cela me permettra de reprendre ces classeurs et d'y ajouter la nouvelle fonction "Débit" avec les mêmes arguments et là cela pourra refonctionner directement.
@+ HD
MichD
Je n'ai jamais testé leur temps d'exécution à chacune et s'il y a une différence notable ce serait surtout sur de grandes plages. Cependant c'est sûrement plus rapide qu'une boucle pour chaque cellule de la plage!
Je n'ai jamais testé leur temps d'exécution à chacune
et s'il y a une différence notable ce serait surtout sur
de grandes plages. Cependant c'est sûrement plus rapide
qu'une boucle pour chaque cellule de la plage!
Je n'ai jamais testé leur temps d'exécution à chacune et s'il y a une différence notable ce serait surtout sur de grandes plages. Cependant c'est sûrement plus rapide qu'une boucle pour chaque cellule de la plage!
Cependant c'est sûrement plus rapide qu'une boucle pour chaque cellule de la plage!
Nous sommes d'accord lol D'ailleurs, il me semble que c'est déjà vous MichD qui m'aviez conseillé d'utiliser les fonctions natives d'Excel via Evaluate en VBA. La différence d'exécution est très importante.
En tout cas, un grand MERCI à vous tous.
@+ HD
Cependant c'est sûrement plus rapide
qu'une boucle pour chaque cellule de la plage!
Nous sommes d'accord lol
D'ailleurs, il me semble que c'est déjà vous MichD qui m'aviez conseillé
d'utiliser les fonctions natives d'Excel via Evaluate en VBA. La différence
d'exécution est très importante.
Cependant c'est sûrement plus rapide qu'une boucle pour chaque cellule de la plage!
Nous sommes d'accord lol D'ailleurs, il me semble que c'est déjà vous MichD qui m'aviez conseillé d'utiliser les fonctions natives d'Excel via Evaluate en VBA. La différence d'exécution est très importante.
En tout cas, un grand MERCI à vous tous.
@+ HD
HD
Ouille... je coince encore... mais cette fois si sur les dates...
J'ai voulu ajouter une option sur les dates... mais j'obtiens alors un #VALEUR!
Function Credit(cpt As String, Optional DateMin As Date, Optional DateMax As Date) As Double Dim PlgSomme As String Dim PlgCrit As String Dim Crit As String Dim PlgCritDate As String Dim Formule As String
With Worksheets("Ecritures") derlig = .Range("C65536").End(xlUp).Row PlgSomme = .Name & "!" & .Range("G2:G" & derlig).Address PlgCrit = .Name & "!" & .Range("C2:C" & derlig).Address PlgCritDate = .Name & "!" & .Range("A2:A" & derlig).Address End With Crit = """=" & cpt & "*""" Formule = "=SUMIFS(" & PlgSomme & "," & PlgCrit & "," & Crit If DateMin <> "" Then Formule = Formule & "," & PlgCritDate & "," & """>=" & CStr(DateMin) & """" Formule = Formule & ")" MsgBox Formule Credit = Evaluate(Formule) End Function
Pour voir d'où vient le problème j'ai voulu afficher la variable "Formule" mais elle ne s'affiche pas...
@+ HD
Ouille... je coince encore... mais cette fois si sur les dates...
J'ai voulu ajouter une option sur les dates... mais j'obtiens alors un
#VALEUR!
Function Credit(cpt As String, Optional DateMin As Date, Optional DateMax As
Date) As Double
Dim PlgSomme As String
Dim PlgCrit As String
Dim Crit As String
Dim PlgCritDate As String
Dim Formule As String
With Worksheets("Ecritures")
derlig = .Range("C65536").End(xlUp).Row
PlgSomme = .Name & "!" & .Range("G2:G" & derlig).Address
PlgCrit = .Name & "!" & .Range("C2:C" & derlig).Address
PlgCritDate = .Name & "!" & .Range("A2:A" & derlig).Address
End With
Crit = """=" & cpt & "*"""
Formule = "=SUMIFS(" & PlgSomme & "," & PlgCrit & "," & Crit
If DateMin <> "" Then Formule = Formule & "," & PlgCritDate & "," &
""">=" & CStr(DateMin) & """"
Formule = Formule & ")"
MsgBox Formule
Credit = Evaluate(Formule)
End Function
Pour voir d'où vient le problème j'ai voulu afficher la variable "Formule"
mais elle ne s'affiche pas...
Ouille... je coince encore... mais cette fois si sur les dates...
J'ai voulu ajouter une option sur les dates... mais j'obtiens alors un #VALEUR!
Function Credit(cpt As String, Optional DateMin As Date, Optional DateMax As Date) As Double Dim PlgSomme As String Dim PlgCrit As String Dim Crit As String Dim PlgCritDate As String Dim Formule As String
With Worksheets("Ecritures") derlig = .Range("C65536").End(xlUp).Row PlgSomme = .Name & "!" & .Range("G2:G" & derlig).Address PlgCrit = .Name & "!" & .Range("C2:C" & derlig).Address PlgCritDate = .Name & "!" & .Range("A2:A" & derlig).Address End With Crit = """=" & cpt & "*""" Formule = "=SUMIFS(" & PlgSomme & "," & PlgCrit & "," & Crit If DateMin <> "" Then Formule = Formule & "," & PlgCritDate & "," & """>=" & CStr(DateMin) & """" Formule = Formule & ")" MsgBox Formule Credit = Evaluate(Formule) End Function
Pour voir d'où vient le problème j'ai voulu afficher la variable "Formule" mais elle ne s'affiche pas...
@+ HD
MichD
Transforme cette procédure en fonction
Attention à la double déclaration des variables!
'-------------------------------------- Sub test() Dim PlgSomme As String Dim PlgCrit As String Dim PlgDate As String Dim DateCrit As String Dim Crit As String Dim Formule As String
If DateCrit <> "" Then Formule = "=SUMIFS(" & PlgSomme & "," & PlgCrit & "," & _ Crit & "," & PlgDate & "," & DateCrit & ")" Else Formule = "=SUMIFS(" & PlgSomme & "," & PlgCrit & "," & Crit & ")" End If
MsgBox Evaluate(Formule)
End Sub '--------------------------------------
MichD --------------------------------------------------------------- "HD" a écrit dans le message de groupe de discussion : l84v7f$1fee$
Ouille... je coince encore... mais cette fois si sur les dates...
J'ai voulu ajouter une option sur les dates... mais j'obtiens alors un #VALEUR!
Function Credit(cpt As String, Optional DateMin As Date, Optional DateMax As Date) As Double Dim PlgSomme As String Dim PlgCrit As String Dim Crit As String Dim PlgCritDate As String Dim Formule As String
With Worksheets("Ecritures") derlig = .Range("C65536").End(xlUp).Row PlgSomme = .Name & "!" & .Range("G2:G" & derlig).Address PlgCrit = .Name & "!" & .Range("C2:C" & derlig).Address PlgCritDate = .Name & "!" & .Range("A2:A" & derlig).Address End With Crit = """=" & cpt & "*""" Formule = "=SUMIFS(" & PlgSomme & "," & PlgCrit & "," & Crit If DateMin <> "" Then Formule = Formule & "," & PlgCritDate & "," & """>=" & CStr(DateMin) & """" Formule = Formule & ")" MsgBox Formule Credit = Evaluate(Formule) End Function
Pour voir d'où vient le problème j'ai voulu afficher la variable "Formule" mais elle ne s'affiche pas...
@+ HD
Transforme cette procédure en fonction
Attention à la double déclaration des variables!
'--------------------------------------
Sub test()
Dim PlgSomme As String
Dim PlgCrit As String
Dim PlgDate As String
Dim DateCrit As String
Dim Crit As String
Dim Formule As String
If DateCrit <> "" Then
Formule = "=SUMIFS(" & PlgSomme & "," & PlgCrit & "," & _
Crit & "," & PlgDate & "," & DateCrit & ")"
Else
Formule = "=SUMIFS(" & PlgSomme & "," & PlgCrit & "," & Crit & ")"
End If
MsgBox Evaluate(Formule)
End Sub
'--------------------------------------
MichD
---------------------------------------------------------------
"HD" a écrit dans le message de groupe de discussion :
l84v7f$1fee$1@saria.nerim.net...
Ouille... je coince encore... mais cette fois si sur les dates...
J'ai voulu ajouter une option sur les dates... mais j'obtiens alors un
#VALEUR!
Function Credit(cpt As String, Optional DateMin As Date, Optional DateMax As
Date) As Double
Dim PlgSomme As String
Dim PlgCrit As String
Dim Crit As String
Dim PlgCritDate As String
Dim Formule As String
With Worksheets("Ecritures")
derlig = .Range("C65536").End(xlUp).Row
PlgSomme = .Name & "!" & .Range("G2:G" & derlig).Address
PlgCrit = .Name & "!" & .Range("C2:C" & derlig).Address
PlgCritDate = .Name & "!" & .Range("A2:A" & derlig).Address
End With
Crit = """=" & cpt & "*"""
Formule = "=SUMIFS(" & PlgSomme & "," & PlgCrit & "," & Crit
If DateMin <> "" Then Formule = Formule & "," & PlgCritDate & "," &
""">=" & CStr(DateMin) & """"
Formule = Formule & ")"
MsgBox Formule
Credit = Evaluate(Formule)
End Function
Pour voir d'où vient le problème j'ai voulu afficher la variable "Formule"
mais elle ne s'affiche pas...
'-------------------------------------- Sub test() Dim PlgSomme As String Dim PlgCrit As String Dim PlgDate As String Dim DateCrit As String Dim Crit As String Dim Formule As String
If DateCrit <> "" Then Formule = "=SUMIFS(" & PlgSomme & "," & PlgCrit & "," & _ Crit & "," & PlgDate & "," & DateCrit & ")" Else Formule = "=SUMIFS(" & PlgSomme & "," & PlgCrit & "," & Crit & ")" End If
MsgBox Evaluate(Formule)
End Sub '--------------------------------------
MichD --------------------------------------------------------------- "HD" a écrit dans le message de groupe de discussion : l84v7f$1fee$
Ouille... je coince encore... mais cette fois si sur les dates...
J'ai voulu ajouter une option sur les dates... mais j'obtiens alors un #VALEUR!
Function Credit(cpt As String, Optional DateMin As Date, Optional DateMax As Date) As Double Dim PlgSomme As String Dim PlgCrit As String Dim Crit As String Dim PlgCritDate As String Dim Formule As String
With Worksheets("Ecritures") derlig = .Range("C65536").End(xlUp).Row PlgSomme = .Name & "!" & .Range("G2:G" & derlig).Address PlgCrit = .Name & "!" & .Range("C2:C" & derlig).Address PlgCritDate = .Name & "!" & .Range("A2:A" & derlig).Address End With Crit = """=" & cpt & "*""" Formule = "=SUMIFS(" & PlgSomme & "," & PlgCrit & "," & Crit If DateMin <> "" Then Formule = Formule & "," & PlgCritDate & "," & """>=" & CStr(DateMin) & """" Formule = Formule & ")" MsgBox Formule Credit = Evaluate(Formule) End Function
Pour voir d'où vient le problème j'ai voulu afficher la variable "Formule" mais elle ne s'affiche pas...
@+ HD
DanielCo
Mais avec Evaluate, tu exécutes la fonction Excel, donc tu nas de gain que dans le cas où tu n'as qu'une partie de la feuille à recalculer.
Du gain par rapport à des formules Excel ? ou du gain par rapport à une macro purement VBA sans utilisation de fonction Excel ? Par rapport à une boucle VBA la différence de rapidité est en tout cas flagrante. Pour ce qui est des formules Excel, je cherche à créer une fonction du style Débit(numéro de cpte) pour mes collègues car ils ne sauront pas utiliser le Somme.Si.Prod. De plus, via un ancien logiciel, nous avons déjà des classeurs avec des formules nommées "Débit" cela me permettra de reprendre ces classeurs et d'y ajouter la nouvelle fonction "Débit" avec les mêmes arguments et là cela pourra refonctionner directement.
@+ HD
Pour une formule, ce qui est le plus rapide, c'est la formule à base de fonctions natives Excel. Pour une question de commodité d'emploi, la fonction VBA est presque aussi rapide, et plus facile à manier. Dans les deux cas, tu seras pénalisé si tu utilises de grandes plages et / ou beaucoup de ces formules. Dans certains cas, s'il esst possible de ne pas tout recalculer (par exemple, si seule une portion de ligne doit être recalculée, il est avantageux de remplacer la formule par sa valeur et de ne recalculer par macro que les cellules impactées. Daniel
Mais avec Evaluate, tu exécutes la fonction Excel, donc tu nas de gain que
dans le cas où tu n'as qu'une partie de la feuille à recalculer.
Du gain par rapport à des formules Excel ? ou du gain par rapport à une macro
purement VBA sans utilisation de fonction Excel ? Par rapport à une boucle
VBA la différence de rapidité est en tout cas flagrante. Pour ce qui est des
formules Excel, je cherche à créer une fonction du style Débit(numéro de
cpte) pour mes collègues car ils ne sauront pas utiliser le Somme.Si.Prod. De
plus, via un ancien logiciel, nous avons déjà des classeurs avec des formules
nommées "Débit" cela me permettra de reprendre ces classeurs et d'y ajouter
la nouvelle fonction "Débit" avec les mêmes arguments et là cela pourra
refonctionner directement.
@+
HD
Pour une formule, ce qui est le plus rapide, c'est la formule à base de
fonctions natives Excel. Pour une question de commodité d'emploi, la
fonction VBA est presque aussi rapide, et plus facile à manier. Dans
les deux cas, tu seras pénalisé si tu utilises de grandes plages et /
ou beaucoup de ces formules. Dans certains cas, s'il esst possible de
ne pas tout recalculer (par exemple, si seule une portion de ligne doit
être recalculée, il est avantageux de remplacer la formule par sa
valeur et de ne recalculer par macro que les cellules impactées.
Daniel
Mais avec Evaluate, tu exécutes la fonction Excel, donc tu nas de gain que dans le cas où tu n'as qu'une partie de la feuille à recalculer.
Du gain par rapport à des formules Excel ? ou du gain par rapport à une macro purement VBA sans utilisation de fonction Excel ? Par rapport à une boucle VBA la différence de rapidité est en tout cas flagrante. Pour ce qui est des formules Excel, je cherche à créer une fonction du style Débit(numéro de cpte) pour mes collègues car ils ne sauront pas utiliser le Somme.Si.Prod. De plus, via un ancien logiciel, nous avons déjà des classeurs avec des formules nommées "Débit" cela me permettra de reprendre ces classeurs et d'y ajouter la nouvelle fonction "Débit" avec les mêmes arguments et là cela pourra refonctionner directement.
@+ HD
Pour une formule, ce qui est le plus rapide, c'est la formule à base de fonctions natives Excel. Pour une question de commodité d'emploi, la fonction VBA est presque aussi rapide, et plus facile à manier. Dans les deux cas, tu seras pénalisé si tu utilises de grandes plages et / ou beaucoup de ces formules. Dans certains cas, s'il esst possible de ne pas tout recalculer (par exemple, si seule une portion de ligne doit être recalculée, il est avantageux de remplacer la formule par sa valeur et de ne recalculer par macro que les cellules impactées. Daniel
MichD
Une façon d'écrire la fonction personnalisée à copier dans un module STANDARD.
À partir d'une procédure, pour appeler la fonction :
'------------------------------------------------ Sub test2() Dim LaDate As Variant ' Laisser ce "type" pour cette variable LaDate = CDate("09/12/2013")
'Les paramètres de la fonction personnalisée '1 - Nom de la feuille où sont les données '2 - Lettre de la colonne à additionner '3 - Lettre de la colonne servant de critère '4 - Critère : cela peut être une date ou une chaine de caractère débutant par le chiffre que l'on inscrit
End Sub '------------------------------------------------
Si on veut appeler cette fonction à partir d'une cellule : Voici la syntaxe : A3 est l'adresse d'une cellule contenant la date critère Excel doit reconnaitre le contenu de cette cellule comme étant une date. On ne peut pas inscrire : "09/12/2013" (trop long à expliquer pourquoi)
=Credit("Ecritures"; "C"; "A"; A3) OU =Credit("Ecritures"; "C"; "D"; 7)
'------------------------------------------------ Function Credit(NomFeuille As String, _ ColonneAdd As String, ColonneCritere As String, _ Crit As Variant, Optional DateMin As Date, _ Optional DateMax As Date) As Double
Dim DerLig As Long Dim PlgSomme As String Dim PlgCrit As String Dim Formule As String
With Worksheets(NomFeuille) DerLig = .Cells(65536, ColonneAdd).End(xlUp).Row PlgSomme = .Name & "!" & .Range(.Cells(1, ColonneAdd), _ .Cells(DerLig, ColonneAdd)).Address PlgCrit = .Name & "!" & .Range(.Cells(1, ColonneCritere), _ .Cells(DerLig, ColonneCritere)).Address End With
If IsDate(Crit) Then Crit = """>" & CLng(Crit) & _ """" Else: Crit = """=" & CStr(Crit) & "*"""
Formule = "=SUMIFS(" & PlgSomme & "," & PlgCrit & "," & Crit & ")" Credit = Evaluate(Formule) End Function '------------------------------------------------
Une façon d'écrire la fonction personnalisée à copier
dans un module STANDARD.
À partir d'une procédure, pour appeler la fonction :
'------------------------------------------------
Sub test2()
Dim LaDate As Variant ' Laisser ce "type" pour cette variable
LaDate = CDate("09/12/2013")
'Les paramètres de la fonction personnalisée
'1 - Nom de la feuille où sont les données
'2 - Lettre de la colonne à additionner
'3 - Lettre de la colonne servant de critère
'4 - Critère : cela peut être une date ou une chaine de
caractère débutant par le chiffre que l'on inscrit
End Sub
'------------------------------------------------
Si on veut appeler cette fonction à partir d'une cellule :
Voici la syntaxe :
A3 est l'adresse d'une cellule contenant la date critère
Excel doit reconnaitre le contenu de cette cellule comme étant une date.
On ne peut pas inscrire : "09/12/2013" (trop long à expliquer pourquoi)
=Credit("Ecritures"; "C"; "A"; A3)
OU
=Credit("Ecritures"; "C"; "D"; 7)
'------------------------------------------------
Function Credit(NomFeuille As String, _
ColonneAdd As String, ColonneCritere As String, _
Crit As Variant, Optional DateMin As Date, _
Optional DateMax As Date) As Double
Dim DerLig As Long
Dim PlgSomme As String
Dim PlgCrit As String
Dim Formule As String
With Worksheets(NomFeuille)
DerLig = .Cells(65536, ColonneAdd).End(xlUp).Row
PlgSomme = .Name & "!" & .Range(.Cells(1, ColonneAdd), _
.Cells(DerLig, ColonneAdd)).Address
PlgCrit = .Name & "!" & .Range(.Cells(1, ColonneCritere), _
.Cells(DerLig, ColonneCritere)).Address
End With
If IsDate(Crit) Then Crit = """>" & CLng(Crit) & _
"""" Else: Crit = """=" & CStr(Crit) & "*"""
Formule = "=SUMIFS(" & PlgSomme & "," & PlgCrit & "," & Crit & ")"
Credit = Evaluate(Formule)
End Function
'------------------------------------------------
Une façon d'écrire la fonction personnalisée à copier dans un module STANDARD.
À partir d'une procédure, pour appeler la fonction :
'------------------------------------------------ Sub test2() Dim LaDate As Variant ' Laisser ce "type" pour cette variable LaDate = CDate("09/12/2013")
'Les paramètres de la fonction personnalisée '1 - Nom de la feuille où sont les données '2 - Lettre de la colonne à additionner '3 - Lettre de la colonne servant de critère '4 - Critère : cela peut être une date ou une chaine de caractère débutant par le chiffre que l'on inscrit
End Sub '------------------------------------------------
Si on veut appeler cette fonction à partir d'une cellule : Voici la syntaxe : A3 est l'adresse d'une cellule contenant la date critère Excel doit reconnaitre le contenu de cette cellule comme étant une date. On ne peut pas inscrire : "09/12/2013" (trop long à expliquer pourquoi)
=Credit("Ecritures"; "C"; "A"; A3) OU =Credit("Ecritures"; "C"; "D"; 7)
'------------------------------------------------ Function Credit(NomFeuille As String, _ ColonneAdd As String, ColonneCritere As String, _ Crit As Variant, Optional DateMin As Date, _ Optional DateMax As Date) As Double
Dim DerLig As Long Dim PlgSomme As String Dim PlgCrit As String Dim Formule As String
With Worksheets(NomFeuille) DerLig = .Cells(65536, ColonneAdd).End(xlUp).Row PlgSomme = .Name & "!" & .Range(.Cells(1, ColonneAdd), _ .Cells(DerLig, ColonneAdd)).Address PlgCrit = .Name & "!" & .Range(.Cells(1, ColonneCritere), _ .Cells(DerLig, ColonneCritere)).Address End With
If IsDate(Crit) Then Crit = """>" & CLng(Crit) & _ """" Else: Crit = """=" & CStr(Crit) & "*"""
Formule = "=SUMIFS(" & PlgSomme & "," & PlgCrit & "," & Crit & ")" Credit = Evaluate(Formule) End Function '------------------------------------------------