Function theNumsAnalysis(TempString As String) As Variant '数字分析
'作用: 分析 TempString 的内容,以方便处理有关系或连续的数字 (类似于Office打印中的页范围)
'参数: TempString 以“,”、“-”分隔的字符串.如:"20405,7,1-3,20304-20306"分析结果为:20405,20407,20401,20402,20403,20304,20305,20306
'返回值:- Null 没有内容时,返回null
' - 下标从0开始的1维数组
Dim theTemp As Variant, theTemp1 As Variant, theTemp2 As Variant, varitm As Variant, i As Integer, j As Long, FirstLen As Byte
Dim Temp1 As String, Temp2 As String, MinusLen As Byte
If Isnull(TempString) Then 'Null值情况
theNumsAnalysis = Null
Exit Function
End If
theTemp = Split(TempString, ",")
If InStr(theTemp(0), "-") > 0 Then '如果包含"-"
theTemp1 = Trim(Left(theTemp(0), InStr(theTemp(0), "-") - 1)) '取出"-"前面的内容
Else
theTemp1 = theTemp(0)
End If
FirstLen = Len(theTemp1)
For Each varitm In theTemp
If InStr(varitm, "-") > 0 Then
MinusLen = InStr(varitm, "-") '得到"-"位置
Temp2 = Trim(Right(varitm, Len(varitm) - MinusLen))
Temp1 = Right(Trim(Left(varitm, MinusLen - 1)), Len(Temp2))
For j = Val(Temp1) To Val(Temp2)
If i = 0 Then
theTemp2 = Left(theTemp1, FirstLen - Len(Temp2)) & j
Else
theTemp2 = theTemp2 & "," & Left(theTemp1, Abs(FirstLen - Len(Temp2))) & j
End If
i = i + 1
Next j
Else
If i = 0 Then
theTemp2 = Left(theTemp1, Len(theTemp1) - Len(varitm)) & varitm
Else
theTemp2 = theTemp2 & "," & Left(theTemp1, Len(theTemp1) - Len(varitm)) & varitm
End If
End If
i = i + 1
Next varitm
theNumsAnalysis = Split(theTemp2, ",")
End Function
调用试例:
Function a()
Dim theTemp1 As Variant
theTemp1 = theNumsAnalysis("20405,7,1-3,20304-20306")
MsgBox Join(theTemp1, "、")
End Function