My Excel Project(3) Gao Feng

[gaofengld]

Util--------------------------------------------------------------------------------

Public Function IsNotNull(value As Variant) As Boolean

If IsNull(value) Then

IsNotNull = False

Else

If IsEmpty(value) Then

IsNotNull = False

Else

If value = "" Then

IsNotNull = False

Else

IsNotNull = True

End If

End If

End If

End Function

Public Function DeleteLinefeed(ByVal value As Variant) As Variant

If IsNotNull(value) Then

value = Replace(Replace(value, Chr(10), ""), Chr(13), "")

Else

value = ""

End If

DeleteLinefeed = value

End Function

Public Function IsInitialArray(ByRef arrays As Variant) As Boolean

If IsArray(arrays) Then

If IsEmpty(arrays(0)) Then

IsInitialArray = False

Else

IsInitialArray = True

End If

Else

IsInitialArray = False

End If

End Function

Public Function AppendDimArray(ByRef arrays As Variant, value As Variant, row As Variant, column As Variant)

Dim tempArray As Variant

Dim i As Integer

Dim j As Integer

If IsArray(arrays) Then

If row > UBound(arrays) Or column > UBound(arrays, 2) Then

ReDim tempArray(1 To UBound(arrays), 1 To UBound(arrays, 2))

For i = 1 To UBound(arrays)

For j = 1 To UBound(arrays, 2)

tempArray(i, j) = arrays(i, j)

Next j

Next i

If row > UBound(arrays) And column <= UBound(arrays, 2) Then

ReDim arrays(1 To row, LBound(arrays, 2) To UBound(arrays, 2))

ElseIf row <= UBound(arrays) And column > UBound(arrays, 2) Then

ReDim arrays(LBound(arrays) To UBound(arrays), 1 To column)

End If

For i = 1 To UBound(tempArray)

For j = 1 To UBound(tempArray, 2)

arrays(i, j) = tempArray(i, j)

Next j

Next i

End If

Else

ReDim arrays(1 To row, 1 To column) As Variant

End If

arrays(row, column) = value

End Function

Public Function AppendArray(ByRef arrays As Variant, ByVal value As Variant)

Dim arrayTemp As Variant

Dim flag As Boolean

Dim i As Long

If IsArray(arrays) Then

If arrays(UBound(arrays)) <> "" Then

flag = False

Else

For i = LBound(arrays) To UBound(arrays)

If arrays(i) = "" Then

arrays(i) = value

flag = True

Exit For

End If

Next i

End If

If flag = False Then

ReDim arrayTemp(1 To UBound(arrays) + 1)

For i = LBound(arrays) To UBound(arrays)

arrayTemp(i) = arrays(i)

Next i

arrayTemp(UBound(arrays) + 1) = value

ReDim arrays(1 To UBound(arrayTemp))

arrays = arrayTemp

arrayTemp = Null

End If

Else

ReDim arrays(1 To 1)

arrays(0) = value

End If

End Function

Public Function AppendLinesOFArray(ByRef array1 As Variant, ByRef array2 As Variant)

Dim result As Variant

Dim i As Long

Dim j As Long

Dim k As Long

If IsArray(array2) Then

If IsArray(array1) Then

ReDim result(LBound(array1) To (UBound(array1) + UBound(array2)), LBound(array1, 2) To UBound(array1, 2))

For i = LBound(array1) To UBound(array1)

For j = LBound(array1, 2) To UBound(array1, 2)

result(i, j) = array1(i, j)

Next j

k = k + 1

Next i

For i = LBound(array2) To UBound(array2)

For j = LBound(array2, 2) To UBound(array2, 2)

result(k + i, j) = array2(i, j)

Next j

Next i

ReDim array1(LBound(result) To UBound(result)) As Variant

array1 = result

Set result = Nothing

Else

ReDim array1(LBound(array2) To UBound(array2), LBound(array2, 2) To UBound(array2, 2))

array1 = array2

End If

End If

End Function

Public Function IsNotArray(ByRef arrays As Variant) As Boolean

If IsArray(arrays) Then

IsNotArray = False

Else

IsNotArray = True

End If

End Function

Public Function AppendColumnToArray(ByRef arrays As Variant, values As Variant, column As Integer)

Dim result As Variant

Dim valueArray As Variant

Dim i As Long

Dim j As Long

ReDim result(LBound(arrays) To UBound(arrays), LBound(arrays, 2) To (UBound(arrays, 2) + 1))

ReDim valueArray(LBound(arrays) To UBound(arrays))

If IsNotArray(values) Then

For i = LBound(valueArray) To UBound(valueArray)

valueArray(i) = values

Next i

Else

For i = LBound(values) To UBound(values)

valueArray(i) = values(i)

Next i

End If

For i = LBound(result) To UBound(result)

For j = LBound(result, 2) To UBound(result, 2)

If j < column Then

result(i, j) = arrays(i, j)

ElseIf j = column Then

result(i, j) = valueArray(i)

Else

result(i, j) = arrays(i, j - 1)

End If

Next j

Next i

ReDim arrays(LBound(result) To UBound(result))

arrays = result

Set result = Nothing

End Function

Public Function SplitStr(str As Variant, flag As Variant) As Variant

Dim result As Variant

Dim resultTmp As Variant

Dim i As Integer

resultTmp = Split(str, flag)

ReDim result(1 To UBound(resultTmp) + 1)

For i = LBound(result) To UBound(result)

result(i) = resultTmp(i - 1)

Next i

Set resultTmp = Nothing

SplitStr = result

End Function

Util--------------------------------------------------------------------------------