Array Values lost between functions

[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

 

[JLGWhiz]

Try putting a macro call to your Print routine in the array sub as shown below:

'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)
Print_MPR
End Sub

Your array procedure is not written as a function and will not hold the
array values after it has completed.
Even the functions lose their values after they run, but they are applied
withing another procedure.



Once the array

 

[Chip Pearson]

Just as a matter of style, I would strongly recommend that you include both
the lower and upper bounds when you declare an array. The reason is that
the lower bound of an array is determined by the Option Base statement (if
any) at the top of the module. For example, depending on the Option Base
statement, the following array has either 10 or 11 elements:

Dim Arr(10)

If you have any inclination to build up a library of reusable code, as any
good programmer should, you may encounter problems when moving code between
modules with different Option Base statements. Do yourself a favor and
declare your array as

Dim Arr(0 To 9)
' or
Dim Arr(1 to 10)

It will save you debugging time in the future.

--
Cordially,
Chip Pearson
Microsoft MVP - Excel, 10 Years
Pearson Software Consulting
www.cpearson.com
(email on the web site)