Pass array of sheet names to subroutine

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

HartJF - 21 Sep 2007 20:08 GMT

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 to this Message

JLGWhiz - 21 Sep 2007 20:50 GMT

One place is avarSheetSub and the other is just avarSheet.

> I want to insert a row in one or more adjacent sheets. I have written a
> subroutine:
[quoted text clipped - 20 lines]
>
> I know I've done something wrong, but what?!?

Reply to this Message

HartJF - 21 Sep 2007 20:56 GMT

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.

> One place is avarSheetSub and the other is just avarSheet.
>
[quoted text clipped - 22 lines]
> >
> > I know I've done something wrong, but what?!?

Reply to this Message

JLGWhiz - 21 Sep 2007 20:58 GMT

Disregard that. Did you try it with Parentheses

sbrInsertRow(avarSheet)

> I want to insert a row in one or more adjacent sheets. I have written a
> subroutine:
[quoted text clipped - 20 lines]
>
> I know I've done something wrong, but what?!?

Reply to this Message

HartJF - 21 Sep 2007 21:04 GMT

No luck with sbrInsertRow(avarSheet), sbrInsertRow(avarSheet()), or
sbrInsertRow avarSheet()

> Disregard that. Did you try it with Parentheses
>
[quoted text clipped - 24 lines]
> >
> > I know I've done something wrong, but what?!?

Reply to this Message

JLGWhiz - 21 Sep 2007 21:18 GMT

try sbrInsertRow(avarSheet(i))

> No luck with sbrInsertRow(avarSheet), sbrInsertRow(avarSheet()), or
> sbrInsertRow avarSheet()
[quoted text clipped - 27 lines]
> > >
> > > I know I've done something wrong, but what?!?

Reply to this Message

HartJF - 21 Sep 2007 21:30 GMT

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"

> try sbrInsertRow(avarSheet(i))
>
[quoted text clipped - 29 lines]
> > > >
> > > > I know I've done something wrong, but what?!?

Reply to this Message

HartJF - 21 Sep 2007 22:48 GMT

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!

> try sbrInsertRow(avarSheet(i))
>
[quoted text clipped - 29 lines]
> > > >
> > > > I know I've done something wrong, but what?!?

Reply to this Message