Best way (performance) to manage big amount of data in Excel with VBA

[PeteWright]

Hello!

As the topic title says I need to manage a big amount of data and I'm not sure what method has the best performance.

Some information on what I deal with:
I have points with geographic coordinates and some attributes which are stored in a text file at the beginning and then imported into an Excel Sheet via VBA on user button click.
Let's assume we have 9.999 points which have 6 properties (Point no, Easting, Northing, Height, Attribute1, Attribute2).
That makes about 60.000 Excel cells.


Now all data is stored in an Excel Sheet, but this makes the handling a little uncomfortable (e.g. selecting by cell reference not by index, etc.)
In order to have more control (e.g. count, sort, reorder, remove, duplicate, etc.) over the points, I would like to store them in an multidimensional array.

I already have a user defined data type:

Private Type Point
 n as Integer
 y As Double
 x As Double
 z As Double
 a1 As String
 a2 As String
End Type

Dim P1001 As Point
P.n = 1001
P.x = 4567890.123
P.y = 3456789.012
P.z = 123.456
P.a1 = "Zone 1"
P.a2 = "Category 2"

Is this the way to go or are there better methods to store the data?
How about the performance of multidimensional Arrays with lot of data?
What if the number of points increases by ten times to 99.999 points?

Any help appreciated

 

[offthelip]

If I was dealing with this I would probably choose just a two dimensional variant array, the reason being that this is the only type of array that you can read and write from the worksheet. I might possibly using a three dimensional aray if you are dealing with lots of worksheets with the same data structure, but this would require writing specific software to load it from the worksheets and write it back. this would deal with 100000 points without any problems. This wouldn't be incompatible with user defined type, but personally I probably wouldn't bother, because the variant array second index would give you the , n,x,y,z,a1,a2 presuming they are in successive columns

 

[DanteAmor]

Depending on the use of the matrix, but handling 200,000 or 300,000 data is no problem if you store it in an matrix, processing times can be seconds.
If you need an index you can pass the matrix to a dictionary, that can be done in an instant.
You can put here some of your needs to obtain data from the matrix and we will help you with the code to read the data from the matrix and put the data, say in memory or on another sheet.

Store in a matrix, example:

Sub test()
  Dim a As Variant
  a = Range("A1:F" & Range("A" & Rows.Count).End(3).Row).Value2
End Sub

 

[PeteWright]

Depending on the use of the matrix, but handling 200,000 or 300,000 data is no problem if you store it in an matrix, processing times can be seconds.
If you need an index you can pass the matrix to a dictionary, that can be done in an instant.
You can put here some of your needs to obtain data from the matrix and we will help you with the code to read the data from the matrix and put the data, say in memory or on another sheet.

Store in a matrix, example:

Sub test()
  Dim a As Variant
  a = Range("A1:F" & Range("A" & Rows.Count).End(3).Row).Value2
End Sub

Could you explain the .End(3) and .Value2 ?

My workbook has many sheets with some data that is just there for processing the "points".

What I would like to do:
1. import points from text file into a specific sheet via VBA per user button click (this already works fine)
2. store the point information and add the ability that Excel/VBA "knows" when the user has removed or altered it in the sheet (this is where I could need a little help)
3. process the point data (I'm already working on that one)
4. export an edited version of the data to a new text file (I think I can handle that one too)

Please let me know if you need any further information or some sample data or sample code.
TIA

 

[DanteAmor]

Could you explain the .End(3) and .Value2 ?

Range("A" & Rows.Count).End(3).Row
It means that from column "A" of the total rows (rows.count) upwards (End(3) or End(xlup)) get the row number (.row)

2. store the point information and add the ability that Excel/VBA "knows" when the user has removed or altered it in the sheet (this is where I could need a little help)

You could explain step by step what you mean or what is your idea or the final goal.