none
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 02, 2018 2:16 PM

All replies

  • Hard to understand without some sample data.
    Tuesday, January 02, 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 Veck Tuesday, January 02, 2018 4:50 PM spelling
    Tuesday, January 02, 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 02, 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 02, 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 02, 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 mogulman52 Tuesday, January 02, 2018 11:55 PM
    Tuesday, January 02, 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 03, 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 03, 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 04, 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 04, 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 04, 2018 3:52 PM