Array <---> Range

  • Thread starter Thread starter Charley Kyd
  • Start date Start date
C

Charley Kyd

If Test is defined as a contiguous three-cell column, this macro works:

Sub Foo()
Dim rngTest As Range, saTest() As String

Set rngTest = ThisWorkbook.Names("Test").RefersToRange
ReDim saTest(1 To 3, 1)

saTest(1, 1) = "a"
saTest(2, 1) = "b"
saTest(3, 1) = "c"

rngTest = saTest
End Sub

The nice thing about this approach is that we can very quickly write data to
the spreadsheet, and without looping.

However, what if we want to go in the other direction? This statement fails:
saTest = rngTest

So, can anyone suggest a way to load an array with values from a
range--without looping?

Thanks.

Charley
 
Sub Bar(rngTest as range)

dim vArray as variant
dim j as long

vArray=rngTest

for j=1 to 3
msgbox vArray(j,1)
next j

end sub

--- this uses a variant to hold an array, rather than an array of variants.

hth
Charles
______________________
Decision Models
FastExcel Version 2 now available.
www.DecisionModels.com/FxlV2WhatsNew.htm
 
Charles said:
Sub Bar(rngTest as range)

dim vArray as variant
dim j as long

vArray=rngTest

for j=1 to 3
msgbox vArray(j,1)
next j

end sub
Click to expand...

Charley Kyd's array is type String(). It can't be loaded directly from
a worksheet range without looping.

Alan Beban
 
Things can be speeded up considerably, however, by looping in memory
rather than looping to the range. E.g.,

Sub Foo()
Dim rngTest As Range, saTest() As String, srTest As Variant

Set rngTest = ThisWorkbook.Names("Test").RefersToRange
ReDim saTest(1 To rngTest.Rows.Count, 1)

srTest = rngTest

For i = 1 To rngTest.Rows.Count
saTest(i, 1) = srTest(i, 1)
Next
End Sub

Alan Beban
 
Hi Charley,

Thank you for posting in MSDN managed newsgroup!

I'd suggest you can try the method below to load one array from the range.

'code begin ---------------------------------------------------
Dim rngTest As Range
Dim saTest
Dim resultArray()

saTest = ThisWorkbook.Names("Test").RefersToRange.Value
MsgBox LBound(saTest, 1)
MsgBox UBound(saTest, 1)

resultArray = saTest
'code end ----------------------------------------------------

However, in this way, what you get back will an array of variant, not an
array of string. You may need to change the type of saTest.

Please feel free to let me know if you have any further questions.

Does this answer your question? Thank you for using Microsoft NewsGroup!

Wei-Dong Xu
Microsoft Product Support Services
Get Secure! - www.microsoft.com/security
This posting is provided "AS IS" with no warranties, and confers no rights.
 
If I had wanted to use variants, there would have been no problem in the
first place. I thought my Dim statement made that obvious, but I guess I
should have stated it explicitly.

Thanks anyway.

Charley
 
Hi Charley,

Thank you for replying!

You are very right. Based on my research, without looping, we can only use
an array of variant in this scenario. After that, we will need to change
the type of the array if necessary.

It is my pleasure to be some of service.

Thank you for using Microsoft NewsGroup!

Wei-Dong Xu
Microsoft Product Support Services
Get Secure! - www.microsoft.com/security
This posting is provided "AS IS" with no warranties, and confers no rights.
 
Hmmm...My first response didn't appear in the newsgroup. I'll try again.

If I had wanted to use variants, there would have been no problem in the
first place. I thought my Dim statement made that obvious, but I guess I
should have stated the problem more clearly.

Thanks anyway.

Charley
 
Hi Charley,

Thank you for replying!

I have replied your first reponse this morning. Since the reply doesn't
appear, I list the reply for you.

You are very right. Based on my research, without looping, we can only use
an array of variant in this scenario. After that, we will need to change
the type of the array if necessary.

It is my pleasure to be some of service.

Thank you for using Microsoft NewsGroup!

Wei-Dong Xu
Microsoft Product Support Services
Get Secure! - www.microsoft.com/security
This posting is provided "AS IS" with no warranties, and confers no rights.
 
Back
Top