Creating a "set" of arrays by reference

M

mosta

Board Regular
Joined
Jun 5, 2008
Messages
56
I need to call the same sub, several times, with several different arrays as an argument each time. Call the arrays First(), Second(), ... . I am trying to avoid writing:

Call MySub(First())
Call MySub(Second())

over and over. So I try to stick my arguments in an array:

Dim v as Variant
v = Array(First(), Second(), ...)

And I can loop through v to call the MySub with each argument.

But now I discover that v does not pass each array ByRef, it only passes ByVal--it makes copies and the originals, which I need the MySub to change for later use, never get changed (only copies of them get changed).

Is there a simple way to do this? To tidy up? I think I could create a Collection of my array arguments--but I can't add them as a list all at once can I? So I would then need 20 Add statements to fill my collection and I've only made more of a mess...

Ideas? Thanks.
 

Excel Facts

Waterfall charts in Excel?
Office 365 customers have access to Waterfall charts since late 2016. They were added to Excel 2019.
Sort by date Sort by votes
Hi mosta

A solution is to use instead a procedure with a variable number of parameters.

For ex., try running this code:

Code: 
Sub Test()
Dim v1() As Variant, v2() As Variant
 
v1 = Array(1, 2)
v2 = Array("a", "b", "c")
 
MsgBox "v1(0): " & v1(0) & ", v2(1): " & v2(1)
 
Call test1(v1, v2)
 
MsgBox "v1(0): " & v1(0) & ", v2(1): " & v2(1)
End Sub
 
 
Sub test1(ParamArray v())
 
v(0)(0) = 3
v(1)(1) = "d"
End Sub

As you can see the values of the arrays were changed inside Test1() but and you can confirm the arrays in Test1() are the same as in Test(), not copies of them.
 
Upvote 0
PGC, thank you so much. ParamArray is one of those basic things I hadn't yet managed to pick up despite some fairly intensive experience for a couple years. A very useful tool, and exactly what I asked for/ wanted. Thanks again, mosta.
 
Upvote 0
You must log in or register to reply here.

Similar threads

S
  • Question
Date formatting issue sheet to VBA Array to Sheet
Replies
2
Views
147
MARK858
MARK858
D
  • Question
Difficulty with trying to use ComplexSorting subroutine
Replies
0
Views
237
DonEB
D
S
  • Question
Trendline values using Dynamic Range in Excel - comparing with LINEST function
Replies
1
Views
203
Domenic
D
P
  • Solved
single-formula combining of multiple dynamic arrays (VSTACK?)
Replies
4
Views
813
Plod518
P
D
  • Solved
Moving lines of a 2D array to a second 2D array based on a condition
Replies
3
Views
276
Peter_SSs
Peter_SSs

Forum statistics

Threads
1,214,895
Messages
6,122,128
Members
449,066
Latest member
Andyg666

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top