Getting multiple subroutines to use an Array

[DonEB]

I have a program that has one subroutine that calls another subroutine that calls yet another subroutine. The call for each subroutine is dependent upon the needs of the program. This is NOT an endless loop. There is a max of 4 calls to the subroutine. In the first subroutine, I created an array using the following: Dim arrPlayers As Variant. Later am able to determine size of that arr and redefined as follows: ReDim arrPlayers (1 to i). Note: I know the size of the array will be either 5, 9, 13, 17 or 21 depending on other variables retrieved thru the program.

Another variable called PlayerCount is retrieved from the worksheets and if it is 4 then there is no call to another subroutine. All is handled via the initial subroutine. If PlayerCount is set to 8 or higher, then additional subroutines will be called and this is where my problem exists and I'll try to show it using a simple example I created below.

My question is: Is it possible to have subsequent subroutines utilize the same array and if so... how? I've attempted to a number of different approaches based on things I've read on-line but I'm obviously missing something critical. With my current approach below, I get a "Compile Error: Variable not defined" message ... and I'll indicate where below.

As you can see, if a subroutine is called upon then new values get added to the arrPlayers() array and one value within the arrayPlayers(1) can get "added to" reflect a new value.

I hope all this makes sense. I couldn't share all the code because it's too voluminous. I attempted to reduce it down to the bare minimum to focus on the issue that's causing me stop dead in my tracks at this point in time.

Sub STEP01()

Dim arrPlayers As Variant
Dim i As Integer
i = 13 
ReDim arrPlayers(1 To i)

arrPlayers(1) = 10
arrPlayers(2) = 1
arrPlayers(3) = 2
arrPlayers(4) = 3
arrPlayers(5) = 4

'
'  Used a separate subroutine simply to separate the process
If PlayerCount > 4 then        
   Call TESTSUB1
Endif
End Sub

Sub TESTSUB1()      '<=======  This is what gets highlighted with the following message "Compile Error: Variable not defined"

Dim i As Integer
i = 13
ReDim Preserve arrPlayers(1 To i)

'arrPlayers(1) = arrPlayers(1) + 14
arrPlayers(6) = 5
arrPlayers(7) = 6
arrPlayers(8) = 7
arrPlayers(9) = 8

If PlayerCount > 8 then        
    Call TESTSUB2
Endif
End Sub

Sub TESTSUB2()

Dim i As Integer
i = 13
ReDim Preserve arrPlayers(1 To i)

'arrPlayers(1) = arrPlayers(1) + 32
arrPlayers(10) = 9
arrPlayers(11) = 10
arrPlayers(12) = 11
arrPlayers(13) = 12

End Sub

I would appreciate and help or guidance anyone could provide at this time.

Thanks,
Don

 

[offthelip]

Declare the array right at the top of your module above the sub step01 statement ( assuming that is the first macro in your module) using the public declaration, this means the aray is available everywhere

Public ArrPlayers () as variant
Sub STEP01()

You can then use in all the subroutines

 

[Domenic]

Another way to utilize the same array is to pass it to your sub procedure. In order to do so, you'll need to amend the sub procedure definition for TESTSUB1 so that it accepts an argument, like so...

Sub TESTSUB1(ByRef arr As Variant)

Note that arr is declared as ByRef, so any changes you make within TESTSUB1 is reflected in the variable in the calling code. Then you can call it like this...

Call TESTSUB1(arrPlayers)

By the way, you can omit the Call keyword when calling your sub procedure...

TESTSUB1 arrPlayers

By the way, I see that you have not declared nor assigned PlayrCount a value. Anyway, here's an example...

Sub STEP01()

    Dim arrPlayers As Variant
    Dim i As Long
   
    i = 13
   
    ReDim arrPlayers(1 To i)
   
    arrPlayers(1) = 10
    arrPlayers(2) = 1
    arrPlayers(3) = 2
    arrPlayers(4) = 3
    arrPlayers(5) = 4
   
    TESTSUB1 arrPlayers
   
    For i = LBound(arrPlayers) To UBound(arrPlayers)
        Debug.Print arrPlayers(i)
    Next i
   
End Sub

Sub TESTSUB1(ByRef arr As Variant)

    arr(6) = 5
    arr(7) = 6
    arr(8) = 7
    arr(9) = 8
   
End Sub

Hope this helps!

 

[DonEB]

Thank you both for your quick responses. As it turns out, I had incorrectly declared the Public array in a different module (i.e., one that did not contain the subroutines that were going to be using it) and the explanation as to where the array needed to be declared pointed that error out to me.

Domenic... I am intrigued by the alternative approach you provided. You implied that your alternative suggestion was simply that... an alternative. However, I am curious to know if one approach is recommended over the other?

Regardless, I very much appreciate your responses. I am now able to move forward with my little project. Thank you!!

 

[Domenic]

It's considered good programming practice to declare variables locally, and then pass them to your sub procedure or function as an argument. For additional information, have a look at the following post...

Usage of global variables and local variables in different subs

I'm copy pasting stuff from Excel to PowerPoint using VBA. It's working, but I need to clean up my code. The problem is I have no clue what is good practice when it comes to variables (or objects) ...

stackoverflow.com

Hope this helps!