当前位置：文江博客话题详情

Excel-VBA - VBA 中有类似 Javas Set 容器的东西吗？

发布于 2024-10-13 05:00:45 字数 118 浏览 4 评论 0原文

VBA中有类似Java的Set容器的东西吗？我找不到任何东西，Google 似乎也没有帮助，因为 set 是 VBA 中的保留工作。

任何想法都会很棒。现在我唯一的选择是字典或数组。

谢谢。

原文

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

客…行舟 2024-10-20 05:00:45

VBA 有一个内置的“Collection”对象，许多人认为 MS Scripting Runtime 中的“Dictionary”对象足够标准，它本质上是 VBA 的一部分。

然而，两者都不会强制一般对象的唯一性。是否可以让它们为您的应用程序强制执行唯一性取决于细节。

例如，如果您想要一组字符串，这很容易。只需使用“字典”，并使用它的键作为您的“集合”。 “Dictionary”有一个“Exists”方法，因此编写您自己的有限“Set”类非常容易，其中所有实际工作都由包含的“Dictionary”完成。（如果您使用的是 Excel，这将适用于任何简单的 Variant 值 - 字符串、数字、布尔值、错误。）

如果您有对象实例，事情可能会变得更加复杂。 “字典”对象可以使用对象作为键，但它使用对象标识作为其相等性测试。例如，我刚刚在 VBA 立即窗口中输入以下内容：（

set d=new Dictionary
set c1=new Collection
set c2=c1
d(c1) = 42
fv d
<Dictionary: keys: #V(0..0){<Collection: 0 elems: >}, items: #V(0..0){42%}>
d(c2)=99
fv d
<Dictionary: keys: #V(0..0){<Collection: 0 elems: >}, items: #V(0..0){99%}>
set c3=new Collection
d(c3)=101
fv d
<Dictionary: keys: #V(0..1){<Collection: 0 elems: >,<Collection: 0 elems: >}, items: #V(0..1){99%,101%}>

“fv”是我用于调试的 VBA 例程 - 它只是打印出内容的字符串表示形式。）您可以看到相同的对象被视为相等，但完全相同有价值的对象不是。

如果您需要一组具有自定义相等性测试的集合，则需要编写重要的代码。但是，如果您至少可以编写从对象实例到可用作字典键的值的映射，那么您仍然可以避免编写自己的“这个东西是否已经是集合的成员？”代码。

VBA 中的哈希表/关联数组

VBA has a 'Collection' object built in, and many people consider the 'Dictionary' object from the MS Scripting Runtime to be sufficiently standard that it's essentially part of VBA.

However, neither will enforce uniqueness for general objects. Whether or not you can make them enforce uniqueness for your application depends on the details.

For example, if you want a set of strings, it's easy. Just use a 'Dictionary', and use its keys as your "set". 'Dictionary' has an 'Exists' method, so it would be pretty easy to write your own limited 'Set' class where all of the real work is done by a contained 'Dictionary'. (If you're using Excel, this will work with any simple Variant value - strings, numbers, booleans, errors.)

If you have object instances, things can get more complicated. The 'Dictionary' object can use objects as keys, but it uses object identity as its equality test. For example, I just typed this into the VBA Immediate window:

set d=new Dictionary
set c1=new Collection
set c2=c1
d(c1) = 42
fv d
<Dictionary: keys: #V(0..0){<Collection: 0 elems: >}, items: #V(0..0){42%}>
d(c2)=99
fv d
<Dictionary: keys: #V(0..0){<Collection: 0 elems: >}, items: #V(0..0){99%}>
set c3=new Collection
d(c3)=101
fv d
<Dictionary: keys: #V(0..1){<Collection: 0 elems: >,<Collection: 0 elems: >}, items: #V(0..1){99%,101%}>

('fv' is a VBA routine I use for debugging - it just prints out a string representation of stuff.) You can see that identical objects are treated as equal, but identically valued objects aren't.

If you need a set that has a custom equality test, you'll need to write non-trivial code. However, if you can at least write a mapping from object instance to a value that can be used as a Dictionary key, you can still avoid having to write your own "is this thing already a member of the set?" code.

Some relevane links:

Is there a way to write an equality test for a VBA class with private members without exposing knowledge of the existence of those private members?

Hash Table/Associative Array in VBA

回复收藏 0 原文

~没有更多了~