功能可以在VBA中编辑的全局数组 [英] Global Array that Function Can Edit in VBA

问题描述

我正在使用Excel VBA，并尝试使用全局数组来跟踪不同的计数。不同的子函数和函数可以访问全局数组 array_count ，但我的目标是使函数 Chart 编辑值在阵列中。现在，函数对数组的编辑不会转移到其他子元素。

I am working with Excel VBA and am trying to use a global array to keep track of different counts. Different subs and functions are able to access the global array array_count, but my goal is to have function Chart edit the values in the array. Right now, the function's edits to the array do not carry over to the other subs.

' Initialize variables
Private counter As Integer
Private Account As String

Public array_count As Variant

' Iterate over rows, reading Account
Sub RowInsert()

    array_count = Array(-1, -1, -1)

    Debug.Print array_count(0)
    Debug.Print array_count(1)
    Debug.Print array_count(2)

    For counter = 0 To 1

    Account = Worksheets("Journal").Cells(counter + 2, 2)

    Call Record(Account)

    Next counter

    End Sub

    ' Record the transaction in proper T-account 
Sub Record(Account As String)

        Dim target_row As Long

        With Worksheets("Ledger").Range("a1:c20")

            Set Header = .Find(Account)

                Debug.Print Account

            If Header Is Nothing Then

                Debug.Print "Not found."

            End If

            Dim n As Integer
            n = Chart(Account)

            Debug.Print n

            target_row = Header.row + 2 + array_count(n)

            Debug.Print target_row

            Rows(target_row).Insert Shift:=xlDown

            Cells(target_row - 1, 1) = Worksheets("Journal").Cells(counter + 2, 1).Value

                Debug.Print counter

            If IsEmpty(Worksheets("Journal").Cells(counter + 2, 3)) Then

                Cells(target_row - 1, 3) = Worksheets("Journal").Cells(counter + 2, 4).Value

            Else

                Cells(target_row - 1, 2) = Worksheets("Journal").Cells(counter + 2, 3).Value

            End If

        End With

    End Sub

    ' Count the number of entries in each T-account
    Function Chart(Account As String) As Integer

    If Account = "Cash" Then

        array_count(0) = array_count(0) + 1
        Chart = array_count(0)


    ElseIf Account = "Equipment" Then

        array_count(1) = array_count(1) + 1
        Chart = array_count(1)

        Debug.Print array_count(1)


    End If

    End Function

推荐答案

如果我意识到正确，你的问题是，当你运行RowInsert（）时，总是得到与array_count（0）相同的值（我想这就是为什么你有调试

If I realize correctly, your issue is that whenever you run RowInsert() you always get the same values of the array_count(0) (I suppose, that is why you have the debug.prints there).

原因是这样的：

array_count = Array(-1, -1, -1)

你正在重新启动该数组每次（使它看起来像-1，-1，-1）。我是对吗？

you are reinitiating the array every time (making it look like -1,-1,-1). Am I right?

这篇关于功能可以在VBA中编辑的全局数组的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！