VBA - Determine whether there are consecutive zeros in an array

  • I have written some VBA code that randomly generates a 1 or 0 depending on a probability. It then stores these into an array, which is defined by the the number of data points I have (rows) and by the number of iterations the user wants to run through (columns).

    I have transposed the data into excel and it looks as I'd expect, e.g.

    1 0 1

    1 1 1

    1 0 1

    0 0 1

    0 0 1

    1 1 0

    1 0 1

    Unfortunately I am not too familiar with VBA so I am struggling with the next part;

    I would like to now consider one column (iteration) and find if there are 3 consecutive zeros. If there are, I would like to create a new array that inputs a 1 when there are 3 consecutive zeros found, and a 0 otherwise. So the above example would look like

    0 1 0

    This would allow me to do some analysis on the number of 1's to 0's etc.

    My code so far is shown below

  • Hi Stephen,

    Apologies, I sent the above from my mobile and found it a bit fiddley.

    I would like to learn how to create a new array, whereby I look through the first column in the array arr, and determine whether or not there are 3 consecutive zeros. If so I would like to put 1 into the array, if not 0. This is where the 0 1 0 comes from - it is what I would like the example data to return.

    Hope this is clear :)

  • Partially yes. The zeros need to be consecutive for there to be an outputted 1. Additionally, I am unsure (as I am fairly new to coding in general) if adding it as a new row as you suggest will make it easy to analyse. As my next step would be to count the number of 1's to 0's that are outputted.

  • Try this. I don't think there's a quick way to check for 3 consecutive zeros so this involves a loop. I have simplified your code a little just to do this bit of it, but I hope you can follow. Sample workbook also attached.

    An alternative to adding a last row to the array would be to create a new array of 1 x 3.

  • Stephen,

    That is fantastic thank you, as I have said I'm fairly new to this so some of what you have done within this code has developed my knowledge.

    I had started to do a flowchart for my code and realised I only really needed to count the zeros in a for loop (as you have done) - so to anyone reading this, it does help if you first plan out code within a flow chart as the problem may actually be simpler than expected!

    I have tested it a few times and I can see a few columns in which there are 3+ consecutive zeros however it does not return a 1 in the final row, I shall look into this.

    Again, thanks for your help!

    EDIT: I think it just required ctr to be reset to 0 after the k loop.

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!