View Full Version : Fun Excel - Spiral through a range
malik641
10-18-2008, 12:22 AM
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][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! :hi:
[B]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
10-18-2008, 04:36 AM
Interesting puzzle, here's my attempt.
malik641
10-18-2008, 02:16 PM
Nice solution Andy :thumb
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
10-18-2008, 06:26 PM
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
10-19-2008, 03:26 AM
Similar to OP's.
Hey Joseph,
Quite the brain-teaser for me leastwise. (Especially when I think I read the "goal" wrong initially.:dunno )
Anyways, hopefully one of the two ways is 'on the mark'.
Have a good day,
Mark
p45cal
10-19-2008, 07:46 AM
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.
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
10-19-2008, 10:00 PM
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
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
10-20-2008, 09:29 AM
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
11-24-2008, 05:09 PM
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 :thumb 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.
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.