Code Example: Different Ways to Work With Arrays

This post is inspired by this StackOverflow question asking about passing a 2D array around... Let's say you have two Subs or Functions (or one of each) between which you want to pass an array (any array) of values. Here is an example of Subs trying to do something like this, but unfortunately failing:

Sub StartSub()
Dim myValues() As Integer
' Fill the array
StopSub myValues
End Sub

Sub StopSub(otherValues As Integer)
MsgBox otherValues(2)
End Sub

The right solution to this problem is not always obvious, but there are a number of options at your disposal. Let's have a look...

---

Pass the array as a variant
This may not the best in terms of memory use, but makes things much easier if you ever need to change the size of the array in future coding. There is also a danger here of not knowing what type of data is being passed, but this should be simple to catch.

Sub StartSub()
Dim myValues() As Integer
' Fill the array
StopSub myValues
End Sub

Sub StopSub(otherValues As Variant)
MsgBox otherValues(2)
End Sub

---

Don't pass anything
This example Dims the array outside the scope of the two Subs, which makes it available everywhere within the module. (You could also make this public everywhere if you chose to do so.) In this case, you just have to make sure everything is initialized properly, and that nothing else changes or manipulates the array outside its intended use.

Dim publicValues() As Integer
Sub StartSub()
' Fill the array
StopSub
End Sub

Sub StopSub()
MsgBox publicValues(2)
End Sub

---

Pass the array as an array
Perhaps the most obvious solution, if not obvious in its implementation, is to accept an array in your second Sub.

Sub StartSub()
Dim myValues() As Integer
' Fill the array
StopSub myValues
End Sub

Sub StopSub(otherValues() As Integer)
MsgBox otherValues(2)
End Sub