在VBA事件中准备的阵列。如何从另一个事件中提取其内容？ [英] Array prepared within a VBA event. How to extract its contents from inside a different event?

问题描述

此问题应适用于任何发生事件的VBA：

This question should apply to any VBA situ with events:

我已经在MS Access的Form_Load（）事件中填充了一个数组。现在，我想从ListBox_DblClick（）事件中提取，取消引用该数组的内容。

I've filled an array within a Form_Load() event in MS Access. Now I would like to extract, dereference, the contents of that array from within a ListBox_DblClick() event.

我将该数组调到表单模块顶部（如果是Excel，那将是一个工作表模块）它们都共享；

I Dim'd the array at the top of the form module (if it was Excel, it would be a sheet module) they both share; no luck in having the ListBox_DblClick event recognize that there is an array anywhere.

感谢任何帮助：

Dim ArrWhatever() As String

Function ThisArr(tmpVal1, tmpVal2, tmpVal3)
 Dim numOfCols As Long
 Dim I, J, x As Long

 If Len(tmpVal1) > 0 Then
  ReDim Preserve ArrWhatever(numOfCols, 1 To J)
  Arr(1, J) = tmpVal1
  Arr(1, J) = tmpVal2
  Arr(1, J) = tmpVal3
  J = J + 1
 End If
End Function

Form_Load()
 ...
 retVal = ThisArr(val1, val2, val3)
End Sub

如果随后通过使用

If the contents are subsequently extracted by using

For x = LBound(Arr, 2) To UBound(Arr, 2)
 Debug.Print ArrWhatever(1, x) & "  " & ArrWhatever(2, x) & "  " & ArrWhatever(3, x)
Next

从Form_Load事件内部，然后将找到所有内容

from inside the Form_Load event, then everything is found.

但是到目前为止，在同一表单上相邻的另一个事件识别数组并不算运气。

But so far no luck in getting a different event adjacent on the same form to recognize the array.

推荐答案

让我们从一个简单的单维示例开始。在访问权限2010中经过测试。

Let's start with a simple single Dimension example. Tested in access 2010.

Option Compare Database
Option Explicit

Dim singleArray() As String 'start with one element
Dim currentLength As Integer 'variable to keep track of number of elements


Private Sub Command0_Click()
    Debug.Print "Accessed from Event " & singleArray(0) 'access from event

    Call PrintArray 'calling function to print array
End Sub

Private Sub Form_Load()

    'make 3 calls to my function to populate array
    Call PopulateArray("val1")
    Call PopulateArray("val2")
    Call PopulateArray("val3")

    'print it
    Call PrintArray

End Sub

Sub PopulateArray(value As String)
   'all arrays are 0 based by default, so the first time it is called, it will create 1 element at position 0
    ReDim Preserve singleArray(currentLength)

    'put the value in the array
    singleArray(currentLength) = value

    'increment variable so the next time the function is called, the value will be placed one position higher
    currentLength = currentLength + 1

End Sub

Sub PrintArray()
    Dim x As Integer
    'loop through array and print values
    For x = LBound(singleArray) To UBound(singleArray)
        Debug.Print singleArray(x)
    Next
End Sub

编辑：双数组样本，事先知道边界

double array sample, knowing the bounds up front

Option Explicit


Dim doubleArray() As String 'array declaration that will become two element array

Dim currentLength As Integer 'variable to keep track of number of elements for array 1


Private Sub Command0_Click()
    Debug.Print "Accessed from Event " & doubleArray(0, 0) 'access from event

    Call PrintArray 'calling function to print array
End Sub

Private Sub Form_Load()

    Dim arrayLength As Integer, arrayWidth As Integer

    'determine bounds of array
    arrayLength = 2 'can you write code to determine the length before you start adding records?
    arrayWidth = 1 'if you have a fixed number of elements on each "record", this can stay the same


    ReDim doubleArray(0 To arrayLength, 0 To arrayWidth)

    Call PopulateDoubleArray("val11", "val12")
    Call PopulateDoubleArray("val21", "val22")
    Call PopulateDoubleArray("val31", "val32")

    Call PrintArray


End Sub


Sub PopulateDoubleArray(value As String, value2 As String)

    'put the value in the array
    doubleArray(currentLength, 0) = value
    doubleArray(currentLength, 1) = value2

    'increment variable so the next time the function is called, the value will be placed one position higher
    currentLength = currentLength + 1

End Sub

Sub PrintArray()
    Dim x As Integer

    'loop through array and print values
    For x = LBound(doubleArray) To UBound(doubleArray)
        Debug.Print doubleArray(x, 0) & " " & doubleArray(x, 1)
    Next

End Sub

这篇关于在VBA事件中准备的阵列。如何从另一个事件中提取其内容？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！