Comment forcer le calcul d'une cellule en VBA

JNP

XLDnaute Barbatruc
Bonjour :),
J'ai crée une fonction qui calcule le nombre d'heure sup à récupérer en fonction de la couleur de fond de la cellule:
Function HeuresARécupérer(Heures)
Dim NombreCellule As Integer, I As Integer
NombreCellule = Heures.Count
For I = 1 To NombreCellule
Select Case Heures(I).Interior.ColorIndex
Case 35
HeuresARécupérer = HeuresARécupérer + Heures(I).Value
End Select
Next I
End Function

Cette formule, je l'injecte dans une feuille via VBA pour chaque mois ainsi que la somme avec les instructions suivantes:
Cells(36, 3).Value = "=heuresarécupérer(C4:C34)"
Cells(36, 4).Value = "=heuresarécupérer(D4: D34)"
Cells(36, 5).Value = "=heuresarécupérer(E4:E34)"
Cells(36, 6).Value = "=heuresarécupérer(F4:F34)"
Cells(36, 7).Value = "=heuresarécupérer(G4:G34)"
Cells(36, 8).Value = "=heuresarécupérer(H4:H34)"
Cells(36, 9).Value = "=heuresarécupérer(I4:I34)"
Cells(36, 10).Value = "=heuresarécupérer(J4:J34)"
Cells(36, 11).Value = "=heuresarécupérer(K4:K34)"
Cells(36, 12).Value = "=heuresarécupérer(L4:L34)"
Cells(36, 13).Value = "=heuresarécupérer(M4:M34)"
Cells(36, 14).Value = "=heuresarécupérer(N4:N34)"
Cells(36, 15).Value = "=SOMME(C36:N36)"

Le problème est que le calcul ne s'effectue pas dans la feuille. Malgré la présence d'heures, la valeur reste à "0" et la somme m'affiche "#NOM?" :confused:
Je suis obligé de doublecliquer dans chaque cellule en sélectionnant la plage pour qu'à la validation, le calcul s'éffectue enfin...:mad:
Merci d'avance de vos solution pour forcer le calcul des cellules via VBA... :)
 

pierrejean

XLDnaute Barbatruc
Re : Comment forcer le calcul d'une cellule en VBA

bonjour JNP

A tester

en fin de macro ecriture des formules mettre

Code:
..........
[FONT=Courier New][COLOR=#c0c0c0]Cells(36, 15).Value = "=SOMME(C36:N36)"[/COLOR][/FONT]
Calculate
 
edit:
 
oui oui Staple tu as raison !!!
 

Luki

XLDnaute Accro
Re : Comment forcer le calcul d'une cellule en VBA

bonjour à tous

Application.volatile à intégrer au début de la fonction pour qu'elle se recalcule automatiquement, sinon c'est F9 et tendinite assurée !:p

Function HeuresARécupérer(Heures)
Application.volatile
Dim NombreCellule As Integer, I As Integer
NombreCellule = Heures.Count
For I = 1 To NombreCellule
Select Case Heures(I).Interior.ColorIndex
Case 35
HeuresARécupérer = HeuresARécupérer + Heures(I).Value
End Select
Next I
End Function

Edit :
Sinon, dans ta macro, tu peux ecrire : Calculate, qui équivaut à "calculer maintenant". Valable dans le cas où tu as vraiment beaucoup de formules qui pourraient ralentir ton code.

Re Edit: Pierrejean l'avait déjà dit! je radote un peu moi!
 
Dernière édition:

Staple1600

XLDnaute Barbatruc
Re : Comment forcer le calcul d'une cellule en VBA

Re


Merci pierrejean (testé ca fonctionne)
Code:
Sub test()
Dim n As Long
For n = 1 To 12
Cells(36, n + 2).Formula = "=heuresarécupérer(" & Chr(66 + n) & "4:" & Chr(66 + n) & "34)"
Next n
Cells(36, 15).Formula = [B]"=SUM(C36:N36)"[/B]
End Sub

Et il faut mettre SUM à la place de SOMME

edit: Moi je dis BRAVO pierrejean, à toi le mérite
 
Dernière édition:

Luki

XLDnaute Accro
Re : Comment forcer le calcul d'une cellule en VBA

re Staple, Pj

Sans rire :p vous ne voulez vraiment pas de mon application.volatile?

Paske, sauf erreur (et sans avoir testé dans le cas de jnp) c'est là que le bât blesse... mais si vous voulez coder pour le fun, c'est respectable ! :D

A + :)
 

pierrejean

XLDnaute Barbatruc
Re : Comment forcer le calcul d'une cellule en VBA

bonsoir

@ luki

oui c'est vrai avec un volatile ca va largement aussi bien

pardonne-nous de ne pas avoir insisté sur ce point

d'ici une dizaine de poste on dira:

" et le canard etait toujours vivant "

pour rappeler de bons souvenirs de ceux qui comme moi on connu Robert Lamoureux
 

JNP

XLDnaute Barbatruc
Re : Comment forcer le calcul d'une cellule en VBA

Re bonjour,
J'ai testé toutes vos solutions... et ça ne marche pas :mad:
Le problème vient à priori que le recalcul n'a lieu qu'à la modification d'une cellule en manuel, et non pendant l'envoi de VBA...
Je vous joint le fichier test si ça vous permet de mieux comprendre mon problème. La macro à lancer est "EntréeHeuresSup" en ayant sélectioné une cellule dans la colonne d'un présent, et en face d'une date dans la 1° colonne. La feuille est crée automatiquement (Il faut la supprimer après l'avoir affichée quand on veux faire un nouvel essai).

Merci d'avance.

PS: Très bien "volatile", je ne connaissais pas
 

Pièces jointes

  • Heures sup.zip
    30.7 KB · Affichages: 120
  • Heures sup.zip
    30.7 KB · Affichages: 104
  • Heures sup.zip
    30.7 KB · Affichages: 115
Dernière édition:

Luki

XLDnaute Accro
Re : Comment forcer le calcul d'une cellule en VBA

Re,

Quand vous vous y mettez tous les deux, il faut vous retenir :D, sinon vous êtes capables de reinventer xl juste pour une ligne de code!

Toujours pas de nouvelles de jnp, espérons qu'il ne soit pas atteint de grippe aviaire....

Bye
 

Luki

XLDnaute Accro
Re : Comment forcer le calcul d'une cellule en VBA

re,

il semble qu'il y ait un soucis lors du masquage de la feuille, public ou pas.
En fait j'ai déjà eu des trucs bizarres malgré le canard laqué dans des feuilles où il y a beaucoup de fonctions perso.

La feuille n'est pas recalculée je pense, car elle est masquée avant la fin de la macro. J'ai testé un "calculate" dans le userform_terminate et ça semble marcher...

Code:
Private Sub UserForm_Terminate()
Calculate
ActiveSheet.Visible = False
Sheets("Planning").Activate
Unload QuitterFeuille
End Sub
A tester chez toi jnp
 

Statistiques des forums

Discussions
311 720
Messages
2 081 910
Membres
101 837
dernier inscrit
Ugo