excel - 如何在 vba 中使用 Redim Preserve

Question

Sub C()
    
    Dim V
    
    For K = 7 To C_Count
      If 0 < Cells(9, K) And Cells(9, K) < 100 Then
        lngV = lngV + 1
        ReDim Preserve V(lngV)
        V(lngV) = Cells(9, K)
      End If
    
    Next
End Sub

我只想在 Cells(9,k) 大于 0 且小于 100 时才向数组添加一个值，但该代码会出现错误。有人可以帮我解决这个问题吗？

标签： excelvba

Answer 1

使用ReDim Preserve

• 您需要使用括号声明变量才能“应用”第一个变量ReDim Preserve：Dim V() As Variant
• 请注意，这可以写得更有效，即通过创建对行范围的引用并将其列数用于ReDim数组。最后，只需要一个或一个都不ReDim Preserve需要。

Option Explicit

Sub WriteToZeroBasedArray()
    
    Dim V() As Variant ' Note the parentheses!
    Dim Index As Long
    Dim cCount As Long: cCount = 10 ' e.g.
    
    Dim c As Long
    For c = 7 To cCount
        If 0 < Cells(9, c).Value And Cells(9, c).Value < 100 Then
            ReDim Preserve V(Index)
            ' A safer way (Option Base related):
            'ReDim Preserve V(0 To Index)
            V(Index) = Cells(9, c).Value
            Index = Index + 1
        End If
    Next c
    
    If Index = 0 Then Exit Sub
    
    Debug.Print Join(V, vbLf)

End Sub
    
Sub WriteToOneBasedArray()
    
    Dim V() As Variant ' Note the parentheses!
    Dim Index As Long
    Dim cCount As Long: cCount = 10 ' e.g.
    
    Dim c As Long
    For c = 7 To cCount
        If 0 < Cells(9, c).Value And Cells(9, c).Value < 100 Then
            Index = Index + 1
            ReDim Preserve V(1 To Index)
            V(Index) = Cells(9, c).Value
        End If
    Next c
    
    If Index = 0 Then Exit Sub
    
    Debug.Print Join(V, vbLf)

End Sub