如何正确使用VBA类模块的集合? [英] How do I correctly work with a collection of VBA Class Modules?

问题描述

我想创建一个机场集合.

I want to create a collection of airports.

机场有许多参数，但为简单起见，我们假设在机场舱位模块"中定义了这样的机场舱位:

An airport has many parameters, but for the sake of simplicity, let's assume an airport class is defined like this in Airport Class Module:

'Class Airport
 Public name As String ' Stores name of the airport
 Public flights As Long ' Stores number of flights in that airport

然后我的模块很大，但这是我从Excel文件中读取列并将值存储在airport集合中的部分，删除重复的部分:

Then my module is pretty big but this is the part where I read columns from an Excel file and store values in airports collection, removing duplicate ones:

Dim airports As Collection
Set airports = New Collection

'Putting airports in collection
                Dim c As Range
                For Each c In wsToCheck.Range("D:D")
                On Error Resume Next
                Dim airport As New Airport
                airport.name = c.Value
                airports.Add airport, c.Value
                On Error GoTo 0
                Next

如果我在中间

Debug.Print airport.name

Debug.Print airport.name

我知道名字了，但是我知道了

I get the name, but when I do

Debug.Print airport(1).name

Debug.Print airports(1).name

什么都不会打印(但也不会出现错误).

Nothing is printed (but no error neither).

我以前使用过一组字符串，并且可以正常工作.但是现在每个机场我需要多个字符串.

I was using a collection of strings before and it was working. But I need more than one string per airport now.

我的代码有什么问题?我在使用收藏集吗?

What is the wrong in my code? Am I using collections right?

推荐答案

您的代码有两个问题.

You have two problems with your code.

第一个是可能正在创建具有数百万个项目的Collection，因为要遍历的范围是D列(D:D)的全部.这需要绑定.

The first is that are probably creating a Collection with millions of items because the range you're iterating over is all of column D (D:D). This needs to be bound.

第二个问题是变量名airport与类Airport完全相同.这很容易混淆VBA，因此您需要为其中之一选择其他名称.

The second issue is your variable name airport is the exact same name as your class Airport. This can easily confuse VBA, so you need to choose a different name for one of them.

这是一个有效的示例:

Option Explicit

Sub test()
    Dim wsToCheck As Worksheet
    Set wsToCheck = ThisWorkbook.Sheets("Sheet1")

    Dim airportNames As Range
    Set airportNames = wsToCheck.Range("D1:D10")

    Dim airports As Collection
    Set airports = New Collection

    'Putting airports in collection
    Dim i As Long
    Dim c As Range
    For Each c In airportNames
        Dim thisAirport As Airport
        'Debug.Print c.Address & "=" & c.Value
        Set thisAirport = New Airport
        thisAirport.name = c.Value
        thisAirport.flights = i
        i = i + 1
        airports.Add thisAirport
    Next

    'now print them out
    For i = 1 To airports.Count
        Debug.Print airports(i).name & ", " & airports(i).flights
    Next i

End Sub

这篇关于如何正确使用VBA类模块的集合?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！