Pass large number of parameters

[NMadson]

I've written a VBA process that already passes 12 parameters to a Sub. In re-writing my code I find I want to pass another 12. There are four permutations of three variables. Since they are similar I thought there must be an easier way to pass these values. I looked at passing a two-dimensional array (variable name, variable value) but haven't worked with arrays that much and couldn't easily find a code example on the Web that would appear to solve my problem. Then I thought I could just write the values to a table in my database, since all my procedure is doing is updating some or all of the variables at each iteration of a loop.

Then I began to wonder if I shouldn't just type in the 12 parameters and be done with it.

Does anyone have any thoughts as to the efficiency and effectiveness of one solution over the other?

TIA,
Nolan

 

[NMadson]

I've written a VBA process that already passes 12 parameters to a Sub. In re-writing my code I find I want to pass another 12. There are four permutations of three variables. Since they are similar I thought there must be an easier way to pass these values. I looked at passing a two-dimensional array (variable name, variable value) but haven't worked with arrays that much and couldn't easily find a code example on the Web that would appear to solve my problem. Then I thought I could just write the values to a table in my database, since all my procedure is doing is updating some or all of the variables at each iteration of a loop.

Then I began to wonder if I shouldn't just type in the 12 parameters and be done with it.

Does anyone have any thoughts as to the efficiency and effectiveness of one solution over the other?

TIA,
Nolan

I'm sorry. After going back to my problem I realized there might be a better way to describe the array (if that's the way to go) I want. There would be two columns and twelve rows. The columns are "Variable Name" and "Variable Value". (The fact that Variable Name is itself the result of three three iterations of (Hi, Avg, Lo) of four factors isn't relevant.)

N.

 

[jackd]

Your issue may be better communicated and understood if you just tell us in plain English WHAT you are trying to do.

Perhaps, the not relevant stuff would help in a description of the issue.

Loops and Array info at http://www.classanytime.com/mis333k/sjloopsarrays.html

 

[xenou]

It's always awkward passing in a lot of variables but if it works you don't need to change it. That said - yes, you have at least a half dozen options:

• Array
• VBA Collection
• Scripting Dictionary (aka associative array)
• Custom Class or Custom Type
• Store in a (temporary) Table
• Store in hidden Form Controls

 

[NMadson]

It's always awkward passing in a lot of variables but if it works you don't need to change it. That said - yes, you have at least a half dozen options:

• Array
• VBA Collection
• Scripting Dictionary (aka associative array)
• Custom Class or Custom Type
• Store in a (temporary) Table
• Store in hidden Form Controls

Thanks xenou.

I went with an array since I was somewhat familiar with that approach. I'll have to study up on the rest.

Nolan