VBA读取Excel公式单元格显示值报类型兼容错误的解决方法

问题原因 #

当单元格内的RECHERCHEV（即VLOOKUP）公式返回#N/A这类错误值时，你用.Value读取到的是VBA内置的Error特殊类型，无法直接和字符串"#N/A"做等值比较，因此会触发类型兼容性报错。

解决方案 #

方案1：直接读取显示文本（最简单） #

直接用单元格的.Text属性替代.Value，该属性返回的就是单元格界面上实际显示的内容，天然为字符串类型，无需额外转换：

' 把原来读取val1的行替换为下面这行即可
val1 = WB1.Sheets("Avant").Cells(i, 7).Text

注意：如果单元格列宽过窄导致内容显示为###，.Text读取到的也会是###，如果要避免这个问题可以用方案2。

方案2：错误判断后转字符串（更稳妥） #

先通过IsError判断单元格是否返回错误值，再做对应处理，不受显示格式影响：

Private Sub Check_Commentaires()
    'Declarations des variables
    Dim WB1 As Workbook
    Dim i As Long, val2 As String
    Dim val1 As String
    Dim cellVal As Variant
    Set WB1 = ActiveWorkbook
    
    For i = 2 To 10000
        cellVal = WB1.Sheets("Avant").Cells(i, 7).Value
        ' 先判断是否为错误值再转字符串
        If IsError(cellVal) Then
            val1 = CStr(cellVal)
        Else
            val1 = CStr(cellVal)
        End If
        val2 = CStr(WB1.Sheets("Avant").Cells(i, 14).Value & "")
        
        If (val1 = "#N/A" And val2 = "") Or (val1 = "0" And val2 = "") Then
            ThisWorkbook.Sheets("Feuil1").Cells(4, 6).Value = "NOK"
            WB1.Close False
            Exit Sub
        Else
            ThisWorkbook.Sheets("Feuil1").Cells(4, 6).Value = "OK"
        End If
    Next
    WB1.Close False
End Sub

额外优化：原代码里的变量没有指定具体类型，统一指定类型后也能减少类型兼容类的报错概率。

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