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

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!

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

Julian wrote:
>
> 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

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


"Dave Peterson" <[email protected]> wrote in message
news:[email protected]...
> Option Explicit
> sub testme
> dim myArr as variant
> myArr = activesheet.range("a1:K11").value
> end sub
>
> Julian wrote:
>>
>> 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

myarr = selection.value

I'm not sure what flexible means???

Julian wrote:
>
> Thanks...
> But what should I do if the selection is flexible?
>
> "Dave Peterson" <[email protected]> wrote in message
> news:[email protected]...
> > Option Explicit
> > sub testme
> > dim myArr as variant
> > myArr = activesheet.range("a1:K11").value
> > end sub
> >
> > Julian wrote:
> >>
> >> 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

--

Dave Peterson

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" <[email protected]> wrote in message
news:[email protected]...
> myarr = selection.value
>
> I'm not sure what flexible means???
>
> Julian wrote:
>>
>> Thanks...
>> But what should I do if the selection is flexible?
>>
>> "Dave Peterson" <[email protected]> wrote in message
>> news:[email protected]...
>> > Option Explicit
>> > sub testme
>> > dim myArr as variant
>> > myArr = activesheet.range("a1:K11").value
>> > end sub
>> >
>> > Julian wrote:
>> >>
>> >> 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
>
> --
>
> 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"))


Julian wrote:
>
> 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" <[email protected]> wrote in message
> news:[email protected]...
> > myarr = selection.value
> >
> > I'm not sure what flexible means???
> >
> > Julian wrote:
> >>
> >> Thanks...
> >> But what should I do if the selection is flexible?
> >>
> >> "Dave Peterson" <[email protected]> wrote in message
> >> news:[email protected]...
> >> > Option Explicit
> >> > sub testme
> >> > dim myArr as variant
> >> > myArr = activesheet.range("a1:K11").value
> >> > end sub
> >> >
> >> > Julian wrote:
> >> >>
> >> >> 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
> >
> > --
> >
> > Dave Peterson

--

Dave Peterson

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


"Dave Peterson" <[email protected]> wrote in message
news:[email protected]...
> 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"))
>
>
> Julian wrote:
>>
>> 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" <[email protected]> wrote in message
>> news:[email protected]...
>> > myarr = selection.value
>> >
>> > I'm not sure what flexible means???
>> >
>> > Julian wrote:
>> >>
>> >> Thanks...
>> >> But what should I do if the selection is flexible?
>> >>
>> >> "Dave Peterson" <[email protected]> wrote in message
>> >> news:[email protected]...
>> >> > Option Explicit
>> >> > sub testme
>> >> > dim myArr as variant
>> >> > myArr = activesheet.range("a1:K11").value
>> >> > end sub
>> >> >
>> >> > Julian wrote:
>> >> >>
>> >> >> 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
>> >
>> > --
>> >
>> > Dave Peterson
>
> --
>
> Dave Peterson