如何防止在Excel中更新单元格？

时间：2021-07-22 22:23:43

I have a pretty large Range (10,000 rows, 10 cols) that I fill line by line on an everyday basis. I also have a smaller Range (366 rows, 5 cols), in which, for every cell, I run a macro. That macro does pretty much what DSUM or SUMIF do, but with multiple criteria. The problem is that after having this fancy stuff implemented, I can't fill my stuff decently, coz on every cell I fill the macro is ran dozens of times, and every times it loops hundreds, taking around 5 seconds to let me fill the next cell.

我有一个相当大的范围(10,000行,10列),我每天逐行填写。我也有一个较小的范围(366行,5列),其中,对于每个单元格,我运行一个宏。该宏几乎与DSUM或SUMIF的作用相同,但具有多个标准。问题是,在实现了这个花哨的东西之后,我不能很好地填充我的东西,因为我填充宏的每个单元都运行了几十次,并且每次都循环数百次,花大约5秒让我填充下一个细胞。

I'd like to be able to prevent the smaller range to be updated (or the macro not to be ran), so I can fill it all normally, and then press a button to update everything.

我希望能够防止较小的范围被更新(或者不要运行宏),所以我可以正常填充它,然后按一个按钮来更新所有内容。

3 个解决方案

#1

2  

You will need to lock the target range (setting the Locked property of the Range to true). Then you will need to protect the sheet by calling the Protect() method of your worksheet. Below are examples of how to lock and unlock the cells A1:A4.

您需要锁定目标范围(将Range的Locked属性设置为true)。然后,您需要通过调用工作表的Protect()方法来保护工作表。以下是如何锁定和解锁单元格A1:A4的示例。

Locks:

Sheet1.Range("A1:A4").Locked = true
Sheet1.Protect()

Unlocks:

Sheet1.Unprotect()

Edit: After re-reading the question, it seems as though the OP is using the Worksheet_Change event. To which I have one word:

编辑:重新阅读问题后,似乎OP正在使用Worksheet_Change事件。我有一个字:

STOP

Just put in an ActiveX button into the sheet, and then assign a macro to that, as described here.

只需在工作表中放入一个ActiveX按钮,然后为其指定一个宏,如此处所述。

The Worksheet_Change event is fraught with peril. Beware ye who enter, for there be dragons.

Worksheet_Change事件充满了危险。要小心你们进入,因为有龙。

#2

1  

Well there's this:

那就是这个:

      Application.Calculation = xlCalculationManual

But that stops all recalculations, until you rest it to automatic or explicity call for a Recalc. Is that sufficient, or do you need more?

但是这会停止所有重新计算,直到您将其重新设置为自动或明确调用Recalc。这还不够,还是需要更多?

If you need to limit the recalc restriction to only prevnting specific macros from executing, then the following:

如果您需要将重新计算限制限制为仅执行特定宏,则以下内容:

  1. Add a "Global IShouldNotRun as Boolean" to your macro(s)

    2. 在宏中添加“Global IShouldNotRun as Boolean”

  2. Your macro(s) should check the IShouldNotRun value and exit if it is true.

    3. 您的宏应该检查IShouldNotRun值并退出,如果它是真的。

  3. write another macro that toggles this global.

    4. 写另一个切换全局的宏。

  4. Have whatever is doing you data loading call the routine to set the global to True.

    5. 无论你做什么,数据加载调用例程将global设置为True。

  5. Reset it after you are done loading and call Calculate, CalculateFull or CalculateFullRebuild.

    6. 完成加载后重置它并调用Calculate,CalculateFull或CalculateFullRebuild。

#3

0  

I don't have ecell on my computer so I cannot check the exact path, but as far as I remember after going to Tools/Options menu there is a tab 'calculation' or 'computation' where you can disable the automatic formula recalculation.

我的计算机上没有ecell,所以我无法检查确切的路径,但据我记得,在进入工具/选项菜单后,有一个标签'计算'或'计算',您可以在其中禁用自动公式重新计算。

#1

2  

You will need to lock the target range (setting the Locked property of the Range to true). Then you will need to protect the sheet by calling the Protect() method of your worksheet. Below are examples of how to lock and unlock the cells A1:A4.

您需要锁定目标范围(将Range的Locked属性设置为true)。然后,您需要通过调用工作表的Protect()方法来保护工作表。以下是如何锁定和解锁单元格A1:A4的示例。

Locks:

Sheet1.Range("A1:A4").Locked = true
Sheet1.Protect()

Unlocks:

Sheet1.Unprotect()

Edit: After re-reading the question, it seems as though the OP is using the Worksheet_Change event. To which I have one word:

编辑:重新阅读问题后,似乎OP正在使用Worksheet_Change事件。我有一个字:

STOP

Just put in an ActiveX button into the sheet, and then assign a macro to that, as described here.

只需在工作表中放入一个ActiveX按钮,然后为其指定一个宏,如此处所述。

The Worksheet_Change event is fraught with peril. Beware ye who enter, for there be dragons.

Worksheet_Change事件充满了危险。要小心你们进入,因为有龙。

#2

1  

Well there's this:

那就是这个:

      Application.Calculation = xlCalculationManual

But that stops all recalculations, until you rest it to automatic or explicity call for a Recalc. Is that sufficient, or do you need more?

但是这会停止所有重新计算,直到您将其重新设置为自动或明确调用Recalc。这还不够,还是需要更多?

If you need to limit the recalc restriction to only prevnting specific macros from executing, then the following:

如果您需要将重新计算限制限制为仅执行特定宏,则以下内容:

  1. Add a "Global IShouldNotRun as Boolean" to your macro(s)

    2. 在宏中添加“Global IShouldNotRun as Boolean”

  2. Your macro(s) should check the IShouldNotRun value and exit if it is true.

    3. 您的宏应该检查IShouldNotRun值并退出,如果它是真的。

  3. write another macro that toggles this global.

    4. 写另一个切换全局的宏。

  4. Have whatever is doing you data loading call the routine to set the global to True.

    5. 无论你做什么,数据加载调用例程将global设置为True。

  5. Reset it after you are done loading and call Calculate, CalculateFull or CalculateFullRebuild.

    6. 完成加载后重置它并调用Calculate,CalculateFull或CalculateFullRebuild。

#3

0  

I don't have ecell on my computer so I cannot check the exact path, but as far as I remember after going to Tools/Options menu there is a tab 'calculation' or 'computation' where you can disable the automatic formula recalculation.

我的计算机上没有ecell,所以我无法检查确切的路径,但据我记得,在进入工具/选项菜单后,有一个标签'计算'或'计算',您可以在其中禁用自动公式重新计算。

标签：

相关文章