Excel Data to 2D Array
Excel Data to 2D Array
Excel Data to 2D Array
Excel Data to 2D Array
Excel Data to 2D Array
Excel Data to 2D Array Excel Data to 2D Array Excel Data to 2D Array Excel Data to 2D Array Excel Data to 2D Array Excel Data to 2D Array Excel Data to 2D Array Excel Data to 2D Array
Excel Data to 2D Array Excel Data to 2D Array
Excel Data to 2D Array
Go Back  Xtreme Visual Basic Talk > > > Excel Data to 2D Array


Reply
 
Thread Tools Display Modes
  #1  
Old 09-19-2016, 09:52 AM
Cripp Cripp is offline
Newcomer
 
Join Date: May 2010
Location: South Bend, IN
Posts: 5
Default 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
File Type: png Book1.png (3.8 KB, 4 views)
Reply With Quote
  #2  
Old 09-19-2016, 07:35 PM
Kluz's Avatar
KluzExcel Data to 2D Array Kluz is offline
Sapience.Aquire

Super Moderator
* Expert *
 
Join Date: Oct 2003
Location: Lake Bluff, Ill., U.S.
Posts: 3,444
Default

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
Reply With Quote
  #3  
Old 09-20-2016, 02:37 PM
Cripp Cripp is offline
Newcomer
 
Join Date: May 2010
Location: South Bend, IN
Posts: 5
Default

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!
Reply With Quote
Reply

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

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off

Forum Jump

Advertisement:





Free Publications
The ASP.NET 2.0 Anthology
101 Essential Tips, Tricks & Hacks - Free 156 Page Preview. Learn the most practical features and best approaches for ASP.NET.
subscribe
Programmers Heaven C# School Book -Free 338 Page eBook
The Programmers Heaven C# School book covers the .NET framework and the C# language.
subscribe
Build Your Own ASP.NET 3.5 Web Site Using C# & VB, 3rd Edition - Free 219 Page Preview!
This comprehensive step-by-step guide will help get your database-driven ASP.NET web site up and running in no time..
subscribe
Excel Data to 2D Array
Excel Data to 2D Array
Excel Data to 2D Array Excel Data to 2D Array
Excel Data to 2D Array
Excel Data to 2D Array
Excel Data to 2D Array Excel Data to 2D Array Excel Data to 2D Array Excel Data to 2D Array Excel Data to 2D Array Excel Data to 2D Array Excel Data to 2D Array
Excel Data to 2D Array
Excel Data to 2D Array
 
Excel Data to 2D Array
Excel Data to 2D Array
 
-->