VBA从单元格区域取数据时数组返回空值问题求助

我写了一段未完成的VBA函数ResinOutDate，想要从工作表“Reorder Calculator”的A6:A371和D6:D371区域读取数据到DatesArray和WorkDayArray中，但发现这两个数组里有大量空值，可数据源区域明明没有空值。我参考网上示例用Range.Value语句把区域数据赋值给数组，问题还是没解决。相关代码如下：

Public Function ResinOutDate(ByVal Tday As Date, ByVal WDays As Long) As Integer
    Dim DatesArray As Variant
    DatesArray = ThisWorkbook.Sheets("Reorder Calculator").Range("A6:A371").Value
    
    Dim WorkDayArray As Variant
    WorkDayArray = ThisWorkbook.Sheets("Reorder Calculator").Range("D6:D371").Value
    
    Dim i As Integer
    Dim Workdaycount As Integer
    Workdaycount = 0

    For i = 1 To 366
        If DatesArray(i) = Tday Then
            Do Until Workdaycount = WDays
                Workdaycount = Workdaycount + WorkDayArray(i)
                i = i + 1
            Loop
            Exit For
        End If
    Next i
    ResinOutDate = i
End Function

调试显示DatesArray和WorkDayArray存在大量空值，但数据源区域A6:A371、D6:D371均无空值。

核心原因 #

用Range.Value将单列区域赋值给Variant数组时，得到的是二维数组（格式为Array(行号, 列号)），而你在代码里用DatesArray(i)、WorkDayArray(i)这种一维数组的方式访问，实际读取的是数组中第i行第1列以外的位置（这些位置本就无数据，自然是空值）。

比如A6:A371区域对应的数组维度是(1 To 366, 1 To 1)，正确的访问方式应该是DatesArray(i, 1)，而非DatesArray(i)。

修复后的代码 #

Public Function ResinOutDate(ByVal Tday As Date, ByVal WDays As Long) As Integer
    Dim DatesArray As Variant
    DatesArray = ThisWorkbook.Sheets("Reorder Calculator").Range("A6:A371").Value
    
    Dim WorkDayArray As Variant
    WorkDayArray = ThisWorkbook.Sheets("Reorder Calculator").Range("D6:D371").Value
    
    Dim i As Integer
    Dim Workdaycount As Integer
    Workdaycount = 0

    For i = 1 To UBound(DatesArray, 1)
        ' 改为二维数组访问方式
        If Not IsEmpty(DatesArray(i, 1)) And DatesArray(i, 1) = Tday Then
            Do Until Workdaycount >= WDays
                Workdaycount = Workdaycount + WorkDayArray(i, 1)
                i = i + 1
                ' 增加边界判断，防止数组越界
                If i > UBound(DatesArray, 1) Then Exit Do
            Loop
            Exit For
        End If
    Next i
    ResinOutDate = i
End Function

额外优化说明 #

1. 用UBound(DatesArray, 1)代替硬编码的366，让代码自动适配区域行数变化；
2. 增加IsEmpty判断，避免数据源意外出现空值时触发报错；
3. 将循环终止条件改为Workdaycount >= WDays，避免因WorkDayArray中数值过大导致死循环。

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