Array prepared within a VBA event. How to extract its contents from inside a different event?

Question

This question should apply to any VBA situ with events:

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.

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.

Thanks for any help:

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

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.

Yes, code please. This should work. Be sure to have `Option Explicit` at the top of each module. — Andre, Jun 13 '16 at 14:06
I didn't Dim the array as Public because I was handling the transaction purely from within a form module, and not across a general purpose mod. — ni37osllb, Jun 13 '16 at 14:28
I have not used Option Explicit for this. Should it make a difference, other than for performance? — ni37osllb, Jun 13 '16 at 14:29
@ni37osllb Option Explicit will make sure you don't have any spelling issues in variable names. If something is not spelled exactly the same, VBA silently creates a new blank variant variable. It is just good practice to save you from yourself. — Jeremy, Jun 13 '16 at 14:31
few things: 1. your function name is the same as the array name. Perhaps some confusion in the compiler there? 2. numOfCols is declared in the function and is therefore always 0. Is that intended? 3. I and J are variants, not Longs. Must use a datatype for each declaration. — Jeremy, Jun 13 '16 at 14:34
4. You are not actually returning anything from your function, so no need to assign retval to anything. 5. Your Debug loop is using the numbers 1, then 2, then 2. Is that intended? — Jeremy, Jun 13 '16 at 14:40
@Jeremy, thanks. i actually use a 3 in the real established code on the page. That was my typo on reproducing the code in this question. — ni37osllb, Jun 13 '16 at 14:58
All: i don't really use Arr as the name of the array. I'll fix it in the code up there. — ni37osllb, Jun 13 '16 at 14:58
@ni37osllb having your actual code helps... sometimes, people complain that stackoverflow answerers are grumpy, and it is posts like these that make us that way... :) — Jeremy, Jun 13 '16 at 15:07
Nobody even needed to ask for the code. A simple array--even 1D--filled in one event and dereferenced in the other event, on the same module. A respondent can easily make one up faster than they can deal with how I'm filling it. I see nothing on the web, and my attempted solution doesn't work. Anyone ever tried it? — ni37osllb, Jun 13 '16 at 15:17
@ni37osllb yes we do it all the time. I'm trying to plug your code into an Access module, and I receive an error on the ReDim Preserve line stating "subscript out of range" so you have basic fundamental errors in the code you are posting. By you posting YOUR own exact code, we can determine if what you are doing is possible or not, or if the problem lies elsewhere, which is often the case. — Jeremy, Jun 13 '16 at 15:25
Okay, Jeremy, please post a simple 1D or 2D solution here that everyone can learn from. Or please direct us elsewhere where it's already been answered with simple working code. — ni37osllb, Jun 13 '16 at 15:26
http://www.tutorialspoint.com/vba/vba_arrays.htm also read how to ask a good question. http://stackoverflow.com/help/how-to-ask — Jeremy, Jun 13 '16 at 15:30
That's not a valid answer. That link is array construction--not passing values from one module event to a separate event. — ni37osllb, Jun 13 '16 at 15:32
post your actual code and actual error messages you are getting otherwise we can't help. — Jeremy, Jun 13 '16 at 15:32
You post a simple sample that works. You know, since you do it all the time. — ni37osllb, Jun 13 '16 at 15:33

Jeremy · Accepted Answer · 2016-06-13T17:03:06.163

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

Edit: 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

@ni37osllb There are some tricky spots it seems in that if you are using a 2D array, you can only ReDim Preserve the last element. see http://stackoverflow.com/questions/6276761/vb6-redimensioning-of-2d-dynamic-array and http://stackoverflow.com/questions/16369217/redim-preserve-to-a-multi-dimensional-array-in-visual-basic-6. Let us know what end goal you are trying to achieve, and we can help with that. That is why it important to not only present us your problem, but also the larger context of what you are trying to do. — Jeremy, Jun 13 '16 at 16:17
That's exactly what's happening: I can retrieve only the last value for the 2D array from the other event in my attempts. Thank you, very much, for your sample. If I can't get it working in 2D, I guess I'll just delimit the values together into a 1D like the one you've helpfully provided. — ni37osllb, Jun 13 '16 at 16:53
@ni37osllb so the issue is not with an array at the module level, but with 2d arrays. Can you explain what are you trying to do at a higher level? — Jeremy, Jun 13 '16 at 16:54
Basically, I just need to have two values for each array index. val1a, val1b; val2a, val2b; val3a, val3b. — ni37osllb, Jun 13 '16 at 16:56
@ni37osllb I added a double array sample, but you'd need to know the number of elements you are adding before you start adding elements. Is there a way you can calculate the length of what you need beforehand? — Jeremy, Jun 13 '16 at 17:04
Okay, yes, I can come up with a length beforehand in the process. I'll feed it to arrayLength and then it's good to go! Thank you! I'll try this now. — ni37osllb, Jun 13 '16 at 18:05

score 0 · Answer 2 · answered Jun 13 '16 at 14:54

0

If you had Option Explicit in your module, it would have directly given you a compiler error about the ambiguous variable name Arr.

You can't have the same name for a variable and a function.

Additionally, this line enforces variable declaration and reports undeclared variables/constants already at compile time.

To have this automatically in new modules, set the Require Variable Declaration option in the VBA Editor. This is really a must have for VBA development.

answered Jun 13 '16 at 14:54

Andre

26,751
7
36
80

do you have working code that you can supply here, that if an observer of the thread were to drop it into an Excel sheet module or an Access form module, would allow an array that is filled in one event to be extracted from another? – ni37osllb Jun 13 '16 at 15:02
Have any respondents actually tried what I'm attempting here? – ni37osllb Jun 13 '16 at 15:04
There is nothing specific to it - you just need to use **different** names for variable and function. – Andre Jun 13 '16 at 15:05
my real code does use different names for variable and function (and i've clarified it upstairs), and it doesn't work. Do you have a working sample you would like to post? – ni37osllb Jun 13 '16 at 15:11
@ni37osllb: Then you should have posted your real code. Please have a look at http://stackoverflow.com/help/how-to-ask – Andre Jun 14 '16 at 05:46

Array prepared within a VBA event. How to extract its contents from inside a different event?

2 Answers2

Linked