How to get rows/cols from Excel-sheet into a VBA-Array without loops? 3

[hannal]

Hi!

My main problem is how to convert a Range object into an Array without using any loops?

This is the background:

I need to write a user-defined function in VBA for Excel that takes a Range (2 cols and many rows) in an Excel-sheet as an argument and rearrange the rows so that the values in the left column is sorted by the quicksort algorithm.

The function should also take a string as an argument which it would use to do a binary search in the left (now sorted) column and finally the function would return the value in the right column that is on the same row as the found string.

How can I do this?

I already have two subs, one that sorts a 1-dim array (it must be an array) with quicksort, and one that performs a binary search on a 1-dim array.

My main problem is, as said above, how to convert a Range object into an Array without using any loops. Or maybe there is a totally different approach that is better? I don’t want to use Excel-functions, like vlookup or match.

Please Help!

/Hl

 

[xlbo]

You can create a range array by using

Dim mArr as variant

mArr = range("A1:A100&quot

You can then reference this by using

mArr(1,1) (the 1st cell in 1st col)
mArr(1,2) (the 1st cell in 2nd col)

so the 1st number will refer to the relative position in the range vertically and the 2nd number refers to whether it is in col1 or col2 in the range


Rgds, Geoff
It's Super Happy Campo Funtime!
Want the best answers to your questions ? - then read me baby one more time - faq222-2244

 

[castor2003]

Hannal, this is your lucky day, I am a Visual Foxpro Programmer who accidently landed on this Forum. Well the following will place a range in an array without using loops.

Public Sub c2003()

Dim vLove As Variant

Range("a1").Value = "Love"
Range("b1").Value = 100
Range("a2").Value = "Hate"
Range("b2").Value = 0
Range("a3").Value = "Peace"
Range("b3").Value = 80

'The above would have populated the range A1:B3. Now vLove is a 2x3 array with the above values

vLove = Range("a1:b3").Value2

End Sub

Good Luck!!!

 

[hannal]

Thank you so much, both of you! It worked beautifully when I wrote my function like this:

Function myFun(area1 As Range)
   Dim area2 As Variant
     ‘…mycode…
   area2 = area1.Value2
   mySub area2
     ‘…mycode…
End Function

With:

Sub mySub(area3 As Variant)
   Dim i, j, k, l As Integer
     ‘…mycode…
   area3(i,j) = area3(k,l)
     ‘…mycode…etc
End mySub

/ HL

 

[chandlm]

XLBO:

Excellent tip. This saves so much hassle of writing the loop to add the cell values to the array. Also seems alot quicker

This is something that I will be incorporating into my future macros.

Cheers

Matt

 

[xlbo]

chandlm - certainly is quicker. I use this method in a kinda lookup function that I use on massive data sets - vlookup can take hours to recalc but with a VBA method using this to pick up the 2 sets of data, it can be reduced to (for my data sets) apprx 30 secs

Rgds, Geoff
It's Super Happy Campo Funtime!
Want the best answers to your questions ? - then read me baby one more time - faq222-2244

 

[ssVBAdev]

Yup! Star From me too. I like it!

Hope you have a good "Super Happy Campo Funtime!"


********************
What's the best way to get the answers you need?? See FAQ222-2244 for details!