EXCEL VBA错误：“编译错误：预期数组”； [英] EXCEL VBA Error: "Compile Error: Expected Array"

问题描述

有人可以帮助我吗？

在Excel工作簿中处理数组时，我遇到了编译错误（...：预期数组）。

I have been getting a compile error (...: "Expected Array") when dealing with arrays in my Excel workbook.

基本上，我有一个母亲数组（2D，变体类型）和四个婴儿数组（1D，双精度类型）。被调用的子例程创建公共声明的数组，我的主宏最终将这些数组用于显示目的。不幸的是，最后一个婴儿阵列无法解决（给出了编译错误：期望的阵列）。奇怪的是，如果我删除了最后一个婴儿阵列（ final（按照声明/定义的顺序），倒数第二个婴儿阵列将开始淘汰。

Basically, I have one 'mother' array (2D, Variant type) and four 'baby' arrays (1D, Double type). The called subroutine creates the publicly declared arrays which my main macro ends up using for display purposes. Unfortunately, the final of the baby arrays craps out (giving the "Compile Error: Expected Array"). Strangely, if I remove this final baby array ('final' - as in the order of declaration/definition) the 2nd to last baby array starts crapping out.

这是我的代码：

 Public Mother_Array() as Variant, BabyOne_Array(), BabyTwo_Array(), BabyThree_Array(), BabyFour_Array() as Double 'declare may other variables and arrays, too

Sub MainMacro()
    'do stuff

   Call SunRaySubRoutine(x, y)

    'do stuff

    Range("blah") = BabyOne_Array: Range("blahblah") = BabyTwo_Array
    Range("blahbloh" = BabyThree_Array: Range("blahblue") = BabyFour_Array

End Sub

Sub SunRaySubRoutine(x,y)
    n = x * Sheets("ABC").Range("A1").Value + 1

    ReDim Mother_Array(18, n) as Variant, BabyOne_Array(n), BabyTwo_Array(n) as Double
    ReDim BabyThree_Array(n), BabyFour_Array(n) as Double

    'do stuff

    For i = 0 to n

        BabyOne_Array(i) = Mother_Array(0,i)
        BabyTwo_Array(i) = Mother_Array(2,i)
        BabyThree_Array(i) = Mother_Array(4,i)
        BabyFour_Array(i) = Mother_Array(6,i)
    Next        

End Sub

我试图将所有数组声明为Variant类型，但无济于事。我试图给BabyFour_Array（）一个不同的名称，但无济于事。

I have tried to declare all arrays as the Variant type, but to no avail. I have tried to give BabyFour_Array() a different name, but to no avail.

真正令人奇怪的是，即使我注释掉了BabyFour_Array（）的组成部分，该数组的每个元素仍为零值。

What's really strange is that even if I comment out the part which makes the BabyFour_Array(), the array still has zero values for each element.

还有一点奇怪的是，第一个婴儿阵列永远不会发cra（尽管第二个婴儿阵列不会发once一次（可能是30次）。

What's also a bit strange is that the first baby array never craps out (although, the 2nd one crapped out once (one time out of maybe 30).

创可贴：作为一个临时解决方案，我刚刚公开声明了第五个虚拟数组（不会被填充或重新标注尺寸）。系统出现编译错误：期望的数组。

BANDAID: As a temporary fix, I just publicly declared a fifth dummy array (which doesn't get filled or Re-Dimensioned). This fifth array has no actual use besides tricking the system out of having the "Compile Error: Expected Array".

有人知道是什么原因导致Excel VBA出现此编译错误：期望的数组问题吗？

Does anyone know what's causing this "Compile Error: Expected Array" problem with Excel VBA?

谢谢

Elias

推荐答案

在全局声明中，您只将最后一个婴儿数组声明为 Double 。您将前三个声明为 Variants 。但是在子例程中，您正在重命名，其中一胎和三胎为Variants，二胎和四胎为Doubles。

In your global declarations you are only declaring the last baby array as Double. You're declaring the first three as arrays of Variants. But in the subroutine you are Redimming babies one and three as Variants, and two and four as Doubles.

请参阅此 Chip Pearson页面，然后向下滚动至注意使用一个Dim语句声明的变量。

See this Chip Pearson page and scroll down to "Pay Attention To Variables Declared With One Dim Statement."

在VBA中，当您声明以下内容时：

In VBA when you declare something like:

Dim x, y, z as Long

只有z是 Long ，其余的是 Variants 。正确的格式为：

only z is a Long, the rest are Variants. The correct form is:

Dim x为Long，Y为Long，Z为Long （或

您可以看到这将如何导致您描述的行为，即最后一个错误，而其他错误没有。

You can see how this would cause the behavior you describe, i.e., the last one errors, but the others don't.

这篇关于EXCEL VBA错误：“编译错误：预期数组”；的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！