Assigning to an array

G

Geoff

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
 
D

Doug Glancy

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
 
A

Alan Beban

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
 
G

Geoff

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 said:
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 said:
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
Click to expand...
Click to expand...
 
G

Geoff

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 said:
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
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
Click to expand...
Click to expand...
 
A

Alan Beban

Geoff said:
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 :)
Click to expand...

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Assigning Variables To An array Throughout a loop 3
assigning values to an array 2
Assign function return value to an array 2
assigning a query to an array 5
Assigning a value to an array cell 1
error 1004 when assigning an array to a range 7
Writing an array(2d) of user types to a worksheet 3
Storing an array to a Session 3

Top