使用Range.Value提取整表值异常：大量数值单元格被遗漏

在Excel VBA中使用Range.Value将整个工作表的值提取到Variant二维数组时，大量包含数值的单元格被跳过，数组对应元素为空。

执行以下代码后生成以制表符分隔的文本文件，每行对应工作表一行：

Dim lastRow As Long
Dim lastCol As Integer
Dim ws As Worksheet
Dim spreadsheetArray As Variant

Set ws = Application.ThisWorkbook.Worksheets(spreadsheetName)

lastRow = ws.UsedRange.Rows.Count
lastCol = ws.UsedRange.Columns.Count

' Copy the values from the spreadsheet into a 2 dimensional array of Variant
spreadsheetArray = ws.Range(ws.Cells(1, 1), ws.Cells(lastRow, lastCol)).Value

Open "excel.txt" For Append As #1 ' Open file for output.

Print #1, "Array for sheet " & spreadsheetName & ", rows=" & UBound(spreadsheetArray, 1) & ", columns=" & UBound(spreadsheetArray, 2)

Dim r, c As Long
Dim line As String

' Each cell's value is delimited by a tab and each row is delimited by CR/LF
For r = 1 To UBound(spreadsheetArray, 1)
    line = Str(r) & Chr(9)
    
    For c = 1 To UBound(spreadsheetArray, 2)
        line = line & spreadsheetArray(r, c) & Chr(9)
    Next c
    
    Print #1, line
Next r

Close #1

运行后将生成的excel.txt内容粘贴到新工作表，与原表对比发现数据缺失。进一步排查发现：几乎所有缺失数据都来自含UDF（用户自定义函数）的单元格，包括Control行的十六进制内容也是由UDF生成的。明明Excel已正确显示这些单元格的求值结果，为何Range.Value无法获取？

原因分析 #

Range.Value读取值时依赖Excel的计算缓存，UDF出现以下情况会导致缓存异常，进而让Value读取为空：

• UDF未标记为易失性，且关联数据源未触发重算，Excel未更新该单元格的计算结果缓存；
• UDF执行时返回Empty或Null，但Excel界面保留了旧的计算结果；
• 工作表处于手动计算模式，UDF结果未被重新计算，Value读取的是旧的空值。

解决办法 #

1. 强制重算目标区域
   在读取Range.Value前触发目标区域重算，确保UDF结果更新到缓存：

' 在赋值数组前添加该行
ws.Range(ws.Cells(1, 1), ws.Cells(lastRow, lastCol)).Calculate
spreadsheetArray = ws.Range(ws.Cells(1, 1), ws.Cells(lastRow, lastCol)).Value

2. 改用Range.Value2或Range.Text

• Range.Value2是Value的轻量版本，不处理货币/日期格式，对UDF返回的数值兼容性更好；
• Range.Text直接读取单元格显示的文本，不受计算缓存影响，但会保留单元格格式。
  示例替换：

spreadsheetArray = ws.Range(ws.Cells(1, 1), ws.Cells(lastRow, lastCol)).Value2

3. 优化UDF逻辑
   确保UDF返回明确值，避免返回Empty或Null；若UDF依赖动态内容，添加Application.Volatile True标记为易失性，保证每次计算刷新结果：

Function MyCustomUDF(inputVal As Variant) As Variant
    Application.Volatile True ' 标记为易失性函数
    ' UDF核心逻辑
    If inputVal = "" Then
        MyCustomUDF = "" ' 返回明确空值，避免Empty
    Else
        MyCustomUDF = inputVal * 2 ' 示例计算逻辑
    End If
End Function

4. 检查计算模式
   若工作表为手动计算模式，切换为自动计算或手动触发全表重算：

Application.Calculation = xlCalculationAutomatic
' 或手动全表重算
Application.CalculateFull

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