Shortest Path Between Cells (VBA)

[basschmidt]

Dear all,

I am new to this forum so excuse me if this question is already solved..

So, here's my problem..
I am working on a project to clear festival events with simulation.

Therefore I need a grid of Excel cells (the field), let's say 11x11 (B2:L12).
And the exit is in cell A7.

I am looking for an algorithm which fills this field with distances to the exit.
For horizontal and vertical movements the distance is 1,
and for diagonal movements the distance is 1.5

Say I made a class Coordinate which has two points, X & Y
I declare a variable point, as the location I am in (the active cell) and a constant exit (the coordinate of the exit)

And then I would like a function in VBA which does:

Please Login or Register  to view this content.

Does anyone has an idea how to approach this??

Many thanks!

Greetings,

Bas

[patel45]

attach please a sample file and explain better how you want use it

[basschmidt]

Sorry that I am not clear..

Shortest distance algorithms usually use the distances between A to B, A to C etc. and take the minimum.
In my case, these distances are not given, but they are cells.

Here is my file:

Grid Shortest Distance.xls


Gr. Bas

[Andy Pope]

This will handle an empty room with varying exit. 999 denotes the exit.

Please Login or Register  to view this content.

[basschmidt]

Yes! That's it! Thanks Andy!

Just out of curiousity, is it possible to implement bars (obstacles) in the field with this method?
I actually dropped this idea because I didn't know how to do this, but do you have an idea?

Many thanks!

Bas

[Andy Pope]

Obstacles will make this a much more complex problem, certainly beyond a forum post solution.

Major things to consider would be how far ahead you should check and whether multple blocks should be anticipated.

[Andy Pope]

Okay I could not leave it alone.

This variation will handle interior walls and multiple exits. I have added conditional formatting so exit, walls and unassigned distances will appear coloured.

[sks4444]

i have node list and find all available paths between any 2 nodes. as attached in excel or excel vba.1.jpg2.jpg

[FDibbins]

sks4444 welcome to the forum

Unfortunately your post does not comply with Rule 2 of our Forum RULES. Do not post a question in the thread of another member -- start your own thread.

If you feel an existing thread is particularly relevant to your need, provide a link to the other thread in your new thread.

Old threads are often only monitored by the original participants. New threads not only open you up to all possible participants again, they typically get faster response, too.