我正在努力使UDF函数在其引用单元格更新时更新。
这是代码:
Function ConCatRange(CellBlock As Range) As String
Application.Volatile True
Dim cell As Range
Dim sbuf As String
For Each cell In CellBlock
On Error GoTo fred
If Len(cell.Text) > 1 Then sbuf = sbuf & cell.Text & Chr(10)
Next
ConCatRange = Left(sbuf, Len(sbuf) - 1)
fred:
End Function
但是,当引用单元格为空时,它不会显示#VALUE!-这就是我想要的。相反,它不会更新和保留上次填充单元格的数据。
谢谢!
如果要查看#VALUE!
,请删除On Error
语句。这将允许Left()
功能报告其故障。
试试这个:
Function ConCatRange(CellBlock As Range) As String
Application.Volatile True
Dim cell As Range
Dim sbuf As String
For Each cell In CellBlock
On Error GoTo exitErr
If Len(cell.Text) = 0 Then GoTo exitErr
If Len(cell.Text) > 1 Then sbuf = sbuf & cell.Text & Chr(10)
Next
ConCatRange = Left(sbuf, Len(sbuf) - 1)
Exit Function
exitErr:
ConCatRange = CVErr(xlErrValue)
End Function
在循环中,您检查文本长度是否为0,如果找到,则返回CVErr(xlErrValue(-也称为"#VALUE!">