I would like to run the same code over a number of worksheets, and to this
end I've declared a static array as follows:

Dim tmpBk As Workbook
Dim wsArr(1 To 8) As String
Dim wsName As String
Dim tmpWs As Worksheet
Dim tmpRng As Range
Dim dataName As String
Dim firstDay As Date

firstDay = DateSerial(yr, mth, 1)
dataName = DATAPATH & yr & "hi\" & Format(firstDay, "mmyy") & "hi.xls"
Workbooks.Open dataName, 0, True
wsArr = Array("Value1", "Value2", "Value3", "Value4", _
"Value5", "Value6", "Value7", "Value8")

Set tmpBk = Workbooks.Open(HCMCPATH & "something.xls", 0)
For Each wsName In wsArr
Set tmpWs = tmpBk.Worksheets(wsName)
[snip]
Next wsName

When I try to compile this I get the error "Can't assign to array". I know I
could go through one by one and assign the values by saying
wsArr(1) = Value1
for instance but I'd like to get them all done at once.

Any suggestions?

TIA
--
There are 10 types of people in the world - those who understand binary and
those who don't.

Remove:
Dim wsArr(1 To 8) As String
--
Gary''s Student - gsnu200791

Geoff,

Try this:

Dim tmpBk As Workbook
Dim wsArr As Variant
Dim tmpWs As Worksheet
Dim tmpRng As Range
Dim dataName As String
Dim firstDay As Date

firstDay = DateSerial(yr, mth, 1)
dataName = DATAPATH & yr & "hi\" & Format(firstDay, "mmyy") & "hi.xls"
Workbooks.Open dataName, 0, True
Set tmpBk = Workbooks.Open(HCMCPATH & "something.xls", 0)
Set wsArr = tmpBk.Worksheets(Array("Value1", "Value2", "Value3", "Value4",
"Value5", "Value6", "Value7", "Value8"))
For Each tmpWs In wsArr
'[snip]
Next tmpWs

hth,

Doug

"Geoff" <(E-Mail Removed)> wrote in message
news:82342CC5-A8E9-4AA5-A299-(E-Mail Removed)...
>I would like to run the same code over a number of worksheets, and to this
> end I've declared a static array as follows:
>
> Dim tmpBk As Workbook
> Dim wsArr(1 To 8) As String
> Dim wsName As String
> Dim tmpWs As Worksheet
> Dim tmpRng As Range
> Dim dataName As String
> Dim firstDay As Date
>
> firstDay = DateSerial(yr, mth, 1)
> dataName = DATAPATH & yr & "hi\" & Format(firstDay, "mmyy") & "hi.xls"
> Workbooks.Open dataName, 0, True
> wsArr = Array("Value1", "Value2", "Value3", "Value4", _
> "Value5", "Value6", "Value7", "Value8")
>
> Set tmpBk = Workbooks.Open(HCMCPATH & "something.xls", 0)
> For Each wsName In wsArr
> Set tmpWs = tmpBk.Worksheets(wsName)
> [snip]
> Next wsName
>
> When I try to compile this I get the error "Can't assign to array". I know
> I
> could go through one by one and assign the values by saying
> wsArr(1) = Value1
> for instance but I'd like to get them all done at once.
>
> Any suggestions?
>
> TIA
> --
> There are 10 types of people in the world - those who understand binary
> and
> those who don't.

If the functions in the freely downloadable file at
http://home.pacbel.net/beban are available to your workbook

Dim wsArr() As String
ReDim wsArr(0 To 0)
Assign Array("Value1", "Value2", "Value3", "Value4", "Value5", _
"Value6", "Value7", "Value8"), wsArr

Alan Beban

Geoff wrote:
> I would like to run the same code over a number of worksheets, and to this
> end I've declared a static array as follows:
>
> Dim tmpBk As Workbook
> Dim wsArr(1 To 8) As String
> Dim wsName As String
> Dim tmpWs As Worksheet
> Dim tmpRng As Range
> Dim dataName As String
> Dim firstDay As Date
>
> firstDay = DateSerial(yr, mth, 1)
> dataName = DATAPATH & yr & "hi\" & Format(firstDay, "mmyy") & "hi.xls"
> Workbooks.Open dataName, 0, True
> wsArr = Array("Value1", "Value2", "Value3", "Value4", _
> "Value5", "Value6", "Value7", "Value8")
>
> Set tmpBk = Workbooks.Open(HCMCPATH & "something.xls", 0)
> For Each wsName In wsArr
> Set tmpWs = tmpBk.Worksheets(wsName)
> [snip]
> Next wsName
>
> When I try to compile this I get the error "Can't assign to array". I know I
> could go through one by one and assign the values by saying
> wsArr(1) = Value1
> for instance but I'd like to get them all done at once.
>
> Any suggestions?
>
> TIA

Thanks Doug,

that's pretty similar to what I ended up doing:

Dim tmpBk As Workbook
Dim wsArr
Dim i As Integer
Dim tmpWs As Worksheet
Dim tmpRng As Range
Dim dataName As String
Dim firstDay As Date

firstDay = DateSerial(yr, mth, 1)
dataName = DATAPATH & yr & "hi\" & Format(firstDay, "mmyy") & "hi.xls"
Workbooks.Open dataName, 0, True
wsArr = Array("Value1", "Value2", "Value3", "Value4", _
"Value5", "Value6", "Value7", "Value8")
Set tmpBk = Workbooks.Open(HCMCPATH & "something.xls", 0)
For i = LBound(wsArr) To UBound(wsArr)
Set tmpWs = tmpBk.Worksheets(wsArr(i))
[snip]
Next i

Do you think there's anything to be gained (performance-wise) by using
static arrays? I guess this was the thinking that led me to the approach of
declaring the array to begin with. Just to put it in context, I am
refactoring/rewriting a lot of the code in a legacy project (originally
written for XL97) here. The old sub selected all the sheets at once to run
the code over them, as well as opening up a lot of source workbooks
unnecessarily, and eventually this led to Excel running out of memory while
running this particular macro, so I guess I would like to try to improve
performance as much as possible.

Thanks
Geoff
--
There are 10 types of people in the world - those who understand binary and
those who don't.


"Doug Glancy" wrote:

> Geoff,
>
> Try this:
>
> Dim tmpBk As Workbook
> Dim wsArr As Variant
> Dim tmpWs As Worksheet
> Dim tmpRng As Range
> Dim dataName As String
> Dim firstDay As Date
>
> firstDay = DateSerial(yr, mth, 1)
> dataName = DATAPATH & yr & "hi\" & Format(firstDay, "mmyy") & "hi.xls"
> Workbooks.Open dataName, 0, True
> Set tmpBk = Workbooks.Open(HCMCPATH & "something.xls", 0)
> Set wsArr = tmpBk.Worksheets(Array("Value1", "Value2", "Value3", "Value4",
> "Value5", "Value6", "Value7", "Value8"))
> For Each tmpWs In wsArr
> '[snip]
> Next tmpWs
>
> hth,
>
> Doug
>
> "Geoff" <(E-Mail Removed)> wrote in message
> news:82342CC5-A8E9-4AA5-A299-(E-Mail Removed)...
> >I would like to run the same code over a number of worksheets, and to this
> > end I've declared a static array as follows:
> >
> > Dim tmpBk As Workbook
> > Dim wsArr(1 To 8) As String
> > Dim wsName As String
> > Dim tmpWs As Worksheet
> > Dim tmpRng As Range
> > Dim dataName As String
> > Dim firstDay As Date
> >
> > firstDay = DateSerial(yr, mth, 1)
> > dataName = DATAPATH & yr & "hi\" & Format(firstDay, "mmyy") & "hi.xls"
> > Workbooks.Open dataName, 0, True
> > wsArr = Array("Value1", "Value2", "Value3", "Value4", _
> > "Value5", "Value6", "Value7", "Value8")
> >
> > Set tmpBk = Workbooks.Open(HCMCPATH & "something.xls", 0)
> > For Each wsName In wsArr
> > Set tmpWs = tmpBk.Worksheets(wsName)
> > [snip]
> > Next wsName
> >
> > When I try to compile this I get the error "Can't assign to array". I know
> > I
> > could go through one by one and assign the values by saying
> > wsArr(1) = Value1
> > for instance but I'd like to get them all done at once.
> >
> > Any suggestions?
> >
> > TIA
> > --
> > There are 10 types of people in the world - those who understand binary
> > and
> > those who don't.
>
>
>

Thanks Alan

I tried accessing the link there and got a 502 error - I'm not sure whether
this is a problem with the server at that end or maybe the webmarshall here.

Thanks anyway
--
There are 10 types of people in the world - those who understand binary and
those who don't.


"Alan Beban" wrote:

> If the functions in the freely downloadable file at
> http://home.pacbel.net/beban are available to your workbook
>
> Dim wsArr() As String
> ReDim wsArr(0 To 0)
> Assign Array("Value1", "Value2", "Value3", "Value4", "Value5", _
> "Value6", "Value7", "Value8"), wsArr
>
> Alan Beban
>
> Geoff wrote:
> > I would like to run the same code over a number of worksheets, and to this
> > end I've declared a static array as follows:
> >
> > Dim tmpBk As Workbook
> > Dim wsArr(1 To 8) As String
> > Dim wsName As String
> > Dim tmpWs As Worksheet
> > Dim tmpRng As Range
> > Dim dataName As String
> > Dim firstDay As Date
> >
> > firstDay = DateSerial(yr, mth, 1)
> > dataName = DATAPATH & yr & "hi\" & Format(firstDay, "mmyy") & "hi.xls"
> > Workbooks.Open dataName, 0, True
> > wsArr = Array("Value1", "Value2", "Value3", "Value4", _
> > "Value5", "Value6", "Value7", "Value8")
> >
> > Set tmpBk = Workbooks.Open(HCMCPATH & "something.xls", 0)
> > For Each wsName In wsArr
> > Set tmpWs = tmpBk.Worksheets(wsName)
> > [snip]
> > Next wsName
> >
> > When I try to compile this I get the error "Can't assign to array". I know I
> > could go through one by one and assign the values by saying
> > wsArr(1) = Value1
> > for instance but I'd like to get them all done at once.
> >
> > Any suggestions?
> >
> > TIA
>

Geoff wrote:
> Thanks Alan
>
> I tried accessing the link there and got a 502 error - I'm not sure whether
> this is a problem with the server at that end or maybe the webmarshall here.
>
> Thanks anyway

Sorry. It needs 2 l's in pacbell.

http://home.pacbell.net/beban

At any rate, you received a number of responses indicating that you
won't have a problem if you use a Variant() array (or an array contained
within a Variant variable) rather than an array of a different type. The
function I was referring you to allows assignment to arrays of any
built-in type.

Alan Bebam