Consulting

Page 2 of 2 FirstFirst 1 2
Results 21 to 34 of 34

Thread: VBA Help

  1. #21
    Knowledge Base Approver VBAX Wizard p45cal's Avatar
    Joined
    Oct 2005
    Location
    Surrey UK
    Posts
    5,844
    You haven't been forgotten. I only have intermittent access to a computer this week and the coming weekend.
    p45cal
    Everyone: If I've helped and you can't be bothered to acknowledge it, I can't be bothered to look at further posts from you.

  2. #22
    VBAX Regular
    Joined
    Sep 2019
    Posts
    17
    Location
    Thanks, appreciate it!

  3. #23
    Knowledge Base Approver VBAX Wizard p45cal's Avatar
    Joined
    Oct 2005
    Location
    Surrey UK
    Posts
    5,844
    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?
    p45cal
    Everyone: If I've helped and you can't be bothered to acknowledge it, I can't be bothered to look at further posts from you.

  4. #24
    VBAX Regular
    Joined
    Sep 2019
    Posts
    17
    Location
    The units of distance are meters for both, and constant diameter.

  5. #25
    Knowledge Base Approver VBAX Wizard p45cal's Avatar
    Joined
    Oct 2005
    Location
    Surrey UK
    Posts
    5,844
    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?
    Attached Files Attached Files
    Last edited by p45cal; 09-28-2019 at 02:50 PM.
    p45cal
    Everyone: If I've helped and you can't be bothered to acknowledge it, I can't be bothered to look at further posts from you.

  6. #26
    VBAX Regular
    Joined
    Sep 2019
    Posts
    17
    Location
    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.
    Last edited by cmnewf; 09-28-2019 at 08:47 PM.

  7. #27
    Knowledge Base Approver VBAX Wizard p45cal's Avatar
    Joined
    Oct 2005
    Location
    Surrey UK
    Posts
    5,844
    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).
    p45cal
    Everyone: If I've helped and you can't be bothered to acknowledge it, I can't be bothered to look at further posts from you.

  8. #28
    VBAX Regular
    Joined
    Sep 2019
    Posts
    17
    Location
    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?

  9. #29
    Knowledge Base Approver VBAX Wizard p45cal's Avatar
    Joined
    Oct 2005
    Location
    Surrey UK
    Posts
    5,844
    Quote Originally Posted by cmnewf View Post
    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.
    p45cal
    Everyone: If I've helped and you can't be bothered to acknowledge it, I can't be bothered to look at further posts from you.

  10. #30
    VBAX Regular
    Joined
    Sep 2019
    Posts
    17
    Location
    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!

  11. #31
    VBAX Regular
    Joined
    Sep 2019
    Posts
    17
    Location
    vbaExpress65850Elevation Profile (1).xlsm

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

  12. #32
    VBAX Regular
    Joined
    Sep 2019
    Posts
    17
    Location
    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?

  13. #33
    VBAX Regular
    Joined
    Sep 2019
    Posts
    17
    Location
    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.

  14. #34
    Knowledge Base Approver VBAX Wizard p45cal's Avatar
    Joined
    Oct 2005
    Location
    Surrey UK
    Posts
    5,844
    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
    p45cal
    Everyone: If I've helped and you can't be bothered to acknowledge it, I can't be bothered to look at further posts from you.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •