时间戳

首页 > 解决方案 > 忽略或避免 UDF 中的循环引用？

vba - 忽略或避免 UDF 中的循环引用？

问题描述

我正在尝试做一个计划者。我有一张桌子，我想变成这样：

ID 家长编号 开始日期 期间 结束日期
1 2021 年 1 月 1 日 10 2021 年 11 月 1 日
2 1 2021 年 6 月 5 日 2 2021 年 8 月 5 日
3 2 2021 年 8 月 5 日 1 2021 年 9 月 5 日
4 3 2021 年 9 月 5 日 5 14/05/2021
5 2,3,4 14/05/2021 4 18/05/2021

开始日期将基于父 ID 与任务 ID 的匹配，因此任务 2 遵循任务 1，任务 2 遵循任务 3 等等。我还希望能够针对可能同时发生的多个任务进行测试，所以任务 5 可以在任务 2、3 或 4 之后开始，最后结束。

我为开始日期计算编写了这个 UDF。

Option Explicit

Function LastPredecessor(PreList As String, TaskList As Range, TaskDueDate As Range)

Dim ID
Dim Dates
Dim nPres, i As Integer

On Error Resume Next

Pres = Split(PreList, ",")
nPres = UBound(Pres)
ReDim Dates(0 To nPres, 0)

For i = 0 To nPres
    Dates(i, 0) = IDX_Match(CInt(Pres(i)), TaskList, TaskDueDate)
Next

LastPredecessor = WorksheetFunction.Max(Dates)

End Function

Function IDX_Match(LookupVal, MatchRange As Range, LookupRange As Range, Optional MatchType As Integer = 0)
    
    IDX_Match = WorksheetFunction.Index(LookupRange.Value2, WorksheetFunction.Match(LookupVal, MatchRange.Value2, MatchType))
    
End Function

该函数在表中的开始日期中调用，如下所示

=LastPredecessor([@Parent ID],[ID],[End Date])

如果结束日期与开始日期无关，则效果很好，但是一旦我尝试更新结束日期，并添加开始日期 + 持续时间作为结束日期的计算，它就会产生一个循环错误。

我确信我做错了一些非常简单的事情，但很高兴知道我需要做什么来解决这个问题。

编辑：

感谢@Toddleson，以下是稍作修改的版本

Function LastPredecessor(PreList As String, EndDates As Range, IDColumn As Range) As Date
    Dim Preds() As String, PredDates() As Long
    
    Preds = Split(PreList, ",")
    ReDim PredDates(UBound(Preds))
    For i = LBound(Preds) To UBound(Preds)
        PredDates(i) = IDColumn(WorksheetFunction.Match(CInt(Preds(i)), IDColumn, 0)).Offset(0, EndDates.Column - IDColumn.Column).Value2
    Next i
    LastPredecessor = Application.WorksheetFunction.Max(PredDates)
End Function

标签： vbauser-defined-functionscircular-reference

解决方案

好的，我重写了函数。它应该完全按照您对我的描述进行。

Function LastPredecessorV2(PreList As String, EndDates As Range, IDColumn As Range) As Date
    Dim Preds() As String, PredDates() As Long
    
    Preds = Split(Replace(PreList, " ", ""), ",")
    ReDim PredDates(UBound(Preds))
    For i = LBound(Preds) To UBound(Preds)
        PredDates(i) = IDColumn.Find( _
                        What:=CInt(Preds(i)), _
                        LookAt:=xlWhole _
                    ).Offset(0, EndDates.Column - IDColumn.Column).Value2 'Corrected
    Next i
    LastPredecessorV2 = CDate(Application.WorksheetFunction.Max(PredDates))
End Function

以下是参数输入的外观。这些范围可以是整个列，它仍然有效。 参数示例

所有结束日期都使用公式（开始日期 + 持续时间（以天为单位））。第一个任务开始日期是该列中唯一的独立值，所有其他的都使用公式。

编辑：我应该提到您可以在范围之间插入列，并且该功能仍然可以工作。

推荐阅读