Home » Forums » Open Source Learning Center » Question and Answer

Range To Array

Vishesh's picture
Submitted by Vishesh on 19 June, 2010 - 18:25

Paste the below code in a module and try running each of the 3 procedures. The procedure named Code2 gives an error, Code1 runs fine. To fix the error in Code2 some changes have been made and the procedure with the changes is called the FinalCode.

Can you conclude anything based on these codes ? Why Code2 gives error ? See the range assigned in the code. 

Sub Code1()
 
    Dim a()
 
    Dim rng As Range
 
    Set rng = Sheet1.Range("A1:B3")
 
    a() = rng
 
    Set rng = Nothing
 
End Sub
 
 
 
Sub Code2()
 
    Dim a()
 
    Dim rng As Range
 
    Set rng = Sheet1.Range("A1")
 
    a() = rng 'Error Type Mismatch

    Set rng = Nothing
 
End Sub
 
 
 
Sub FinalCode()
 
    'This code is running OK

    Dim a()
 
    Dim rng As Range
 
    Set rng = Sheet1.Range("A1")
 
    If rng.Cells.Count > 1 Then
 
        a() = rng
 
    Else
 
        ReDim Preserve a(0)
 
        a(0) = rng
 
    End If
 
    Set rng = Nothing
 
End Sub
‹ Copy row values to multiple sheets Help needed on Optimizing search operation ›
»
Nick's picture

Excel Range to array

Submitted by Nick on 28 June, 2010 - 11:17.

I use this:

  

Sub FinalCode1()
 
    Dim a
 
    Dim rng As Range
 
    Set rng = Sheet1.Range("A1:B3")
    a = rng.Value
 
    Set rng = Sheet1.Range("A1")
    a = rng.Value
 
    Set rng = Nothing
 
End Sub

 

for the multi dimensional range, "a" is an array, and for the single value, "a" is that value.

- the output is slightly different, but the code is simpler.

 

»