vba程序实例

Sub ggsmart()

Dim i%, xrow%, j%, xcount%

Dim arr() As String

xrow = [a65536].End(3).Row '最后一个非空单元格行号

j = 1 '数组索引号

xcount = Application.WorksheetFunction.CountIf([a1:a65536], "张*") '统计有多少姓张的学生

ReDim arr(1 To xcount) '重新定义数组大小，元素共有xcount个

For i = 1 To xrow

If Left(Cells(i, 1).Value, 1) = "张" Then

arr(j) = Cells(i, 1).Value '给数组元素赋值

j = j + 1 '索引号加1

End If

Next i

[b1:b65536].Clear '清除原有数据

[b1].Resize(xcount, 1) = Application.WorksheetFunction.Transpose(arr) '将数组输入单元格区域

End Sub
Sub a()

Dim arr(1 To 100)

For i = 1 To 100

arr(i) = Cells(i, 1)

Next

End Sub

Sub ggsmart()

Dim arr(10 To 50)

MsgBox "数组的上标是：" & UBound(arr) & Chr(13) _

& "数组的下标是：" & LBound(arr) & Chr(13) _

& "数组的元素个数是：" & UBound(arr) - LBound(arr) + 1

End Sub

Sub test1()

'创建数组，并赋值

Dim arr(1 To 60000), i As Long

For i = 1 To 60000

arr(i) = i

Next i

'将数组的值写入单元格（A列）

[a1:a65536].Clear '清除原有数据

Dim irow As Long

Dim startime As Double

startime = Timer

For irow = 1 To 60000

Cells(irow, 1) = arr(irow)

Next irow

MsgBox "数组写入共用了" & Timer - startime & "秒！"

End Sub