We help IT Professionals succeed at work.

VBA:  Work with a Specific Part of an Array

bishop3000
bishop3000 asked
on
4,032 Views
Last Modified: 2010-04-01
Hi,
I've converted an Excel range  into a VBA array. Now, I'd like to work with specific data in the array--the equivalent of two columns at a time.
I can grab one value from the array (i.e. '1,1' for the first number), but I can't figure out how to work with a larger data set within the array

Here is my code for getting one value:

   Sub Averages()
   Dim myarray As Variant, ColumnsOneTwoAvg as Integer

   myarray = Range("b1:g3").Value
   ColumnsOneTwoAvg = WorksheetFunction.Average(myarray(1, 1))   ' something like (myarray(1:3,1:2)) ?
   MsgBox ColumnsOneTwoAvg

   End Sub

Thank you very much.

-Michael
Comment
Watch Question

CERTIFIED EXPERT
Most Valuable Expert 2012
Top Expert 2014
Commented:
Unlock this solution and get a sample of our free trial.
(No credit card required)
UNLOCK SOLUTION
CERTIFIED EXPERT
Commented:
Unlock this solution and get a sample of our free trial.
(No credit card required)
UNLOCK SOLUTION

Author

Commented:
Thanks Itlebearand3,
I might end up directly referencing the range.
I'm going to apply this logic to a very large set of data--I'd like to work with an array rather than a range to increase processing speed.

Your suggestion for averaging more than one value in an array is very helpful. -But Is there practical code for manipulating, say, 1000 values in a subset of an array?

Thanks again!

-Michael
CERTIFIED EXPERT
Most Valuable Expert 2012
Top Expert 2014

Commented:
I would expect that direct referencing would actually be faster...array's....particularly large ones....can become very intensive.....

The collection of cells that holds your data can be treated as an array of values anyway, if you think about it right....

To manipulate mutiple values directly, you'd use a For loop with something like:

For intRow = 2 To 1000
   ' Add 1 to each value in column C
   Cells(intRow, "C").Value = Cells(intRow, "C").Value + 1
Next


Regards,

Rob.

Author

Commented:
Much obliged Rob,
Using ranges rather than VBA arrays probably makes the code more readable as well.

Take care,
Michael
CERTIFIED EXPERT
Most Valuable Expert 2012
Top Expert 2014

Commented:
Yeah, it's better for your menal state too (LOL!) not having to deal with the headache of two sets of data, that are, essentially, the same thing....

Good luck with it.

Rob.

Commented:
I disagree with you guys.  Often times it is important to work with arrays instead of off the worksheet.  Perhaps it is beyond the intended scope of VBA, but in my specific case I need to work with arrays in VBA.  I pull the data off the worksheet once and I write data to the worksheet once.  Communication between VBA and Excel is the slowest step of the process.

So, suffice it to say, I'm curious as well if there is any way to work with just part of the data of an array.  There certainly should be, but I've read several threads where no one thought it was possible.  The best answer I've received is to create another array and copy the data one at a time into it OR to copy the entire array and then redim it down to size.  Neither solution is very attractive.  

Commented:
I found the solution!  To work with a specific range of an array, you must build an indexed array first, then use the worksheetfunction Index() to work with a specific range.

I'm not sure how to do all of that, but it's the only viable solution I've found, and I'm surprised the experts didn't come up with it. :)
Unlock the solution to this question.
Thanks for using Experts Exchange.

Please provide your email to receive a sample view!

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.