Passing Arrays to sub procedures

[ramblinwreck]

I have code that is used to build graphs in Excel. A particular section of the code takes input passed via arguments in the call line of code that identifes a section of the associated spreadsheet from which a series of data is identified to constitute the data to be displayed on the graph just built.

Because each implementation of the code is specific to the number of series to be displayed on the graph, I was trying to use an array that contained specific cell column information that would be passed into the subprocedure. The sub procedure would then use the passed array information on cell columns in the determination of exactly where to get the series data to be displayed.

The code will probably make this easier to understand.

The user selects which graph to build based on selection of a command button:

Private Sub Form_Load()
Dim int21Down As Integer
Set pt = New excelwrksheet
int21Down = pt.rowinfo - 21
ReDim varColumn(3)
varColumn(1) = 3 'column location of current date miles
varColumn(2) = 68 'column location of current date moving average of miles
varColumn(3) = 11 'column location of actual dates
Call deletepreviouslegend(2, "Last 21 Runs")
Call addseries("Last 21 Runs", "combinedruncrunchdata", pt.rowinfo, pt.rowinfo, int21Down, "", 3, varColumn)

And then the subsequent code:

Sub addseries(strSubSheetName, strSubSheetName1, intSubAdjustRow, intSubAdjustRowup, intSubAdjustRowdown, intSubGoal, intSeriesCount, ParamArray intSubColumn())
Dim intN As Integer
Worksheets("combinedruncrunchdata").Activate
If strSubSheetName = "Cum Miles" Then
For intN = 0 To (-intSubAdjustRowdown + intSubAdjustRowup)
    Worksheets("combinedruncrunchdata").Cells((intSubAdjustRowdown + intN), 11).Value = Worksheets("combinedruncrunchdata").Cells(14, 13).Value - intFirstDayOfMonth + intN
Next intN
End If
    
With Worksheets(strSubSheetName1) 'sets appropriate rows for current month to goal
If strSubSheetName = "cum miles" Then
    .Range(.Cells(intSubAdjustRowdown, 10), _
        .Cells(intSubAdjustRowup, 10)).Value = intSubGoal
End If
intI = 0
varI = 1
For Each varI In varSubColumn
intI = intI + 1
Worksheets(strSubSheetName).ChartObjects(1).Chart. _
SeriesCollection.Add _
Source:=ActiveWorkbook.Worksheets(strSubSheetName1).Range(.Cells(intSubAdjustRowdown, varSubColumn(intI)), _
.Cells(intSubAdjustRowup, varSubColumn(intI)))
Next varI

Notes:

1. It is my understanding that the array in the argument MUST be of the variant type although in my case, I only need an integer type array.
2. I have tried several things to get the last line of the above code to work including:
- setting the passing argument array to integer
- using control variables as integers and as variants
3. Currently when the last line of code attempts to execute I get error 92 that I did not initialize the for each next loop (and somehow jumped into the loop inappropriately).
4. I think what I don't know is how to manipulate arrays using code. I'm familiar with arrays from other programming languages but this is the first occasion I have had to use them in VB.
5. I searched the forums but came up empty.

thanks for your help.

 

[ramblinwreck]

I worked around the problem by doing away with the param array argument altogether and just declared a public array that holds the information and that can be accessed by the call addseries subroutine.