Wastewater Treatment Plant Material Balance

[PolishEagle612]

Hello, new to the blog.

I work for a consulting firm that focuses on work for wastewater treatment plants. As part of the work we are often tasked with putting together mass balances to determine the fate of water and solids through the system. We often do this in excel, because everyone in our company has access to excel and it is easy to use. The unfortunate part is that you are also unable to see the process flow diagram of the system. What I would like to do is add data to shapes and as you create shapes and add connectors, the values from the shapes will added to the shape they are connected to. For example, if you have three shapes each with 50 gpm flow and each has a connector that is connected to a single shape, then I would like the fourth shape to auto calculate and be populated with 150 gpm. The other tricky part is there are often recycle streams, where a portion of the water is recycled back to the front of the process. I have attached a material balance of a simplified process as an example. If anyone has any ideas I am all ears.

[vojo]

I suppose adding flow diagram to Excel is not of interest (separate sheet, drop some shapes, ref values on other worksheet)?
   May want to play around with this approach as a rough draft of what you want.

there are ways to do link visio and excel, but I am not an expert at that.

you may want to consider using piping isometrics in visio to give the drawing more of piping/flow appearance

[PolishEagle612]

Vojo,

Thanks for the reply. We often add process flow diagrams to excel, but the issue is we constantly make chances to the material balances and have to adjust formulas accordingly. I would ideally like to create boxes for the process flow diagram that can either have inputs or calculations. This may be impossible, but just trying to figure out what is possible.

[vojo]

Well...several perspectives

Visio in general is best at static drawings.  Yes, smart shapes and custom properties possible, but best at static drawings

if formulas fixed but parameters (triggers, values, etc) change, you can use shape data to manually change parameters
if formulas change, you can define user functions in the user cell section of the shape sheet manually
   (need to be skilled with both user define formulas as well as shapesheet specifics)
   (to start:  draw a square then right click and select shapesheet...from there, lots of reading on each cell and function does)
   (you can launch VBA macros from shape sheet cells...in essence, a library of different macros that can be triggered)

there are ways to link excel to a drawing, as mentioned before...others can elaborate how the linkage works.
(its pretty complex..but can be done... its what you originally asked).

lastly, you can create a visio "DLL" with all your functions, UI, etc included (this is a lot of work).  google visio add ons

One implementation option could be (keep everything in visio).
- visio sheet 1:  Some user friendly way to input various parameters...maybe even user functions....scope out how fcns change
- Visio sheet 2:  actual drawing with each shape shapesheet picking up parameters from visio sheet 1.

The more elaborate the implementation, the more fluent with visio you need to be.....
start small with experiments / "crawl walk run"   (20 years in and I still discover new capabilities).

IMHO, this will be a big project.

[wapperdude]

I believe this will, ultimately, take some code.  For Visio, connectivity info is contained in the connectors, not the shapes.  Codewise, you could select your 4th shape, then search for incoming connectors attached to it.  Next, find the shape at the opposite end of each connector, grab the info, and sum the results.  Use this result to update shape 4.  This approach is well suited for variable topologies, i.e., adding / removing number of feeds going into shape 4.  However, if topology doesn't change, just the size values, I'd do it all in Excel, and push values into appropriate shapes.

If you do a forum search for calculate, there are many hits.  Some have needs close to yours...a small, sample list:

http://visguy.com/vgforum/index.php?topic=8566.msg37245#msg37245
http://visguy.com/vgforum/index.php?topic=3538.msg13784#msg13784
http://visguy.com/vgforum/index.php?topic=7105.msg29863#msg29863

In your example, does the visual representation of shape 4 change with the value, or is it merely a text change?

[Yacine]

... the issue is we constantly make chances to the material balances and have to adjust formulas accordingly. I would ideally like to create boxes for the process flow diagram that can either have inputs or calculations. This may be impossible, but just trying to figure out what is possible.

Visio can do a lot. The difficulty is to properly formulate your problem.

Have a look at the enclosed files.
The vsd shows the application. the vss has macros to convert arbitrary shapes into calculating ones.

Rgds,