Shortest Path: Incoming, Outgoing, either; identify connections, highlight

Started by wapperdude, March 12, 2015, 04:45:31 PM

Previous topic - Next topic

0 Members and 1 Guest are viewing this topic.


This post provides a macro to look at a connected network of shapes, i.e., nodes and allows various viewing options.  All of these options are available from a non-modal userform.  These include showing either incoming, outgoing or either (i.e., both = all) connections.  Connections are 1-D shapes that can be either connector or line objects.  In fact, everything is DRIVEN from the userform.  It remains open until the user cancels it.  Just park it out of the way.

A second main option is to choose whether or not to calculate and show the shortest path.  The method is based upon Dijkstra's method as previously posted by Yacine.  It deviates from that method in that:  1.)  It doesn't pre-calc all of the connected pair distances, but only those that are needed, 2.) it doesn't pre-sort based upon shortest distance 1st, 3.) it allows for connector directionality, 4.) it colors the nodes and the connectors, and 5.) it is Visio-centric in that it uses connector collections to form "the neighborhood" of connected nodes.  One mode thing, there is no hard-coded limit to the number of shapes and connectors, which was preset to 100.

Concerning (1):  when analyzing directional shapes, it is not necessary to pre-calc all of the shape pair distances.  Thus, this potentially saves some time.
Concerning (2):  pre-sorting based upon distance is unnecessary as the comparison requires looking at all viable paths.  So, there's no advantage to do this.
Concerning (3):  this requires a few more conditional case conditions, so the sorting algorithm is slightly more complicated.

The attached Visio file has two pages...(1) is a simple diagram to facilitate code checking, and (2) is a more complex diagram.

The code is heavily commented to facilitate it's understanding.  Where appropriate, it calls sub-routines.  Some of the code is taken verbatim from Yacine's post.


Visio 2019 Pro



Noisy Cricket

Absolutely loving it!
Don't have an immediate use for it though.

Noisy Cricket

Alright! I've got use for it.

The thing: I've got two lists of locations: Origins (up to 10) and destinations (up to 10) and a number of transports between these locations (hundreds).

Origins connect to all destinations (= 10 connections)
Origins connect to all origins (intermediate shipment) = (9 connections for each point (if one-way) = 81
Destinations connect to all other destinations (= 9 connections for each point (if one way) = 81

This would then form a logistic network similar to the above.
Secondly, the line thickness of the connector may indicate the number of transports along each route/leg.

Eventually it would look like a logistic network / Sankey diagram

Questions at hand:
1) Has this been done before?!
How could this be created programatically? (to avoid having to build these / allow other people to use same)
2) How to make Visio understand which connection it's processing (and when to write the # to the thickness cells)?

Sample data
Origin Destination Number of transports
Origin 1   Destination 4   10
Origin 2   Destination 2   5
Origin 3   Destination 1   9
Origin 4   Destination 3   5
Origin 2   Origin1            3
Destination 1 Destination 4

Hoping above mentioned provides some challenge for the Pro's!


Another app:

2 unrelated (ungroup, uncontainer, independent) shapes connected together could use this as a basis for
establish a "comm" channel between shapes.

For example
  - shape 1 dropped
  - shape 2 dropped
  - shape 3 dropped
  - 1<=> 2 connection
  - adapting this macro, the link between 1 and 2 could be used to indicate 1<=> 2 can exchange info
      - thus a macro or cells could identify that shape 2 has the data shape 1 needs (vs shape 3).
      - shape 2 could build on its data and data from shape 1 to do things (report, behaviors, etc)
      - e.g   Shape 1 has custom prop of IP address X....shape 2 has custom prop of IP address Y
           Shape 2 could report "srce IP and dest IP" for the connection.


Noisy Cricket,
Some time ago I made a utility for working with graphs, but no one showed interest in it. Look, maybe  it come in handy to you ...
Such data as your can be imported through Excel.
The shortest way she defines like this.
Sorry, I forgot to attach the link... :)

Noisy Cricket

Yep... it more or less looks the part!

Will give it a spin!


And I gave it a spin.

Importing from Excel gives intelligible error message.
The manual mentions it was evaluated for excel/visio 2007.

Working with 2010 here.

I think the addin has all the components I'm looking for but can't get my head around the addin itself and using the addin makes distribution of the .vsd I'm working on a bit harder (and relying on 3rd party support)


Error when importing from Excel may occur if the document, created from a template, have not yet been saved.
You must save the document, and then perform the import. Excel file must be in the same folder as the document file.

Noisy Cricket

I think a remake of wapperdude's sheet is the way forward although the current code is way too elaborate for me.

What is the simplest way to
1) Cycle through nodes
2) Determine node on other end of connector
3) Write text to connector between shapes
4) Write number to shapedata or user field to indicate number of time the route is taken?!

Was able to find a snippet that loops through the connectors, get shape data from the connected shapes and write some text to the connector. (hurray!) Everything seems to work....

Thanks for the effort so far!

Next challenge: P&ID.... How to visualise the flow of water in a piping system?!?!?!?