A method for summarizing corn test field data by using VBA platform

Abstract

The application discloses a corn test field data summarizing method using a VBA platform, and the basic principle of realizing automatic data summarizing by using a VBA language is as follows: R1: opening a program file, the program file containing source codes of all automatic event operations; under the action of the codes, a test data summarizing workbook is created; R2: while creating a new workbook, a 'variety source' table is newly created, the source codes are synchronously transmitted, and the program file is exited; R3: the source codes in the 'variety source' table complete the creation of other nine sub-tables and the transmission of sub-table event codes; R4: data entry; a manual interactive information import mechanism and a corresponding fault-tolerant mechanism are adopted; R5: under the action of respective event function codes, the entered data complete automatic calculation, summarizing and transmission of the data, and meanwhile, corresponding formats are standardized, and a complete report is formed; the application can not only improve data summarizing efficiency, but also reduce a data summarizing error rate.

Description

Technical Field

[0001] This invention relates to the field of data aggregation technology, and more specifically to a method for aggregating maize experimental field data using a VBA platform. Background Technology

[0002] With the continuous progress of society, the number of field trials for various crops is increasing, and the requirements for standardized processing and rapid data aggregation are also rising. Various agricultural enterprises, public institutions, and research institutes have a growing need for efficient methods of data aggregation and processing.

[0003] Existing methods for summarizing maize experimental field data mostly use Excel functions and formulas for calculation and summarization. These methods require pre-prepared summary tables and preset function formulas, which are not only inefficient but also prone to summarization errors (such as accidentally deleting function formulas due to operational mistakes).

[0004] Therefore, how to provide a method for summarizing maize experimental field data that can not only improve the efficiency of data summarization but also reduce the error rate of data summarization is a problem that urgently needs to be solved by those skilled in the art. Summary of the Invention

[0005] In view of this, the purpose of this invention is to provide a method for summarizing maize experimental field data using the VBA platform.

[0006] To achieve the above objectives, the present invention adopts the following technical solution:

[0007] A method for summarizing maize experimental field data using the VBA platform, implemented using the VBA language, includes the following steps:

[0008] S1: Open the program file, which contains the source code for all automated event operations. With the source code, a new experimental data summary workbook is created: the user-inputted preset workbook renaming value is captured, and the name of the new workbook is changed to the preset workbook renaming value;

[0009] S2: While creating a new summary workbook, the program file creates a distribution table of experimental variety area numbers (variety source table) through manual interactive information entry, and imports the source code from the program file into the newly created worksheet event code. This worksheet event code includes: 1. Function code for creating the other 9 sub-worksheets; 2. Source code for the other 9 sub-worksheet event programs; 3. Function code for automatically generating a table of variety codes and area number arrangement information.

[0010] S3: After the new summary workbook and the "Variety Source" worksheet are created and the event source code of all worksheets is transferred, the program file will automatically exit.

[0011] S4: The summary workbook, under the source code of the "Variety Source" worksheet, completes the creation of the other 9 sub-tables and the transmission of sub-table event codes through manual information interaction.

[0012] At this point, based on the requirements for regional corn trial surveys and data aggregation, the summary workbook and its 10 subsheets have been completed, and event handler code for automated aggregation has been added. The 10 worksheets are as follows:

[0013] Table Name: Summary – This table summarizes data from all sub-tables (printable report version)

[0014] Title: Cover - Report Cover

[0015] Table Title: Experimental Operation - Record of Basic Experimental Information and Field Operation Management

[0016] Table Title: Variety Origin - Experimental Plot Arrangement and Distribution Table

[0017] Table Title: Growth Period - Survey Form on Growth Period of Experimental Varieties

[0018] Table Title: Plant Height and Ear Position – Survey Table of Plant Height and Ear Position of Test Varieties

[0019] Table Title: Disease Resistance and Stress Resistance – Field Survey Table of Disease Resistance and Stress Resistance of Test Varieties

[0020] Table Title: Variety Testing – Indoor Variety Testing Survey of Ear Characteristics of 10 Sample Ears of Experimental Varieties

[0021] Table Title: Yield - Record of Harvest Yield of Triple-Repeated Field Plots for Experimental Varieties

[0022] Table Title: Variety Overview – Summary of Field Performance of Experimental Varieties

[0023] S5: Data Entry. After the field trial, all survey data are entered into the corresponding tables. Data entry utilizes a manual interactive information import mechanism and corresponding error-tolerant processing code control.

[0024] S6: The entered data, under the action of its respective event function code program, completes the automated calculation, summarization, and transmission of the data, while simultaneously standardizing the corresponding format processing.

[0025] S7: After all form data is entered, a complete test report is automatically generated. Finally, save this file as a file with the extension ".xlxs" to disable the function of the event code.

[0026] This invention can not only improve the efficiency of data aggregation, but also reduce the error rate of data aggregation.

[0027] The 10 electronic forms include a "Cover" form, a "Trial Operation" form, a "Variety Source" form, a "Summary" form, a "Growth Period" form, a "Plant Height and Ear Position" form, a "Disease Resistance and Stress Resistance" form, a "Variety Evaluation" form, a "Yield" form, and a "Variety Overview" form.

[0028] The report cover sheet includes the report title, test year, test group, test unit, test location, altitude, test executor, and contact number;

[0029] The experimental field operation management information form includes the experimental report title, basic information of the pilot unit (unit name, technical personnel, contact information, etc.), experimental plot design, experimental field selection, previous crop and production status, field fertilization and pesticide application, disease and pest control and other field operation management information, as well as records of climate conditions during the experiment and information such as problems and suggestions encountered during the experiment.

[0030] The variety source table includes the field plot number and the order of the experimental variety code. Based on the triple repetition of the experimental variety code, the plot number was identified accordingly, and all other survey information sub-tables were generated based on the plot variety code.

[0031] The header of the summary table includes all the target trait fields required for the test report, which are also the key fields for the headers of each sub-table.

[0032] The header of the growth period table includes: experimental variety code, sowing date, emergence date, tasseling date, leaf filament stage, maturity date, growth vigor, plant uniformity, ear position uniformity, plant type, purity, and growth period.

[0033] The header of the plant height and ear position table includes: plot number, target traits (plant height and ear position) arranged vertically, and the numbers of 10 sample plants and their average values ​​arranged horizontally;

[0034] The disease resistance and stress resistance table header includes plot number, test variety code, plant number, total number of plants, number of lodged plants, number of broken plants, number of empty stalks, number of double ears, as well as the number of plants with grade 9 sheath blight, number of plants with stem rot, number of plants with powdery mildew, and large leaf spot, small leaf spot, southern rust, gray leaf spot, white spot, ear rot, number of flowers, grains, and ears, and other diseases; the number of triple-repeated diseases and the disease level must be based on survey data;

[0035] The header of the variety test table includes the variety code, ear number, ear type, grain color, grain shape, cob color, cob diameter, ear length, tip barrenness length, number of rows per ear, number of grains per row, ear weight, single ear grain weight, shelling percentage, and weight per 100 grains.

[0036] The yield survey form includes the plot number, variety code, number of plants harvested, number of ears harvested, and grain weight of the plot in the header. It is then filled in horizontally in three duplicates, with the plot number arranged vertically in the field.

[0037] The variety review and evaluation form includes the experimental variety code, variety overview, and variety recommendations in its header. It provides a comprehensive evaluation of the variety's disease resistance, stress resistance, yield, quality traits, adaptability, and field performance.

[0038] The method for summarizing maize experimental field data using the VBA platform is characterized by: using the VBA platform to set the format of each electronic form, and the format of each electronic form is uniform; wherein, the format includes column width, row height, border, cell alignment, font, fill color, merged cells, number format, etc.

[0039] The method for summarizing maize experimental field data using the VBA platform is characterized by: using the VBA platform to automatically add underlines to the data information entered in the report cover and experimental field operation management record form through VBA code, and forming a WORD-style layout effect that can be directly printed.

[0040] The method for summarizing maize experimental field data using the VBA platform is characterized by: using VBA code to automatically address the header of the summary table through the header information of each sub-table and automatically filling the data into the corresponding table of the summary table.

[0041] The aforementioned method for summarizing maize experimental field data using the VBA platform is characterized by: using VBA code to implement data entry for each sub-table and automating data calculations. For example, in data processing, it involves finding the control (ck) value and performing complex calculations such as difference, summation, average, maximum, and minimum values.

[0042] The method for summarizing maize experimental field data using the VBA platform is characterized by further including error prediction and fault tolerance processing for the input content during manual interaction:

[0043] The error prediction includes providing input prompts and examples to help users fill in information correctly;

[0044] The error prediction includes checking the format, type, and range of user input data immediately;

[0045] The error prediction includes using drop-down menus, radio buttons, or checkboxes to restrict the user's input options;

[0046] The fault tolerance process includes providing clear error messages when a user enters incorrect data, guiding the user on how to correct it;

[0047] The fault tolerance process includes rapid refresh of calculated and summarized values ​​during data entry, so that the previous state can be restored in the event of an error.

[0048] The fault tolerance process includes allowing users to change individual data points, and then updating the calculated and summary values ​​accordingly to ensure the accuracy of the summary report data.

[0049] As can be seen from the above technical solution, compared with the prior art, the present invention discloses a method for summarizing maize experimental field data using the VBA platform. The present invention uses VBA code to uniformly and systematically create various summary tables. The summary calculation is automatically processed by VBA code after the data is entered. Even those who do not understand Excel functions and formulas can easily operate it. The present invention can not only improve the efficiency of data summarization, but also reduce the error rate of data summarization. Attached Figure Description

[0050] To more clearly illustrate the technical solutions in the embodiments of the present invention or the prior art, the drawings used in the description of the embodiments or the prior art will be briefly introduced below. Obviously, the drawings described below are only embodiments of the present invention. For those skilled in the art, other drawings can be obtained based on the provided drawings without creative effort.

[0051] Figure 1 A flowchart of a method for summarizing maize experimental field data using the VBA platform provided by the present invention;

[0052] Figure 2 This is a schematic diagram of the structure of an electronic device provided in an embodiment of the present invention. Detailed Implementation

[0053] The technical solutions of the embodiments of the present invention will be clearly and completely described below with reference to the accompanying drawings. Obviously, the described embodiments are only some embodiments of the present invention, and not all embodiments. Based on the embodiments of the present invention, all other embodiments obtained by those skilled in the art without creative effort are within the scope of protection of the present invention.

[0054] like Figure 1 As shown, this embodiment of the invention discloses a method for summarizing maize experimental field data using the VBA platform. The following steps are implemented using the VBA language:

[0055] S01: Double-click to open the program file. A prompt message box will pop up. Enter the name of the test summary report in the message box, such as "XX Pilot Spring Sowing Group A". After clicking the "OK" button on the message box, an Excel file named "XX Pilot Spring Sowing Group A" will be created in the background, and a "Variety Source" table will be created and active in the window.

[0056] S02: Based on the prompts on the right side of the "Variety Source" table window, follow the instructions in the "Friendly Reminder" to complete the entry of the maize experimental variety code and its field arrangement order. After entry is complete, the program will automatically create the other 9 tasks and load the program source code. The program will automatically exit after the task is completed.

[0057] S03: Filling out the "Cover" form. Enter all information; data will be automatically underlined. Format and generate the printable layout.

[0058] S04: Filling in the "Experimental Operation" form; data is automatically underlined. Format and generate a printable layout.

[0059] S05: The "Summary" table. Upon first activation, a message box will automatically pop up indicating that data cannot be entered into this table. After clicking "OK," another input box will appear to capture user pilot information. For example, entering "Wuhan" and clicking "OK" will fill in the required pilot information in column A of the "Summary" table. No further manual operation is required for this table; data entered into the sub-tables will be automatically summarized into the corresponding cells.

[0060] S06: The "Growth Period Table" is used to input data on the growth periods of experimental varieties recorded in field surveys. Upon first activation, a dialog box will pop up asking for the sowing date of this group of experiments. Since the sowing date for the same group of experiments is the same date, an interactive input method is used, allowing for one-time input that automatically imports the sowing dates for all varieties, eliminating the need for multiple entries. Other growth periods are entered manually based on the actual recorded data. When reporting the maturity date of a maize variety, the program automatically calculates the growth period and fills it into the "Growth Period" column, eliminating the need for manual calculation and input. When the emergence and maturity dates of a variety change, the growth period value will be calculated accordingly to ensure accuracy. When reporting agronomic traits related to the seedling stage of a maize variety, a data verification drop-down fill function is added to the table column; users can simply click to fill in the corresponding attribute value. All data entered in this table will be automatically synchronized to the "Summary" table, requiring no manual operation.

[0061] S07: Filling in the "Plant Height and Ear Position" table: Enter the plant height and ear position values ​​of 10 sample plants from the field survey according to the variety code. After the 10th data value is entered, the average plant height and ear position value of this variety will be automatically calculated in the last average column, and the data will be automatically synchronized to the corresponding table in the "Summary" table.

[0062] S08: Filling out the "Disease Resistance and Stress Resistance" table. This table requires data entry according to the field plot sequence number, recording three duplicate values ​​to ensure consistency with the field record sequence number, reducing the need for data verification. This table also includes an automatic summary calculation table; as data is entered, the summary value of the three duplicates is calculated simultaneously and automatically filled into the corresponding values ​​for each variety. For example, when calculating the number of lodged plants, the average of the three duplicates for the lodging rate is automatically calculated and filled; for large leaf spot and small leaf spot, the maximum value of the three duplicates is automatically calculated and filled into the corresponding variety code attribute. This table includes a data verification function where applicable. Simultaneously, this table automatically fills in the required data values ​​for the "Summary" table, reducing manual operation.

[0063] S09: Filling in the “Examination Species” table. As the survey values ​​of 10 varietal sample ears are entered, this table will first calculate and summarize the average values ​​for each varietal based on its trait attributes in the varietal average column; the relevant attributes will then be verified using data; the average values ​​will be simultaneously filled into the corresponding varietal attribute columns at the bottom of this table; the trait attribute values ​​required for the “Summary” table will be filled in synchronously; and if data is entered incorrectly and a correction is made, the average calculated values ​​will also be updated synchronously.

[0064] S10: Filling in the "Yield" table. Data such as "Actual Plants Harvested" and "Actual Ears Harvested" in this table are automatically filled in synchronously when data is entered in other sub-tables. Users only need to fill in the weight of ears of the plot arranged in the field, and the program will automatically calculate the corresponding grain yield data of the plot. While this table is being filled in and calculated, the corresponding data will also be automatically filled in synchronously in the "Summary" table.

[0065] S11: The "Variety Overview" form will automatically transmit the entered content to the corresponding table in the "Summary" form.

[0066] After completing the entry of all the above sub-tables, all tables in the "Summary" table will be filled out synchronously, thus forming a complete printable test report. Users can print the report with the required content as needed.

[0067] The various embodiments in this specification are described in a progressive manner, with each embodiment focusing on its differences from other embodiments. Similar or identical parts between embodiments can be referred to interchangeably. For the apparatus disclosed in the embodiments, since they correspond to the methods disclosed in the embodiments, the description is relatively simple; relevant parts can be referred to the method section.

[0068] The above description of the disclosed embodiments enables those skilled in the art to make or use the invention. Various modifications to these embodiments will be readily apparent to those skilled in the art, and the general principles defined herein may be implemented in other embodiments without departing from the spirit or scope of the invention. Therefore, the invention is not to be limited to the embodiments shown herein, but is to be accorded the widest scope consistent with the principles and novel features disclosed herein.

Claims

1. A method for summarizing maize experimental field data using the VBA platform, characterized in that, Achieve the following effect using VBA: E1. Report Cover Table: The table is filled in to form a Word-style printout of the table, with data information automatically underlined and formatted. E2. Experimental field operation and management record form; generated as a tabular Word-style printable page; automatic underlining of data information, formatting and beautification, etc. E3. Experimental plot distribution table (variety source table); Based on the input of the variety plot arrangement order of the pilot project, an automatic table of correspondence between variety code and plot number information is generated; E4. Fertility Period Survey Form; Based on the entered survey data, the system automatically calculates and processes the data, and automatically transmits the processed test data to the summary report (printed version); E5. Plant Height and Ear Position Survey Form; Based on the survey data, the system automatically calculates and summarizes the plant height and ear position information of 10 sample corn varieties, calculates the average value, and automatically transmits the processed test data to the summary report (printed version). E6. Field Survey Form for Disease Resistance and Stress Resistance: According to the experimental requirements, the disease resistance and stress resistance data of maize varieties are automatically calculated, summarized and processed. This form is divided into three replicates. The treatment of each trait can be to find the maximum value or average value of the three replicates among varieties, or to directly process them. The data is automatically calculated and transmitted to a summary report (printed version); E7. Indoor Survey Form for Ear Traits; This form calculates the trait values ​​of 10 ear samples for each variety; It automatically processes and transmits various calculations involving average values ​​between varieties to a summary report (printed version); E8. Field plot harvest yield record form; The yield information is recorded according to the order of field plots. After the data is entered, the yield value of triple-repeat for each variety is automatically calculated and summarized, and then transmitted to the summary report (printed version). E9. Overview of Variety Field Performance; After the data (briefly describing the characteristics of the variety according to the test requirements) is entered, it will be automatically transmitted to the summary report (printed version); E10, Summary Table of Test Data; This table does not require manual entry; the VBA code calculates and summarizes the information from each sub-table here.

2. The method for summarizing maize experimental field data using a VBA platform according to claim 1, characterized in that: Create an Excel summary workbook containing templates for the 10 electronic forms, including a variety plot distribution table (variety source), a report cover table, a field trial operation information table, a summary statistics table (printed version), a growth period table, a plant height and ear position table, a disease resistance and stress resistance table, an indoor variety test table, a yield survey table, and a variety overview and evaluation table.

3. The method for summarizing maize experimental field data using a VBA platform according to claim 2, characterized in that: The report cover sheet includes the report title, test year, test group, test unit, test location, altitude, test executor, and contact number; The experimental field operation management information form includes the experimental report title, basic information of the pilot unit (unit name, technical personnel, contact information, etc.), experimental plot design, experimental field selection, previous crop and production status, field fertilization and pesticide application, disease and pest control and other field operation management information, as well as records of climate conditions during the experiment and information such as problems and suggestions encountered during the experiment. The variety source table includes the field plot number and the order of the experimental variety code. Based on the triple repetition of the experimental variety code, the plot number was identified accordingly, and all other survey information sub-tables were generated based on the plot variety code. The header of the summary table includes all target trait fields required for the test report, which are contained in the keyword fields of the header of each sub-table; The header of the growth period table includes: experimental variety code, sowing date, emergence date, tasseling date, leaf filament stage, maturity date, growth vigor, plant uniformity, ear position uniformity, plant type, purity, and growth period. The header of the plant height and ear position table includes: plot number, target traits (plant height and ear position) arranged vertically, and the numbers of 10 sample plants and their average values ​​arranged horizontally; The disease resistance and stress resistance table header includes plot number, test variety code, plant number, total number of plants, number of lodged plants, number of broken plants, number of empty stalks, number of double ears, as well as the number of plants with grade 9 sheath blight, number of plants with stem rot, number of plants with powdery mildew, and large leaf spot, small leaf spot, southern rust, gray leaf spot, white spot, ear rot, number of flowers, grains, and ears, and other diseases; the number of triple-repeated diseases and the disease level must be based on survey data; The header of the variety test table includes the variety code, ear number, ear type, grain color, grain shape, cob color, cob diameter, ear length, tip barrenness length, number of rows per ear, number of grains per row, ear weight, single ear grain weight, shelling percentage, and weight per 100 grains. The header of the yield table includes the plot number, variety code, number of plants actually harvested, number of ears actually harvested, and grain weight of the plot, which are then filled in horizontally in three repetitions, with the plot numbers arranged vertically in the field. The header of the variety overview table includes the experimental variety code, variety overview, and variety recommendations; it mainly provides a comprehensive evaluation of the variety's disease resistance, stress resistance, yield, quality traits, adaptability, and field performance.

4. The method for summarizing maize experimental field data using a VBA platform according to claim 1, characterized in that: Use the VBA platform to set the format of each electronic form, ensuring that the format of each electronic form is consistent. The format includes column width, row height, borders, cell alignment, font, fill color, merged cells, number format, etc.

5. The method for summarizing maize experimental field data using a VBA platform according to claim 1, characterized in that: Using the VBA platform, the data entered into the report cover and experimental field operation management record form is automatically underlined using VBA code, and a WORD-style layout that can be printed directly is generated.

6. The method for summarizing maize experimental field data using a VBA platform according to claim 1, characterized in that: Using VBA code, data entry from each sub-table is automatically directed to the header of the summary table based on the table header information, and the data is automatically filled into the corresponding table in the summary table.

7. The method for summarizing maize experimental field data using a VBA platform according to claim 1, characterized in that: VBA code is used to implement data entry for each sub-table and to automate data calculations. For example, in data processing, the comparison (ck) value is searched and corresponding complex calculations such as difference, sum, average, maximum, and minimum are performed.

8. The method for summarizing maize experimental field data using a VBA platform according to claim 1, characterized in that, This also includes predicting and handling errors in the input during human interaction: The error prediction includes providing input prompts and examples to help users fill in information correctly; The error prediction includes checking the format, type, and range of user input data immediately; The error prediction includes using drop-down menus, radio buttons, or checkboxes to restrict the user's input options; The fault tolerance process includes providing clear error messages when a user enters incorrect data, guiding the user on how to correct it; The fault tolerance process includes rapid refresh of calculated and summarized values ​​during data entry, so that the previous state can be restored in the event of an error. The fault tolerance process includes allowing users to change individual data points, and then updating the calculated and summary values ​​accordingly to ensure the accuracy of the summary report data.

9. An electronic device comprising a memory, a processor, and a computer program stored in the memory and executable on the processor, characterized in that, When the processor executes the computer program, it implements the method for summarizing maize experimental field data using the VBA platform as described in any one of claims 1 to 8.

Images