Excel のタイムスタンプ式を作成する

Question

さまざまな範囲の変更日を監視できる本格的なソリューションをご紹介します。これは、VBA で配列を使用するための Chip Pearson のツールそして機能からスタックオーバーフローユーザー Thomas による回答。

基本的な考え方は、すべての監視対象範囲 (過去または現在) のアドレスが最新の更新日付とともに保存されるグローバル配列によって、関数と Worksheet_Change Sub が相互作用できるようにすることです。Worksheet_Change Sub は、変更された範囲をすべて保存されている範囲と照合して、この配列を更新します。関数は配列内で監視対象範囲を検索し、見つかった場合は保存されている変更日付を返します。見つからない場合は、今日の日付を返します (その後、配列に追加されます)。

また、ブックが閉じられ、タイムスタンプの配列が割り当て解除されたときにタイムスタンプが失われるのを防ぐには、配列を Workbook_Close イベントでシートに書き込み、Workbook_Open イベントで配列に書き直す必要があります。

モジュールに次のコードを貼り付けます。

Public funcInstances() As Variant

Public Function MOD_DATE_OF(monitor As Range)
Application.Volatile True
Dim i As Long
Dim tmpArray() As Variant

If Not IsDimensioned(funcInstances) Then
    ReDim funcInstances(1 To 1, 1 To 2) As Variant
    funcInstances(1, 1) = monitor.Address
    funcInstances(1, 2) = Date
Else
    For i = 1 To UBound(funcInstances, 1)
        If funcInstances(i, 1) = monitor.Address Then
            MOD_DATE_OF = Format(funcInstances(i, 2), "yyyy-mm-dd")
            Exit Function
        End If
    Next i
    tmpArray = ExpandArray(funcInstances, 1, 1, "")
    Erase funcInstances
    funcInstances = tmpArray
    funcInstances(UBound(funcInstances, 1), 1) = monitor.Address
    funcInstances(UBound(funcInstances, 1), 2) = Date
End If
MOD_DATE_OF = Format(funcInstances(UBound(funcInstances, 1), 2), "yyyy-mm-dd")
End Function

'ExpandArray() is the work of Chip Pearson.  Code copied from http://www.cpearson.com/excel/vbaarrays.htm
Function ExpandArray(Arr As Variant, WhichDim As Long, AdditionalElements As Long, _
        FillValue As Variant) As Variant
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
' ExpandArray
' This expands a two-dimensional array in either dimension. It returns the result
' array if successful, or NULL if an error occurred. The original array is never
' changed.
' Parameters:
' --------------------
' Arr                   is the array to be expanded.
'
' WhichDim              is either 1 for additional rows or 2 for
'                       additional columns.
'
' AdditionalElements    is the number of additional rows or columns
'                       to create.
'
' FillValue             is the value to which the new array elements should be
'                       initialized.
'
' You can nest calls to Expand array to expand both the number of rows and
' columns. E.g.,
'
' C = ExpandArray(ExpandArray(Arr:=A, WhichDim:=1, AdditionalElements:=3, FillValue:="R"), _
'    WhichDim:=2, AdditionalElements:=4, FillValue:="C")
' This first adds three rows at the bottom of the array, and then adds four
' columns on the right of the array.
'
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
Dim Result As Variant
Dim RowNdx As Long
Dim ColNdx As Long
Dim ResultRowNdx As Long
Dim ResultColNdx As Long
Dim NumRows As Long
Dim NumCols As Long
Dim NewUBound As Long

Const ROWS_ As Long = 1
Const COLS_ As Long = 2


''''''''''''''''''''''''''''
' Ensure Arr is an array.
''''''''''''''''''''''''''''
If IsArray(Arr) = False Then
    ExpandArray = Null
    Exit Function
End If

'''''''''''''''''''''''''''''''''
' Ensure the dimension is 1 or 2.
'''''''''''''''''''''''''''''''''
Select Case WhichDim
    Case 1, 2
    Case Else
        ExpandArray = Null
        Exit Function
End Select

''''''''''''''''''''''''''''''''''''
' Ensure AdditionalElements is > 0.
' If AdditionalElements  < 0, return NULL.
' If AdditionalElements  = 0, return Arr.
''''''''''''''''''''''''''''''''''''
If AdditionalElements < 0 Then
    ExpandArray = Null
    Exit Function
End If
If AdditionalElements = 0 Then
    ExpandArray = Arr
    Exit Function
End If

NumRows = UBound(Arr, 1) - LBound(Arr, 1) + 1
NumCols = UBound(Arr, 2) - LBound(Arr, 2) + 1

If WhichDim = ROWS_ Then
    '''''''''''''''
    ' Redim Result.
    '''''''''''''''
    ReDim Result(LBound(Arr, 1) To UBound(Arr, 1) + AdditionalElements, LBound(Arr, 2) To UBound(Arr, 2))
    ''''''''''''''''''''''''''''''
    ' Transfer Arr array to Result
    ''''''''''''''''''''''''''''''
    For RowNdx = LBound(Arr, 1) To UBound(Arr, 1)
        For ColNdx = LBound(Arr, 2) To UBound(Arr, 2)
            Result(RowNdx, ColNdx) = Arr(RowNdx, ColNdx)
        Next ColNdx
    Next RowNdx
    '''''''''''''''''''''''''''''''
    ' Fill the rest of the result
    ' array with FillValue.
    '''''''''''''''''''''''''''''''
    For RowNdx = UBound(Arr, 1) + 1 To UBound(Result, 1)
        For ColNdx = LBound(Arr, 2) To UBound(Arr, 2)
            Result(RowNdx, ColNdx) = FillValue
        Next ColNdx
    Next RowNdx
Else
    '''''''''''''''
    ' Redim Result.
    '''''''''''''''
    ReDim Result(LBound(Arr, 1) To UBound(Arr, 1), UBound(Arr, 2) + AdditionalElements)
    ''''''''''''''''''''''''''''''
    ' Transfer Arr array to Result
    ''''''''''''''''''''''''''''''
    For RowNdx = LBound(Arr, 1) To UBound(Arr, 1)
        For ColNdx = LBound(Arr, 2) To UBound(Arr, 2)
            Result(RowNdx, ColNdx) = Arr(RowNdx, ColNdx)
        Next ColNdx
    Next RowNdx
    '''''''''''''''''''''''''''''''
    ' Fill the rest of the result
    ' array with FillValue.
    '''''''''''''''''''''''''''''''
    For RowNdx = LBound(Arr, 1) To UBound(Arr, 1)
        For ColNdx = UBound(Arr, 2) + 1 To UBound(Result, 2)
            Result(RowNdx, ColNdx) = FillValue
        Next ColNdx
    Next RowNdx

End If
''''''''''''''''''''
' Return the result.
''''''''''''''''''''
ExpandArray = Result

End Function

'IsDimensioned() is the work of StackOverflow user @Thomas.  Code copied from https://stackoverflow.com/a/5480690/657668
Public Function IsDimensioned(vValue As Variant) As Boolean
    On Error Resume Next
    If Not IsArray(vValue) Then Exit Function
    Dim i As Integer
    i = UBound(vValue)
    IsDimensioned = Err.Number = 0
End Function

適切なワークシートモジュールに次のコードを貼り付けます。

Private Sub Worksheet_Change(ByVal Target As Range)
Application.EnableEvents = False
Dim j As Long
If IsDimensioned(funcInstances) Then
    For j = 1 To UBound(funcInstances, 1)
        If Not Intersect(Target, Range(funcInstances(j, 1))) Is Nothing Then
            funcInstances(j, 2) = Date
        End If
    Next j
    Me.Calculate
End If
Application.EnableEvents = True
End Sub

最後に、ThisWorkbook モジュールに次のコードを貼り付けます。

Private Sub Workbook_BeforeClose(Cancel As Boolean)
If IsDimensioned(funcInstances) Then
    Application.ScreenUpdating = False
    'Store array on a new temporary and hidden worksheet.
    Dim tmpS As Worksheet, tmpR As Range
    Set tmpS = Worksheets.Add
    tmpS.Name = "TEMP Record of Timestamps"
    tmpS.Visible = xlSheetHidden
    Set tmpR = tmpS.Range("A1:B1").Resize(UBound(funcInstances, 1), 2)
    tmpR.Value = funcInstances
    ThisWorkbook.Save
    Application.ScreenUpdating = True
End If
End Sub

Private Sub Workbook_Open()
Dim ws As Worksheet, tstamps As Range
Dim wsfound As Boolean
wsfound = False
Application.ScreenUpdating = False
For Each ws In ThisWorkbook.Worksheets
    If ws.Name = "TEMP Record of Timestamps" Then
        wsfound = True
        Exit For
    End If
Next ws
If wsfound Then
    Set tstamps = ws.UsedRange
    funcInstances = tstamps.Value
    Application.DisplayAlerts = False
    ws.Delete
    Application.DisplayAlerts = True
End If
Application.ScreenUpdating = True
End Sub

このページを偶然見つけた人への注意:コメントの多くは以前の不完全な解決策に関するものなので、混乱しないでください。

Answer 1