Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Array <--- Range
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Array <--- Range
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 "Charley Kyd" wrote in message ... 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Array <--- Range
"Charley Kyd" wrote
Dim rngTest As Range, saTest() As String . . . So, can anyone suggest a way to load an array with values from a range--without looping? Charles Williams wrote: 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 Charley Kyd's array is type String(). It can't be loaded directly from a worksheet range without looping. Alan Beban |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Array <--- Range
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 Alan Beban wrote: "Charley Kyd" wrote Dim rngTest As Range, saTest() As String . . . So, can anyone suggest a way to load an array with values from a range--without looping? Charles Williams wrote: 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 Charley Kyd's array is type String(). It can't be loaded directly from a worksheet range without looping. Alan Beban |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Array <--- Range
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. |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Array <--- Range
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 "Charley Kyd" wrote in message ... 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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Array <--- Range
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 "Charley Kyd" wrote in message ... 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 |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Array <--- Range
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
operating over a range in an array | Excel Discussion (Misc queries) | |||
How to fix an array or range | Excel Discussion (Misc queries) | |||
Range as array | Excel Programming | |||
Range as array | Excel Programming | |||
How Can I copy all value of the array into the range? | Excel Programming |