找到你要的答案

Q:Detect change from nested formulas

Q:检测嵌套公式的变化

I have a very complex workbook with many tabs. The tabs may have either normal data or formulas in various cells. In the case of formulas, the formulas may be nested from one sheet to the next (i.e. a formula on sheet1 refers to a formula on sheet2 which in turn refers to a formula on sheet3, etc.).

I have a hidden tab that contains the following: source sheet, source range, target sheet, and target range.

A named range has been created over these 4 fields and all applicable rows.

When we wish to save data to the database, we loop through every row in the range mapping and copy the data from the source sheet/range to the target sheet/range. After this, the applicable data is serialized into XML and sent to a web service to be saved.

The problem that we wish to resolve is that we want to mark a cell on a hidden sheet when a change is made by the user to a source range. Since formulas can be nested, the Worksheet_Change event does not pick up the change.

Since a change on one sheet may affect another sheet that is not the active sheet, the Workbook_SheetChange event does not catch the change either.

Is there any way form me to catch when a sheet defined in the mapping is changed, even if it is the result of a formula change several levels deep?

Edit

Thank you for your responses. I was attempting to find the fastest and least process intensive way to determine if data changes within a monitored range. The data may consist of actual data or of nested formulas.

My research showed that I could not actually achieve this result by taking range intersections as I could not detect if the data within a monitored range was modified. This is due to the fact that the monitored range may not be on the active sheet and also may contain formulas.

I have shown the method used to actually detect a change below. If there is any feedback on a better way to achieve the same result, I would appreciated it.

我有一个非常复杂的工作簿与许多标签。标签可以有正常的数据或公式在不同的细胞。在公式的情况下,该公式可以嵌套一片下(即指公式Sheet1 Sheet2上反过来指Sheet3等公式,公式)。

我有一个隐藏的标签,其中包含以下内容:源表,源范围,目标表和目标范围。

在这4个字段和所有适用的行上创建了一个命名范围。

当我们希望将数据保存到数据库时,我们将遍历范围映射中的每一行,并将数据从源表/范围复制到目标表/范围。在这之后,适用的数据序列化为XML和发送到Web服务可以节省。

我们希望解决的问题是,当用户更改到源范围时,我们希望在隐藏页上标记一个单元格。从公式可以嵌套,这worksheet_change事件不接的变化。

从一片的变化可能会影响另一个表,不主动表的workbook_sheetchange事件没有赶上变化。

当映射中定义的一张表被改变时,是否有任何形式让我捕获,即使它是一个公式改变几个层次的结果?

编辑

谢谢你的回复。我试图找到最快和最少的过程密集型的方式,以确定是否在监测范围内的数据变化。数据可以由实际数据或嵌套公式组成。

我的研究表明,我真的无法实现这个结果,采取范围交叉点,因为我不能检测,如果在监测范围内的数据被修改。这是由于监视范围可能不在活动表上,也可能包含公式的事实。

我已经展示了用于实际检测以下更改的方法。如果有任何反馈,以更好的方式实现相同的结果,我将不胜感激。

answer1: 回答1:

Worksheet_Change event will not work if a cell value is changed by a formula, you need Worksheet_Calculate.

Check out my example workbook here.

And Here for the WebPage of example codes

worksheet_change事件不会如果一个单元格的值是由公式改变工作,你需要worksheet_calculate。

在这里检查我的示例工作簿。

这里是示例代码的网页

answer2: 回答2:

There is no "easy" way to detect if a nested formula has changed when the formula being monitored is not on the active sheet. While my hope was to detect the modified range and use an intersection of ranges to set a flag, this was not possible because the Worksheet_Change event does not work on formulas and the Workbook_SheetChange event only works on the active sheet. Since my workbooks have over 20+ tabs and 20 - 30 ranges being monitored, this approach does not work. This approach was desired for speed purposes.

Instead, the workbook will need to "check" to see if the current values are the same as the last time the save to database event was called. If not, a dirty flag will be set.

The code for this approach is provided below.

An example of the mapping range is shown in the picture below though in practice there are 20-30 rows comprising this range.

There are three other sheets where Sheet3 contains actual data in A1:H1 and Sheet2 has formulas pointing to Sheet3. Sheet1 has formulas pointing to Sheet2.

As the mapping range indicates, we are looking at a range on Sheet1, even though changes may be made to Sheet3.

The code used is as provided below.

Option Explicit
Public Sub DetermineIfEditOccurred()

Dim oMappingRange As Range
Dim szSourceTab As String
Dim szSourceRange As String
Dim oSourceRange As Range
Dim szTargetTab As String
Dim szTargetRange As String
Dim oTargetRange As Range
Dim oWorksheetSource As Worksheet
Dim oWorksheetTarget As Worksheet
Dim oRangeIntersection As Range
Dim nRowCounter As Long
Dim nCellCounter As Long

Dim szSourceValue As String
Dim szTargetValue As String
Dim oCell As Range
Dim bIsDirty As Boolean

If Range(ThisWorkbook.Names("DirtyFlag")).Value = 0 Then
    Set oMappingRange = Range(ThisWorkbook.Names("Mapping"))

    For nRowCounter = 1 To oMappingRange.Rows.Count
        szSourceTab = oMappingRange(nRowCounter, 1)
        szSourceRange = oMappingRange(nRowCounter, 2)

        szTargetTab = oMappingRange(nRowCounter, 3)
        szTargetRange = oMappingRange(nRowCounter, 4)

        Set oWorksheetSource = ThisWorkbook.Worksheets(szSourceTab)
        Set oWorksheetTarget = ThisWorkbook.Worksheets(szTargetTab)
        Set oSourceRange = oWorksheetSource.Range(szSourceRange)
        Set oTargetRange = oWorksheetTarget.Range(szTargetRange)

        nCellCounter = 1

        For Each oCell In oSourceRange.Cells
            szSourceValue = oCell.Value

            If szSourceValue = "#NULL!" Or _
               szSourceValue = "#DIV/0!" Or _
               szSourceValue = "#VALUE!" Or _
               szSourceValue = "#REF!" Or _
               szSourceValue = "#NAME?" Or _
               szSourceValue = "#NUM!" Or _
               szSourceValue = "#N/A" Then

               szSourceValue = ""
            End If

            szTargetValue = GetCellValueByPosition(oTargetRange, nCellCounter)

            If szSourceValue <> szTargetValue Then
                Range(ThisWorkbook.Names("DirtyFlag")).Value = 1
                bIsDirty = True
                Exit For
            End If

            nCellCounter = nCellCounter + 1
        Next

        If bIsDirty Then
            Exit For
        End If
    Next
End If
End Sub
Public Function GetCellValueByPosition(oRange As Range, nPosition As Long) As String

   Dim oCell As Range
   Dim nCounter As Long
   Dim szValue As String

   nCounter = 1

   For Each oCell In oRange
       If nCounter = nPosition Then
            szValue = oCell.Value

            Exit For
       End If

       nCounter = nCounter + 1
   Next

   GetCellValueByPosition = szValue
End Function

The Workbook_SheetChange event is as follows:

Option Explicit
Private Sub Workbook_BeforeClose(Cancel As Boolean)
    Call DetermineIfEditOccurred
End Sub
Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
    If Sh.Name <> "MAPPING" Then
        Call DetermineIfEditOccurred
    End If
End Sub

当被监视的公式不在活动页上时,没有“简单”的方法来检测嵌套的公式是否发生了变化。而我的希望是检测修正的范围和使用一个交叉口范围内设置一个标志,这是不可能的因为worksheet_change事件不能在公式和workbook_sheetchange事件只能在活动工作表。因为我的作业有超过20 +标签和20 - 30的范围进行监测,这种方法不工作。这种方法是为了速度的目的。

相反,工作簿将需要“检查”以查看当前值是否与上次保存到数据库事件的时间相同。如果没有,将设置脏标志。

此方法的代码如下。

映射的范围内的一个例子是显示在下面的图片,虽然在实践中有20-30行包括这个范围。

还有其他三个表中包含实际的数据:3 A1 H1和Sheet2指着Sheet3有公式。有公式指向Sheet2 Sheet1。

作为映射的范围内,我们正在寻找在Sheet1的范围,尽管变化可能使Sheet3。

所使用的代码如下所提供。

Option Explicit
Public Sub DetermineIf编辑Occurred()

Dim oMappingRange As Range
Dim szSourceTab As String
Dim szSourceRange As String
Dim oSourceRange As Range
Dim szTargetTab As String
Dim szTargetRange As String
Dim oTargetRange As Range
Dim oWorksheetSource As Worksheet
Dim oWorksheetTarget As Worksheet
Dim oRangeIntersection As Range
Dim nRowCounter As Long
Dim nCellCounter As Long

Dim szSourceValue As String
Dim szTargetValue As String
Dim oCell As Range
Dim bIsDirty As Boolean

If Range(ThisWorkbook.Names("DirtyFlag")).Value = 0 Then
    Set oMappingRange = Range(ThisWorkbook.Names("Mapping"))

    For nRowCounter = 1 To oMappingRange.Rows.Count
        szSourceTab = oMappingRange(nRowCounter, 1)
        szSourceRange = oMappingRange(nRowCounter, 2)

        szTargetTab = oMappingRange(nRowCounter, 3)
        szTargetRange = oMappingRange(nRowCounter, 4)

        Set oWorksheetSource = ThisWorkbook.Worksheets(szSourceTab)
        Set oWorksheetTarget = ThisWorkbook.Worksheets(szTargetTab)
        Set oSourceRange = oWorksheetSource.Range(szSourceRange)
        Set oTargetRange = oWorksheetTarget.Range(szTargetRange)

        nCellCounter = 1

        For Each oCell In oSourceRange.Cells
            szSourceValue = oCell.Value

            If szSourceValue = "#NULL!" Or _
               szSourceValue = "#DIV/0!" Or _
               szSourceValue = "#VALUE!" Or _
               szSourceValue = "#REF!" Or _
               szSourceValue = "#NAME?" Or _
               szSourceValue = "#NUM!" Or _
               szSourceValue = "#N/A" Then

               szSourceValue = ""
            End If

            szTargetValue = GetCellValueByPosition(oTargetRange, nCellCounter)

            If szSourceValue <> szTargetValue Then
                Range(ThisWorkbook.Names("DirtyFlag")).Value = 1
                bIsDirty = True
                Exit For
            End If

            nCellCounter = nCellCounter + 1
        Next

        If bIsDirty Then
            Exit For
        End If
    Next
End If
End Sub
Public Function GetCellValueByPosition(oRange As Range, nPosition As Long) As String

   Dim oCell As Range
   Dim nCounter As Long
   Dim szValue As String

   nCounter = 1

   For Each oCell In oRange
       If nCounter = nPosition Then
            szValue = oCell.Value

            Exit For
       End If

       nCounter = nCounter + 1
   Next

   GetCellValueByPosition = szValue
End Function

的workbook_sheetchange事件如下:

Option Explicit
Private Sub Workbook_BeforeClose(Cancel As Boolean)
    Call DetermineIf编辑Occurred
End Sub
Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
    If Sh.Name <> "MAPPING" Then
        Call DetermineIf编辑Occurred
    End If
End Sub
excel  vba  excel-vba