Fun Excel - Spiral through a range

[malik641]

One of my professors told me about a job interview question that I thought was interesting:

Given any 2d array, how would you loop through the array in a "spiral" format?

I thought I would give it a try and I used Excel VBA because I can represent looping through the range of a worksheet visually by coloring which cell I'm in during the loop.

So, if you have a 2d array of:

[1][2][3][4]
[5][6][7][8]
[9][a][b][c]

Looping through would look like:

1, 2, 3, 4, 8, c, b, a, 9, 5, 6, 7


See if you can solve it using VBA and excel. I attached my example, so don't open the code if you want to solve it yourself.

Good luck and have fun!

EDIT: Oh, and please attach a workbook and not your code example, so other people can try without the (easier) temptation to cheat!

[Andy Pope]

Interesting puzzle, here's my attempt.

[malik641]

Nice solution Andy

I wanted to do something like when I first attempted this, but I kept getting stuck. Now after seeing your code, it makes much more sense!

[p45cal]

I thought this'd be a good one for trying recursion (a sub calling itself). This worked, but when testing on larger ranges ended up with a stack error, and a few problems with ranges on the edges of the sheet, so I abandoned it (it's included in the attached).

However, the second macro (test2) works on any size range, anywhere.

Pascal

[Krishna Kumar]

Similar to OP's.

[GTO]

Hey Joseph,

Quite the brain-teaser for me leastwise. (Especially when I think I read the "goal" wrong initially. )

Anyways, hopefully one of the two ways is 'on the mark'.

Have a good day,

Mark

[p45cal]

I thought this'd be a good one for trying recursion (a sub calling itself). This worked, but when testing on larger ranges ended up with a stack error, and a few problems with ranges on the edges of the sheet, so I abandoned it.
Pascal

This one does use recursion without the drawbacks mentioned earlier and has been tidied up.

[GTO]

This one does use recursion without the drawbacks mentioned earlier and has been tidied up.

@p45cal:

Greetings Pascal,

Just wanted to mention as to: Nice recursion! Impressive and efficient :-)

@malik641

Hey Joseph,

After submitting first try, got to look at others, including your example; only then realizing (probably/maybe?) what was supposed to be done.

Not particularly efficient, but shucks, w/three different ways, one has to be correct, right?

This was an interesting exercise - thank you so much!

Mark

[Demosthine]

Evening Everyone.

Well Mark, third time's the charm, right? It looks like that last spiral matches the description of the project. I must admit, though, the coloring you've added is a very nice touch. Hmm. Maybe I have gamers disease. I like the bright, colorful flashes. It's mesmerizing. I'll have to modify the code to get it to just loop, and loop, and loop...

P45Cal, that was a truly impressive display you put on there. My only comment is that you may want to add a slight delay in your loop. In was just a blur on my machine. That or I'll have to go borrow Mark's Flintstonian laptop. :-)


Now let's see you do it backwards...

Take care guys.
Scott

[GTO]

Now let's see you do it backwards...

Very funny and not a chance. My poor eyes were going blurry as was, and the laptop... I actually thought I heard whimpering.

[p45cal]

Now let's see you do it backwards...

Scott

like this?

ps. no delay put in, but do it on a big enough range..

[malik641]

I know it's been over a month, but I just want to say that I loved everyone's solution! I hope you all had fun I most especially like the spiral backwards code by p45cal nice work!

I just wanted to pay my respects. I have been entrenched with so much homework that I haven't had time for fun with VBAX.