如何在VBA中将动态单元格引用存储到变量中？

完全可行，但要注意**x是动态变化的**，不能只初始化一次dayOutput变量，需要在每次x的值更新后，重新指定这个Range变量的引用。

具体修改要点 #

1. 保留dayOutput的变量声明，但不要在函数开头固定赋值，而是在需要使用单元格前，根据当前x值动态绑定引用。
2. 所有重复的ws.Cells(outputNR, x)都可以替换为dayOutput，提升代码可读性。
3. 必须给dayOutput指定工作表（ws.Cells(...)），避免默认引用激活工作表的问题。

修改后的关键代码片段 #

Public Function annDays(ByVal cfDate As Date, ByVal expDate As Date, ByVal currentPeriod As Date, ByVal outputNR As Long, ByVal inputNR As Long) As Double
    Dim ws As Worksheet
    Set ws = Sheets("SUMMARY")

    Dim dayOutNR As Long, dayInNR As Long
    dayOutNR = outputNR
    dayInNR = inputNR

    'restrict incorrect logic to reduce workings
    If expDate <= cfDate Or _
        cfDate = 0 Or _
        expDate = 0 Then
        Exit Function
    End If
    
    Dim dayOutput As Range, x As Long
    'Calculate total days in range, accounting for LeapYear
    Dim y As Long, leapYr As Date
    x = 25 ' 第一次设置x值
    Set dayOutput = ws.Cells(dayOutNR, x) ' 绑定对应单元格

    For y = Year(cfDate) To Year(expDate)
        leapYr = DateSerial(y, 2, 29)
        If Day(leapYr) = 29 And leapYr >= cfDate And leapYr <= expDate Then
            dayOutput = DateDiff("d", cfDate, expDate)
        Else
            dayOutput = DateDiff("d", cfDate, expDate) + 1
        End If
    Next y

    'create variable to increment (set to April 1, xxxx by default)
    Dim incMonth As Date
    If Month(cfDate) = 1 Or Month(cfDate) = 2 Or Month(cfDate) = 3 Then
        incMonth = DateSerial(Year(cfDate) - 1, 4, 1)
    Else
        incMonth = DateSerial(Year(cfDate), 4, 1)
    End If

    'Calculates & increments number of months before currentPeriod (x = 13 is April)
    x = 13 ' x值更新，重新绑定dayOutput
    Set dayOutput = ws.Cells(dayOutNR, x)
    Do While Month(incMonth) <> Month(currentPeriod)
        incMonth = DateAdd("m", 1, incMonth)
        x = x + 1
        Set dayOutput = ws.Cells(dayOutNR, x) ' 每次x递增后更新引用
    Loop

    If currentPeriod >= expDate Then
        If cfDate <= leapYr And expDate >= leapYr Then
            dayOutput = DateDiff("d", cfDate, expDate)
            GoTo bottomFunction
        Else
            dayOutput = DateDiff("d", cfDate, expDate) + 1
            GoTo bottomFunction
        End If

    ElseIf cfDate <= currentPeriod Then
        If cfDate <= leapYr And currentPeriod >= leapYr Then
            dayOutput = DateDiff("d", cfDate, DateSerial(Year(currentPeriod), Month(currentPeriod) + 1, 0))
            currentPeriod = DateAdd("m", 1, currentPeriod)
            x = x + 1
            Set dayOutput = ws.Cells(dayOutNR, x) ' x更新后重新绑定
        Else
            dayOutput = DateDiff("d", cfDate, DateSerial(Year(currentPeriod), Month(currentPeriod) + 1, 0)) + 1
            currentPeriod = DateAdd("m", 1, currentPeriod)
            x = x + 1
            Set dayOutput = ws.Cells(dayOutNR, x) ' x更新后重新绑定
        End If
    End If

bottomFunction:
    ' 后续逻辑（如有）
End Function

核心注意点 #

每次x的值发生变化时，必须重新执行Set dayOutput = ws.Cells(dayOutNR, x)，确保变量始终指向正确的目标单元格。

内容的提问来源于stack exchange，提问作者Sheldon