# Moving sequentially through a large amount of data.

• ### Question

• Hello all,

I am a complete beginner to coding, but have just begun a research project that requires the processing of around 10,000 lines of particle data in excel, so need to write some code for it.

I have come into problems before I have even hit the first hurdle. I was attempting to define certain cells as x, y, and z coordinates so that i can refer to them later on, but I cannot seem to move sequentially through the data without compile errors.

I tried to define objects as the same object, but with an offset, so that that object is now the cell below what that object was, and I am failing to understand why this does not work. I planned to add a loop afterwards, but did not get to that stage.

Is there a better way to move sequentially through data? Or does anyone know why this code encounters errors?

Option Explicit

Sub ForceDistanceFromCentreOfMass()

Dim ParticleLength As Double
Dim ParticleToFind As Double
Dim ParticleToFindx As Double
Dim ParticleToFindy As Double
Dim ParticleToFindz As Double

Dim ForcesToFind As Double
Dim DistanceFromCOM As Double
Dim ContactDistance As Double

ParticleLength = 0.09
ParticleToFind = Worksheets("p4p").Range("A4")
ParticleToFindx = Worksheets("p4p").Range("E4")
ParticleToFindy = Worksheets("p4p").Range("F4")
ParticleToFindz = Worksheets("p4p").Range("G4")

ForcesToFind = Worksheets("p4c").Range("A4", Range("A4").End(xlDown))
ContactDistance = ActiveCell.Offset(8, 0)

'Finding the Distance between the centre of mass of ParticleToFind and the location of the contact - have not tested if this needs debugging yet'
Select Case ForcesToFind
Case Is = ParticleToFind
ContactDistance = Sqr(((ActiveCell.Offset(2, 0) - ParticleToFindx) ^ 2) + ((ActiveCell.Offset(3, 0) - ParticleToFindy) ^ 2) + ((ActiveCell.Offset(4, 0) - ParticleToFindz) ^ 2))

End Select

'ParticleToFind offset thingy to make the code move sequentially through the list - THIS IS THE SECTION I AM REFERRING TO IN THE QUESTION'
Worksheets("p4p").Range("A4").Activate
ParticleToFind = ActiveCell.Offset(0, 1)

Worksheets("p4p").Range("E4").Activate
ParticleToFindx = ActiveCell.Offset(0, 1)

Worksheets("p4p").Range("F4").Activate
ParticleToFindy = ActiveCell.Offset(0, 1)

Worksheets("p4p").Range("G4").Activate
ParticleToFindz = ActiveCell.Offset(0, 1)

End Sub

Tuesday, January 2, 2018 2:16 PM

### All replies

• Hard to understand without some sample data.
Tuesday, January 2, 2018 3:04 PM
• There are 2 pages that I need to refer to, one with the position of particles in space, then another with positions of particle interactions.

worksheet p4p

 ID GROUP VOLUME MASS PX PY PZ 79 0 6.10E-15 8.90E-12 0.0289038 0.0149051 0.0143728 138 0 6.10E-15 8.90E-12 0.028381 0.0340247 0.0202788 127 0 6.10E-15 8.90E-12 0.0232838 0.0456547 0.0196089 44 0 6.10E-15 8.90E-12 0.0400006 0.0575132 0.00499976 139 0 6.10E-15 8.90E-12 0.0421546 0.0455995 0.0238371 316 0 6.10E-15 8.90E-12 0.0401854 0.00117239 0.0475021 231 0 6.10E-15 8.90E-12 0.0259298 0.0351064 0.042028 291 0 6.10E-15 8.90E-12 0.0167704 0.0375161 0.0506774 194 0 6.10E-15 8.90E-12 0.0476117 0.0382044 0.0334519 367 0 6.10E-15 8.90E-12 0.055452 0.046779 0.0541756 64 0 6.10E-15 8.90E-12 0.0677471 0.0257251 0.00499853 154 0 6.10E-15 8.90E-12 0.0640754 0.0047871 0.0252969 5 0 6.10E-15 8.90E-12 0.112327 0.0232154 0.00500057 197 0 6.10E-15 8.90E-12 0.103014 0.0259657 0.0255825 87 0 6.10E-15 8.90E-12 0.0767178 0.0430055 0.014463 187 0 6.10E-15 8.90E-12 0.0740222 0.0581047 0.0257575 109 0 6.10E-15 8.90E-12 0.119713 0.0469076 0.014523 257 0 6.10E-15 8.90E-12 0.066065 0.0155927 0.0456772 279 0 6.10E-15 8.90E-12 0.117552 0.00697011 0.0539757 274 0 6.10E-15 8.90E-12 0.106793 0.0559376 0.0435597 99 0 6.10E-15 8.90E-12 0.0162888 0.0649911 0.0195398

and then worksheet p4c

 P1 P2 CX CY CZ 79 154 0.06887 0.0059177 0.0202211 79 87 0.0725213 0.00630618 0.0151416 79 139 0.0356035 0.0118805 0.0192652 79 138 0.0219917 0.0170523 0.0189713 79 127 0.0111023 0.0191675 0.0188738 79 100 0.289288 0.0248177 0.0182579 138 139 0.0407743 0.0531321 0.0198166 138 139 0.041274 0.0560902 0.0198035 127 138 0.0407224 0.0708086 0.0140704 127 262 0.0350655 0.0736904 0.0200274 127 367 0.0337483 0.0660752 0.0211707 127 138 0.0109118 0.00884225 0.025585 127 177 0.00609845 0.0116637 0.0300146 44 84 0.0354156 0.0553569 0.00499976 44 138 0.0412832 0.0644026 0.00928348 44 127 0.0378357 0.0762355 0.00976407 44 99 0.0284209 0.0974011 0.00632393 139 154 0.0355263 0.00141651 0.0252951 139 194 0.041936 0.0408027 0.0285898 139 194 0.0424356 0.0437608 0.0285767 139 210 0.0461474 0.0694034 0.0287128

So I wanted to select say particle 79, and determine the distance of the contact from one end of the particle (Euler-Bernoulli beam theory).

• Edited by Tuesday, January 2, 2018 4:50 PM spelling
Tuesday, January 2, 2018 4:09 PM
• Worksheet p4p has the positions of the particles in space, and worksheet p4c has the positions of the interactions between particles. I just was wondering how I would move sequentially through the data on worksheet p4p, as using offsets eg.

Worksheets("p4p").Range("A4").Activate
ParticleToFind = ActiveCell.Offset(0, 1)

to define objects does not seem to work like I thought.

Tuesday, January 2, 2018 4:12 PM
• Can you go through a sample calculation using real data.  I'm still confused on the input and output you want and when you say move sequentially through the data.
Tuesday, January 2, 2018 5:11 PM
• Basically, the analysis is to find out if there is enough force to cause particle breakage within several thousand needle-like particles, and assess how much breakage occurs.

So I select one particle from p4p, which has coordinates attached to it defining the centre of mass of the particle.

I then need to move to p4c and find all contacts involving that particular particle.

I then need to find the distance of those contacts from the centre of mass of the original particle.

Then I need to multiply the distance of the contact by the downward force of the contact (FZ). (I did not paste this data earlier, but it is there)

eg.

 P1 P2 CX CY CZ FX FY FZ 79 154 0.06887 0.0059177 0.0202211 -7.80E-11 -3.81E-11 -3.02E-10 79 87 0.0725213 0.00630618 0.0151416 -1.20E-10 -2.44E-11 6.74E-12 79 139 0.0356035 0.0118805 0.0192652 -1.17E-10 2.94E-10 -9.18E-10 79 138 0.0219917 0.0170523 0.0189713 1.32E-10 -7.48E-11 -4.59E-10

I will then sum the forces on the beam and assess whether the particle would have broken. Then Repeat for the next few thousand particles.

Overall, Input = moments on the particle (or force*distance)

Output = does this value exceed literature value for breakage of this type of particle.

Recap:

My question for this board is simply how to move through data while defining each cell in the ID column as an object in turn, so that I can reference that object in function that I will use later in the subroutine.

Tuesday, January 2, 2018 5:44 PM
• As an example it looks to me like you take id = 79 on p4p and look at p4c for p1 = 79 and calc a formula.  I've done a lot of this for calculation integrated circuit lifetimes.  This is structured for simplicity and update-ability.  I use enums to keep track of columns and user defined types for variables for formula.  It is just much easier to understand and write.

If you ordered the data you could speed up the searching.
```Option Explicit

Public Enum ep
ID = 1
GRP
VOL
MASS
PX
PY
PZ
End Enum

Public Enum ec
P1 = 1
P2
CX
CY
CZ
FX
FY
FZ
End Enum

Type p4UT
ID As Integer
GRP As Integer
VOL As Double
MASS As Double
PX As Double
PY As Double
PZ As Double
End Type

Type c4UT
P1 As Long
P2 As Long
CX As Double
CY As Double
CZ As Double
FX As Double
FY As Double
FZ As Double
End Type

Sub Calc()
Dim wsp As Worksheet
Dim wsc As Worksheet
Dim p As p4UT
Dim c As c4UT
Dim curId As Long
Dim r As Double
Dim i As Long, k As Long

Set wsp = Worksheets("p4p")
Set wsc = Worksheets("p4c")

i = 2
While wsp.Cells(i, ep.ID) <> ""
curId = wsp.Cells(i, ep.ID)
p.MASS = wsp.Cells(i, ep.MASS)
p.VOL = wsp.Cells(i, ep.VOL)
p.PX = wsp.Cells(i, ep.PX)
p.PY = wsp.Cells(i, ep.PY)
p.PZ = wsp.Cells(i, ep.PZ)
k = 2
While wsc.Cells(k, ec.P1) <> ""
If wsc.Cells(k, ec.P1) = curId Then
c.CX = wsc.Cells(k, ec.CX)
c.CY = wsc.Cells(k, ec.CY)
c.CZ = wsc.Cells(k, ec.CZ)
r = p.PX * p.PY / p.PZ * c.CX   ' made up formula
End If
k = k + 1
Wend
i = i + 1
Wend
End Sub```

• Edited by Tuesday, January 2, 2018 11:55 PM
Tuesday, January 2, 2018 11:53 PM
• The first question is, do you really have to use Excel? There are other possibilities. A database table is designed for that type of thing. A spreadsheet is designed to be used interactively so it is less efficient for non-interactive processing.

If you can load the spreadsheet into memory then it will be significantly faster; perhaps an array.

Even better would be to use VB.Net and to load the spreadsheet into a collection or some collections in memory.

Can you consider those possibilities?

Sam Hobbs
SimpleSamples.Info

Wednesday, January 3, 2018 1:17 AM
• I've found Excel to be an excellent tool for this type of scientific application.  It offers users a quick way to experiment with data.  Users can easily plot data or do other manipulations.

Excel falls short if you have to do 1000's of runs since it could take hours and there is no good way to leverage compute farms.

Wednesday, January 3, 2018 11:54 PM
• I said:

A spreadsheet is designed to be used interactively

You said:

Users can easily plot data or do other manipulations.

So yes, Excel is excellent for users. The term "interactively" strongly implies users. It can be a pain for developers. Excel is not designed to be a repository of data for convenient and efficient access by programs.

Sam Hobbs
SimpleSamples.Info

Thursday, January 4, 2018 12:35 AM
• Thank you mogulman! I'm not familiar with most of that code, but some looks familiar. I am encountering a run-time error when I step though it though. (Run Time Error 13, Type mismatch) And curId = wsp.Cells(i, ep.ID) was highlighted with the debugging tool.

Do I perhaps have some settings wrong?

Just to clarify mogulman, I can insert the mathematical function while referring to particle positions as PX PY PZ and contact positions as CX CY CZ?

I was using excel because that was what my supervisor recommended, along with VBA. I've since been heavily recommended using c#, since people say that it is easier to learn, but I am a fair way in now, and need to make progress. Thanks for the recommendations though!

Thursday, January 4, 2018 2:13 PM
• I put the workbook on my OneDrive account.  Download it and try it.  It is possible you didn't set it up the same.  You don't need an account to access it.
Thursday, January 4, 2018 3:52 PM