• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 3555
  • Last Modified:

VBA: Work with a Specific Part of an Array

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
0
bishop3000
Asked:
bishop3000
  • 3
  • 2
  • 2
  • +1
2 Solutions
 
RobSampsonCommented:
Hi Michael,

While you're working in VBA, you may as well reference the cells directly, rather than re-store them into an array and work with that.....

Can you just use:
   ColumnsOneTwoAvg = WorksheetFunction.Average(Range("B2:C3"))
   MsgBox ColumnsOneTwoAvg

Regards,

Rob.
0
 
ltlbearand3Commented:
I am not sure why you want to convert the range to an array as you could just run the average over the range

i.e.  WorksheetFunction.Average(Range("B1:G3"))

However if you want to use this with an array you can do the following

For an average of cells b1 & c1:
WorksheetFunction.Average(myarray(1, 1), myarray(1, 2))

For an average of cells b1 & b2:
WorksheetFunction.Average(myarray(1, 1), myarray(2, 1))

For an average of cells b1, b2, c1 & c2:
WorksheetFunction.Average(myarray(1, 1), myarray(1, 2), myarray(2, 1), myarray(2, 2))

0
 
bishop3000Author 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
0
Cloud Class® Course: CompTIA Cloud+

The CompTIA Cloud+ Basic training course will teach you about cloud concepts and models, data storage, networking, and network infrastructure.

 
RobSampsonCommented:
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.
0
 
bishop3000Author Commented:
Much obliged Rob,
Using ranges rather than VBA arrays probably makes the code more readable as well.

Take care,
Michael
0
 
RobSampsonCommented:
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.
0
 
datanewbCommented:
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.  
0
 
datanewbCommented:
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. :)
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 3
  • 2
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now