Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Losing my array from one sub to another
All,
I can't seem to figure out what I'm doing wrong here. I am trying to pass three arrays from one sub to another. I have made arrSelectedFiles/Species/Layers global arrays by putting them above the Sub routines. When I populate these arrays in the second function, I can see the values, but when it returns to the original array, they are not populated. Here is my code: Dim arrSelectedFiles() As String Dim arrSelectedSpecies() As String Dim arrSelectedLayers() As String Sub PivotTableGraphs(lngFileCount) 'Go to Data Page and Populate arrays with options to graph ReDim arrFileList(1 To lngFileCount) As String ReDim arrSpeciesList(1 To 10) As String ReDim arrLayers(1 To 4) As String Sheets("Data").Select Range("D3").Select For j = 1 To lngFileCount arrFileList(j) = ActiveCell.Offset(0, 14 * (j - 1)).Value Next j For j = 1 To 10 arrSpeciesList(j) = ActiveCell.Offset(5, j - 3).Value Next j arrLayers(1) = "Top Layer" arrLayers(2) = "Mid Layer" arrLayers(3) = "Bottom Layer" arrLayers(4) = "All Layers" 'Select Files to Plot Call SelectToGraph(arrFileList, lngFileCount, 1) 'Select Species to Plot Call SelectToGraph(arrSpeciesList, lngFileCount, 2) 'Select Layers to Plot Call SelectToGraph(arrLayers, lngFileCount, 3) 'Create Graphs For FileNum = 1 To UBound(arrSelectedFiles) If Not arrSelectedFiles(FileNum) = "Not" Then For LayerNum = 1 To UBound(arrSelectedLayers) If Not arrSelectedLayers(LayerNum) = "Not" Then 'Create Graph Else: End If Next SpecNum Next LayerNum Else: End If Next FileNum End Sub Sub SelectToGraph(arrList, lngFileCount, GraphOption) Dim i, TopPos As Integer Dim PrintDlg As DialogSheet Dim CurrentSheet As Worksheet Dim cb As CheckBox Application.ScreenUpdating = False ' Add a temporary dialog sheet Set CurrentSheet = ActiveSheet Set PrintDlg = ActiveWorkbook.DialogSheets.Add ' Add the checkboxes TopPos = 40 For i = 1 To UBound(arrList) PrintDlg.CheckBoxes.Add 78, TopPos, 150, 16.5 PrintDlg.CheckBoxes(i).Text = arrList(i) TopPos = TopPos + 13 Next i ' Move the OK and Cancel buttons PrintDlg.Buttons.Left = 240 ' Set dialog height, width, and caption With PrintDlg.DialogFrame .Height = Application.Max _ (68, PrintDlg.DialogFrame.Top + TopPos - 34) .Width = 230 If GraphOption = 1 Then .Caption = "Select Files to Graph" ElseIf GraphOption = 2 Then .Caption = "Select Species to Graph" Else .Caption = "Select Layers to Graph" End If End With ' Change tab order of OK and Cancel buttons ' so the 1st option button will have the focus PrintDlg.Buttons("Button 2").BringToFront PrintDlg.Buttons("Button 3").BringToFront ' Display the dialog box CurrentSheet.Activate Application.ScreenUpdating = True ReDim Preserve arrSelectedFiles(1 To lngFileCount) ReDim Preserve arrSelectedSpecies(1 To 10) ReDim Preserve arrSelectedLayers(1 To 4) i = 1 If PrintDlg.Show Then For Each cb In PrintDlg.CheckBoxes If cb = 1 Then If GraphOption = 1 Then arrSelectedFiles(i) = arrList(i) ElseIf GraphOption = 2 Then arrSelectedSpecies(i) = arrList(i) Else arrSelectedLayers(i) = arrList(i) End If Else If GraphOption = 1 Then arrSelectedFiles(i) = "Not" ElseIf GraphOption = 2 Then arrSelectedSpecies(i) = "Not" Else arrSelectedLayers(i) = "Not" End If End If i = i + 1 Next cb End If ' Delete temporary dialog sheet (without a warning) Application.DisplayAlerts = False PrintDlg.Delete ' Reactivate original sheet CurrentSheet.Activate End Sub |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Losing my array from one sub to another
Hi,
Set them as global ones then theywill be available to all functions Regards Trev B "Matt S" wrote: All, I can't seem to figure out what I'm doing wrong here. I am trying to pass three arrays from one sub to another. I have made arrSelectedFiles/Species/Layers global arrays by putting them above the Sub routines. When I populate these arrays in the second function, I can see the values, but when it returns to the original array, they are not populated. Here is my code: Dim arrSelectedFiles() As String Dim arrSelectedSpecies() As String Dim arrSelectedLayers() As String Sub PivotTableGraphs(lngFileCount) 'Go to Data Page and Populate arrays with options to graph ReDim arrFileList(1 To lngFileCount) As String ReDim arrSpeciesList(1 To 10) As String ReDim arrLayers(1 To 4) As String Sheets("Data").Select Range("D3").Select For j = 1 To lngFileCount arrFileList(j) = ActiveCell.Offset(0, 14 * (j - 1)).Value Next j For j = 1 To 10 arrSpeciesList(j) = ActiveCell.Offset(5, j - 3).Value Next j arrLayers(1) = "Top Layer" arrLayers(2) = "Mid Layer" arrLayers(3) = "Bottom Layer" arrLayers(4) = "All Layers" 'Select Files to Plot Call SelectToGraph(arrFileList, lngFileCount, 1) 'Select Species to Plot Call SelectToGraph(arrSpeciesList, lngFileCount, 2) 'Select Layers to Plot Call SelectToGraph(arrLayers, lngFileCount, 3) 'Create Graphs For FileNum = 1 To UBound(arrSelectedFiles) If Not arrSelectedFiles(FileNum) = "Not" Then For LayerNum = 1 To UBound(arrSelectedLayers) If Not arrSelectedLayers(LayerNum) = "Not" Then 'Create Graph Else: End If Next SpecNum Next LayerNum Else: End If Next FileNum End Sub Sub SelectToGraph(arrList, lngFileCount, GraphOption) Dim i, TopPos As Integer Dim PrintDlg As DialogSheet Dim CurrentSheet As Worksheet Dim cb As CheckBox Application.ScreenUpdating = False ' Add a temporary dialog sheet Set CurrentSheet = ActiveSheet Set PrintDlg = ActiveWorkbook.DialogSheets.Add ' Add the checkboxes TopPos = 40 For i = 1 To UBound(arrList) PrintDlg.CheckBoxes.Add 78, TopPos, 150, 16.5 PrintDlg.CheckBoxes(i).Text = arrList(i) TopPos = TopPos + 13 Next i ' Move the OK and Cancel buttons PrintDlg.Buttons.Left = 240 ' Set dialog height, width, and caption With PrintDlg.DialogFrame .Height = Application.Max _ (68, PrintDlg.DialogFrame.Top + TopPos - 34) .Width = 230 If GraphOption = 1 Then .Caption = "Select Files to Graph" ElseIf GraphOption = 2 Then .Caption = "Select Species to Graph" Else .Caption = "Select Layers to Graph" End If End With ' Change tab order of OK and Cancel buttons ' so the 1st option button will have the focus PrintDlg.Buttons("Button 2").BringToFront PrintDlg.Buttons("Button 3").BringToFront ' Display the dialog box CurrentSheet.Activate Application.ScreenUpdating = True ReDim Preserve arrSelectedFiles(1 To lngFileCount) ReDim Preserve arrSelectedSpecies(1 To 10) ReDim Preserve arrSelectedLayers(1 To 4) i = 1 If PrintDlg.Show Then For Each cb In PrintDlg.CheckBoxes If cb = 1 Then If GraphOption = 1 Then arrSelectedFiles(i) = arrList(i) ElseIf GraphOption = 2 Then arrSelectedSpecies(i) = arrList(i) Else arrSelectedLayers(i) = arrList(i) End If Else If GraphOption = 1 Then arrSelectedFiles(i) = "Not" ElseIf GraphOption = 2 Then arrSelectedSpecies(i) = "Not" Else arrSelectedLayers(i) = "Not" End If End If i = i + 1 Next cb End If ' Delete temporary dialog sheet (without a warning) Application.DisplayAlerts = False PrintDlg.Delete ' Reactivate original sheet CurrentSheet.Activate End Sub |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Losing my array from one sub to another
Trev,
How? Thanks, Matt "Trev B" wrote: Hi, Set them as global ones then theywill be available to all functions Regards Trev B "Matt S" wrote: All, I can't seem to figure out what I'm doing wrong here. I am trying to pass three arrays from one sub to another. I have made arrSelectedFiles/Species/Layers global arrays by putting them above the Sub routines. When I populate these arrays in the second function, I can see the values, but when it returns to the original array, they are not populated. Here is my code: Dim arrSelectedFiles() As String Dim arrSelectedSpecies() As String Dim arrSelectedLayers() As String Sub PivotTableGraphs(lngFileCount) 'Go to Data Page and Populate arrays with options to graph ReDim arrFileList(1 To lngFileCount) As String ReDim arrSpeciesList(1 To 10) As String ReDim arrLayers(1 To 4) As String Sheets("Data").Select Range("D3").Select For j = 1 To lngFileCount arrFileList(j) = ActiveCell.Offset(0, 14 * (j - 1)).Value Next j For j = 1 To 10 arrSpeciesList(j) = ActiveCell.Offset(5, j - 3).Value Next j arrLayers(1) = "Top Layer" arrLayers(2) = "Mid Layer" arrLayers(3) = "Bottom Layer" arrLayers(4) = "All Layers" 'Select Files to Plot Call SelectToGraph(arrFileList, lngFileCount, 1) 'Select Species to Plot Call SelectToGraph(arrSpeciesList, lngFileCount, 2) 'Select Layers to Plot Call SelectToGraph(arrLayers, lngFileCount, 3) 'Create Graphs For FileNum = 1 To UBound(arrSelectedFiles) If Not arrSelectedFiles(FileNum) = "Not" Then For LayerNum = 1 To UBound(arrSelectedLayers) If Not arrSelectedLayers(LayerNum) = "Not" Then 'Create Graph Else: End If Next SpecNum Next LayerNum Else: End If Next FileNum End Sub Sub SelectToGraph(arrList, lngFileCount, GraphOption) Dim i, TopPos As Integer Dim PrintDlg As DialogSheet Dim CurrentSheet As Worksheet Dim cb As CheckBox Application.ScreenUpdating = False ' Add a temporary dialog sheet Set CurrentSheet = ActiveSheet Set PrintDlg = ActiveWorkbook.DialogSheets.Add ' Add the checkboxes TopPos = 40 For i = 1 To UBound(arrList) PrintDlg.CheckBoxes.Add 78, TopPos, 150, 16.5 PrintDlg.CheckBoxes(i).Text = arrList(i) TopPos = TopPos + 13 Next i ' Move the OK and Cancel buttons PrintDlg.Buttons.Left = 240 ' Set dialog height, width, and caption With PrintDlg.DialogFrame .Height = Application.Max _ (68, PrintDlg.DialogFrame.Top + TopPos - 34) .Width = 230 If GraphOption = 1 Then .Caption = "Select Files to Graph" ElseIf GraphOption = 2 Then .Caption = "Select Species to Graph" Else .Caption = "Select Layers to Graph" End If End With ' Change tab order of OK and Cancel buttons ' so the 1st option button will have the focus PrintDlg.Buttons("Button 2").BringToFront PrintDlg.Buttons("Button 3").BringToFront ' Display the dialog box CurrentSheet.Activate Application.ScreenUpdating = True ReDim Preserve arrSelectedFiles(1 To lngFileCount) ReDim Preserve arrSelectedSpecies(1 To 10) ReDim Preserve arrSelectedLayers(1 To 4) i = 1 If PrintDlg.Show Then For Each cb In PrintDlg.CheckBoxes If cb = 1 Then If GraphOption = 1 Then arrSelectedFiles(i) = arrList(i) ElseIf GraphOption = 2 Then arrSelectedSpecies(i) = arrList(i) Else arrSelectedLayers(i) = arrList(i) End If Else If GraphOption = 1 Then arrSelectedFiles(i) = "Not" ElseIf GraphOption = 2 Then arrSelectedSpecies(i) = "Not" Else arrSelectedLayers(i) = "Not" End If End If i = i + 1 Next cb End If ' Delete temporary dialog sheet (without a warning) Application.DisplayAlerts = False PrintDlg.Delete ' Reactivate original sheet CurrentSheet.Activate End Sub |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Losing my array from one sub to another
Your public Dim does not match your ReDim variable names.
"Matt S" wrote in message ... Trev, How? Thanks, Matt "Trev B" wrote: Hi, Set them as global ones then theywill be available to all functions Regards Trev B "Matt S" wrote: All, I can't seem to figure out what I'm doing wrong here. I am trying to pass three arrays from one sub to another. I have made arrSelectedFiles/Species/Layers global arrays by putting them above the Sub routines. When I populate these arrays in the second function, I can see the values, but when it returns to the original array, they are not populated. Here is my code: Dim arrSelectedFiles() As String Dim arrSelectedSpecies() As String Dim arrSelectedLayers() As String Sub PivotTableGraphs(lngFileCount) 'Go to Data Page and Populate arrays with options to graph ReDim arrFileList(1 To lngFileCount) As String ReDim arrSpeciesList(1 To 10) As String ReDim arrLayers(1 To 4) As String Sheets("Data").Select Range("D3").Select For j = 1 To lngFileCount arrFileList(j) = ActiveCell.Offset(0, 14 * (j - 1)).Value Next j For j = 1 To 10 arrSpeciesList(j) = ActiveCell.Offset(5, j - 3).Value Next j arrLayers(1) = "Top Layer" arrLayers(2) = "Mid Layer" arrLayers(3) = "Bottom Layer" arrLayers(4) = "All Layers" 'Select Files to Plot Call SelectToGraph(arrFileList, lngFileCount, 1) 'Select Species to Plot Call SelectToGraph(arrSpeciesList, lngFileCount, 2) 'Select Layers to Plot Call SelectToGraph(arrLayers, lngFileCount, 3) 'Create Graphs For FileNum = 1 To UBound(arrSelectedFiles) If Not arrSelectedFiles(FileNum) = "Not" Then For LayerNum = 1 To UBound(arrSelectedLayers) If Not arrSelectedLayers(LayerNum) = "Not" Then 'Create Graph Else: End If Next SpecNum Next LayerNum Else: End If Next FileNum End Sub Sub SelectToGraph(arrList, lngFileCount, GraphOption) Dim i, TopPos As Integer Dim PrintDlg As DialogSheet Dim CurrentSheet As Worksheet Dim cb As CheckBox Application.ScreenUpdating = False ' Add a temporary dialog sheet Set CurrentSheet = ActiveSheet Set PrintDlg = ActiveWorkbook.DialogSheets.Add ' Add the checkboxes TopPos = 40 For i = 1 To UBound(arrList) PrintDlg.CheckBoxes.Add 78, TopPos, 150, 16.5 PrintDlg.CheckBoxes(i).Text = arrList(i) TopPos = TopPos + 13 Next i ' Move the OK and Cancel buttons PrintDlg.Buttons.Left = 240 ' Set dialog height, width, and caption With PrintDlg.DialogFrame .Height = Application.Max _ (68, PrintDlg.DialogFrame.Top + TopPos - 34) .Width = 230 If GraphOption = 1 Then .Caption = "Select Files to Graph" ElseIf GraphOption = 2 Then .Caption = "Select Species to Graph" Else .Caption = "Select Layers to Graph" End If End With ' Change tab order of OK and Cancel buttons ' so the 1st option button will have the focus PrintDlg.Buttons("Button 2").BringToFront PrintDlg.Buttons("Button 3").BringToFront ' Display the dialog box CurrentSheet.Activate Application.ScreenUpdating = True ReDim Preserve arrSelectedFiles(1 To lngFileCount) ReDim Preserve arrSelectedSpecies(1 To 10) ReDim Preserve arrSelectedLayers(1 To 4) i = 1 If PrintDlg.Show Then For Each cb In PrintDlg.CheckBoxes If cb = 1 Then If GraphOption = 1 Then arrSelectedFiles(i) = arrList(i) ElseIf GraphOption = 2 Then arrSelectedSpecies(i) = arrList(i) Else arrSelectedLayers(i) = arrList(i) End If Else If GraphOption = 1 Then arrSelectedFiles(i) = "Not" ElseIf GraphOption = 2 Then arrSelectedSpecies(i) = "Not" Else arrSelectedLayers(i) = "Not" End If End If i = i + 1 Next cb End If ' Delete temporary dialog sheet (without a warning) Application.DisplayAlerts = False PrintDlg.Delete ' Reactivate original sheet CurrentSheet.Activate End Sub |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Losing my array from one sub to another
"JLGWhiz" wrote: Your public Dim does not match your ReDim variable names. JLG, I have these specified above: Dim arrSelectedFiles() As String Dim arrSelectedSpecies() As String Dim arrSelectedLayers() As String Then in the second Sub routine, I have these below, which I refer to later in the first sub. THe problem is, after I return from the second, I no longer have these arrays! I'm so frustrated! ReDim Preserve arrSelectedFiles(1 To lngFileCount) ReDim Preserve arrSelectedSpecies(1 To 10) ReDim Preserve arrSelectedLayers(1 To 4) Thanks, Matt |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Losing my array from one sub to another
All,
I figured out the problem. I had the public arrays defined above this module, but it really should be above the original module at the beginning of my code. Moving them over to the first module fixed the problem. Thanks for taking the time, Matt "Matt S" wrote: All, I can't seem to figure out what I'm doing wrong here. I am trying to pass three arrays from one sub to another. I have made arrSelectedFiles/Species/Layers global arrays by putting them above the Sub routines. When I populate these arrays in the second function, I can see the values, but when it returns to the original array, they are not populated. Here is my code: Dim arrSelectedFiles() As String Dim arrSelectedSpecies() As String Dim arrSelectedLayers() As String Sub PivotTableGraphs(lngFileCount) 'Go to Data Page and Populate arrays with options to graph ReDim arrFileList(1 To lngFileCount) As String ReDim arrSpeciesList(1 To 10) As String ReDim arrLayers(1 To 4) As String Sheets("Data").Select Range("D3").Select For j = 1 To lngFileCount arrFileList(j) = ActiveCell.Offset(0, 14 * (j - 1)).Value Next j For j = 1 To 10 arrSpeciesList(j) = ActiveCell.Offset(5, j - 3).Value Next j arrLayers(1) = "Top Layer" arrLayers(2) = "Mid Layer" arrLayers(3) = "Bottom Layer" arrLayers(4) = "All Layers" 'Select Files to Plot Call SelectToGraph(arrFileList, lngFileCount, 1) 'Select Species to Plot Call SelectToGraph(arrSpeciesList, lngFileCount, 2) 'Select Layers to Plot Call SelectToGraph(arrLayers, lngFileCount, 3) 'Create Graphs For FileNum = 1 To UBound(arrSelectedFiles) If Not arrSelectedFiles(FileNum) = "Not" Then For LayerNum = 1 To UBound(arrSelectedLayers) If Not arrSelectedLayers(LayerNum) = "Not" Then 'Create Graph Else: End If Next SpecNum Next LayerNum Else: End If Next FileNum End Sub Sub SelectToGraph(arrList, lngFileCount, GraphOption) Dim i, TopPos As Integer Dim PrintDlg As DialogSheet Dim CurrentSheet As Worksheet Dim cb As CheckBox Application.ScreenUpdating = False ' Add a temporary dialog sheet Set CurrentSheet = ActiveSheet Set PrintDlg = ActiveWorkbook.DialogSheets.Add ' Add the checkboxes TopPos = 40 For i = 1 To UBound(arrList) PrintDlg.CheckBoxes.Add 78, TopPos, 150, 16.5 PrintDlg.CheckBoxes(i).Text = arrList(i) TopPos = TopPos + 13 Next i ' Move the OK and Cancel buttons PrintDlg.Buttons.Left = 240 ' Set dialog height, width, and caption With PrintDlg.DialogFrame .Height = Application.Max _ (68, PrintDlg.DialogFrame.Top + TopPos - 34) .Width = 230 If GraphOption = 1 Then .Caption = "Select Files to Graph" ElseIf GraphOption = 2 Then .Caption = "Select Species to Graph" Else .Caption = "Select Layers to Graph" End If End With ' Change tab order of OK and Cancel buttons ' so the 1st option button will have the focus PrintDlg.Buttons("Button 2").BringToFront PrintDlg.Buttons("Button 3").BringToFront ' Display the dialog box CurrentSheet.Activate Application.ScreenUpdating = True ReDim Preserve arrSelectedFiles(1 To lngFileCount) ReDim Preserve arrSelectedSpecies(1 To 10) ReDim Preserve arrSelectedLayers(1 To 4) i = 1 If PrintDlg.Show Then For Each cb In PrintDlg.CheckBoxes If cb = 1 Then If GraphOption = 1 Then arrSelectedFiles(i) = arrList(i) ElseIf GraphOption = 2 Then arrSelectedSpecies(i) = arrList(i) Else arrSelectedLayers(i) = arrList(i) End If Else If GraphOption = 1 Then arrSelectedFiles(i) = "Not" ElseIf GraphOption = 2 Then arrSelectedSpecies(i) = "Not" Else arrSelectedLayers(i) = "Not" End If End If i = i + 1 Next cb End If ' Delete temporary dialog sheet (without a warning) Application.DisplayAlerts = False PrintDlg.Delete ' Reactivate original sheet CurrentSheet.Activate End Sub |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
LOSING FORMATS | Excel Discussion (Misc queries) | |||
Keep Losing VBAProject | Excel Programming | |||
Redimming an array dynamically assigned from range (how to redim first dimension of a 2-D array? /or/ reverse the original array order) | Excel Programming | |||
meaning of : IF(Switch; Average(array A, array B); array A) | Excel Worksheet Functions | |||
Losing the #DIV/0! | Excel Worksheet Functions |