?
Solved

VBA:  Work with a Specific Part of an Array

Posted on 2007-10-07
8
Medium Priority
?
3,057 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
0
Comment
Question by:bishop3000
  • 3
  • 2
  • 2
  • +1
8 Comments
 
LVL 65

Accepted Solution

by:
RobSampson earned 375 total points
ID: 20031701
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
 
LVL 20

Assisted Solution

by:ltlbearand3
ltlbearand3 earned 375 total points
ID: 20031703
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
 

Author Comment

by:bishop3000
ID: 20031852
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
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
LVL 65

Expert Comment

by:RobSampson
ID: 20032078
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
 

Author Comment

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

Take care,
Michael
0
 
LVL 65

Expert Comment

by:RobSampson
ID: 20032095
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
 

Expert Comment

by:datanewb
ID: 29340297
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
 

Expert Comment

by:datanewb
ID: 29341192
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

Featured Post

Prep for the ITIL® Foundation Certification Exam

December’s Course of the Month is now available! Enroll to learn ITIL® Foundation best practices for delivering IT services effectively and efficiently.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Freeze panes is an option within all variants of Excel to enable parts of a sheet to remain stationary when the cursor is in another part of the sheet. This is a very useful feature which is overlooked or under used.
This article describes how to use a set of graphical playing cards to create a Draw Poker game in Excel or VB6.
The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.

850 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question