Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Load an array with Sheet names
varr is a Variant array. I wish to load it with names of sheets in the
activeworkbook. I have: If Global_PrintAllBooks_Sheets = True Then ReDim varr(1 To Worksheets.Count) For i = 1 To UBound(varr) varr(i) = ws.Name i = i + 1 Next I receive run time error 91 (Object variable or With block variable not set) on varr(i) = ws.Name Why is this please? Regards. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Load an array with Sheet names
Be sure you have: Set ws = Worksheets and change varr(i) = ws.Name to
read varr(i) = ws(i).Name. Also, take out: i = i + 1 (the Next satement causes i to increment by one automatically) "S G Booth" wrote: varr is a Variant array. I wish to load it with names of sheets in the activeworkbook. I have: If Global_PrintAllBooks_Sheets = True Then ReDim varr(1 To Worksheets.Count) For i = 1 To UBound(varr) varr(i) = ws.Name i = i + 1 Next I receive run time error 91 (Object variable or With block variable not set) on varr(i) = ws.Name Why is this please? Regards. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Load an array with Sheet names
I am not too sure why you would like to load an array with the sheet names.
The sheets are a collection and the names can be accessed with a for next loop. dim wks as worksheet for each wks in worksheets msgbox wks.name next wks If you still need the array you can populate it from within the loop... but I am not sure that it is necessary. HTH "S G Booth" wrote: varr is a Variant array. I wish to load it with names of sheets in the activeworkbook. I have: If Global_PrintAllBooks_Sheets = True Then ReDim varr(1 To Worksheets.Count) For i = 1 To UBound(varr) varr(i) = ws.Name i = i + 1 Next I receive run time error 91 (Object variable or With block variable not set) on varr(i) = ws.Name Why is this please? Regards. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Load an array with Sheet names
Had ws already Dimmed as Worksheet, so this seems to work:
ReDim varr(1 To Worksheets.Count) For i = 1 To UBound(varr) varr(i) = Worksheets(i).Name Next Many thanks for your help. Regards. "Bob" wrote in message ... Be sure you have: Set ws = Worksheets and change varr(i) = ws.Name to read varr(i) = ws(i).Name. Also, take out: i = i + 1 (the Next satement causes i to increment by one automatically) "S G Booth" wrote: varr is a Variant array. I wish to load it with names of sheets in the activeworkbook. I have: If Global_PrintAllBooks_Sheets = True Then ReDim varr(1 To Worksheets.Count) For i = 1 To UBound(varr) varr(i) = ws.Name i = i + 1 Next I receive run time error 91 (Object variable or With block variable not set) on varr(i) = ws.Name Why is this please? Regards. |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Load an array with Sheet names
I appreciate that, thanks. It's just that later in the routine
a fairly complicated "print" routine runs. It seems fairly robust, so far, and revolves around the sheet names held by varr. This later routine populates varr via a listbox ( ie if selected, then add to varr) and thus allows a user to select sheets to be entered into the print routine. What I have yet to accommodate is for all sheets to be worked with. Rather than mess with the later code, I hoped to populate varr with all the sheet names. Regards and thanks. "Jim Thomlinson" wrote in message ... I am not too sure why you would like to load an array with the sheet names. The sheets are a collection and the names can be accessed with a for next loop. dim wks as worksheet for each wks in worksheets msgbox wks.name next wks If you still need the array you can populate it from within the loop... but I am not sure that it is necessary. HTH "S G Booth" wrote: varr is a Variant array. I wish to load it with names of sheets in the activeworkbook. I have: If Global_PrintAllBooks_Sheets = True Then ReDim varr(1 To Worksheets.Count) For i = 1 To UBound(varr) varr(i) = ws.Name i = i + 1 Next I receive run time error 91 (Object variable or With block variable not set) on varr(i) = ws.Name Why is this please? Regards. |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Load an array with Sheet names
Just for future reference: If you wanted to use ws, don't Dim it as a
Worksheet. Dim it as an Object, then Set ws = Worksheets (<<plural since you want the collection) "S G Booth" wrote: Had ws already Dimmed as Worksheet, so this seems to work: ReDim varr(1 To Worksheets.Count) For i = 1 To UBound(varr) varr(i) = Worksheets(i).Name Next Many thanks for your help. Regards. "Bob" wrote in message ... Be sure you have: Set ws = Worksheets and change varr(i) = ws.Name to read varr(i) = ws(i).Name. Also, take out: i = i + 1 (the Next satement causes i to increment by one automatically) "S G Booth" wrote: varr is a Variant array. I wish to load it with names of sheets in the activeworkbook. I have: If Global_PrintAllBooks_Sheets = True Then ReDim varr(1 To Worksheets.Count) For i = 1 To UBound(varr) varr(i) = ws.Name i = i + 1 Next I receive run time error 91 (Object variable or With block variable not set) on varr(i) = ws.Name Why is this please? Regards. |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Load an array with Sheet names
while Stuart has already arrived at a solution and you are providing future
adivce, Why dim it as object if you know it is going to be worksheets? Dim ws As Worksheets should do nicely. -- Regards, Tom Ogilvy "Bob" wrote in message ... Just for future reference: If you wanted to use ws, don't Dim it as a Worksheet. Dim it as an Object, then Set ws = Worksheets (<<plural since you want the collection) "S G Booth" wrote: Had ws already Dimmed as Worksheet, so this seems to work: ReDim varr(1 To Worksheets.Count) For i = 1 To UBound(varr) varr(i) = Worksheets(i).Name Next Many thanks for your help. Regards. "Bob" wrote in message ... Be sure you have: Set ws = Worksheets and change varr(i) = ws.Name to read varr(i) = ws(i).Name. Also, take out: i = i + 1 (the Next satement causes i to increment by one automatically) "S G Booth" wrote: varr is a Variant array. I wish to load it with names of sheets in the activeworkbook. I have: If Global_PrintAllBooks_Sheets = True Then ReDim varr(1 To Worksheets.Count) For i = 1 To UBound(varr) varr(i) = ws.Name i = i + 1 Next I receive run time error 91 (Object variable or With block variable not set) on varr(i) = ws.Name Why is this please? Regards. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Warehouse load sheet - Help!! | Excel Discussion (Misc queries) | |||
How to load multiple names ? | Excel Discussion (Misc queries) | |||
XL2007 and array of sheet names? | Excel Discussion (Misc queries) | |||
return all worksheet tab names and chart sheet tab names in report - an example | Excel Programming | |||
Sheet Names Array | Excel Programming |