Public Function ToArray(rngCell As Range) As Variant
Dim sFormString As String
sFormString = rngCell.Formula
Dim adReturn() As Double
ReDim adReturn(1) As Double
If Not Len(sFormString) - 3 > 0 Then
ToArray = adReturn
Exit Function
Else
sFormString = Mid(sFormString, 3, Len(sFormString) - 3)
End If
Dim vTest As Variant
vTest = Split(sFormString, ",")
ReDim adReturn(LBound(vTest) To UBound(vTest)) As Double
Dim iArrayCounter As Integer
For iArrayCounter = LBound(vTest) To UBound(vTest)
adReturn(iArrayCounter) = vTest(iArrayCounter)
Next iArrayCounter
ToArray = adReturn
End Function
3 回答
这个简短的VBA UDF应该可以胜任 .
(例如,如果带有大括号的字符串在单元格b2中,则需要在另一个单元格中写入的所有内容为= sum(toarray(b2)))
单元格限制为单个数字,字符串,逻辑或错误值 . 单个单元格不能包含数组 . 当在单个单元格公式中计算公式“= {1,2,3}”时,单元格将仅获得数组中的第一个值 .
您可以通过定义名称(例如:test)来使数组常量为命名数组常量,如下所示:
然后引用这个名字