 |

09-19-2016, 09:52 AM
|
Newcomer
|
|
Join Date: May 2010
Location: South Bend, IN
Posts: 5
|
|
Excel Data to 2D Array
Hello,
I have written a program that works with Excel data and is currently working, but I'm trying to improve the speed of the calculations. Since it is interacting with Excel and there is a large amount of data, it takes a long time to calculate. I am in the process of converting the data into arrays to speed up the process, and it's going well on the first steps of the program. However, with this one table, I'm having trouble getting the data into an array how I need it to function.
I have an Excel spreadsheet (Public xlProductsWorkSheet As Excel.Worksheet) with data that looks like the attached image:
I know I can use the command:
Code:
arrStockLengths = xlProductsWorkSheet.UsedRange.Value
to get the entire sheet into the array, but it doesn't quite work.
What I need in the Array (arrStockLengths) is values that look like this:
{Product1, Material1, 25}
{Product1, Material2, 6}
{Product1, Material3, 18}, etc.
{Product2, Material1, 2}
{Product2, Material2, 24}
{Product2, Material3, 13}, etc.
I tried the following code, to no avail:
Code:
Dim arrStockLengths(,) As Object, strProduct As String, strMaterial As String, dblLength As Double
intProductColumn = 2
intProductRow = 2
arrStockLengths = Nothing
With xlProductsWorkSheet
Do Until .Cells(1, intProductColumn).Value = ""
strProduct = .Cells(1, intProductColumn).Value
Do Until .Cells(intProductRow, 1).Value = ""
strMaterial = .Cells(intProductRow, 1).Value
dblLength = .Cells(intProductRow, intProductColumn).Value
arrStockLengths(arrStockLengths.GetUpperBound(0) + 1, 0) = {strProduct, strMaterial, dblLength}
intProductRow += 1
Loop
intProductColumn += 1
intProductRow = 2
Loop
End With
I only need this to run one time, which will get all the "Stock Lengths" into the Array arrStockLengths, and then I can calculate the data with the Array.
Please help! Thanks!
|
|

09-19-2016, 07:35 PM
|
 |
Sapience.Aquire
Super Moderator * Expert *
|
|
Join Date: Oct 2003
Location: Lake Bluff, Ill., U.S.
Posts: 3,459
|
|
Search for "dynamic array vba" and google will should provide a third of a million hits...don't read them all. You will need to know the dimension of the first of your array's fields and the second field will be grown with Redim Preserve. Your use of two nested loops is the correct approach. Use the Range object's CurrentRegion (.Rows, .Columns) child object to determine the row and column count.
intProductRow = intProductRow + 1 is the preferred increment statement.
|
__________________
No the other right mouse click
|

09-20-2016, 02:37 PM
|
Newcomer
|
|
Join Date: May 2010
Location: South Bend, IN
Posts: 5
|
|
Thank you very much for the response Kluz. I actually figured it out just before your reply. Essentially, I did what you said. I counted the number elements needed in the array based on the Excel data, and ReDimmed the array to that size. Then, it was simply to loop through the Excel data and put it into the array. Thanks again!
|
|
Tags
|
data, array, arrstocklengths, excel, product1, intproductcolumn.value, strproduct, product2, strmaterial, dbllength, intproductrow, .cellsintproductrow, intproductcolumn, program, xlproductsworksheet, .cells1, string, material2, calculate, time, material3, process, material1, speed, loop  |
Currently Active Users Viewing This Thread: 1 (0 members and 1 guests)
|
|
Thread Tools |
|
Display Modes |
Linear Mode
|
Posting Rules
|
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts
HTML code is Off
|
|
|
|
|
|