Excel VBA集合和自定义数据类型 [英] Excel VBA Collections and Custom Data Types

问题描述

我想学习如何创建自定义数据类型，然后与集合一起使用。我以另一种方式解决了问题，但是这开始与一个时间表报告我是自动化。我最初想要一个具有不同数据类型的二维数组。当我不能这样做，一些研究导致了一个自定义数据类型的集合的想法。然而，我发现的例子不断推动我创建一个类。我不太舒服，似乎这应该是可行的。这是我正在寻找的东西（我开始在这个网站上找到一个例子）：

I'm trying to learn how to create a custom data type, then use that with a collection. I solved the problem another way, but this started with a timesheet report I was automating. I originally wanted a 2 dimensional array with varying data types. When I couldn't do that, some research led to the idea of a collection of custom data types. However the examples I have found keep pushing me to create a class. I am not yet comfortable with that and it seems like this should be doable. Here is kind of what I am a looking for (I started with an example I found on this site):

Option Explicit

'***** User defined type
Public Type MyType
  MyInt As Integer
  MyString As String
  MyDoubleArr(2) As Double
End Type

Public ColThings As Collection

Sub CollectionTest()
  Dim x As Integer
  Dim vrecord As MyType

  For x = 1 To 4
      vrecord.MyInt = x
      vrecord.MyString = "Matt"
      vrecord.MyDoubleArr(0) = x + 5
      vrecord.MyDoubleArr(1) = x + 6
      vrecord.MyDoubleArr(2) = x + 7
      ColThings.Add vrecord
  Next x

  For x = 1 To 4
     Debug.Assert vrecord.MyInt & " - " & vrecord.MyString & " - " &   vrecord.MyDoubleArr(0) & ", " & vrecord.MyDoubleArr(1) & ", " & vrecord.MyDoubleArr(0)
  Next x
End Sub

错误I get是：
编译错误：
只有在公共对象模块中定义的用户定义类型才能被强制转换到变体或从变体传递或传递给后缀函数

The error I get is: Compile Error: "Only user-defined types defined in public object modules can be coerced to or from a variant or passed to late-bound functions"

我不是VBA的新手，但我想尝试下一步。

I am not a novice with VBA, but I am trying to make the next step.

提前感谢。

推荐答案

再次感谢大家。我感谢帮助。我的周末教育因为我在达拉斯的天气而放慢了（周六下雨，我的互联网下雨了3个小时），母亲节和运动，但我已经开始了。

Thank you to everyone again. I appreciate the help. My weekend education has been slowed by our weather here in Dallas (3" of rain on Sat played hell with my internet), mother's day, and exercise. However, I have started.

有一个伟大的周末。

这篇关于Excel VBA集合和自定义数据类型的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！