如何停止刷新的Excel NOW()公式 [英] How to stop an excel NOW() formula from refreshing

问题描述

我有一个正在使用的excel报告，其中插入了多个SQL表，因此我有一个命令按钮，该命令按钮运行一个刷新所有表的宏.我正在尝试向该宏添加一些代码，这些代码将创建上次刷新表的时间戳.

I have an excel report I'm using that has multiple SQL tables inserted and therefore I have a command button that runs a macro that refreshes all the tables. I'm trying to add some code to that macro that will create a timestamp of the last time I refreshed the tables.

这是我到目前为止的内容:

Here's what I have so far:

Sub Button1_Click()
    ThisWorkbook.RefreshAll
    Range("I17").Formula = "=Now()"
End Sub

这可以满足我的要求，但问题是我的报告中还有很多其他单元会不断更新工作表，从而导致时间戳单元(I17)不断刷新.

This does what I want but the issue is my report has a lot of other cells that constantly update the sheet, which in turn cause the timestamp cell (I17) to refresh constantly.

该如何做才能使NOW()函数锁定"到那个时间，除非我特别单击宏按钮，否则不会刷新?

What can I do to make it so that the NOW() function will "lock" to that time and won't refresh unless I specifically click my macro button?

推荐答案

每次根据定义 进行重新计算时，公式都会重新评估其结果.如果不是这种情况，Excel将毫无用处.

A formula re-evaluates its result every time it's re-calculated, by definition. If that wasn't the case, Excel would be useless.

那么您想要的不是一个公式，而是一个值.

What you want isn't a formula then, but a value.

因此，不要使用Excel的 NOW()函数，而是使用VBA的 DateTime.Now 属性来分配单元格的 value :

So instead of using Excel's NOW() function, use VBA's DateTime.Now property to assign the cell's value:

ActiveSheet.Range("I17").Value = VBA.DateTime.Now

所有内容都隐含限定为:

With everything implicitly qualified it's as short as:

[I17] = Now

我鼓励您找到中间立场-隐式引用是错误的非常常见的来源.只要您没有 Now 变量，常量，过程或模块，而该变量，常量，过程或模块会隐藏 VBA.属性:

I encourage you to find a middle ground - implicit references are a terribly common source of bugs. Something like this seems acceptable, as long as you don't have a Now variable, constant, procedure, or module in scope that would hide the VBA.DateTime property:

ActiveSheet.Range("I17") = Now

以上指令明确地将活动范围的 Range 限定为调用，隐式使用了 Range 对象的 default属性( Value )，并隐式限定 VBA.DateTime 标准库模块中的 Now 属性.

The above instruction explicitly qualifies the Range call with the active sheet, implicitly uses the Range object's default property (Value), and implicitly qualifies the Now property from the VBA.DateTime standard library module.

这篇关于如何停止刷新的Excel NOW()公式的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！