Comment forcer le calcul d'une cellule en VBA

[JNP]

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?"
Je suis obligé de doublecliquer dans chaque cellule en sélectionnant la plage pour qu'à la validation, le calcul s'éffectue enfin...
Merci d'avance de vos solution pour forcer le calcul des cellules via VBA...

 

[Staple1600]

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

Bonjour


Essaye :Cells(36, 3).Formula
à la place de

Cells(36, 3).Value


(en fait remplacer .Value par .Formula dans ton code)

 

[pierrejean]

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

bonjour JNP

A tester

en fin de macro ecriture des formules mettre

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

 

[Staple1600]

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

Bonjour pierrejean


Je verrais bien quelque chose du genre

Dim i As Long
For i = 3 To 15
Cells(36,i).Formula="=heuresarécupérer(ET LA JE SECHE)
Next

 

[Luki]

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 !

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!

 

[pierrejean]

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

re

Salut luki

@ Staple

quelque chose comme (non testé)

For n = 1 To 12
Cells(36, n + 2).Formula = "=heuresarécupérer(" & Chr(66 + n) & "4:" & Chr(66 + n) & "34)"
Next n

 

[Staple1600]

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

Re


Merci pierrejean (testé ca fonctionne)

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

 

[pierrejean]

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

Re

bravo Staple

variante pour francophile

Cells(36, 15).Formulalocal = [B]"=SOMME(C36:N36)"[/B]

on s'amuse comme des petits fous !!!

 

[Luki]

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

re Staple, Pj

Sans rire 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 !

A +

 

[Staple1600]

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

Bonsoir

pardon Luki, j'ai testé en ayant volatisé la fonction

Mais avant j'avais testé sans mon volatile sans plumes

et ca fonctionnait aussi

 

[pierrejean]

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]

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

Re bonjour,
J'ai testé toutes vos solutions... et ça ne marche pas
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

 

[Luki]

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

Re,

Quand vous vous y mettez tous les deux, il faut vous retenir , 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

 

[Staple1600]

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

Re


Public Function HeuresARécupérer(Heures)

Et ca marche

edit: Luki, tu as loupé JNP à 2 minutes près

 

[Luki]

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...

Private Sub UserForm_Terminate()
Calculate
ActiveSheet.Visible = False
Sheets("Planning").Activate
Unload QuitterFeuille
End Sub

A tester chez toi jnp