在 VBA 中使用范围数组 - Excel
VBA 是否支持使用范围变量数组?
dim rangeArray() as range
dim count as integer
dim i as integer
count = 3
redim rangeArray(1 to count)
for i = 1 to count
msgbox rangeArray(i).cells(1,1).value
next
我无法让它在这种类型的应用程序中工作。我想按一定顺序存储一系列范围作为“主副本”。然后我可以添加、删除、排序或对该数组执行任何操作,然后将其打印到 Excel 中的一系列范围。 excel 似乎并不支持这一点 - 它只是强制您将数据存储在电子表格中,并且您必须重新读取它才能使用它。
Does VBA support using an array of range variables?
dim rangeArray() as range
dim count as integer
dim i as integer
count = 3
redim rangeArray(1 to count)
for i = 1 to count
msgbox rangeArray(i).cells(1,1).value
next
I can't get it to work in this type of application. I want to store a series of ranges in a certain order as a "master copy". I can then add, delete, sort or do whatever to this array and then just print it out to a series of ranges in excel. It doesn't seem like excel supports this - it just forces you to store your data in the spreadsheet and you have to reread it in order to use it.
如果你对这篇内容有疑问,欢迎到本站社区发帖提问 参与讨论,获取更多帮助,或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱,如果其他用户或者作者回复了您的评论,将不能在第一时间通知您!
发布评论
评论(3)
不,数组不能容纳对象。但是对象可以容纳对象。我认为您可能想要的是一个由各种特定的其他 Range 对象组成的 Range 对象。在此示例中,rMaster 是我的“数组”,包含三个单元。根据我新发现的知识,数组可以保存范围(thnx jtolle),下面是如何在数组中存储范围并对它们进行排序的示例
No, arrays can't hold objects. But oObjects can hold objects. I think what you may want is a Range object that consists of various specific other Range object. In this example, rMaster is my "array" that holds three cells.With my new found knowledge that arrays can hold ranges (thnx jtolle), here's an example of how you would store ranges in an array and sort them
目前尚不完全清楚您想要做什么,但是...
如果您想要一个集合,为什么不使用 VBA 集合对象?
Collection.Item 可以是任何对象,包括 Range。
Range对象不保存数据;它保存对工作表单元格的引用。如果您想要集合中的范围内容,则必须将它们复制到工作表中或从工作表中复制它们。
与 Java 一样,VBA 变量是短暂的,无论是在数组还是集合中。如果您想关闭文件并在再次打开文件时保留数据,则必须将其放在工作表单元格中。工作表是您的持久机制。
我要在这里迈出一大步,所以如果我离得很远,请忽略我。我认为您正在寻找的建议设置一个单独的工作表作为您的“数据库”,其中填充了保存原始数据的列表/表对象。在它的前面,是您的“用户表”,您可以在其中参考数据库表中的数据来执行有趣的操作。说出一切。
It's not entirely clear what you want to do, but...
If you want a collection, why not use a VBA Collection Object?
A Collection.Item can be any object, including a Range.
A Range object doesn't hold data; it holds a reference to worksheet cells. If you want the Range contents in your collection, you'll have to copy them to and from the worksheet.
As with Java, your VBA variables are ephemeral, whether in an Array or Collection. If you want to close the file and have the data there when you open it again, you have to have it in worksheet cells. The worksheets are your persistence mechanism.
I'm going to take a big leap here so if I'm way off, ignore me. What I think you're looking for suggests setting up a separate worksheet as your "database", populated with List/Table objects holding your raw data. In front of that, is your "user sheet" where you do the interesting stuff, referring to the data in the database sheet. Name everything.
我不太清楚你在说什么。
如果您询问是否有能力创建映射到任何内容并独立存在的
Range
,那么不,没有办法。Range
对象只是引用工作表的特定区域的对象。它没有任何自己的存储空间或其他东西。Range
类的多个不同实例也可以引用同一工作表区域。如果您只想在数组中存储一些引用,那就没问题,那就这么做吧。您的代码的唯一问题是您在使用数组元素之前没有对其进行初始化:由于
Range
是引用类型,因此所有元素都通过Nothing
进行初始化默认。It's not completely clear for me what you're talking about.
If you're asking about an ability to create
Range
s that map to nothing and exist on their own, then no, there's no way. ARange
object is just something that refers to a certain area of a worksheet. It doesn't have any storage of its own or something. Several different instances ofRange
class can refer to the same worksheet area, too.And if you just want to store some references in an array, then that's fine, go for it. The only problem with your code is that you don't initialize the array elements before using them: as the
Range
is a reference type, all elements get initialized withNothing
s by default.