J'ai un classeur assez grand qui prend un très long temps pour calculer. Il s'agissait d'un défi difficile de le faire calculer tout le chemin, car Excel est tellement désireux d'abandonner silencieusement le calcul si vous le regardez.
Pour aider à atténuer le problème, j'ai créé un code VBA pour initier le calcul, qui est initié par une forme, et le résultat est qu'il n'est pas tout aussi facile d'interrompre le processus de calcul, mais il est toujours possible. (Je peux facilement faire cela en cliquant sur la fermeture X sur le formulaire, mais j'imagine qu'il existe d'autres manières)
Plutôt que de prendre plus d'étapes pour essayer de rendre plus difficile l'interruption de calcul, je voudrais que le code détecte si le calcul est terminé, il peut donc notifier à l'utilisateur plutôt que de forger de manière aveuglément dans le reste des étapes. dans mon code. Jusqu'à présent, je ne trouve aucun moyen de le faire.
J'ai vu des références à application.Calculttate, mais la valeur est XLDone après avoir interrompre le calcul, même si j'interrompt le calcul après quelques secondes (cela prend normalement environ une heure).
Je ne peux pas penser à un moyen de le faire en vérifiant la valeur des cellules, car je ne sais pas lequel est calculé en dernier. Je vois qu'il y a un moyen de marquer des cellules comme «sale», mais je n'ai pas été capable de trouver un moyen de vérifier la saleté d'une cellule. Et je ne sais pas si c'est même le bon chemin à prendre, car je devrais probablement vérifier toutes les cellules dans chaque feuille.
L'acte de calcul de l'interruption ne pose pas d'erreur, alors mon erreur n'est pas déclenchée.
Y a-t-il quelque chose qui me manque? Des idées?
Des idées?
8 Réponses :
Je ne l'ai jamais utilisé mais je pense que cela pourrait fonctionner pour empêcher le calcul d'être interrompu.
application.calcultinterrupkey = xlnokey
Cela peut aider à prévenir les interruptions accidentelles, mais ne me laissera toujours pas savoir si le calcul a été interrompu ou non.
Vous avez raison. C'est plus une fin tourne autour du problème. (En supposant que ça marche.)
Je pense que l'astuce que vous devez implémenter (si votre candidature fonctionne dans Excel 2007 ou plus tard) est de gérer cela avec l'événement Application.Aftercalculate, qui est soulevé après que les deux calculs soient terminés et Il n'y a pas de requêtes exceptionnelles.
Si vous n'avez jamais travaillé avec des événements dans VBA avant, il existe une bonne vue d'ensemble de Cpearson .com .
Je pense que j'entends que vous ayez besoin d'un moyen de surveiller si chaque étape dans les calculs étant exécutée a été exécutée.
supposant que vous n'êtes pas intéressé par la réingénierie du classeur pour utiliser des méthodes plus faciles à utiliser. Suivi que les calculs de feuille de calcul (tels que des calculs volatils dans les tables VBA ou pivot), cela peut fonctionner pour vous: p>
dans VB, vous pouvez utiliser .Nablecalculation strong> et et . strong> pour définir une feuille de calcul entière comme "sale" (besoin de calcul), puis recalculer. La différence clé entre cela et votre processus actuel est que nous allons effectuer ces actions une feuille de calcul à la fois em> en mode manuel. En initiant les calculs une feuille de calcul à une époque de VBA, vous pourrez effectuer des actions intermédiaires supplémentaires pouvant être utilisées pour suivre jusqu'où vous avez eu le processus de calcul. P> Veuillez noter que cette approche suppose. Une structure de classeur assez linéaire de telle sorte que votre classeur produira les résultats corrects si nous recalculons d'abord la feuille1, puis une feuille2, une feuille3, etc., dans n'importe quel ordre souhaité. Si vos dépendances de formule sont plus "spaghettis" que linéaires, cela ne fonctionnera probablement pas pour vous. Il suppose également que vous travaillez dans Excel 2000 ou plus tard. P> Par exemple, vous pouvez écrire une routine VBA qui accomplit les étapes suivantes.
Vous devrez connaître vos dépendances afin de savoir quels calculs doivent venir avant d'autres et commencer avec la feuille de calcul dans un état "propre" où aucun calcul n'est actuellement en attente. P> Étape 1 Strong>: Définissez la feuille active sur la première feuille de calcul où la recalculition est nécessaire p> .Calculate
End With
Merci pour la suggestion, mais mon expérience jusqu'à présent a été que si le calcul est interrompu, tout code étant exécuté passe immédiatement à partir de l'appel .calculé, exactement comme s'il s'était terminé normalement. Donc, le résultat du compteur ne serait pas différent de savoir si le calcul a été interrompu ou non. Je dois encore enquêter sur l'approche axée sur les événements mentionnée dans une autre réponse, cependant.
Les tableaux dans Excel peuvent être un peu stupides. C'est que pour accomplir des tâches, les gens évitent d'utiliser des colonnes / rangées intermédiaires pour stocker des données (temporaires), des tableaux doivent donc recalculer le personnel du début à chaque fois, qui devient donc vraiment lent. Ma suggestion serait:
Peut-être que ce qui suit fonctionnerait:
Do Until Application.CalculationState = xlDone
DoEvents
Loop
Private sub SomeCodeThatGeneratesFormulas
Application.Calculation = xlCalculation.xlCalculationManual
'...Some formulas are copied here'
Application.OnTime DateTime.DateAdd ("s",.01,DateTime.Now), "Module1.CalculateFullRebuildAndSubsequentSteps" 'By using Application.OnTime, this method will be called in a way that locks the end-user out of providing inputs into Excel until the calculation itself is complete.
end sub
public sub CalculateFullRebuildAndSubsequentSteps
Application.CalculateFullRebuild
'...Do next steps, i.e. paste as values'
end sub
sur la barre d'état, côté droit, il dira J'utilise Office 2010, mais il devrait être là dans toutes les versions. C'est juste un peu subtil, alors il est facile de manquer. calculer (n processeurs) x% (où n est le nombre de processeurs sur votre ordinateur et X% est combien il a terminé) lors de la recalculition. Si vous ne voyez pas de texte là-bas, ce n'est pas recalculé.
La solution (MSDN) de Charles Williams ci-dessus a fonctionné pour moi où j'avais 1000 de Vlookup's de Vlookup qui ne recalculait pas lorsque le code changeait la valeur de la recherche en raison d'une boucle d'itération. Les résultats ont été biaisés car des calculs n'exécutaient pas à 100% d'achèvement.
au début de mon sous-programme, le code exécute Ceci éliminé des calculs inutiles par Excel jusqu'à ce que j'étais prêt jusqu'à ce que j'étais prêt . maintenant au point critique, le code exécute ayant extrêmement excité pour effectuer un calcul complet, le code pourrait ensuite enregistrer le résultat et passer à la prochaine itération ... mais avant de le faire se souvenir à la fin
Vous pouvez empêcher l'interruption de se produire, mais je suggérerais de dépenser des efforts accélérant le calcul: une heure à calculer est extrêmement excessive. Voir prise de décisionModels.com/optspeed.htm ou msdn.microsoft.com/en-us/library/ff700515.aspx
Je suis tout à fait d'accord. Mais même si je réduit le temps d'exécution du script par un ordre de grandeur, cela fonctionnera pendant 6 minutes, et je me demanderai toujours, étant donné la tendance d'Excel à être si silencieuse sur le calcul interrompu, que mes données soient exactes. Je suis en fait moins intéressé par la prévention de l'interruption et plus intéressé à savoir si une interruption s'est produite.
Grande question. Je remarque que lorsque le calcul est interrompu, le mot "calculer" apparaît dans la barre d'état. Autre que simplement regarder la barre d'état, je ne sais aucun moyen de tirer parti de cette information.
Application.CalculState correspond à la barre d'état Prêt / Calculer / Calculer le message. Depuis que Ben dit que c'est XLDone lorsqu'il est interrompu, je soupçonne qu'il peut utiliser VBA pour calculer de manière itérative. Difficile à dire sans regarder le code ...
Je recommanderais de casser le classeur dans plusieurs classeurs (préférables générer un nouveau fichier à chaque fois). Cela dira avec confiance que le calcul est complet
>> Etant donné que Excel est tellement désireux d'abandonner silencieusement le calcul si vous le regardez << avec une expérience dans chaque version d'Excel, cette déclaration est absurde.
Mon classeur le plus complexe à 128 Mo a pris 20 minutes pour calculer et générer manuellement les données pour un rapport. (Là où quatre rapports totales.) J'ai redessiné du classeur pour utiliser des tables de pivot comme des calculatrices, retravaillé les formules, des valeurs précalculées ajoutées, des tableaux d'utilisation utilisés pour des calculs, etc. Le résultat a été rapporté 2 à 5 minutes. Dans Excel 2007, les résultats sont de 1 à 3 minutes. Une heure n'est pas une solution.
Je ne sais pas à quel moment de calcul exceptionnel exactement la durée de calcul qu'il convient de s'arrêter et de dire: "Accrochez-vous, cela prend beaucoup trop longtemps, reportons comment cela se fait", mais je suis assez sûr que c'est bien avant une heure ...
La raison pour laquelle il faut une heure est dû à un grand nombre de formules de matrices hautement complexes, non pas à cause de grandes quantités de données. Je conviens que c'est une solution totalement inappropriée, et c'est parce que Excel n'est pas un outil approprié pour ce que nous faisons. Nous avons le temps prévu de réécrire complètement le système dans un langage de programmation réel dans l'année prochaine ou deux. Cependant, cela ne m'aidait pas à faire en sorte que nos données soient correctes entre-temps. Ma question se tiendrait toujours si elle n'a pris qu'une minute pour calculer.
Vous pouvez lire la valeur de sortie ancienne, stockez-la dans une plage nommée via VBA, écrivez une formule similaire à si (A1 <> OldResult, "Terminé", "") à une cellule où A1 est le résultat. Si le calcul est interrompu depuis cela dépend du résultat qu'il n'aurait pas été calculé et ne montrerait pas «fait».
Une idée intéressante. Cela fonctionnerait bien si je travaillais vers une seule valeur de résultat, voire quelques-uns. Malheureusement, il existe des centaines de valeurs de résultat dans plusieurs feuilles de calcul. En outre, il n'est pas inconceptible qu'une valeur de résultat serait identique à la même chose après le traitement.