Input array with UDF and reference specific cells in relation to input array

  • Hello!


    I'm in need of coding UDF as it's easier to maintain through large number of workbooks. I need to make UDF that will input two angles and sum them up. I'm working with angles in DMS format, but D, M and S need to be in separate cells. I've already made it in excel as formula:

    Code
    1. =IF(AND((E10+F10/60+G10/3600)+(B12+C12/60+D12/3600)>360,(E10+F10/60+G10/3600)+(B12+C12/60+D12/3600)-360>180),(E10+F10/60+G10/3600)+(B12+C12/60+D12/3600)-360-180,
    2. IF(AND((E10+F10/60+G10/3600)+(B12+C12/60+D12/3600)>360,(E10+F10/60+G10/3600)+(B12+C12/60+D12/3600)-360<180),(E10+F10/60+G10/3600)+(B12+C12/60+D12/3600)-360,
    3. IF(AND((E10+F10/60+G10/3600)+(B12+C12/60+D12/3600)<=360,(E10+F10/60+G10/3600)+(B12+C12/60+D12/3600)>=180),(E10+F10/60+G10/3600)+(B12+C12/60+D12/3600)-180,
    4. (E10+F10/60+G10/3600)+(B12+C12/60+D12/3600))
    5. ))

    I have never coded in VBA. With my UDF I want to input two angles (each as 1 array that contains 3 horizontal cells next to each other) and output in 3 horizontal cells next to each other. Searching on the internet I haven't found a way to reference specific cell from array. So most left cell of input arrays is D, middle one M and right one S. How would it be coded?

    Sorry if my request isn't clear. English isn't my first language.

    Thanks in advance for all replays.

  • Hello and Welcome to the Forum :)


    Would you mind attaching a small sample, with a few records ... along with your expected result ...;)

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Thumbs Up" icon, below, in the bottom right corner:)

  • I have attached *.xlsx and *.pdf with examples of current Excel formula I have been using and expected result with new UDF. Current excel formula I'm using only calculates decimal degrees. I have stopped working on it as UDF would be much simpler to maintain. Hoping it makes my task much more understandable.

  • Thanks for your sample file ...


    Not sure to really understand your expected final result ...


    Meanwhile your question has reminded me of an interesting file made by Chip ...


    see attached LatLong file


    Hope this will help

    :)

    Files

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Thumbs Up" icon, below, in the bottom right corner:)

  • So ...


    May be you need 3 UDFs to integrate ... for example your D6 formula .....into your results located in H6, I6 and J6 ...


    Thanks for your clarification

    Files

    • Test DMS.xlsx

      (12.37 kB, downloaded 19 times, last: )

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Thumbs Up" icon, below, in the bottom right corner:)

  • I wanted to input array of 3 horizontal cells containing DMS angle and output it in same DMS format in 3 horizontal cells.


    Thanks for LatLong file. It actually is much much simpler formula than I'm using right now with exactly same output. :) And also might be better to use than my idea to create UDF formula because I have to do much more operations other than just summing up angles.


    I'm suprised that time functions can be used to calculate angles.

  • Let me try to understand your initial calculation ...


    Say we take Row 4 ...


    1. First Angle is D 120 M 20 S 55 ... or in Decimals = 120.34861


    2. Second Angle is D 96 M 30 S 20 ... or in Decimals = 96.50555


    So it produces a total of 216.85416 in Decimals ... and back in D M S ... D 216 M 51 S 15


    Is this right or wrong ...?

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Thumbs Up" icon, below, in the bottom right corner:)

  • Yes, it will help my very very much. That's exactly what I was looking for. Thanks for helping. ;)


    Glad to hear this is helping you out :)


    Thanks a lot for your Thanks ...AND for the Like :thumbup:

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Thumbs Up" icon, below, in the bottom right corner:)