是否可以根据VBA中预先存在的变量值声明常量?

您可以在Redim中使用变量,而不是在Dim中。实际上你不需要常数。

lr = Tables.Cells(Rows.Count, 1).End(xlUp).Row
Const constlr As Integer = lr
Dim TableNamesArr() As String
Redim TableNamesArr(2 To constlr, 1 To 4)
Dim i As Integer, j As Integer
For i = 2 To lr
    For j = 1 To 4
        TableNamesArr(i, j) = Tables.Cells(i, j).Value
    Next j
Next i

您可以通过一种简单的方式从范围中读取值,而无需循环:

Dim DirArray As Variant
DirArray = Range("a1:a5").Value

您可以通过选择或其他方法参考您的范围,而不是范围(“a1:a5”) Selection.Value 或 Activesheet.UsedRange.Value .

如果您的范围在表中,您可以尝试从 here :

Sub MultiColumnTable_To_Array()

Dim myTable As ListObject
Dim myArray As Variant
Dim x As Long

'Set path for Table variable
  Set myTable = ActiveSheet.ListObjects("Table1")

'Create Array List from Table
  myArray = myTable.DataBodyRange

'Loop through each item in Third Column of Table (displayed in Immediate Window [ctrl + g])
  For x = LBound(myArray) To UBound(myArray)
    Debug.Print myArray(x, 3)
  Next x

End Sub