Best way to pass many parameters to subroutine?

[JenniferMurphy]

I am working on a very complex (for me, at least) macro that will process a large sheet table. The table has several "helper" rows above the table that tell the macro what to do with the data in each column of the table. The code works, but it is difficult to read. I want to move sections of the code to subroutines. The subroutines will be easier to read and understand and the top level code will be much simpler and the logic flow clearer.

Here's the problem. There are quite a few variables and some of them are fairly large. And some of the subroutines will need to make changes to some of the values in some of the arrays. As I see it, I have 3 options:

1. Pass the variables as ByVal parameters.
2. Pass the variables as ByRef parameters.
3. Make some of the variables global so they don't have to be passed.

I don't think #1 will work. (a) VBA will have to make copies of several large arrays, which seems inefficient and (b) the subroutine will need to make changes to some of those arrays that can be used by the calling code.

#2 seems like the preferred way to go. The subroutine can work with the actual arrays, so any changes will be available to the calling code. And the calling syntax clearly spells out which variables each subroutine will use.

#3 seems like the simplest. I only need a few global variables. The rest can be passed. As I understand it, there are 3 types of scope of VBA variables:

1. Procedure level. Declare these with a DIm statement inside the procedure. They arfe only available to that procedure.
2. Module level. Declare these with a Private statement outside all procedures in that module, usually at the top. They are available to all procedures in that module.
3. Global level. Declare these with a Public statement outside all procedures in any module, usually at the top. They are available to all procedures in all modules.

All of the code for this projewct will be in one module, so I think I would need module level variables.

Here's a little test code:

Private test As String

Sub temp1()
test = "temp1"
Debug.Print test & " in test1"
Call temp2
Debug.Print test & " in test1"
End Sub

Sub temp2()
test = "temp2"
Debug.Print test & " in test2"
End Sub

My plan is to make a few of these variables module-level (Private). Any comments?

 

[JenniferMurphy]

Something important to consider is, if you are going to use the variable in different procedures and each procedure can change its value, then you declare it as public at the beginning of all the code. If it's not the case, then don't put it in the global declarations, as it will be allocating memory throughout the process.

The code is working very nicely. Thanks for the help.