VBA Help

[p45cal]

You haven't been forgotten. I only have intermittent access to a computer this week and the coming weekend.

[cmnewf]

Thanks, appreciate it!

[p45cal]

Right, nearly there; what are the units of distance (horizontal) and the units of elevation (vertical)?
Need to know to calculate the actual spill length of the pipe.

Separately, if the pipe does not have a constant cross-sectional area over its whole length the spill volume can still be calculated if we know the various cross-sectional areas (if it's a circular pipe, diameter will do). Does the pipe have a constant diameter over its whole length?

[cmnewf]

The units of distance are meters for both, and constant diameter.

[p45cal]

The attached has a new formula in column D; it's a user-defined function.
Your chart has been changed and added to in a few ways:
1. Now an x-y scatter chart to make it possible to plot leak points anywhere/any distance, even between the known points.
2. A button labelled Graph which when clicked will ask for a leak point Distance value. It will then plot the lengths of pipe which will empty and plot a red X where the leak occurs.
3. A button labelled Calculate which will calculate the length of pipe which will empty and display it in a message box - it's just a cross check.
4. A button labelled Remove empty pipe plot which will remove those 2 plots added in (2) above.

Is this a homework assignment?

[cmnewf]

That looks awesome! It’s a side project that I was interested in, if I could only learn to code like you. I need to understand what you’ve done. Got a couple of things that I want to add (valves, say an input to place 1 or more valves and then place them in the optimal place on the line), which is hoping to be able to add to this. Like the graph and all that you’ve done, really appreciate it!

Just took a better look at it, and it appears to be close but may be over estimating in a few locations. Distance 0,1,2 look correct. Just checked a few past there, 3 and 4 appear to be overestimating the length. The overlaid line looks to be showing the correct length though.

Looks to be due to the first column being in km, and the second being in meters.

[p45cal]

Pythagoras needs the units to be the same to calculate the actual length of the pipe, so since you probably want to have the result in kilometres let's convert those elevation metres to elevation kilometres.
Change:
PipeActualSpillLength = ((D1 - D2) ^ 2 + (E1 - E2) ^ 2) ^ 0.5
to:
PipeActualSpillLength = ((D1 - D2) ^ 2 + (E1 / 1000 - E2 / 1000) ^ 2) ^ 0.5
(and don't forget to recalculate column D afterwards).

[cmnewf]

That works, thanks! Do you think its possible to code in valves as an input (closure times would be a more difficult step) at certain locations?

[p45cal]

That works, thanks! Do you think its possible to code in valves as an input (closure times would be a more difficult step) at certain locations?

Possible? Almost certainly.
Would I be prepared to do it? Probably not.

[cmnewf]

You’ve helped so much already! That’s all I need, I’m going to try to add that to yours, will let you know where I end up!

[cmnewf]

vbaExpress65850Elevation Profile (1).xlsm

Quick method, going to take a bit to understand all your code!

[cmnewf]

Think I understand a bit of the code now. Following the process, the easiest way I see to do this to be to link ubound and lbound to a table so that it stops at the valve forward or backwards. Ideally I would like to have a column with locations of valves and the ubound and lbound would search for the next higher number (ubound) or lower (lbound). Not sure ubound or lbound can be a variable though?

[cmnewf]

Hello p45cal, could you point me in the right direction to pull the forwards / backwards threshold values for each location. Would like to extract them and put them in new columns.

[p45cal]

You need to follow the variable Threshold.
Look at the Sub SegmentPortion2(D1, D2, E1, E2, Threshold, Vals, XVals) where
1.D1 and D2 are 2 distance values and E1 and E2 are 2 elevation values
2. Threshold is updated in this sub too.

You're probably better plotting the chart progressively while running through the code with F8, then you'll get a good visual of where you're at. The section update chart at the end of blah2 can be duplicated and put inside the forward and backward loops: For i = PointNo + 1 To UBound(Dists) - 1 and For i = PointNo To LBound(Dists) + 1 Step -1