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?

 

[DanteAmor]

It seems that you already have it solved, just comment:

When to Pass an Argument by Value​

• If the calling code element underlying the argument is a nonmodifiable element, declare the corresponding parameter ByVal. No code can change the value of a nonmodifiable element.
• If the underlying element is modifiable, but you do not want the procedure to be able to change its value, declare the parameter ByVal. Only the calling code can change the value of a modifiable element passed by value.

When to Pass an Argument by Reference​

• If the procedure has a genuine need to change the underlying element in the calling code, declare the corresponding parameter ByRef.
• If the correct execution of the code depends on the procedure changing the underlying element in the calling code, declare the parameter ByRef. If you pass it by value, or if the calling code overrides the ByRef passing mechanism by enclosing the argument in parentheses, the procedure call might produce unexpected results.

Source:

Passing Arguments by Value and by Reference - Visual Basic

Learn more about: Passing Arguments by Value and by Reference (Visual Basic)

learn.microsoft.com

----- --

Best way to pass many parameters to subroutine?​

The best way is the one you need for your procedure.

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.

 

[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.

Did you mean "Private", rather than "Public"? These variables will only be used within one module. It was my understanding that Public makes them available to all modules, no?

 

[JenniferMurphy]

It seems that you already have it solved, just comment:

I had "a" solution, but my solutions are not always solutions. So I was just looking for feedback.

The best way is the one you need for your procedure.

Yes, but it also has to work. This code has a lot of variables (dozens) and I want to move a lot of the tedious work to subroutines. But I do not want to have to manage long complicated parameter lists, so I am going with module-level global variables.

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.

After some research and some testing, I believe the facts are these:

• Dim makes the name local to the procedure. These are declared inside the code.
• Private makes the name global to that module. These are declared outside the code.
• Public makes the name global to the project. These are declared outside the code.

I am marking your reply as the solution since it guided me.

Thanks

 

[RoryA]

Dim can be used within a routine or at module-level, where it works the same way as Private.

Also note that array variables can only be passed ByRef.

As a general guideline, if you have a routine with a lot of parameters, it is usually a sign that more refactoring is needed. The generally accepted wisdom for variable scope is to keep it as narrow as possible but there are times when it can simplify things to violate that rule.

 

[JenniferMurphy]

Dim can be used within a routine or at module-level, where it works the same way as Private.

I didn't know that. I probably wouldn't use it. For me, using the most descriptive declaration helps me keep things straight.

I also learned that Const can be used at the module level, but Private Const works better for me.

Also note that array variables can only be passed ByRef.

Does that mean that I have to code the ByRef keyword or that arrays default to ByRef if neither is stated?

As a general guideline, if you have a routine with a lot of parameters, it is usually a sign that more refactoring is needed. The generally accepted wisdom for variable scope is to keep it as narrow as possible but there are times when it can simplify things to violate that rule.

Good polint.

 

[RoryA]

Everything defaults to ByRef if you don't specify otherwise (except property procedures which are always ByVal no matter what you specify).

 

[JenniferMurphy]

Everything defaults to ByRef if you don't specify otherwise (except property procedures which are always ByVal no matter what you specify).

Well that's good to know. I thought it was the other way around. Thanks.

 

[Gokhan Aycan]

Not sure if this would help you, but there is also this: VBA Arrays - ParamArray

 

[JenniferMurphy]

Not sure if this would help you, but there is also this: VBA Arrays - ParamArray

Thanks. I do know about Paramarrays and have used them a few times. I think they are mainly for situations where there are a lot of optional parameters and it's a pain to try and remember the correct order. This allows the caller to specify just the ones needed and in any order. Right?