Excel Data to 2D Array

09-19-2016, 09:52 AM

Cripp

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!

Attached Images

Book1.png (3.8 KB, 4 views)

09-19-2016, 07:35 PM

Kluz

Sapience.Aquire

Super Moderator
* Expert *

Join Date: Oct 2003

Location: Lake Bluff, Ill., U.S.

Posts: 3,467

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

Cripp

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!