VBA Help

Hello,

Can someone help me to create a module (or other) in VBA for excel for the following pseydo-code:

for each [point] in [pointList]:
do until stop:
for each [point+1] in [pointList]:
if [end]: stop if [point].[measure] >= [point+1].[measure]:
[measure]+= [point+1].[measure] – [point].[measure]
else:
{function}FindNextHighestPoint
{function}InterpolateMeasure
[measure] += [interpolatedMeasure]
if {function}InterpolateMeasure or [end]: stop.

The main point of the code is to have one column with distance, and another column with elevations, and to find the distance from one point to the next highest point. Then to run through all of the locations.

Thanks for your help!

Please attach a workbook with data and show what you want to achieve in it.


For the future. The title of the thread should reflect the problem you are facing, not some general statement about help, because by publishing the thread we already know that you need such help. A more accurate title would be, e.g. "Searching for distance between points" or something similar.

Artik

Quote:

---

Originally Posted by Artik

Please attach a workbook with data and show what you want to achieve in it.


For the future. The title of the thread should reflect the problem you are facing, not some general statement about help, because by publishing the thread we already know that you need such help. A more accurate title would be, e.g. "Searching for distance between points" or something similar.

Artik

---

Thanks for letting me know!

I've attached a file, the problem example is a waterline under a city. If the waterline breaks at any point along the line, what would be the potential length of pipe above that point on the entire segment. It would have the potential within the valley, and they anything outside the valley that would be higher than the peaks. Right now i'm just trying to have a column that can calculate the length, and can apply the length to a volume afterwards.

If there was a leak here, would the length be zero?
Attachment 24969

Quote:

---

Originally Posted by p45cal

If there was a leak here, would the length be zero?
Attachment 24969

---

There would be length as there is a higher elevation on the left side of the valley.

So if there were a leak here instead, just a little more to the right:
Attachment 24970
then still, that same higher elevation on the left side should be counted.
Unless there is some nuance that I have missed, I think you're doing it right with your formula in column C.

Quote:

---

Originally Posted by p45cal

So if there were a leak here instead, just a little more to the right:
Attachment 24970
then still, that same higher elevation on the left side should be counted.
Unless there is some nuance that I have missed, I think you're doing it right with your formula in column C.

---

That's where the spreadsheet has an error, it shouldn't include anything to the right or left if there's a value higher in between as gravity wouldn't feed it.

if gravity feeds my diagram in msg#4 it has to feed it too in a point directly below that, as in diagram in msg#6!
I don't think your spreadsheet has an error.

I've changed my mind on your spreadsheet being correct.
On the other hand it's not as simple as the length between the local peaks on either side of the leak above the elevation of the leak, but I may have misunderstood.
The attached has a button which will add minima and maxima indications to column D, as a first step.
I don't understand your pseudo-code in msg#1, but is it something along the lines of these diagrams below?:
Attachment 24990
and:
Attachment 24991

Oops, I got that second diagram wrong at the right hand side:
Attachment 24996

edit post posting: I see the answer to "is it something along the lines of these diagrams below?" is 'yes':
Attachment 24995

You have understood that exactly correct, any chance you know how to code that?

Quote:

---

Originally Posted by p45cal

Oops, I got that second diagram wrong at the right hand side:
Attachment 24996

edit post posting: I see the answer to "is it something along the lines of these diagrams below?" is 'yes':
Attachment 24995

---

I haven't worked it out yet - it may be some time.
I gave you a start.

Like the maximum and minimums, still trying to wrap my head around that!

Quote:

---

Originally Posted by p45cal

I haven't worked it out yet - it may be some time.
I gave you a start.

---

Stared at this for some time and don't understand (sorry, quite new to this!):

If Slope <> PreviousSlope And PreviousSlope <> 0 Then
'Range(Range("B2:B102").Cells(i - 1), Range("B2:B102").Cells(IIf(PreviousSlope = 1, lastup, lastDown))).Offset(, 3).Value = IIf(PreviousSlope = 1, "Max", "Min")

Quote:

---

Originally Posted by p45cal

I haven't worked it out yet - it may be some time.
I gave you a start.

---

cmnewf, please do not quote the entire replies predecessor's. If necessary, insert only the relevant passage to which you refer. You needlessly clog the server with quotes.

Artik

Quote:

---

Originally Posted by cmnewf

Stared at this for some time and don't understand

---

That line is quite complex,but note that it starts with an apostrophe which means it's a commet and doesn't get executed. You could enable it and it will place the same results but in column E. If you step through the code one line at a time with F8 on the keyboard, when you get to that line you'll see it update a few cells in column E. I only used it as a visual while debugging.

Thanks Artik and p45cal! Thanks for the explanation, see how that works now!

cmnewf, oh, no merit mine here. :)

Artik

Those distances in column A, are they measuring horizontal distance rather than actual pipe length?
It might make quite a difference to spill volume.
To illustrate I'll exaggerate the point: say a portion of the pipe is downhill, angled at 45 degrees. Over a horizontal distance of 1 metre (units are irrelevant) the pipe would have to be 1.41 metres long, some 40% longer (and contain 40% more liquid).

Quote:

---

Originally Posted by p45cal

Those distances in column A, are they measuring horizontal distance rather than actual pipe length?
It might make quite a difference to spill volume.
To illustrate I'll exaggerate the point: say a portion of the pipe is downhill, angled at 45 degrees. Over a horizontal distance of 1 metre (units are irrelevant) the pipe would have to be 1.41 metres long, some 40% longer (and contain 40% more liquid).

---

Good question, the distance in column A is 2D (horizontal distance). Over a long enough distance the additional 3D length would be greatly reduced vs looking at one sloped area in particular.

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

Thanks, appreciate it!

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?

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

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?

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.

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).

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?

Quote:

---

Originally Posted by 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?

---

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

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!

Attachment 25176

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

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?

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.

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