Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Pass array of sheet names to subroutine
I want to insert a row in one or more adjacent sheets. I have written a
subroutine: Sub sbrInsertRow(ParamArray avarSheetSub() As Variant) With Worksheets(avarSheetSub()) .Select .Rows(5).Insert .... formatting instructions ... End With End Sub I call the subroutine, sending the names of Sheet 3 to the end: intSheet=Worksheets.Count-3 For i = 0 To intSheet avarSheet(i) = Worksheets(i + 3).Name Next sbrInsertRow avarSheet At a breakpoint on the calling line, debug.print correctly enumerates avarSheet, but at a breakpoint on the With Worksheets line of the sub, avarSheetSub is empty. I know I've done something wrong, but what?!? |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Pass array of sheet names to subroutine
One place is avarSheetSub and the other is just avarSheet.
"HartJF" wrote: I want to insert a row in one or more adjacent sheets. I have written a subroutine: Sub sbrInsertRow(ParamArray avarSheetSub() As Variant) With Worksheets(avarSheetSub()) .Select .Rows(5).Insert ... formatting instructions ... End With End Sub I call the subroutine, sending the names of Sheet 3 to the end: intSheet=Worksheets.Count-3 For i = 0 To intSheet avarSheet(i) = Worksheets(i + 3).Name Next sbrInsertRow avarSheet At a breakpoint on the calling line, debug.print correctly enumerates avarSheet, but at a breakpoint on the With Worksheets line of the sub, avarSheetSub is empty. I know I've done something wrong, but what?!? |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Pass array of sheet names to subroutine
I call the subroutine from a frm_click routine. I have used both identical
and differentiated names in testing. I differentiated the names here to avoid confusion. "JLGWhiz" wrote: One place is avarSheetSub and the other is just avarSheet. "HartJF" wrote: I want to insert a row in one or more adjacent sheets. I have written a subroutine: Sub sbrInsertRow(ParamArray avarSheetSub() As Variant) With Worksheets(avarSheetSub()) .Select .Rows(5).Insert ... formatting instructions ... End With End Sub I call the subroutine, sending the names of Sheet 3 to the end: intSheet=Worksheets.Count-3 For i = 0 To intSheet avarSheet(i) = Worksheets(i + 3).Name Next sbrInsertRow avarSheet At a breakpoint on the calling line, debug.print correctly enumerates avarSheet, but at a breakpoint on the With Worksheets line of the sub, avarSheetSub is empty. I know I've done something wrong, but what?!? |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Pass array of sheet names to subroutine
Disregard that. Did you try it with Parentheses
sbrInsertRow(avarSheet) "HartJF" wrote: I want to insert a row in one or more adjacent sheets. I have written a subroutine: Sub sbrInsertRow(ParamArray avarSheetSub() As Variant) With Worksheets(avarSheetSub()) .Select .Rows(5).Insert ... formatting instructions ... End With End Sub I call the subroutine, sending the names of Sheet 3 to the end: intSheet=Worksheets.Count-3 For i = 0 To intSheet avarSheet(i) = Worksheets(i + 3).Name Next sbrInsertRow avarSheet At a breakpoint on the calling line, debug.print correctly enumerates avarSheet, but at a breakpoint on the With Worksheets line of the sub, avarSheetSub is empty. I know I've done something wrong, but what?!? |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Pass array of sheet names to subroutine
No luck with sbrInsertRow(avarSheet), sbrInsertRow(avarSheet()), or
sbrInsertRow avarSheet() "JLGWhiz" wrote: Disregard that. Did you try it with Parentheses sbrInsertRow(avarSheet) "HartJF" wrote: I want to insert a row in one or more adjacent sheets. I have written a subroutine: Sub sbrInsertRow(ParamArray avarSheetSub() As Variant) With Worksheets(avarSheetSub()) .Select .Rows(5).Insert ... formatting instructions ... End With End Sub I call the subroutine, sending the names of Sheet 3 to the end: intSheet=Worksheets.Count-3 For i = 0 To intSheet avarSheet(i) = Worksheets(i + 3).Name Next sbrInsertRow avarSheet At a breakpoint on the calling line, debug.print correctly enumerates avarSheet, but at a breakpoint on the With Worksheets line of the sub, avarSheetSub is empty. I know I've done something wrong, but what?!? |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Pass array of sheet names to subroutine
try sbrInsertRow(avarSheet(i))
"HartJF" wrote: No luck with sbrInsertRow(avarSheet), sbrInsertRow(avarSheet()), or sbrInsertRow avarSheet() "JLGWhiz" wrote: Disregard that. Did you try it with Parentheses sbrInsertRow(avarSheet) "HartJF" wrote: I want to insert a row in one or more adjacent sheets. I have written a subroutine: Sub sbrInsertRow(ParamArray avarSheetSub() As Variant) With Worksheets(avarSheetSub()) .Select .Rows(5).Insert ... formatting instructions ... End With End Sub I call the subroutine, sending the names of Sheet 3 to the end: intSheet=Worksheets.Count-3 For i = 0 To intSheet avarSheet(i) = Worksheets(i + 3).Name Next sbrInsertRow avarSheet At a breakpoint on the calling line, debug.print correctly enumerates avarSheet, but at a breakpoint on the With Worksheets line of the sub, avarSheetSub is empty. I know I've done something wrong, but what?!? |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Pass array of sheet names to subroutine
That would insert a row on one sheet at a time. My problem is that, since
Sheet2 is an accumulating cover sheet, if I don't insert as a group, the summation formula is destroyed. Sheet2!C15 contains =sum(Sheet3:SheetN!C15), where SheetN is the final sheet. Inserting a row at Sheet3!5:5 makes Sheet2!C15 contain =#REF! I think I need to group Sheet3:SheetN and then insert row 5. I need this in a subroutine because the statement after the calling statement is: sbrInsertRow "Sheet2" "JLGWhiz" wrote: try sbrInsertRow(avarSheet(i)) "HartJF" wrote: No luck with sbrInsertRow(avarSheet), sbrInsertRow(avarSheet()), or sbrInsertRow avarSheet() "JLGWhiz" wrote: Disregard that. Did you try it with Parentheses sbrInsertRow(avarSheet) "HartJF" wrote: I want to insert a row in one or more adjacent sheets. I have written a subroutine: Sub sbrInsertRow(ParamArray avarSheetSub() As Variant) With Worksheets(avarSheetSub()) .Select .Rows(5).Insert ... formatting instructions ... End With End Sub I call the subroutine, sending the names of Sheet 3 to the end: intSheet=Worksheets.Count-3 For i = 0 To intSheet avarSheet(i) = Worksheets(i + 3).Name Next sbrInsertRow avarSheet At a breakpoint on the calling line, debug.print correctly enumerates avarSheet, but at a breakpoint on the With Worksheets line of the sub, avarSheetSub is empty. I know I've done something wrong, but what?!? |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Pass array of sheet names to subroutine
I think I discovered the answer (at least it works!)
When I call sbrInsertRow avarSheet() I am sending just one parameter, an array of variants. When the subroutine receives Sub sbrInsertRow(avarSheetSub as Variant) it receives one parameter, a variant containing an array. I can still refer to individual elements in the receiving array Debug.Print avarSheetSub(0) or I can refer to the entire array Worksheets(avarSheetSub).Select This is terribly confusing, but I found a helpful strand at http://www.mrexcel.com/archive2/13000/14499.htm Thanks for your help JLGWhiz! "JLGWhiz" wrote: try sbrInsertRow(avarSheet(i)) "HartJF" wrote: No luck with sbrInsertRow(avarSheet), sbrInsertRow(avarSheet()), or sbrInsertRow avarSheet() "JLGWhiz" wrote: Disregard that. Did you try it with Parentheses sbrInsertRow(avarSheet) "HartJF" wrote: I want to insert a row in one or more adjacent sheets. I have written a subroutine: Sub sbrInsertRow(ParamArray avarSheetSub() As Variant) With Worksheets(avarSheetSub()) .Select .Rows(5).Insert ... formatting instructions ... End With End Sub I call the subroutine, sending the names of Sheet 3 to the end: intSheet=Worksheets.Count-3 For i = 0 To intSheet avarSheet(i) = Worksheets(i + 3).Name Next sbrInsertRow avarSheet At a breakpoint on the calling line, debug.print correctly enumerates avarSheet, but at a breakpoint on the With Worksheets line of the sub, avarSheetSub is empty. I know I've done something wrong, but what?!? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Populate an array with sheet names? | Excel Programming | |||
XL2007 and array of sheet names? | Excel Discussion (Misc queries) | |||
Pass a Variable List Of Values To Subroutine or Function | Excel Programming | |||
How to pass a workshhet name as a parameter into a subroutine ? | Excel Discussion (Misc queries) | |||
Sheet Names Array | Excel Programming |