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

J

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!
 
D

Dave Peterson

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

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!
 
D

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!
Click to expand...
 
J

Julian

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


Dave Peterson said:
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"))
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

VBA Coding Help for Beginner 0
VBA-Table Word 2019 - Move a selected text, from a cell to the adjacent cell to its right, by pressing the ENTER key 0
Data Validation and drop-down list using IF, AND and what else? 8
calculate total hours/minutes from two time cells 7
VBA for Dependents shortcut menu 0
Getting MIN to work inside of an Array, or another solution 3
making cells uncopyable 1
Arrays and scattered cell values or another way? 16

Top