功能最后修改日期的单元格 [英] Function for last modified date of cell

问题描述

我有一个VBA编写的函数可以做到这一点（见下文）注意：我没有自己写这个函数）

我想要的是一个函数，比如说= LastModifiedDateOf（CELL），其中CELL是一个参数，指示我想要监视的单元格。如果这样的单元格的值得到改变，那么包含该函数的单元格的值将被更新为当前日期。

最终我想要一个用google表格写的函数同样的事情，但我真的不明白这个功能如何工作。

任何人都可以做一个或两个：1）解释下面的功能发生了什么，2）写一个功能在googlescripts（js）做同样的事情？

 公共功能Lastmodified（c As Range）
 
 Lastmodified = Now（）
 
结束函数
  

解决方案

1）解释函数中发生的内容

仅重新计算电子表格单元格内容当他们的参考内容改变时。当你考虑一下，这对效率是有意义的：当没有一个引用的值（也称为参数）已经改变时，计算一个单元格的新值将会浪费，我们只是最终得到相同的值。

您提供的VBA功能的魔力是，只有当引用的单元格更改时，包含它的单元格才会重新计算。这是在电子表格的控制之下，完全独立于脚本本身。

同样的行为表现在Google表格中。在这两种情况下，监视单元格的功能并不重要 - 函数只要在调用时返回当前时间，当单元的值发生变化时，应该 。 >

但是，还有其他原因要调用该函数。在Excel中，您可以手动触发电子表格重新计算，并在当前时间内更新该函数在电子表格中出现的任何地方的输出。同样地，在Google表格中，任何时间您都可以重新计算电子表格。

2）在googlescripts（js）中编写一个函数

  / ** 
 *返回引用单元格或范围上次更改的时间。 
 *初始使用显示当前时间。 
 * 
 * @param {Sheet3！B32}参考要监视的单元格或范围。 
 * @returns上次更改引用的时间。 
 * @customfunction 
 * / 
函数lastModified（引用）{
 //惊喜 - 我们实际上并不在乎什么改变了！ 
 return（new Date（））; 
} 
  

I have a function written in VBA that does what I want it to (see below. note: I did not write this function myself)

What I wanted was to a function, say, "=LastModifiedDateOf(CELL)", where CELL, is a parameter that indicates the cell I want to monitor. If the value of such cell ever gets changed, the cell containing the function has its value updated to the current date.

Ultimately I want a function written in google sheets that does the same thing, but I dont really understand how this function works.

Can anyone do one or both: 1) Explain whats happening in the function below, 2) Write a function in googlescripts (js) that does the same thing?

Public Function Lastmodified(c As Range)

Lastmodified = Now()

End Function

解决方案

1) Explain whats happening in the function

Spreadsheet cell contents are recalculated only when the content of their references change. When you think about it for a bit, this makes sense for efficiency: it would be a waste of computer resources to calculate a new value for a cell when none of the referenced values (aka parameters) has changed... we'd just end up with the same value.

The magic for the VBA function you've provided is that the cell containing it gets recalculated only when the referenced cell is changed. That is under the control of the spreadsheet, and is completely independent of the script itself.

The same behavior is exhibited in Google Sheets. And in both cases, it's not important to the function what the monitored cell is - the function just returns the current time whenever it is called, which should be at times when the cell's value changes.

However, there are other reasons for the function to be called. In Excel, you can manually trigger spreadsheet recalculation, and that would update this function's output anywhere it appeared in the spreadsheet, with the current time. Likewise, in Google Sheets, any time you load the spreadsheet you could trigger a recalculation.

2) Write a function in googlescripts (js) that does the same thing

/**
 * Return the time that the referenced cell or range was last changed.
 * Initial use shows current time.
 *
 * @param {Sheet3!B32}  reference  Cell or range to monitor.
 * @returns                        The time the reference was last changed.
 * @customfunction
 */
function lastModified( reference ) {
  // Surprise - we don't actually care what has changed!
  return( new Date() );
}

这篇关于功能最后修改日期的单元格的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！