20-向VBA数组中写入数据

一、向一维数组写入值

Sub t1()
    Dim arr(1 To 10)
    arr(2) = 190
    arr(10) = 5
End Sub

二、向二维数组写入和读取值

Sub t1()
    Dim arr(1 To 5, 1 To 4)
    Dim x, y
    For x = 1 To 5
        For y = 1 To 4
            arr(x, y) = Cells(x, y)
        Next y
    Next x
    MsgBox arr(3, 1)
End Sub

三、动态数组

当不确定写入的数组大小时,可以使用动态数组,下例就是运用End属性来获取区域的行和列数并传入数组

Sub t1()
    Dim arr(), r, c, x, y
    r = Application.Worksheets("sheet1").Range("a10").End(xlUp).row
    c = Application.Worksheets("sheet1").Range("a1").End(xlToRight).column
    ReDim arr(r, c)
    For x = 1 To r
        For y = 1 To c
            arr(x, y) = Cells(x, y)
        Next y
    Next x
End Sub

四、批量写入

Sub t1()
    Dim arr()
    arr() = Array(1, 2, 3, "a")
End Sub

Sub t2()
    Dim arr()
    arr() = Range("a1:d5")
End Sub
未经允许不得转载:@He » 20-向VBA数组中写入数据

登录

找回密码

注册