Referencing the same array in different Subs in VBA Excel

Question

I have a function that fills a certain array with cell values depending on which OptionButton is selected. How would I reference those same arrays in a seperate function which would feed those values back into the cells? Here is my (working) code so far.

Private Sub CommandButton1_Click()
Dim wave1Array(0 To 30) As String
Dim wave2Array(0 To 30) As String
Dim wave3Array(0 To 30) As String
Dim wave4Array(0 To 30) As String
Dim wave5Array(0 To 30) As String
Dim rng As Range
Dim cell As Range
Dim counter As Long

Set rng = Range("B2", "AF2")
counter = 0

If OptionButton6.Value = True Then
    For Each cell In rng
    wave1Array(counter) = cell.Value
    counter = counter + 1
Next cell
ElseIf OptionButton7.Value = True Then
    For Each cell In rng
    wave2Array(counter) = cell.Value
    counter = counter + 1
Next cell
ElseIf OptionButton8.Value = True Then
    For Each cell In rng
    wave3Array(counter) = cell.Value
    counter = counter + 1
Next cell
ElseIf OptionButton9.Value = True Then
    For Each cell In rng
    wave4Array(counter) = cell.Value
    counter = counter + 1
Next cell
ElseIf OptionButton10.Value = True Then
    For Each cell In rng
    wave5Array(counter) = cell.Value
    counter = counter + 1
Next cell
End If

End Sub

I don't know exactly what you're trying to do, but if you take all your array declarations (Dim wave1Array(0 To 30) As String, etc) out of your Sub() and have them in the module then they would be accessible from any sub within that module... This could also be a bad thing, though, since you should check to make sure they have the values you expect at different times.... — John Bustos
– John Bustos, Commented Jul 15, 2014 at 18:55
@JohnBustos Would I take only the array declarations or could I also move the counter, range, and cell declarations as well? Also how do I actually declare the arrays in the module? I placed them in Module 1, but now my code doesn't work. It can't find the arrays. — DeeWBee
– DeeWBee, Commented Jul 15, 2014 at 18:59
Only take out (and good programming practice is to try and take out as few things as possible) the array declarations themselves since they are the only things your other subs will need. And place them in the very top of the same module where this sub currently resides... Then any other sub in that module will be able to reference those (filled) arrays too.... — John Bustos
– John Bustos, Commented Jul 15, 2014 at 19:04
You can also declare them using Global instead of Dim so they can be accessed across the whole program (outside of the Subs in any module) — MatthewHagemann
– MatthewHagemann, Commented Jul 15, 2014 at 19:13

David Zemens · Accepted Answer · 2014-07-16 03:01:56Z

You have a few different options that I can think of.

As others have mentioned, make a module-level variable(s) as needed. These declarations should go in the same code module as your form controls. If the form controls are on a userform, then they should be declared in the form's code module, not a "standard" module.

'-------------------------------- all in the same code module -------------
Option Explicit
Dim myVariable as String

Private Sub CommandButton1_Click()

    myVariable = "Hello, world!"

End Sub

Private Sub CommandButton2_Click()

    msgBox myVariable

End Sub
'------------------------------- end of this example ----------------------

Public/GLobal variable may be an option but I recall there are some limitations using these with UserForms, and since I'm not sure if you're using a UserForm, I won't recommend that.

A third option would be to pass the arguments from one procedure to another, but that usually only works with "chained" procedures/functions, like when one function calls another function and that does not seem to be what you're doing at all.

For your specific case:

You can also streamline your code to avoid using the counter and cell variables, using direct range-to-array assignment.

'Module-level array variables, accessible by other procedures in this module:
Dim wave1Array()
Dim wave2Array()
Dim wave3Array()
Dim wave4Array()
Dim wave5Array()
Dim wave6Array()

Private Sub CommandButton1_Click()

Dim rng As Range
Dim arr() 

Set rng = Range("B2", "AF2")
'## Converts the row to an array (0 to 30)
arr = Application.Transpose(Application.Transpose(rng.Value))

'## Assigns the array from above to one of the module-level array variables:

If OptionButton6.Value = True Then wave1Array = arr
If OptionButton7.Value = True Then wave2Array = arr
If OptionButton8.Value = True Then wave3Array = arr
If OptionButton9.Value = True Then wave4Array = arr
If OptionButton10.Value = True Then wave5Array = arr
If OptionButton11.Value = True Then wave6Array = arr

End Sub

Note that to do this, you will have to declare them as variant arrays, since a range of cells .Value is a variant type (cells can contain error values which I believe will fail if you try to assign to a string array).

IF you must use strict String arrays, then you will need to use the counter and cell iteration.

Collectives™ on Stack Overflow

Referencing the same array in different Subs in VBA Excel

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related