A story about how I simplified my military service with Excel and VBA
Inspired by the post "What to do an IT specialist in the army or how I wrote games on the VBA"
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 a new environment for me, I was entrusted with a workplace with a computer. (I must say the computer at that time was not bad, which I was glad to.)
They identified me for the position of the tablet command center (KP, our central KP, as far as I know, was underground), in fact, I was not a witness ... but I was engaged in the duties of a timekeeper at the Starting Command Point (UPC).
')
Further briefly what “Flying” was like (the so-called training period, flights or firing range at the training grounds) was for me, and using programming I simplified my military service a bit.
The duties of the timekeeper included:
Squadron commanders drafted plans for certain exercises for certain pilots.
The output was the “planned flight table”, which listed the very exercises, the numbers of the aircraft’s sideboards on which the exercises would be performed, the level of aircraft refueling, the crews, their call signs, the planned take-off time, the landing time, and the runway maintenance time ), sunset time, sunrise and twilight length, the time of weather reconnaissance, the search rescue team duty, and a whole lot more.
You sit, listening to the negotiations of officers of the UPC, KP, landing zone, and in time you transfer to the KP in the transmitter caps: “420th take-off”, “two half a first - take-off”, “three half-first - landing”, etc. etc. while keeping a journal ...
All takeoffs and landings are recorded in the timekeeping log: who, with whom, when took off, when landed, how many flew.
There was once an unpleasant moment when I fell asleep rudely (I was chopped off for 5 minutes, as it turned out) during the flights, and missed the landing of one crew. As it turned out, the fact that the timekeeper is transmitting to a control is sometimes meaningful information, because the time of takeoffs and landings on the air, no one voiced detached.
I must say that before and after this incident I did not miss a single takeoff and landing on my duty.
After the flights, the entire command, flight and technical staff are gathered for the debriefing, for which a total crew raid is prepared from the timekeeping log.
Depending on the weather, there could be from one to three planning tables, one option for possible weather (based on meteorological forecasts). Pilot training is different, not all are allowed to fly in adverse weather conditions.
The planning table was made in 4-6 copies (at KP, UPC, engineer, technicians, timekeeper, top management, if available).
The manufacturing phase of the finishing version of the planning table took an enormous amount of time. After all, for some flights, on average, there were 41 sorties each. Each had an average of 4 exercises.
I remember with horror how, because of one blot, I had to redo EVERYTHING done in 4 hours of work, just like before the flights, something changed in terms of flights and I had to carefully wipe the icons from the table with a blade, and in case of failure to redo everything all over again! Drawing a planned table took from 3 to 10 hours. And if we take into account that the planned table was drawn up 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 manufacture of planned tables manually, I represented what all the icons, digits and squiggles on the planned table are. Having specified incomprehensible for me moments and features of the design of the planning tables, I decided to convert the whole thing into a digital form.
In the field of the software suitable for the development of something more or less convenient to use, was only MS Excel 2003 with VBA, and decided to write on it.
For convenient work, several topical issues were resolved:
Initially, the planned table existed only as a lined A2 format. An excel document with identical markup was created for the job.
All the data that was often used were systematized and placed on separate sheets for automatic filling on the main planning table.
When you open the excel book, it was automatically suggested to enter the name of the planned table and the date of the flight, after which a copy was made to prevent damage to the original document and convenience (one table, one file).
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 ready-made icon on the planning table and press the [Insert] key
If you do not go into details, then to speed up the creation of icons, “templates” of a set of blanks were also created by exercise number.
The line "tasks", you can correct in place and insert into it additional elements of tasks, according to the flight plan.
All data on the icons on the planning table are stored on a separate sheet:
And are used in the future for drawing up statements:
Any workpiece can be changed, in the future it will be copied to the planned table and an icon will be drawn from these blanks:
The graphic blank is also attached, the script that will be executed when using the blank.
I do not 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 did not yet know from whom and how to protect the exel book at all, it was decided to simply request the “secret code” in the form of VB or read it from the code.txt file, after which the book was either closed , or continued to work.
You can download it here: https://yadi.sk/d/VAu1R3m_biSDC
As it turned out, it works crookedly on new versions of the office. Run better in MS Excel 2003.
The planning table could be made an order of magnitude faster and more accurately than manually.
Automatic creation of statements.
Easily make edits to the plan table.
The document is stored electronically and reproduced as many times as you like.
The built-in MSO scaling functions for printing greatly simplified printing in any size.
Respect by the officers.
PS
Before demobilization, I had to “replace” the replacement, because no one perceived the planned manual.
About a year and a half later, another person called up to me, who came to my place and specified how to work with my “hand-made article”.
After another six months, my former direct commander contacted me and complained that he had discovered this “project” in other military units when he was flying to shoot.
Glad I was helpful to at least someone at the time.
Pps
If someone has questions about the conduct of flights, service or planning tables - I will gladly answer to the best of awareness.
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 a new environment for me, I was entrusted with a workplace with a computer. (I must say the computer at that time was not bad, which I was glad to.)They identified me for the position of the tablet command center (KP, our central KP, as far as I know, was underground), in fact, I was not a witness ... but I was engaged in the duties of a timekeeper at the Starting Command Point (UPC).
')
Further briefly what “Flying” was like (the so-called training period, flights or firing range at the training grounds) was for me, and using programming I simplified my military service a bit.
The duties of the timekeeper included:
- The day before the flight:
Production of planned tables. - During the flight:
Alert KP about the take-off and landing crews at the airport;
Logging timekeeping. - After the flight:
Summing up the timing of flights.
Production of planning tables
Squadron commanders drafted plans for certain exercises for certain pilots.
The output was the “planned flight table”, which listed the very exercises, the numbers of the aircraft’s sideboards on which the exercises would be performed, the level of aircraft refueling, the crews, their call signs, the planned take-off time, the landing time, and the runway maintenance time ), sunset time, sunrise and twilight length, the time of weather reconnaissance, the search rescue team duty, and a whole lot more.
Alert KP about takeoffs and landings of crews at the airport
You sit, listening to the negotiations of officers of the UPC, KP, landing zone, and in time you transfer to the KP in the transmitter caps: “420th take-off”, “two half a first - take-off”, “three half-first - landing”, etc. etc. while keeping a journal ...
Logging timekeeping.
All takeoffs and landings are recorded in the timekeeping log: who, with whom, when took off, when landed, how many flew.
There was once an unpleasant moment when I fell asleep rudely (I was chopped off for 5 minutes, as it turned out) during the flights, and missed the landing of one crew. As it turned out, the fact that the timekeeper is transmitting to a control is sometimes meaningful information, because the time of takeoffs and landings on the air, no one voiced detached.
I must say that before and after this incident I did not miss a single takeoff and landing on my duty.
Summing up the timing of flights.
After the flights, the entire command, flight and technical staff are gathered for the debriefing, for which a total crew raid is prepared from the timekeeping log.
Production of scheduled flight schedules
Depending on the weather, there could be from one to three planning tables, one option for possible weather (based on meteorological forecasts). Pilot training is different, not all are allowed to fly in adverse weather conditions.
The planning table was made in 4-6 copies (at KP, UPC, engineer, technicians, timekeeper, top management, if available).
The manufacturing phase of the finishing version of the planning table took an enormous amount of time. After all, for some flights, on average, there were 41 sorties each. Each had an average of 4 exercises.
I remember with horror how, because of one blot, I had to redo EVERYTHING done in 4 hours of work, just like before the flights, something changed in terms of flights and I had to carefully wipe the icons from the table with a blade, and in case of failure to redo everything all over again! Drawing a planned table took from 3 to 10 hours. And if we take into account that the planned table was drawn up 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 manufacture of planned tables manually, I represented what all the icons, digits and squiggles on the planned table are. Having specified incomprehensible for me moments and features of the design of the planning tables, I decided to convert the whole thing into a digital form.
In the field of the software suitable for the development of something more or less convenient to use, was only MS Excel 2003 with VBA, and decided to write on it.
Digital Transfer
For convenient work, several topical issues were resolved:
Digital media transfer
Initially, the planned table existed only as a lined A2 format. An excel document with identical markup was created for the job.
Made a template with automatic substitution of data
All the data that was often used were systematized and placed on separate sheets for automatic filling on the main planning table.
When you open the excel book, it was automatically suggested to enter the name of the planned table and the date of the flight, after which a copy was made to prevent damage to the original document and convenience (one table, one file).
Automated creation of exercise icons
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 ready-made icon on the planning table and press the [Insert] key
If you do not go into details, then to speed up the creation of icons, “templates” of a set of blanks were also created by exercise number.
The line "tasks", you can correct in place and insert into it additional elements of tasks, according to the flight plan.
Automated statement creation
All data on the icons on the planning table are stored on a separate sheet:
And are used in the future for drawing up statements:
Were created blanks "tasks" with their names
Any workpiece can be changed, in the future it will be copied to the planned table and an icon will be drawn from these blanks:
The graphic blank is also attached, the script that will be executed when using the blank.
Protection against third parties (failed)
I do not 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 did not yet know from whom and how to protect the exel book at all, it was decided to simply request the “secret code” in the form of VB or read it from the code.txt file, after which the book was either closed , or continued to work.
The result was something like this:
You can download it here: https://yadi.sk/d/VAu1R3m_biSDC
As it turned out, it works crookedly on new versions of the office. Run better in MS Excel 2003.
What has achieved:
The planning table could be made an order of magnitude faster and more accurately than manually.
Automatic creation of statements.
Easily make edits to the plan table.
The document is stored electronically and reproduced as many times as you like.
The built-in MSO scaling functions for printing greatly simplified printing in any size.
Respect by the officers.
PS
Before demobilization, I had to “replace” the replacement, because no one perceived the planned manual.
About a year and a half later, another person called up to me, who came to my place and specified how to work with my “hand-made article”.
After another six months, my former direct commander contacted me and complained that he had discovered this “project” in other military units when he was flying to shoot.
Glad I was helpful to at least someone at the time.
Pps
If someone has questions about the conduct of flights, service or planning tables - I will gladly answer to the best of awareness.
Source: https://habr.com/ru/post/238427/
All Articles