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)
"JLGWhiz" <(E-Mail Removed)> wrote in message
news:0AE910D2-C2F3-4B1C-A67D-(E-Mail Removed)...
> 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
> "Tim879" wrote:
>
>> 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
>>
>>
Similar Threads
