Array Help

[Tim879]

Hi. I am having an issue with an array that I created. I attached the
code below and can't understand why it does not work. I can add values
to the array and verify that they exist, however once the function
ends, the array's values are lost. What am I missing and any
suggestions on how to correct it?

Note: What I am trying to do is create arrays of sheets to print based
on a tab in the file. The Assign_Sheet_Type Function is where I am
trying to initialize the array. This will be called once the worksheet
opens. The Print MPR Function is where I want to print the array of
sheet.

'Module variables
Private Input_Sheets() As Variant ' Contains all input sheets
Private Supporting_Sheets() As Variant ' Contains all plan, RF and
prior year sheets
Private MPR_Sheets() As Variant ' contains all MPR report sheets
Private Board_Sheets() As Variant ' contains all board pack sheets


Sub Assign_Sheet_Type()

'This macro loops through the Worksheet Type tab and adds each sheet
name to 1 of 4 arrays of sheets
'These arrays are then used to show / hide various sheets in the work
book


Dim Input_Sheets_Count, Supporting_Sheets_Count, MPR_Sheets_Count,
Board_Sheets_Count As Integer
Dim MaxNumSheets, Row As Integer

'go to Worksheet Type tab
Sheets("Worksheet Type").Select

' set the max # of sheets in each array
MaxNumSheets = 100

'assign initial sizes to each array
ReDim Input_Sheets(MaxNumSheets)
ReDim Supporting_Sheets(MaxNumSheets)
ReDim MPR_Sheets(MaxNumSheets)
ReDim Board_Sheets(MaxNumSheets)

'initialize the array indexes
Input_Sheets_Count = 0
Supporting_Sheets_Count = 0
MPR_Sheets_Count = 0
Board_Sheets_Count = 0

'Assign the sheet names to the various arrays based on the data
entered on the spreadsheet
For Row = 1 To 100
If Range("B" & Row).Value = "Input" Then
Input_Sheets(Input_Sheets_Count) = Range("A" & Row).Value
Input_Sheets_Count = Input_Sheets_Count + 1
End If

If Range("C" & Row).Value = "Board" Then
Board_Sheets(Board_Sheets_Count) = Range("A" & Row).Value
Board_Sheets_Count = Board_Sheets_Count + 1
End If

If Range("D" & Row).Value = "MPR" Then
MPR_Sheets(MPR_Sheets_Count) = Range("A" & Row).Value
MPR_Sheets_Count = MPR_Sheets_Count + 1
End If

If Range("C" & Row).Value = "Supporting" Then
Supporting_Sheets(Supporting_Sheets_Count) = Range("A" &
Row).Value
Supporting_Sheets_Count = Supporting_Sheets_Count + 1
End If

Next

'redim the arrays to get rid of the blank values
ReDim Preserve Input_Sheets(Input_Sheets_Count - 1)
ReDim Preserve Board_Sheets(Board_Sheets_Count - 1)
ReDim Preserve MPR_Sheets(MPR_Sheets_Count - 1)
ReDim Preserve Supporting_Sheets(Supporting_Sheets_Count - 1)

End Sub

'**************************************************************************************
Sub Print_MPR()
' Print_MPR Macro
' Macro recorded 9/11/2007 by TB
'
'
On Error GoTo Exit_Print_MPR

Application.ScreenUpdating = False

'remember current sheet
strCurrentSheet = ActiveSheet.Name

Sheets(MPR_Sheets)).Select

ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True

Exit_Print_MPR:
Sheets(strCurrentSheet).Select
Application.ScreenUpdating = True
End Sub

 

[Keith74]

Hi Tim

I think the problem here is variable lifetime. You're running two
procedures at different points in time, even module level variable
only retain there value as long as code is running. Once the code
stops the variable goes back to nothing. Unless there is some reason
why you can't the simplest solution would be to merge the two
procedures into one that you run whenever you want to print.

hth

Keith

 

[Tim879]

Keith

I can do what you suggest but it seems inefficient. If the user prints
something 5 times, then the function to create the print array runs 5
times.

I was hoping that there was a way to create a module level variable
that would retain its value once the function ended.

Any ideas?

Thanks for your help!