vba 此阵列已固定或临时锁定
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/7709400/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
This array is fixed or temporarily locked
提问by user930679
I am using split function and assigning the value in a variable and running the code in loop after few iterations its giving an error of "This array is fixed or temporarily locked (Visual Basic)"..
我正在使用 split 函数并在变量中分配值并在几次迭代后在循环中运行代码，它给出了“此数组已固定或临时锁定（Visual Basic）”的错误..
e.g; here value of movies_cat1 read from excel is in form of this------ "Movies->List All Movies , Movies->World Cinema->Asia , Movies->Movies by Language->Sinhalese , Movies->Drama"
例如; 这里从excel中读取的movies_cat1的值是这样的------“电影->列出所有电影，电影->世界电影->亚洲，电影->电影按语言->僧伽罗语，电影->戏剧”
For crow = 1 To 100
Value = Worksheets("Movies_categories").Range("A" & crow).Value
cat_final = Worksheets("Movies_categories").Range("B" & crow).Value
If Value = "y" Or Value = "Y" Then
'Loop for reading the data from tabsheet- Movies
For crowss = 5 To 3000
movies_cat1 = Worksheets("Movies").Range("B" & crowss).Value
movies_language = Worksheets("Movies").Range("C" & crowss).Value
If movies_language = "English" Then
Temp = Split(movies_cat, ",") 'run time Error:10 occurs here..
For Each boken_c In Temp
flag = 0
boken_c = Trim(boken_c)
If RTrim(LTrim(boken_c)) = LTrim(RTrim(cat_final)) Then
flag = 1
GoTo Line4:
End If
Next boken_c
End If
Next crowss
End If
Line4: Next crow
Error occurs at this statement: Temp = Split(movies_cat, ","), it says that the array is fixed or temporarily locked, because i think initially its taking 'temp' as a variable, but while returning the value of split function, variable 'Temp' becomes array after completion of first loop(i.e after crow = 6,7....)
这个语句出现错误：Temp = Split(movies_cat, ",")，它说数组是固定的或临时锁定的，因为我认为它最初将'temp'作为变量，但是在返回split函数的值时，变量'Temp'在第一个完成后变为数组循环（即在乌鸦 = 6,7 .... 之后）
回答by Deanna
Your line4label is outside the for loop on the temp variable so when you gotoit leaves it locked.
您的line4标签位于临时变量的 for 循环之外，因此当您goto将其锁定时。
You really should restructure your code to not use a goto inside the for each loop.
你真的应该重构你的代码，不要在 for each 循环中使用 goto。
Maybe:
也许：
For crow = 1 To 100
Value = Worksheets("Movies_categories").Range("A" & crow).Value
cat_final = Worksheets("Movies_categories").Range("B" & crow).Value
If Value = "y" Or Value = "Y" Then
'Loop for reading the data from tabsheet- Movies
For crowss = 5 To 3000
movies_cat1 = Worksheets("Movies").Range("B" & crowss).Value
movies_language = Worksheets("Movies").Range("C" & crowss).Value
If movies_language = "English" Then
Temp = Split(movies_cat, ",") 'run time Error:10 occurs here..
For Each boken_c In Temp
flag = 0
boken_c = Trim(boken_c)
If RTrim(LTrim(boken_c)) = LTrim(RTrim(cat_final)) Then
flag = 1
**Exit For**
End If
**If flag = 1 Then Exit For**
Next boken_c
End If
**If flag = 1 Then Exit For**
Next crowss
End If
Next crow
(Note the **d lines.)
（注意 **d 行。）
回答by OtOhWereInTrouble
I had this problem too with VBA. I cannot say I am proud of how I managed to get it, but it is supplied here just in can anyone else accidentally slips up on this.
我用 VBA 也有这个问题。我不能说我为我如何获得它感到自豪，但它提供在这里只是为了防止其他人不小心滑倒。
It is quite interesting to debug as the failure occurs at the call to a sub or function - not at the point of failure. Luckily, you can follow the code through to the offending line of the called routine before it reports the error.
调试非常有趣，因为故障发生在调用子或函数时 - 而不是在故障点。幸运的是，您可以在被调用例程报告错误之前跟踪代码到有问题的行。
Call Sub1(gArray(3))
debug.print gArray(3)
...
Sub Sub1(i as integer)
Redim gArray(0)
End sub
Clearly the VB RT is not going to like this as by the time the debug.print executes, the array dimension does not exist. Ok why the hell would you want to pass a globally declared array anyway? Guilty as charged.
显然 VB RT 不会喜欢这个，因为在 debug.print 执行时，数组维度不存在。好吧，你到底为什么要传递一个全局声明的数组呢？罪有应得。
So in the example above you get the error at the callto Sub1, but the thing causing it is the Redim in the sub.
因此，在上面的示例中，您会在调用Sub1 时遇到错误，但导致它的原因是 sub 中的 Redim。
The moral to the story is do not pass global variables as parameters to subs. Another simple fix is to declare the Sub1 slightly differently:
这个故事的寓意是不要将全局变量作为参数传递给 subs。另一个简单的解决方法是稍微不同地声明 Sub1：
Sub Sub1(ByVal i as integer)
This means the ivariable is copied (but not returned) by the Sub.
这意味着该i变量由 Sub 复制（但不返回）。
回答by Leon Rom
Thanks, Deanna for the answer! I have a similar message on ReDim Preservestatement at the last line in next fragment of code:
谢谢，迪安娜的回答！我ReDim Preserve在下一段代码的最后一行有一条关于语句的类似消息：
If StrComp(Mid(s, 1, lE), txtE, vbBinaryCompare) = 0 Then
For i = iMdl To 1 Step -1
With blks(i)
If .lnEnd = 0 Then ' ".lnEnd" is a member of blks(i)
.lnEnd = ln
GoTo NXT
End If
End With
Next
errBegWith = errBegWith & ln & ", "
NXT:
End If
'...
ReDim Preserve blks(iMdl)
And after extracting assignment .lnEnd = lnfrom inside of the Withthe program works fine:
.lnEnd = ln从With程序内部提取赋值后工作正常：
If StrComp(Mid(s, 1, lE), txtE, vbBinaryCompare) = 0 Then
For i = iMdl To 1 Step -1
If blks(i).lnEnd = 0 Then
blks(i).lnEnd = ln
GoTo NXT
End If
Next
errBegWith = errBegWith & ln & ", "
NXT:
End If
'...
ReDim Preserve blks(iMdl)
