How can I write code that makes a spiral?

Question

I just started learning to programming in Excel VBA, and I have a problem. I want to draw a spiral with a program, but a can not solve it well. I would like to use for and do until and do while cycles only.

Here is my code:

 Sub spiral()
    For i = 1 To 16
        xmm = xmm - 1
        ymm = ymm - 1

        Do Until xp = i
            xp = xp + 1
            Cells(5 + xp, 5) = i
        Loop

        Do Until yp = i
            yp = yp + 1
            Cells(5 + xp, 5 + yp) = i + 1
        Loop

        Do Until xm > xp
            xm = xm + 1
            Cells(5 + xp + i * xmm, 5 + yp) = i + 2
        Loop

        Do Until ym > yp
            ym = ym + 1
            Cells(5 + xp + xmm, 5 + yp + i * ymm) = i + 3
        Loop
    Next i
End Sub

Thanks!

depends, how exact do you want to get? – ashleedawg Sep 04 '18 at 07:47 — ashleedawg, Sep 04 '18 at 07:47

ashleedawg · Answer 1 · 2018-09-04T08:58:52.050

I can't think of any practical reason you'd need this, but that said, I'm often wasting time messing around with odd tasks "just to see if I can."

Access VBA Circles

Recently a spiral was a self-assigned challenge I played with in SQL, although it originated with a circle in Access VBA from another useless project of mine:

Access World : Making an Analog Clock on an MS Access Form

This taught me a number of concepts including how to draw a circle on a grid.

Drawing a circle is almost the identical process as drawing the hands on that clock. If, rather than drawing the whole line of the clock hand, I instead draw only the tip of it (aka, the radius), all the way around, the 360°, then I have a circle.

SQL Circle

^{^{Say that ten times fast!}}

Then one day I wondered how hard it would be to convert the circle code to SQL Server, exploiting the graphing feature of the Stack Exchange Data Explorer (SEDE). A graph is drawn on a grid, so it's the same concept.

Here is one of several results (again, all useless):

SEDE : Draw Random Circles on chart

_{Click Run Query and then when it's finished calculating click the Graph tab. (You'll also need to either login with your Stack Exchange ID, or do the captcha.) There are various constants you can play with in the user prompts (below the SQL).}

SQL Spiral

Then at some point I wondered about spirals.
^{_{(...but probably should have been time for me to get a life? Naaaa, lol)}}

A spiral is simply a modified circle except the radius decreases (or increases) as you progress around the circumference -- and you don't stop at 360° -- instead just keep going and going, and continually changing the radius at a steady rate.

SEDE : Draw a spiral

_{Again, click Run Query and then when it's finished calculating click the Graph tab. (You'll also need to either login with your Stack Exchange ID, or do the captcha.) There are various constants you can play with in the user prompts (below the SQL).}

Spiral in Excel

I'm not going to waste any more time on spirals today, but if I was going to draw one in Excel worksheet cells, I would resize all cells on the worksheet to a tiny square grid (maybe width=1, height=10), and then I would borrow the code from the SQL example, adapting it to Excel (which woudn't take much work).

The Access VBA example may be more confusing to adapt since there a lot of programmatic manipulation of controls going on (which can't be done at runtime).

Or, the Access example could be duplicated in Excel using controls (ie., lines) instead of the grid method you were trying so far.

More Information:

Wikipedia : Midpoint Circle Algorithm
rosettacode : Midpoint circle algorithm examples (code in 39 languages)
tutsPlus.com : Working with circles/spirals
Math Overflow : How to spiral coordinates in an outward pattern on 2D grid?

You're late.... [a moment corresponds to 90 seconds](https://en.wikipedia.org/wiki/Moment_(time)) :) — Darren Bartrup-Cook, Sep 04 '18 at 08:19
@DarrenBartrup-Cook - Upon studying the link provided, I've learned that the value you quoted is actually the ***average*** durantion of a moment. I will now balance out that average by saying *"I'll click **Add Comment** in a moment."* — ashleedawg, Sep 04 '18 at 08:43
@DarrenBartrup-Cook - *ahh, the universe is once again balanced.* — ashleedawg, Sep 04 '18 at 08:43
I just wanted to practise the method how should i use the cycle in cycle algorythms. One of my friend wrote a code within 13 lines so it looked like a good logical task. — Norbert, Sep 04 '18 at 10:36

score 2 · Accepted Answer · answered Sep 04 '18 at 12:58

2

Base on some answers from this thread (Looping in a spiral), I've managed to come with this solution

Sub spiral()
x = 0
y = 0
d = 1
m = 1
i = 1
j = 1
Do While i < 6
    Do While 2 * x * d < m
        Cells(x + 5, y + 5).Value = j
        x = x + d
        j = j + 1
    Loop
    Do While 2 * y * d < m
        Cells(x + 5, y + 5).Value = j
        y = y + d
        j = j + 1
    Loop
    d = -1 * d
    m = m + 1
    i = i + 1
Loop
End Sub

Not sure if it is something that you are looking for thou, as you didn't specify the exact output that you want to achieve.

answered Sep 04 '18 at 12:58

Kirszu

354
3
11

Wow, thanks! It is really helpful to me. Now, I see how embedded cycles are working. :) – Norbert Sep 04 '18 at 13:08
No problem! Glad I could help :) Please consider to mark it as accepted if you think that it solved your problem. – Kirszu Sep 05 '18 at 11:50