Error 424 with Global array.Length for Excel VBA

Question

I keep getting a runtime Error 424 when I try to access arrayCount.Length. I think this might have to do with the fact that arrayCount was declared as a Public Variant. How do I resolve this bug?

' Initialize variables
Private counter As Integer
Private Account As String
Private chartSize As Integer

Public arrayCount As Variant
Public arrayAccounts As Variant

' Iterate over each entry row, determining the corresponding Account

Sub RowInsert()

' Initialize ArrayCount with starting values of -1
arrayCount = Array(-1, -1, -1, -1, -1, -1, -1, -1, -1)
arrayAccounts = Array("Cash", "Equipment", "Prepaid Rent", "Inventory", "Marketable Securities", "Accounts Recievable", "Accounts Payable", "Bonds Payable", "Common Stock")

' BUG HERE
chartSize = arrayAccounts.Length

' Continued...

End Sub

'Continued...

Answer 1

As an alternate approach I compiled all the information using a Dictionary to group the data. Each key in the Dictionary has an ArrayList associated with it. Each element in the ArrayList is an 1 dimensional array of data that holds the Date, Debit and Credit information.

The Ledger is then searched for each Key in the Dictionary. If found the array that the Dictionary's ArrayList is extracted and transposed twice to convert it to a standard 2 dimensional array. The array is then inserted into worksheet.

Sub CompileData()
    Application.ScreenUpdating = False
    Dim x As Long
    Dim Data, Key
    Dim r As Range
    Dim dLedger As Object, list As Object

    Set dLedger = CreateObject("Scripting.Dictionary")
    With Worksheets("Journal")
        For x = 2 To .Range("B" & .Rows.Count).End(xlUp).Row
            Key = Trim(.Cells(x, 2))
            If Not dLedger.Exists(Key) Then
                Set list = CreateObject("System.Collections.ArrayList")
                dLedger.Add Key, list
            End If

            dLedger(Key).Add Array(.Cells(x, 1).Value, .Cells(x, 3).Value, .Cells(x, 4).Value)

        Next
    End With

    With Worksheets("Ledger")

        For Each Key In dLedger
            Set r = Intersect(.Columns("A:C"), .UsedRange).Find(What:=Key)

            If Not r Is Nothing Then
                Set list = dLedger(Key)
                Data = list.ToArray
                Data = Application.Transpose(Data)

                x = dLedger(Key).Count
                With r.Offset(2).Resize(x, 3)
                    .Insert Shift:=xlDown, CopyOrigin:=r.Offset(1)
                    .Offset(-x).Value = Application.Transpose(Data)
                    .Offset(0, 1).Resize(1, 1).FormulaR1C1 = "=""Bal. "" & TEXT(SUM(R[-" & x & "]C:R[-1]C)-SUM(R[-" & x & "]C[1]:R[-1]C[1]),""$#,###"")"
                    r.Offset(1).EntireRow.Delete
                End With
            End If
        Next

    End With
    Application.ScreenUpdating = True

End Sub

Answer 2

I coerced my previous answer to use collections instead of a Dictionary and Arraylists; so that it would be Mac compatible.

Sub MacCompileData()
    Application.ScreenUpdating = False

    Dim lastRow As Long, x As Long
    Dim data, Key
    Dim r As Range
    Dim cLedger As Collection, cList As Collection
    Set cLedger = New Collection

    With Worksheets("Journal")
        lastRow = .Range("B" & .Rows.Count).End(xlUp).Row

        For x = 2 To lastRow
            Key = Trim(.Cells(x, 2))
            On Error Resume Next
            Set cList = cLedger(Key)
            If Err.Number <> 0 Then
                Set cList = New Collection
                cLedger.Add cList, Key
            End If
            On Error GoTo 0

            cLedger(Key).Add Array(.Cells(x, 1).Value, .Cells(x, 3).Value, .Cells(x, 4).Value)

        Next
    End With

    With Worksheets("Ledger")
        For Each r In .Range("A1", .Range("A" & .Rows.Count).End(xlUp))

            If r <> "" Then

            On Error Resume Next
            Key = Trim(r.Text)
            data = getLedgerArray(cLedger(Key))

            If Err.Number = 0 Then
                Set list = cLedger(Key)
                x = cLedger(Key).Count
                With r.Offset(2).Resize(x, 3)
                    .Insert Shift:=xlDown, CopyOrigin:=r.Offset(1)
                    .Offset(-x).Value = data
                    .Offset(0, 1).Resize(1, 1).FormulaR1C1 = "=""Bal. "" & TEXT(SUM(R[-" & x & "]C:R[-1]C)-SUM(R[-" & x & "]C[1]:R[-1]C[1]),""$#,###"")"
                    r.Offset(1).EntireRow.Delete
                End With
            End If

            On Error GoTo 0
            End If
        Next

    End With
    Application.ScreenUpdating = True

End Sub

Function getLedgerArray(c As Collection)
    Dim data
    Dim x As Long
    ReDim data(1 To c.Count, 1 To 3)

    For x = 1 To c.Count
        data(x, 1) = c(x)(0)
        data(x, 2) = c(x)(1)
        data(x, 3) = c(x)(2)
    Next
    getLedgerArray = data
End Function