Assigning to an array
 

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.

Assigning to an array
 

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

Assigning to an array
 

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" wrote in message
...
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.

Assigning to an array
 

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

Assigning to an array
 

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" wrote in message
...
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.

Assigning to an array
 

Geoff,

I'm not sure how much, if any, difference static arrays would have on
performance. I know that what we've done here is a typical practice.

Doug

"Geoff" wrote in message
...
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" wrote in message
...
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.

Assigning to an array
 

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

Assigning to an array
 

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