The story of how I simplified my military service with Excel and VBA

    It was inspired by the post “What to do for an IT specialist in the army or how I wrote games on VBA”.

    image In 2004-2006 I served in the army, and more specifically - in the Air Force. After completing the course of a young fighter and mastering in a new environment for me, they entrusted me with a workstation with a computer. (I must say that the computer at that time was not bad, which I was glad about.)

    They appointed me to the position of tablet computer command post (KP, as far as I know, our central KP was underground), but in reality I was awkward by whom ... but I was engaged in timing responsibilities at the Start Command Post (UPC).

    Further, briefly, what were the “Flights” (the so-called period of training, flights or firing at ranges) for me, and how using programming I simplified my military service a bit.

    The responsibilities of the timekeeper included:
    • The day before the flight:
      Production of planning tables.
    • During flights:
      Notification of KP about takeoffs and landings of crews at the airfield;
      Keeping a timekeeping journal.
    • After flights:
      Summing up flight timing.


    Production of planning tables

    Squadron commanders drafted plans for certain exercises for specific pilots.
    The result was a “Planned Flight Table”, it listed the very exercises, the numbers of the aircraft sides on which the exercises will be performed, the level of aircraft refueling, crews, their callsigns, the planned take-off, landing, service time of the runway (runway) ), the time of sunset, sunrise and the length of twilight, the time of weather reconnaissance, the watch of the search and rescue team, and much more.

    Alert KP about takeoffs and landings of crews at the airport

    image

    You sit, listening to the talks of officers of the UPC, KP, landing zone, and in time transfer to the KP in the transmitter capsule: “420th takeoff”, “two half hundred first - takeoff”, “three half hundred first - landing”, etc. etc. the journal was kept ...

    Keeping a timekeeping journal.

    All take-offs and landings are recorded in the timing book: who, with whom, when took off, when it landed, how many flew.
    There was once an unpleasant moment when I boorishly fell asleep (chopped off for 5 minutes, as it turned out) during the flights, and missed the landing of one crew. As it turned out, what the timekeeper sends to the CP is sometimes significant information, because nobody takes apart the time of takeoffs and landings on air.

    It must be said that before and after this incident, I did not miss a single take-off and landing on duty.

    Summing up flight timing.

    After flights, all command, flight and technical personnel gather for flight analysis, for which an extract of the general crew raid is prepared from the timing magazine.

    Production of planned flight tables


    Depending on the weather, the planned tables could be from one to three, according to one variant for possible weather (based on forecasts of meteorologists). The training of pilots is different, not all are allowed to fly in difficult weather conditions.
    The planned table was made in 4-6 copies (at the control gear, SKP, engineer, technicians, timekeeper, top management, if available).

    The stage of making the final version of the planning table took a huge amount of time. Indeed, for one flight, an average of 41 sorties was performed. Each had an average of 4 exercises.

    I remember with horror how, due to one blot, I had to redo EVERYTHING done in 4 hours of work, just before the flights, something changed in terms of flights and I had to carefully wipe the table icons with a blade, and in case of failure, redo everything! It took 3 to 10 hours to draw a planning table. And when you consider that a planning table was compiled and was handed to me in draft form after 20 hours, sometimes there was no time to sleep before the flights themselves.

    After a month of torment with the production of planning tables by hand, I imagined what all the icons, numbers and squiggles on the planning table are like. Having clarified the moments and peculiarities of the design of the planning tables that were incomprehensible to me, I decided to translate the whole thing into digital form.
    In the field, of the software tools suitable for the development of something more or less convenient for use, there was only MS Excel 2003 with VBA, and I decided to write on it.

    Digital manufacturing


    For convenient work, several urgent issues were resolved:

    Digital paper transfer

    Initially, the planning table existed only as a lined-up form in A2 format. An excel document with identical markup was created for the job.

    image

    A template with automatic data substitution is made.


    All data that was often used was systematized and put on separate sheets for automatic filling in the main planning table. When you open the excel book, it was automatically offered to enter the name of the planning table and the date of the flights, after which a copy was created to prevent damage to the original document and convenience (one table, one file).

    image



    image

    Automated exercise icon creation

    For the convenience of creating icons, it was decided to use the VB form and hot keys.
    So, when creating the next icon, it was enough to select a range of cells or a finished icon on the planning table and press the [Insert] key. Without going into details, to speed up the creation of icons, “templates” of a set of blanks by exercise number were also created. The line “tasks” can be corrected on the spot and insert additional elements of tasks into it, according to the flight plan.

    image



    image



    image

    Automated statement generation


    All data of the icons on the planning table is stored on a separate sheet: And they are used in the future to compile statements:

    image



    image

    The blanks of “tasks” were created with their names

    Any procurement can be changed, in the future it will be copied to the planning table and the icon will be composed of these procurement:

    image
    image

    The graphic procurement is also attached, a script that will be executed when using the procurement.

    Third Party Protection (failed)


    I don’t know why ... but I decided to somehow protect my project from being used by third parties.
    But since then I still had little knowledge and I still did not know from whom and how to protect the exel book, it was decided to simply request a “secret code” in the form of VB or read it from the code.txt file, after which the book either closed , or continued to work.

    The result is something like this:

    image

    You can download it here: https://yadi.sk/d/VAu1R3m_biSDC
    As it turned out, it works crookedly on new versions of the office. It is better to run in MS Excel 2003.

    What I achieved:


    The planned table could be made an order of magnitude faster and more accurately than manually.
    Automatic creation of statements.
    It’s easy to make changes to the planning table.
    The document is stored in electronic form and reproduced as many times as necessary.
    MSO's built-in scaling features for printing greatly simplify printing to any size.
    Respect from the officers.

    PS
    Before demobilization, I had to “train” the replacement in its place, because no one already perceived the planned manual execution.
    After about a year and a half, another person called up with me, who came to my place and specified how to work with my “craft”.
    Six months later, my former direct commander contacted me and complained that he had discovered this “project” in other military units when he flew to firing.

    I am glad that I was useful to at least someone at that time.

    PPS
    If anyone has questions about flights, service or planning tables, I will be happy to answer as much as I can.

    Also popular now: