在VBA中重新设置可变维数 [英] Redim variable number of dimensions in VBA

问题描述

我在Excel宏（VBA）中有一个例子，我想对一个数组进行维度，其中每个维度的维数和行距在运行时确定。我允许用户通过为每个选项类型创建一个列并填充以下可能性来指定一系列组合选项。列表数量和选项数量在运行时通过检查工作表来确定。

某些代码需要遍历每个组合（每列中的一个选择）和我想将结果存储在一个多维数组中。

维数可能在2到6之间，所以我总是可以回到一堆if else块，如果我必须，但感觉就像应该做一个更好的方法。

我认为如果我可以在运行时构造一个字符串来构造 Redim 语句，执行字符串，但这似乎不可能。

有没有办法动态 Redim 维度数？

解决方案

我确定没有办法在单个 ReDim 语句。 选择案例可能比一堆如果...其他块稍微更干净，但你还是写出很多单独的 ReDim s。

在VBA中处理您不知道的数组他们将有多少维度是一个PITA - 以及 ReDim 不是非常灵活，还没有一个整洁的方法来测试一个数组，以查看多少维度它有（你必须循环尝试访问更高的维度和陷阱错误，或者在底层内存结构中进行攻击 - 看到这个问题）。因此，您需要跟踪维度数量，并且每次需要访问数组时也会写入长 Case 语句，因为语法将不同。

我建议创建您认为需要的维数最大的数组，然后设置任何未使用维度的元素数量到1 - 这样你每次访问数组时总是拥有相同的语法，如果需要，可以使用 UBound（）检查。这是Excel开发人员自己为 Range.Value 属性所采取的方法 - 即使对于一维属性也总是返回二维数组范围。

I have a case in an Excel macro (VBA) where I'd like to dimension an array where the number of dimensions and the bounds of each dimension are determined at runtime. I'm letting the user specify a series of combinatorial options by creating a column for each option type and filling in the possibilities below. The number of columns and the number of options is determined at run time by inspecting the sheet.

Some code needs to run through each combination (one selection from each column) and I'd like to store the results in a multidimensional array.

The number of dimensions will probably be between about 2 to 6 so I can always fall back to a bunch of if else blocks if I have to but it feels like there should be a better way.

I was thinking it would be possible to do if I could construct the Redim statement at runtime as a string and execute the string, but this doesn't seem possible.

Is there any way to dynamically Redim with a varying number of dimensions?

解决方案

I'm pretty sure there is no way of doing this in a single ReDim statement. Select Case may be marginally neater than "a bunch of If...Else blocks", but you're still writing out a lot of separate ReDims.

Working with arrays in VBA where you don't know in advance how many dimensions they will have is a bit of a PITA - as well as ReDim not being very flexible, there is also no neat way of testing an array to see how many dimensions it has (you have to loop through attempts to access higher dimensions and trap errors, or hack around in the underlying memory structure - see this question). So you will need to keep track of the number of dimensions, and write long Case statements every time you need to access the array as well, since the syntax will be different.

I would suggest creating the array with the largest number of dimensions you think you'll need, then setting the number of elements in any unused dimensions to 1 - that way you always have the same syntax every time you access the array, and if you need to you can check for this using UBound(). This is the approach taken by the Excel developers themselves for the Range.Value property - which always returns a 2-dimensional array even for a 1-dimensional Range.

这篇关于在VBA中重新设置可变维数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！