1

So I'm trying to split an array without using any loops. xData is an variant that stores data from a csv file, each row in the csv file contains ID;value where each row is then placed into the array.

dData.setNameArray and dData.setDataArray is a custom type object that stores sorted data for later use.

With loops I got:

Dim i As Integer
For i = 1 To UBound(xData)
    dData.setNameArray = Split(xData(i), ";")(0)
    dData.setDataArray = Split(xData(i), ";")(1)
Next i

But due to high amount of data I want to avoid this. I thought of removing the for loop and split the array as it is but it didn't seem to work.

Is there any other way to do this so I can avoid using loops? I found Manipulating arrays without loops where the first answer seems to be in the right direction. But i'm not 100% sure how to use it (if it could be a way to do it).

Fredrik
  • 477
  • 4
  • 22
  • split it into a tmp variant type var and pull the data from there on each iteration. still a loop but less calculation. btw, what's setNameArray and setDataArray ? some sort of custom class method? ftm, what's xdata? –  Jun 22 '18 at 15:44
  • Another method: write the array to a worksheet range (one step); split using the `.TextToColumns` method. Then read it back into a variant array (also done with a single step). – Ron Rosenfeld Jun 22 '18 at 15:46
  • @Jeeped Sorry if I was a bit unclear. setNameArray and setDataArray are custom data types that stores the data for later use. and xData are an array that holds information from a csv file. In the csv file each row of data is stored as ID;Value. – Fredrik Jun 22 '18 at 15:49
  • @RonRosenfeld That sounds like it would have a higher time complexity than a normal for loop? – Fredrik Jun 22 '18 at 15:51
  • 1
    Set up a timer so you can measure it. Be sure to use hidden sheets, and turn off screen updating. – Ron Rosenfeld Jun 22 '18 at 15:53
  • @RonRosenfeld I will try to come up with some kind of timer. The screen updating and hidden sheets are all fixed but thanks for pointing it out. – Fredrik Jun 22 '18 at 15:56
  • 1
    A hi res timer is discussed [here:](https://stackoverflow.com/questions/23939806/high-resolution-timer-code-run-time-overhead) – Ron Rosenfeld Jun 22 '18 at 16:04
  • Your going to need to loop. There is no need for split() - particularly twice! - use instr() to locale the ; then left()/right() to get the two values – Alex K. Jun 22 '18 at 16:15

1 Answers1

1

I think the most efficient way of doing this will be as follows. Notice the use of Long instead of Integer

Dim i As Long
For i = 1 To UBound(xData)
    Dim sa() As String
    sa = Split(xData(i), ";")
    dData.setNameArray = sa(0)
    dData.setDataArray = sa(1)
Next i
igorsp7
  • 441
  • 2
  • 4