Referencing an array value with Run method 1

[shauntwo]

Hi,

I have the following line in a master workbook that passes certain variables to a given function in other workbooks:

bActivity = ActiveWorkbook.Application.Run(vArray(y, 4) & "!" & _
vArray(y, 6), vArray(y, 3), vArray(y, 5), vParameters)

All of my vArray() variables related to previously-assigned values, but I'm having trouble with the very last argument - vParameters. This value is actually a dynamically-built string of query parameters that are formatted for the Run method. To be brief, this method is looped repeatedly for several workbooks that I'm calling, and each book has it's own number of query parameters. As such, my vParameters is an attempt to continue the Run method syntax. For example, vParameters(1) might be assigned ", 1/1/2003, 1/31/2003". What I would like to do is have this text continue in the Run line above, as a continuation of the literal command. Trouble is, the line interprets vParameters as a single value, rather than referring to its contents. Any thoughts?

Thanks for your help,
Shaun

 

[RobBroekhuis]

Could you post the sub declaration code for one or two of your subs, to illustrate?
I think I'd use a variant array as a parameter, but the best solution may be different, depending on what you're trying to accomplish.
Rob

 

[shauntwo]

Rob,

Sure thing. The first snippet of code below is in the module of my main workbook, which calls functions in other workbooks:

For y = 1 To nRows
If vArray(y, 1) = "Y" Then
bActivity = False
Application.Workbooks.Open (vArray(y, 3) & vArray(y, 4))
bActivity = ActiveWorkbook.Application.Run(vArray(y, 4) & "!" & _
vArray(y, 6), vArray(y, 3), vArray(y, 5), vParameters)
...

With the .Run method above, and based upon the first row of data parameters assigned to the arrays, the function below would be executed from the newly-opened workbook:

Function Main(sPath, sFile, sQP1, sQP2 As String)
...

If you notice in the .Run above, vArray(y, 4) and vArray(y,6) indicate the workbook name and function/macro name, respectively. Arguments vArray(y, 3) and vArray(y, 5) pass the intended directory and file name to save the opened workbook as. The remaining argument, vParameters, stores the string ", 1/1/2003, 1/31/2003", but clearly, this is being read literally at the .Run line for a total of 3 arguments. This errors because Function Main declares 4 variables.

Let me know if you need any additional info. Thanks again.
Shaun

 

[RobBroekhuis]

Try using instead:

Function Main(sPath, sFile, vaQP)

where vaQP is a variant array,
and refer to vaQP(0) and vaQP(1) as your parameters.
Then instead of formatting the parameters as a string, create an array with the proper number of elements to pass to your sub. Re-declare your other subs the same way.
Does that make sense?
Rob

 

[shauntwo]

That sounds like a great idea, but how can I pass all of the values assigned to a given row-dimension of vParameters? In other words, vParameters(y, 1) and (y, 2) might correspond to "1/1/2003" and "1/31/2003", but if my .Run line only refers to vParameters, I get a "Subscript out of Range" error. The alternative, explicitly calling each vParameter, puts me back to square one, so to speak.

Am I reading you correctly? Thanks again, Rob.

Shaun

 

[RobBroekhuis]

I see. It will take an additional step, then:

dim vaP(3) as string 'assuming maximum of 4 parameters (adjust)
For y = 1 To nRows
If vArray(y, 1) = "Y" Then
bActivity = False
Application.Workbooks.Open (vArray(y, 3) & vArray(y, 4))
for i=0 to 3:vaP(i)=vaQP(y,i):next i
bActivity = ActiveWorkbook.Application.Run(vArray(y, 4) & "!" & _
vArray(y, 6), vArray(y, 3), vArray(y, 5), vaP)
...

In other words, you'll need to create the one-dimensional array from the two-dimensional one, and use the former as the argument to your functions.
Rob

 

[shauntwo]

Yeah, I realized that last night after your post. Thanks for your great help.

Shaun