PB avec fonction VBA lors ajout et suppression feuille

[willy]

Bonjour,
Je souhaite faire la somme d'une plage et ajouter une valeur renvoyée par une fonction placée dans un module qui dépend également de la plage.
Ex: A1 = 1
A2=2
A3=CP4
A4=CP10
A5=3
A6=SOMME(A1:A5)+demicp(1) résultat A6=20

Voici le code de la fonction demicp(col) qui additionne les valeurs présentes à la suite de CP:

Function demicp(col)
demicp = 0
Dim tabcar(8)
For c = 0 To 8
tabcar(c) = ""
Next c
For l = 1 To 5
valeur = Worksheets(ActiveSheet.Name).Range("a1").Cells(l, col).Value
If valeur > 0 And valeur < 24 Then
GoTo line1
End If
heure = ""
car = 0
'placement des caractères dans un tableau
Do While StrComp(Worksheets(ActiveSheet.Name).Range("a1").Cells(l, col).Characters(car + 1, 1).Caption, "")
tabcar(car) = Worksheets(ActiveSheet.Name).Range("a1").Cells(l, col).Characters(car + 1, 1).Caption
car = car + 1
Loop
'traitement des valeurs avec CP
If tabcar(0) = "C" Then
If tabcar(2) = vide Then
GoTo line1
End If
For j = 2 To car
heure = heure & tabcar(j)
Next j
demicp = CDec(heure) + demicp
End If
'initialisation du tableau
For c = 0 To 8
tabcar(c) = ""
Next c
line1:
Next l
End Function

Cette fonction est opérationnelle quand on modifie les valeurs de la plage A1:A5.
Le soucis survient lorsque que je veux copier cette feuille ou supprimer une feuille du classeur qui utilise la fonction demicp, la valeur retournée par la fonction prend la valeur de la dernier feuille du classeur et les autres feuilles qui utilisent la fonction ont un mauvais résultat dans la cellule A6.

J'aurais voulu savoir comment faire pour résoudre ce pb lors de la copie ou suppression de feuille.
Existe-t-il une une manière de déclarer une fonction qui ne change pas de valeur lors la copie ou la suppression de feuille?

Merci d'avance
Willy

 

[willy]

Ne trouvant pas de solution, J'ai opté pour une autre méthode que l'utilisation d'une fonction :

j'applique les 2 formules suivante à toutes les cellules puis je fais la somme des deux colonnes issues de ces formules:

pour traiter la forme CP 4:
=CNUM(SI(ET(STXT(D14;1;2)="CP";NBCAR(D14)>2);STXT(D14;3;6);0))

pour traiter la forme 4 CP
=CNUM(SI(ET(DROITE(D14)="P";NBCAR(D14)>2);SUBSTITUE(D14;"CP";"");0))

Si quelqu'un a une solution au pb avec la fonction je suis très intéressé car la solution que j'ai trouvé ressemble à du bricolage mais elle a le mérite de résoudre le pb lors d'ajout ou de la suppression de feuille.

Merci

Willy