设置等于一个范围的数组下标时，超出范围VBA错误 [英] Subscript out of range VBA error when setting an array equal to a range

问题描述

目前，我有一个范围，我希望能够进入到一个字符串数组。但是，我不知道它的工作每次我尝试用数组做任何事情的时候，我得到一个标超出范围的错误。我试着只是做了Debug.Print，看是否值进入数组或没有，但，导致同样的错误。以下是我迄今为止..

Currently, I have a range of strings that I would like to be able to enter into an array. However, I'm not sure that it's working and every time I try to do anything with the array, I get a subscript out of range error. I tried just doing a Debug.Print to see if values are going into the array or not but that resulted in the same error. Here's what I have so far..

UsedRow = ActiveWorkbook.Sheets(3).UsedRange.Rows.Count
Dim ProjectCounter As Long
Dim ArrRange As Range

'This determines the number of entries in the array (I know this part works)
i = UsedRow
ProjectCounter = 0
Do While Cells(i, 1).FormulaR1C1 <> vbNullString
    ProjectCounter = ProjectCounter + 1
    i = i - 1
Loop

'Array should have dimensions that match the number of projects
Dim ProjectArray() As Variant
ReDim ProjectArray(ProjectCounter - 1)

'Set range for array to cover
Set ArrRange = ActiveWorkbook.Sheets(3).Range("A" & UsedRow - ProjectCounter & ":A" & UsedRow)

'Populate array with projects
ProjectArray = ArrRange

For i = LBound(ProjectArray) To UBound(ProjectArray)
Debug.Print ProjectArray(i)
Next

这是建立一个数组的正确方法？如果没有，我在做什么错误？谢谢你。

Is this the right way to set up an array? And if not, what am I doing incorrectly? Thanks.

推荐答案

您可以阅读数组到preset的范围，而REDIM。声明变量没有括号。

You can read an array into a preset range without redim. Declare the variant without the parentheses.

Dim ProjectArray as Variant
ProjectArray = ArrRange

您得到的错误，因为你的阵列有2个尺寸。您需要

You get the error because your array has 2 dimensions. You need to

for I = 1 to ubound(ProjectArray)
   debug.print ProjectArray(I,1)
next I

和LBOUND将永远是1，当你做这种方式。

And LBound will always be 1 when you do it this way.

这篇关于设置等于一个范围的数组下标时，超出范围VBA错误的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！