How to transfer selected cells' values into an array in VBA?

[Julian]

For example, If A1:K11 is selected, then the Array in VBA should be:

A(1,1) = A1, A(1,2) = B1 A(1,3) = C1 ...
A(2,1) = A2, A(2,2) = B2 A(2,3) = C2 ...
....

Thanks!

 

[Dave Peterson]

Option Explicit
sub testme
dim myArr as variant
myArr = activesheet.range("a1:K11").value
end sub

 

[Julian]

Thanks...
But what should I do if the selection is flexible?

 

[Dave Peterson]

myarr = selection.value

I'm not sure what flexible means???

 

[Julian]

I mean can I create a Module like this:

Public Function Testing(myInput As Variant)
Dim myArr As Variant
myArr = ActiveSheet.Range(myInput).Value
End Function

Thanks again!

 

[Dave Peterson]

Do you want to pass the address of the range?

Option Explicit
Public Function Testing(myInput As String) As Variant
Testing = ActiveSheet.Range(myInput).Value
End Function
'test it out
Sub testme()
Dim myOtherArr As Variant
myOtherArr = Testing("A1:L7")
End Sub

But you can pass it a range variable, too.

Option Explicit
Public Function Testing(myInput As Range) As Variant
Testing = myInput.Value
End Function
'more testing
Sub testme()
Dim myOtherArr As Variant
myOtherArr = Testing(ActiveSheet.Range("A1:L7"))
End Sub

I like the second version--I don't have to rely on any particular sheet being
the activesheet--just be specific in the call:

myOtherArr = testing(worksheets("sheet99").range("a1:x99"))
or
myOtherArr _
=
testing(workbooks("otherworkbook.xls").worksheets("sheet99").range("a1:x99"))

I mean can I create a Module like this:

Public Function Testing(myInput As Variant)
Dim myArr As Variant
myArr = ActiveSheet.Range(myInput).Value
End Function

Thanks again!

 

[Julian]

I know what to do now, thanks a lot!!

Do you want to pass the address of the range?

Option Explicit
Public Function Testing(myInput As String) As Variant
Testing = ActiveSheet.Range(myInput).Value
End Function
'test it out
Sub testme()
Dim myOtherArr As Variant
myOtherArr = Testing("A1:L7")
End Sub

But you can pass it a range variable, too.

Option Explicit
Public Function Testing(myInput As Range) As Variant
Testing = myInput.Value
End Function
'more testing
Sub testme()
Dim myOtherArr As Variant
myOtherArr = Testing(ActiveSheet.Range("A1:L7"))
End Sub

I like the second version--I don't have to rely on any particular sheet
being
the activesheet--just be specific in the call:

myOtherArr = testing(worksheets("sheet99").range("a1:x99"))
or
myOtherArr _
=
testing(workbooks("otherworkbook.xls").worksheets("sheet99").range("a1:x99"))