【VBA】利用Range声明Array（一维/二维）

【说明】

B2开始到B?（中间不能有空格），定义一维数组Arr_approver()

    Dim R_sh As Worksheet
    Set R_sh = ThisWorkbook.Sheets("result") 
    
    approver_row = R_sh.Range("B2").End(xlDown).Row        
    Arr_approver = R_sh.Range("B2", Cells(approver_row, 2))
    
    For k = LBound(Arr_approver) To UBound(Arr_approver)
        key = Arr_approver(k, 1)
        ifFoundRole = FindRole(key)
        If ifFoundRole = "Y" Then
            R_sh.Cells(k + 1, A_ROLE1) = Arr_Role(MAX_COUNT_ROLE - 3)
            R_sh.Cells(k + 1, A_ROLE2) = Arr_Role(MAX_COUNT_ROLE - 2)
            R_sh.Cells(k + 1, A_ROLE3) = Arr_Role(MAX_COUNT_ROLE - 1)
            R_sh.Cells(k + 1, A_ROLE4) = Arr_Role(MAX_COUNT_ROLE)
        End If
    Next

例子：二维数组

从B2（左上角）到？？（右下角）连续无空格的区域，声明二维数组Arr()

Public Arr

Sub Test(sheetName As String)

R = List_sh.Range("B2").End(xlDown).Row
C = List_sh.Cells(1, Columns.Count).End(xlToLeft).Column

Arr = List_sh.Range("B2", Cells(R, C))

a = Arr(i,j)

 

转载于:https://www.cnblogs.com/dlsunf/p/9361514.html