Comment transformer le code d'une procédure sub en function ? (vba pour excell)

Signaler
Messages postés
42
Date d'inscription
mardi 10 janvier 2006
Statut
Membre
Dernière intervention
26 septembre 2006
-
Messages postés
17286
Date d'inscription
mercredi 2 janvier 2002
Statut
Modérateur
Dernière intervention
23 décembre 2019
-
Bonjour,
L'internaut jpleroisse m'a transmis ce code pour m'aider à additionner toutes les cellules d'une plage de taille variable. Comment faire pour le transformer en fonction ? (en dernier j'ai mis mon code pour infos, mais ça ne marche pas .)

voici le code d'origine qui marche trés bien comme sub:
Sub AddValeurF()
Dim i, cel, total


For i = 1 To range("f20:f1000").End(xlDown).Rows
If range("f20:f1000")(i) <> "" Then
range("f20:f1000")(i).Offset(1, 0).Activate
End If
For Each cel In range("f20:f1000")(i)
If cel.Value <> 0 Then
total = total + cel.Value
End If
Next cel
Next i
ActiveCell.Value = total
End Sub

voici ma transcription (je devrais dire "une de mes tentatives de transcription") en fonction , qui marche pas du tout . Pouvez vous m'aider à la corriger (mon livre "vba pour les nuls" est incomplet sur ce chapitre) ? Je voudrais pouvoir l'appeller depuis une formule dans ma feuille de calcul.
Function total(plage)
Dim i, cel


For i = 1 To range(plage).End(xlDown).Rows
If range(plage)(i) <> "" Then
range(plage)(i).Offset(1, 0).Activate
End If
For Each cel In range(plage)(i)
If cel.Value <> 0 Then
total = total + cel.Value
End If
Next cel
Next i
ActiveCell.Value = total
End Function

drine des iles

6 réponses

Messages postés
133
Date d'inscription
lundi 4 octobre 2004
Statut
Membre
Dernière intervention
14 octobre 2011
1
bonjour,

Function TOTAL(plage As Range)
Dim i, o
For Each o In plage
If IsNumeric(o.Value) Then i = i + o.Value
Next
TOTAL = i
End Function

Pour utiliser cette fonction dans une feuille de calcul si tu veux faire le total des cellules A2:B4
=TOTAL(A2:B4)

Si tu veux utiliser cette fonction depuis VBA

Sub test()
MsgBox TOTAL([A2:B4])
End Sub

Nota : La fonction doit être collée dans un module Standart (Module1...) et non dans un module de feuille.

A+
1
Merci

Quelques mots de remerciements seront grandement appréciés. Ajouter un commentaire

Codes Sources 216 internautes nous ont dit merci ce mois-ci

Messages postés
17286
Date d'inscription
mercredi 2 janvier 2002
Statut
Modérateur
Dernière intervention
23 décembre 2019
62
n'est-ce pas la meme chose que

=SOMME(A2:B4)

Renfield
Admin CodeS-SourceS - MVP Visual Basic
Messages postés
42
Date d'inscription
mardi 10 janvier 2006
Statut
Membre
Dernière intervention
26 septembre 2006

Merci pour ces réponses mais elles ne collent pas tout à fait à ma question.
Je dois pouvoir appeller cette fonction depuis une formule (donc pas ne msgbox ni d'appel de fonction depuis une procédure sub). De plus, le nombre de cellules comprises dans la plage à additionner n'est jamais le même , donc, une addition toute simple comme =somme(a2:b4) n'est pas possible.

pour être plus claire: ma plage peut faire (H20:H40) et le total s'écrire en H41 ou alors, elle peut faire (H20: H150) et le total s'écrire en H151 . Et c'est la fonction qui détermine ça toute seule ;

donc, quand ma formule me demande de remplirl'argument (plage), je met (h20:h1000) pour être sure que toutes les cellules qui contiennent une valeur seront balayées et enplus, je ne suis pas censée revenir dessus: donc si quelqu'un ajoute une ligne dans ma plage de valeur, le nouveaux total doit se calculer automatiquement (et s'inscrire automatiquement dans la bonne cellule).

le débogueur me dit que c'est un problème de référence circulaire???

drine des iles
Messages postés
17286
Date d'inscription
mercredi 2 janvier 2002
Statut
Modérateur
Dernière intervention
23 décembre 2019
62
j'ai rien compris a ton probleme....

si tu tapes ta formule dans une cellule donnée... le nombre de cellule a additionner est connu, non

explique moi le tout comme si j'avais 6 ans
Messages postés
42
Date d'inscription
mardi 10 janvier 2006
Statut
Membre
Dernière intervention
26 septembre 2006

Effectivement le nombre de cellule a additionner n'est pas connu.
Quand je tape ma formule de total en H30, elle va additionner toutes les cellules de H20 à H29. Le lendemain je vais avoir des données en plus donc, je vais insérer 2 lignes (par exemple) en plus: mon total va donc automatiquement se décaler en H32 et le calcul se fera sur la plage H20:H31.
Le surlendemain j'aurais peut-être 150 lignes en plus ou 10 en moins...
bref, la dimension de la plage n'est jamais la même d'un jour sur l'autre donc, il faut un code qui "balaye" toute la colonne, repère les cellules à additionner et inscrit le résultat en dessous de la dernière cellule remplie.

Ce code là je l'ai (voir mon premier message en haut de cette page), il marche trés bien. Mais il est sous forme de sub. Pour le rentrer dans ma cellule H30 (par exemple) je dois le transformer en fonction. Et c'est là mon problème. Si je tape la formule en H30, elle se retrouve parmis les cellules balyées par le code et excell l'intérprète comme une référence circulaire... donc ça marche plus!
Messages postés
17286
Date d'inscription
mercredi 2 janvier 2002
Statut
Modérateur
Dernière intervention
23 décembre 2019
62
j'ai fait le test....

dans une feuille excel, j'ai renseigné une vingtaine de lignes, avec tout un tas de chiffres.

j'ai tapé en D29, la formule =SOMME(D1:D28)

j'ai inséré une ligne en plein milieu, j'ai mis une valeur, et ma formule s'est bien mise a jour toute seule :

=SOMME(D1:D29)

Renfield
Admin CodeS-SourceS - MVP Visual Basic