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

PeteWright

New Member
Joined
Dec 20, 2020
Messages
44
Office Version
  1. 365
  2. 2019
  3. 2016
Platform
  1. Windows
  2. Mobile
  3. Web
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:
VBA Code:
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
 
Last edited:

Excel Facts

Round to nearest half hour?
Use =MROUND(A2,"0:30") to round to nearest half hour. Use =CEILING(A2,"0:30") to round to next half hour.

offthelip

Well-known Member
Joined
Dec 23, 2017
Messages
2,026
Office Version
  1. 2010
Platform
  1. Windows
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

Well-known Member
Joined
Dec 3, 2018
Messages
13,765
Office Version
  1. 2007
Platform
  1. Windows
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:

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

PeteWright

New Member
Joined
Dec 20, 2020
Messages
44
Office Version
  1. 365
  2. 2019
  3. 2016
Platform
  1. Windows
  2. Mobile
  3. Web
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:

VBA Code:
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

Well-known Member
Joined
Dec 3, 2018
Messages
13,765
Office Version
  1. 2007
Platform
  1. Windows
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.
 

Forum statistics

Threads
1,143,909
Messages
5,721,465
Members
422,363
Latest member
Bogus_Potatoes

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top