Air Traffic Control Worksheet

dmeek · Dec 4th 2009

Re: Air Traffic Control Worksheet

I made additional enhancements to my version of the model. I implemented much of the functionality that Ger has in his model (delete flights, clear all flights, highlight and comment cells in the Airspace Grid).

I added a feature whereby if you double-click the Airspace column - then click a cell in the Airspace Grid - the new Airspace coordinates are generated. This only works if there is a CallSign on the row that is double-clicked.

I verified that all code works if you make multiple copies the ASC worksheet.

Fixed a few bugs too.

Regards,
Dave

**Ger Plante** · Dec 4th 2009

Re: Air Traffic Control Worksheet

Hi Dave, well done - looks nice. There is always more than one way to skin a cat - VBA is no different.

I'll take a closer look later on, but am personally not too keen on the scripting dictionary.

I'm engaging with AON off-thread on some additional requirements.

Cheers for now,
Ger

AoN · Dec 4th 2009

Re: Air Traffic Control Worksheet

I thank everyone for their help in this, but Ger Plante has done a wonderful job in getting the project to its omega version in a fraction of the time I had expected it to go. He's agreed to stay on for the completion of the project and it's turned out very well so far.

I have read the concerns of some of the other programmers, to include that the code is difficult to edit. This is true, and it's an issue we are facing now with adding in our final features to the project, but that is also a good thing. The operators, whom tell me what they need/want from this, are not exactly the most computer savvy, let alone cooperative people (they try to be maintainers when we tell them not to be [that's my job and I dislike having to fix something they broke trying to do my job]). I've little doubt they would attempt to mess with the coding of the program to further adjust it to their desires to "test" new ways of doing things. Ultimately, that would probably result is making the entire project a complete waste, whether the code is simple or not. At least with a complex code they are far more discouraged from even attempting such a thing.

I do appreciate everyone's support and willingness to help, and I should've mentioned sooner that Ger Plante had already taken on the project in full, but your assistance is always welcome. I'm afraid I won't be able to pay anyone else, but if anyone wishes to make contributions you are more than welcome to do so.

This has been a great learning experience for me, the code everyone has submitted is amazing compared to how I would have gone about even the simpliest of features. I thank all of you for all your help.

RolfJ · Dec 5th 2009

Re: Air Traffic Control Worksheet

I am interested in this, and since I am just coming off a project I'd like to throw my hat into the ring. To show my sincerity I am posting my suggestion on how to code for the marking of the air space of several planes, for now without any further restriction.

Option Explicit

'+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
'LEGAL NOTICE: This code is property of SoarentComputing
'Copying is not permitted without explicit approval by SoarentComputing
'+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

Const TOPLEFTCELL As String = "J3"
Const AIRSPACEGRID As String = "J3:AY35"

Code

Sub test()
    Dim rAirSpaces As Range
    Set rAirSpaces = Range(Range("D3"), Range("D" & Rows.Count).End(xlUp))
    Call MarkAirSpace(rAirSpaces)
End Sub
Sub MarkAirSpace(rAirSpace As Range)
    
    Range(AIRSPACEGRID).Interior.ColorIndex = xlNone
    
    Dim x As String
    Dim y As String
    Dim z As String
    Dim centerX As Integer
    Dim centerY As Integer
    
    Dim r As Range
    For Each r In rAirSpace
        x = Left(r.Value, 2)
        y = Mid(r.Value, 4, 1)
        z = Mid(r.Value, 5, 1)
        centerX = 3 * (91 - x) + 1
        centerY = 3 * (Asc(y) - 65) + 1
        Select Case z
            Case 1 To 3
                centerY = centerY + z - 1
            Case 4 To 6
                centerX = centerX + 1
                centerY = centerY + z - 4
            Case 7 To 9
                centerX = centerX + 2
                centerY = centerY + z - 7
        End Select
        Range(AIRSPACEGRID).Cells(centerX - 1, centerY - 1).Interior.Color = vbYellow
        Range(AIRSPACEGRID).Cells(centerX - 1, centerY).Interior.Color = vbYellow
        Range(AIRSPACEGRID).Cells(centerX - 1, centerY + 1).Interior.Color = vbYellow
        Range(AIRSPACEGRID).Cells(centerX, centerY - 1).Interior.Color = vbYellow
        Range(AIRSPACEGRID).Cells(centerX, centerY).Interior.Color = vbYellow
        Range(AIRSPACEGRID).Cells(centerX, centerY + 1).Interior.Color = vbYellow
        Range(AIRSPACEGRID).Cells(centerX + 1, centerY - 1).Interior.Color = vbYellow
        Range(AIRSPACEGRID).Cells(centerX + 1, centerY).Interior.Color = vbYellow
        Range(AIRSPACEGRID).Cells(centerX + 1, centerY + 1).Interior.Color = vbYellow
    Next r
End Sub

Display More

I am looking forward to hearing back from you,
Rolf Jaeger
SoarentComputing
http://soarentcomputing.com/So…puting/ExcelSolutions.htm

AoN · Dec 5th 2009

Re: Air Traffic Control Worksheet

Thank you, RoflJ, but as I stated in my last post, Ger Plante will be finishing the project. I appreciate your code, it's rather unique, but further assistance is not required.

dmeek · Dec 7th 2009

Re: Air Traffic Control Worksheet

AoN -

I've continued to develop my version of the project (just for fun), and I added some functionality that you may find interesting: While it is important to know if two aircraft occupy the same airspace - it seems that once this happens there is very little time to correct it! Therefore, I added logic to determine where two flight paths cross.

I added a column called 'Heading' to indicate the direction of travel (0-360). I then compute the coordinates where two flight paths intercect (had to relearn a little Trig for that exercise

This version of the model does not take into account speed-of-travel and distance to the intercection point. This which would be necessary if you want to determine whether to aircraft will arrive in the same airspace at the same time. Also, a time-stamp parameter would be a good addition to indicate when the airspace coordinates were recorded - as I assume jet aircraft can change coordinates rather quickly.

Let me know what you think.

Regards,
Dave

ps - had to zip the file because it was larger the 205k (what's up with the miniscule file-size restrictions!

AoN · Dec 7th 2009

Re: Air Traffic Control Worksheet

Thank you, dmeek. Though you are thinking along the lines of operations, not a bad thing, it's a bit too along those lines. Operations has a system for controlling aircraft factoring in everything from altitude to speed to anything else they need. This spreedsheet is meant to be a tool for the assignment of airspace, not aircraft control. Basically, the only factors of any concern are altitude and assignment, the actual location of the aircraft is irrelavent due to the fact the aircraft is given free reign over the assigned airspace within the assigned altitudes.

Don't get me wrong, it's a brilliant program, but in truth it will hinder the overall system. I love the code though! Gave me a trig refresher to figure it out. ^^'

Thank you for your help, I just wish I could use it.

**Ger Plante** · Dec 7th 2009

Re: Air Traffic Control Worksheet

Sweet holy mosses Dave, you could launch the shuttle and dock with the ISS using that software

Air Traffic Control Worksheet

Participate now!

Share