VBA Parse and compare

[Muret31]

Hello,
I need your help please, i am looking for a vba code to parse following aviation weather message and compare some contents instead of doing it manually:


weather criteria
251544Z 2518/2624 23015KT 9999 SCT020 TN18/2608Z TX26/2617Z BECMG 2522/2524 29007KT BECMG 2606/2608 35005KT 7000 BKN020 PROB30 TEMPO 2612/2614 19010KT 9999 OVC010 BECMG 2622/2624 12005KT =


251544Z = it means the date and time that the message is issued (25MAY and 1544Z time issued)


2522/2524 (whenever it is 4 numbers + slash (/) + 4 numbers, the first two numbers before the slash (/) mean the day and the two numbers after the slash(/) the time (example: from the 25 of May 22H00 untill the 25 of May 24h00)
2518/2624 (another example: it means from 25 of May at 18H00 untill 26 of May at 24H00)


23015KT(whenever it is 5 numbers + KT, the 3 first numbers mean wind direction (230°) and the 2 last numbers mean (wind force) 15KT
9999 (whenever it is 4 numbers it means the horizontal visibility in meters 9999 means 9999 meter (10KM), example 7000 means 7000 meter (7km).

SCT020 (always disregard this, not important)
TN18/2608Z TX26/2617Z (always disregard this, not important)

PROB30 TEMPO: (always disregard this and all the message coming after)

Example:
PROB30 TEMPO 2612/2614 19010KT 9999 OVC010 BECMG 2622/2624 12005KT (i have to disregard all the message after PROB30 TEMPO but i should always consider all the message after BECMG)


BECMG (means a weather change announcement, it is important and i have always to take into account all the message after BECMG)


BKN020 or OVC010 (Whenever it is BKN or OVC, the 3 numbers after BKN (020) or OVC (010) mean a vertical visibility and to convert it into feet, we have to multiply these 3 numbers with 100 (example: BKN020 = 20 X 100 = 2000feet, another example BKN002 = 2 X 100 =200feet, another example OVC010 = 10 X 100 = 1000feet, OVC100 = 100 X100 = 10000feet

Now i need to compare all above creteria with following to see if the estimated arrival time of the airplane if the weather will be good and if the airplane can land safely.


Example:


Arrival criteria:


arrival date: 25 May
time windows: 17h00 - 19h00


Minimum horizontal visibility that the airplane needs to land safely = 550M
Minimum vertical visibility that the airplane needs to land safely= 200 feet

i need to compare all the weather criteria with the arrival criteria in order to know if between time windows (17H00-19h00) taking into account the minimum horizontal and vertical visibility if the aircraft will land safely.


If all the creteria is met (a text box "OK TO LAND") if not (a text box "LANDING NOT AUTHORIZED)


Hope this can help you understand my needs.
Thanks in advance for your feedback.
Kind regards
MU

 

[DanteAmor]

Check if this helps you a bit, put your weather criteria information in cell A2, also put the data from column A and the formulas.


Execute the macro and columns B and C will be filled

---

<table border="1" cellspacing="0" style="font-family:Calibri,Arial; font-size:11pt; background-color:#ffffff; "> <colgroup><col style="font-weight:bold; width:30px; " /><col style="width:258.53px;" /><col style="width:108.36px;" /><col style="width:76.04px;" /><col style="width:76.04px;" /><col style="width:76.04px;" /></colgroup><tr style="background-color:#cacaca; text-align:center; font-weight:bold; font-size:8pt; "><td > </td><td >A</td><td >B</td><td >C</td><td >D</td><td >E</td></tr><tr style="height:109px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >2</td><td >251544Z 2518/2624 23015KT 9999 SCT020 TN18/2608Z TX26/2617Z BECMG 2522/2524 29007KT BECMG 2606/2608 35005KT 7000 BKN020 PROB30 TEMPO 2612/2614 19010KT 9999 OVC010 BECMG 2622/2624 12005KT =</td><td > </td><td > </td><td > </td><td > </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >3</td><td > </td><td > </td><td > </td><td > </td><td > </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >4</td><td >date</td><td style="text-align:right; ">25-may</td><td > </td><td > </td><td > </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >5</td><td >time issued</td><td >1544Z</td><td > </td><td > </td><td > </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >6</td><td > </td><td > </td><td > </td><td > </td><td > </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >7</td><td >arrival date</td><td style="text-align:right; ">25-may</td><td style="text-align:right; ">26-may</td><td > </td><td > </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >8</td><td >time windows</td><td style="text-align:right; ">18</td><td style="text-align:right; ">24</td><td > </td><td > </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >9</td><td > </td><td > </td><td > </td><td > </td><td > </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >10</td><td >wind direction</td><td style="text-align:right; ">230</td><td > </td><td > </td><td > </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >11</td><td >wind force</td><td style="text-align:right; ">15</td><td > </td><td > </td><td > </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >12</td><td > </td><td > </td><td > </td><td > </td><td > </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >13</td><td >horizontal visibility</td><td style="text-align:right; ">9999</td><td style="text-align:right; ">10</td><td >MET</td><td style="text-align:right; ">550</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >14</td><td > </td><td > </td><td > </td><td > </td><td > </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >15</td><td >vertical visibility</td><td >BKN020</td><td style="text-align:right; ">2000</td><td >MET</td><td style="text-align:right; ">200</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >16</td><td > </td><td >OVC010</td><td style="text-align:right; ">1000</td><td >MET</td><td style="text-align:right; ">200</td></tr></table><br /><table style="font-family:Arial; font-size:10pt; border-style: groove ;border-color:#00ff00;background-color:#fffcf9; color:#000000; "><tr><td ><b>Formeln der Tabelle</b></td></tr><tr><td ><table border = "1" cellspacing="0" cellpadding="2" style="font-family:Arial; font-size:9pt;"><tr style="background-color:#cacaca; font-size:10pt;"><td >Zelle</td><td >Formel</td></tr><tr><td >D13</td><td >=IF(B13>E13,"MET","NOT")</td></tr><tr><td >D15</td><td >=IF(B15>E15,"MET","NOT")</td></tr><tr><td >D16</td><td >=IF(B16>E16,"MET","NOT")</td></tr></table></td></tr></table>

---

Sub weather_criteria()
    datas = Split(Range("A2"), " ")
    
    Range("B4").Value = Left(datas(0), 2) & Format(Date, "mmm")
    Range("B5").Value = Mid(datas(0), 3)
    
    Range("B7").Value = Left(datas(1), 2) & Format(Date, "mmm")
    Range("B8").Value = Mid(datas(1), 3, 2)
    Range("C7").Value = Mid(datas(1), 6, 2) & Format(Date, "mmm")
    Range("C8").Value = Mid(datas(1), 8, 2)
    
    Range("B10").Value = Left(datas(2), 3)
    Range("B11").Value = Mid(datas(2), 4, 2)
    
    Range("B13").Value = datas(3)
    Range("C13").Value = Round(datas(3) / 1000, 0)
    
    j = 15
    disregard = True
    For i = 4 To UBound(datas)
        If Left(datas(i), 3) = "BKN" Or Left(datas(i), 3) = "OVC" Then
            Range("B" & j).Value = datas(i)
            Range("C" & j).Value = Mid(datas(i), 4) * 100
            j = j + 1
        End If
    Next
End Sub

 

[Muret31]

Dear Sir,
Many thanks for your help.
It works fine, i will then try to modify it for my needs.
I ill let you know.
Many thanks
MU

 

[DanteAmor]

I'm glad to help you. Thanks for the feedback.

 

[Muret31]

Dear Sir,

I would like to know how i can modify the macro in order to get value after "BECMG" with condition (Example: whenever i have "BECMG" i would like to get the value after ("BECMG 2606/2608 35005KT 7000 BKN020").
Many thanks in advance
MU

 

[DanteAmor]

Dear Sir,

I would like to know how i can modify the macro in order to get value after "BECMG" with condition (Example: whenever i have "BECMG" i would like to get the value after ("BECMG 2606/2608 35005KT 7000 BKN020").
Many thanks in advance
MU

In the text, you have 3 times BECMG, you want to extract the 3 texts after BECMG and where do you want to put it?

251544Z 2518/2624 23015KT 9999 SCT020 TN18/2608Z TX26/2617Z BECMG 2522/2524 29007KT BECMG 2606/2608 35005KT 7000 BKN020 PROB30 TEMPO 2612/2614 19010KT 9999 OVC010 BECMG 2622/2624 12005KT =

 

[Muret31]

Thank you very much for your feedback
Whenever i have a BECMG, it announces a new forecast « for example: BECMG 2522/2524 29007KT » mean between 25May 22h00 and 25May 24h00 the wind direction will be 290degree and 07knots for the force,
And if my airplane is supposed to land between 22h00 and 24h00 25May i have to consider these new values.
So i will need to parse each becoming with its values and compare it with my estimated arrival time to see the impact.

Hope this help you understand my needs.

I remain at your disposal for any other question you may have.

Many thanks
MU

 

[Muret31]

Here is the way I would like them to get parsed:

Time issued= 25May 15h44
Validity = 25May 18h00 until 26May 24h00 wind=230
Force=15
Visibility=9999m or (10km)
SCT020 TN18/2608Z TX26/2617Z « should be disregarded)


BECMG 25May 22h00 25May 24h00 wind=2900
Force=07

BECMG 26May 06h00 26May 08h09 wind=3500
Force=05
Visibility= 7000
Ceiling= 2000 (BKN020 = 20x100=2000)

PROB30 TEMPO 2612/2614 19010KT 9999 OVC010 whenever we have PROB30 Tempo it should be disregarded


BECMG 26May 22h00 26May 24h00 wind=120
Force=05


Whenever we have a BECMG it announces a weather change, sometimes we can have one BECMG or nothing.

For example:

If my airplane is estimated to land at 18h00 25May I must not consider the below message, because it announces a weather between 25May 22h00z and 25May 24h00

BECMG 2522/2524 29007KT

 

[Muret31]

Sorry i have noticed an error on my previous reply:

regarding the wind it is 290 and not 2900
350 and not 3500

Many thanks
MU