arrays - VBA数组故障错误9脚本超出范围

Question

感谢您阅读我的问题，

我收到了一个包含大约 250k 条目的列表，以及每个条目的名称和登录日期，以显示他们登录的时间。我的任务是找出哪些用户连续几天登录，登录频率和次数。

即 Bob smith 连续 3 天一次，连续 5 天 3 次。
joe smith连续8天一次，连续5天8次
ETC

我是 VBA 的新手，一直在努力编写一个程序来做到这一点。
代码:

Option Explicit

Option Base 1

Sub CountUUIDLoop()

    Dim UUID As String
    Dim Day As Date
    Dim Instance() As Variant
    ReDim Instance(50, 50)
    Dim CountUUID As Variant
    Dim q As Integer
    Dim i As Long
    Dim j As Long
    Dim f As Integer
    Dim g As Integer
    Dim LastRow As String
    f = 1
    q = 1
    g = 2

        LastRow = Cells.Find("*", [A1], , , xlByRows, xlPrevious).Row
        For i = q To LastRow
            UUID = Cells(i, "A")
            Instance(f, 1) = UUID

            g = 2
            For j = 1 To LastRow
                If UUID = Cells(j, "A") Then
                    Instance(f, g) = Cells(j, "B")
                    g = g + 1
                End If

            Next j
            f = f + 1
            q = g - 1
        Next i

End Sub

此代码的目标是遍历条目并将它们存储在数组“Instance”中，这样​​ 2D 数组看起来像 [UUID1, B1, B2, B3]
[UUID2、B1、B2、B3、B4]
[UUID3，B1，B2]

UUID 是用户，B1 代表用户登录的日期，b2 代表他们下次登录的日期，等等。有些用户的日期比其他用户多或少。

我的主要问题是设置数组，因为我不断收到不同的错误。我不确定如何定义这个二维数组，部分原因是会有超过 30 000 行，每行有 1->85 列。

任何帮助表示赞赏，让我知道是否需要进一步澄清。再一次，这是我第一次使用 VBA，所以如果我一直在做的一切都是错误的，我很抱歉。

附言我使用 ReDim Instance (50,50) 作为测试，看看我是否可以通过预定义使其工作，但发生了同样的错误。再次感谢!

Answer 1

我建议使用集合和字典而不是数组。下面的代码将以与您想要的方式非常相似的方式构造数据。

Sub collect_logins_by_user_()
    'you need to enable the microsoft scripting runtime
    'in tools - references
    'assuming unique ids are in col A and there are no gaps
    'and assuming dates in col B and there are no gaps
    '
    'The expected runtime for this is O(n) and I have used similar code on more than 250.000 record.
    'It still takes a while obviously, but should run just fine.
    '
    'The the data will bestructed in the following format:
    '{id_1: [d_1, d_2,...], id_2: [d_3, d_4,...], ...}

    Dim current_id As Range: Set current_id = ActiveSheet.Range("A2") 'modify range as required
    Dim logins_by_users As New Dictionary
    While Not IsEmpty(current_id)

        If Not logins_by_users.Exists(current_id.Value) Then
            Set logins_by_users(current_id.Value) = New Collection
        End If
        logins_by_users(current_id.Value).Add current_id.Offset(ColumnOffset:=1).Value
        Set current_id = current_id.Offset(RowOffset:=1)
    Wend

    'Once you have the data structured, you can do whatever you want with it.
    'like printing it to the immediate window.

    Dim id_ As Variant
    For Each id_ In logins_by_users
        Debug.Print "======================================================="
        Debug.Print id_
        Dim d As Variant
        For Each d In logins_by_users(id_)
            Debug.Print d
        Next d
    Next id_
    Debug.Print "======================================================="
End Sub