Excel问题：闪烁的公式栏，空白名称框，无响应，高CPU活动/高页面错误 [英] Excel Problem: Shimmering Formula Bar, Blank Name Box, Non-responsiveness, High CPU Activity/High Page Faults

问题描述

大家好，

我通常不会在这个论坛发帖，但这个问题已经出现太多次让我放手了。我们有一个主要的Excel开发平台，其中我们的工作簿非常庞大（大约32Mb），从C ++创作的XLL导出工作表函数到由一堆VBA代码引用的C＃编写的DLL中加载了大量技术。所以很多事情正在发生，但通常如果我们遇到问题，那么我们可以通过选择性地加载不同的组件并查看哪一个开始引起问题来将其束缚。但是，有一个问题已经无法诊断，我想与Excel社区分享这个问题。

一段时间后，我可能没有比这更具体的了，Excel会话将开始变得反应迟钝。这可能是经过许多繁重的计算，需要几分钟。然而，人们可能会认为这是对unmpton的无助描述的主观。更具体和可观察的症状是公式栏闪烁。我能为此提供的唯一解释是它不断被重新绘制/重写。有时，编辑公式是不可能的，有时也不是。我们组织内的其他人报告名称框为空白且没有单元格地址。很难再现和分析。但是，有一次我能够进行多次实验并获取观察数据。

当发生这样的情况时，查看任务管理器并选择所有可用列表示高CPU活动性和多个PageFaults使用NT Perfomon显示磁盘活动很少，这表明软页面故障不太严重。很明显，Excel正在通过CPU统计来做某事，即使它只是无休止地重新绘制公式栏，但这无法解释页面错误。

我试过在一个会话中卸载所有的VBA代码，看看它是否正在运行，但公式栏仍然闪烁。我试图用Com调用COFreeAllLibraries来卸载dll，但是Process Monitor表示很多dll顽固地拒绝卸载这是令人惊讶的，因为没有Tools-> References将它们锁定在内存中为什么它们仍然存在，非零引用计数？

我甚至用ROTViewer查看了运行对象表，看看是否有什么不对，但我没有看错。

最后，我看了使用Spy ++，但我对Windows API的经验不足以说明正常和异常活动之间的区别。

我所有的证据都是一个公式栏，闪烁和闪烁，好像它不断重新粉刷和CPU活动的负荷。

有时候，有可能进行自己的工作并进行进一步的计算，最终会话停止并且用户别无选择，只能杀死Excel会话并可能失去工作。 />
这适用于在Windows XP SP3上运行的Excel 2003（11.8231.8221）SP3。

如果您遇到类似的事情，请发布任何观察数据或更好的解决方案/解释。

感谢和问候，
Meadensi

---

你的

Hi Guys,

I don't normally post in this forum but this problem has arisen too many times for me to let it go.  We have a major Excel development platform where our Workbooks are huge (circa 32Mb) with a myriad of technologies loaded into a session from C++ authored XLLs exporting worksheet functions to C# authored DLLs being referenced by a heap of VBA code.  So a great deal going on but usually if we have a problem then we can tie it down by selectively loading different components and seeing which one starts to cause problems.  However, there is a problem which has defied diagnosis and I would like to share the issue with the Excel community.

After a while, and I can be no more specific than this, an Excel session will start to become unresponsive.  This can be after many heavy calculations which take minutes.  However, one might consider this a subjective of unhelpul description of a sympton.   A more concrete and observable symptom is that the formula bar is shimmering.  The only explanation I can offer for this is that it is being constantly repainted/rewritten.  Sometimes, editing of the formula is impossible and sometimes not.   Others within our organization have reported the Name Box being blank and without the cell address.  It is difficult to reproduce and so analyze.  However, on one occasion I was able to run many experiments and take observational data.

When such an episode occurs, looking at the Task Manager with all the available columns selected indicates high CPU acticity and multiple PageFaults though using NT Perfomon shows little disk activity, which indicates the less severe soft page fault.  It is clear that Excel is doing something by the CPU statistic even it is only repainting the formula bar endlessly but that wouldn't explain the page faults. 

I have tried unloading all of the VBA code in a session to see if it was running this but the formula bar still flickered.  I tried to unload the dlls with the Com call COFreeAllLibraries but Process Monitor indicated many dlls stubbornly refusing to unload which is suprising because without Tools->References locking them in memory why are they still there, non-zero reference counting? 

I even looked at the Running Object Table using ROTViewer to see if anything was amiss but I saw nothing wrong. 

Lastly, I looked using Spy++ but I am not experienced enough with Windows API to be able to tell the difference between normal and abnormal activity.

All I have as evidence is a formula bar that flickers and shimmers as if it were being constantly repainted and a load of CPU activity. 

Sometimes, it is possible to carry own working and taking further calculations, eventually the session grinds to a halt and the user is left with no alternative but to kill the Excel session and potentially lose work.

This is for Excel 2003 (11.8231.8221) SP3 running on Windows XP SP3.

If you have experienced something similar please post any observational data or better still a solution/explanation.

Thanks and regards,
Meadensi

---

Yours

推荐答案

你看到的闪烁的公式栏可以在这种情况下发生 -

选择非范围对象，例如形状
前一个活动单元格（在选择形状之前）不是空的代码读取（或写入）长循环中的大量范围属性，例如cell.interior.colorindex除了闪烁之外，即使活动单元格为空，代码也会比正常情况慢得多处理。

到避免这种情况 - 如果是Typena我（选择）<> "范围"然后'如果不是Excel VBA使用xlApp.Selection
将对象引用存储到当前选择中选择上一个Activecell，即ActiveWindow.RangeSelection
进程
当还原上一个对象选择时完成。

可能想要包含更多内容，例如，如果.RangeSelection在其外部（禁用事件），可能选择.VisibleRange中的topleft单元格。如果当前选择是嵌入式图表（仅将其视为ChartObject或图表和元素），还可以执行更多操作。

当您的大型复杂项目可能还有其他原因。<

关心，
Peter Thornton（Excel MVP）

The flickering formula bar you see can occur in this scenario -

A non Range object is selected, eg a Shape
The previous activecell (before selecting the shape) is not empty
The code reads (or writes) a large number of range properties in a long loop, eg cell.interior.colorindex
Apart from the flickering the code will process signficantly slower than normal, even if the activecell is empty.

To avoid that -
If Typename(Selection) <> "Range" then ' if not Excel VBA use xlApp.Selection
Store an object reference to the current selection
Select the previous Activecell, ie ActiveWindow.RangeSelection
Process
Restore the previous object selection when done.

Will probably want to include a bit more, eg perhaps select the topleft cell in the .VisibleRange if .RangeSelection is outside it (with events disabled). Also more to do if the current selection is an embedded chart (look at both as merely ChartObject or as chart and an element).

Of course with your massive complex project there might be some other reason.

Regards,
Peter Thornton (Excel MVP)

这篇关于Excel问题：闪烁的公式栏，空白名称框，无响应，高CPU活动/高页面错误的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！