BOM data change rapid comparison method based on VBA
By using a VBA-based BOM data comparison method, data paths are calculated hierarchically, and BOM changes are compared using an automatic traversal algorithm and dictionary objects. This solves the problems of low efficiency and poor accuracy in existing technologies, and achieves fast and accurate data comparison.
Patent Information
- Authority / Receiving Office
- CN · China
- Patent Type
- Applications(China)
- Current Assignee / Owner
- Filing Date
- 2025-11-04
- Publication Date
- 2026-03-31
AI Technical Summary
Existing methods for comparing changes in BOM data are inefficient, rely on manual operation, resulting in a large workload, low efficiency in identifying changes, and inability to guarantee accuracy.
Using a VBA-based approach, the system sets up old and new versions of the BOM (Bill of Materials) table, calculates data paths hierarchically, uses an automatic traversal algorithm to compare data changes, creates dictionary objects to speed up lookups, records changes, and cleans up redundant cells to reduce memory usage.
It enables rapid comparison of BOM data changes, reduces labor costs, improves comparison efficiency and accuracy, and reduces the error rate.
Smart Images
Figure CN121764928A_ABST
Abstract
Description
Technical Field
[0001] This invention belongs to the field of aircraft digital manufacturing technology, and relates to a data management method with BOM as the core in the aircraft production process, specifically a method for rapid comparison of BOM data changes based on VBA. Background Technology
[0002] In aircraft manufacturing, the Bill of Materials (BOM) is crucial for managing product manufacturing process data, listing all necessary parts and components. The EBOM (Engineering Bill of Materials) forms the basis for product design and manufacturing, while the MBOM (Manufacturing Bill of Materials) lists the components required for actual manufacturing, including all production requirements, and is typically used in the later stages of the product lifecycle. The quality and efficiency of BOM management directly determine the profitability of an enterprise. Identifying data changes between different BOM versions is a critical factor affecting BOM management efficiency and the effective transmission and implementation of data. Currently, despite the development of digital product data management, the following issues still exist in comparing BOM data changes: 1. Traditional PDM systems cannot directly process and compare data changes between different BOM versions.
[0003] 2. Manually comparing changes in BOM data using Excel spreadsheets is labor-intensive and inefficient in identifying changes, making the actual comparison process quite difficult.
[0004] 3. Relying on manual comparison of large amounts of data using Excel results in low verification efficiency and the accuracy of data comparison cannot be effectively guaranteed. Summary of the Invention
[0005] To address the aforementioned issues, this invention provides a VBA-based method for rapid comparison of BOM data changes. This method can identify changes in BOM form data, offering high operability, efficiency, and a low error rate.
[0006] The technical solution of the present invention is as follows: A VBA-based method for rapid comparison of BOM data changes includes the following steps: S1, set the BOM before the data change to the old version BOM, and the BOM after the data change to the new version BOM, and select the comparison data path between the old version BOM and the new version BOM. S2 calculates the complete BOM path for each row of data in the tables of the new and old versions of BOM by level. S3: For the complete BOM path, compare the data row by row with the data in the old BOM table to determine the change type. Finally, list the original sequence number of the new BOM table in the old BOM table, and save the new BOM table separately. S4, modify the information in the new version of the BOM obtained in S3 according to the change type and actual changes; S5 compares the tables of the old version BOM and the new version BOM to obtain the valid data range, and creates a dictionary using the serial number column of the old version BOM to speed up the search. It also compares the structure data part of the table, records the changes, and marks the cells. S6 moves the missing serial number to a separate deleted section worksheet and restores the initial time record running time.
[0007] Furthermore, step S2 specifically involves calculating the complete BOM path for each row of data in the EBOM and old BOM tables according to their levels: if the level is "1", the complete path is "part number & parent number"; if the level is "2", the complete path is "part number & complete BOM path of the previous level 1"; if the level is "3", the complete path is "part number & complete BOM path of the previous level 2"; and so on for subsequent levels.
[0008] Furthermore, in S3, the automatic traversal algorithm API is first called, and then the information of that row in the EBOM and old BOM tables is traversed according to the selected full BOM path to obtain the new BOM table and put it into a temporary storage table, and a change type column is generated.
[0009] Furthermore, in S3, the BOM path in the old BOM table is compared with that in the new BOM table. If they are consistent, the design version is compared. If the versions are different, the change type is marked as "upgrade". If they are inconsistent, the BOM path part number is used to determine whether it is a part number change according to the part number change rules. Compared with the part number before the change, only the last digit of the changed part number is modified, and the last digit is replaced from 1 to 9 respectively. Finally, the position number of the new BOM table in the row path in the old BOM table is listed in the serial number column of the old BOM.
[0010] Furthermore, in S5, two dictionary objects are created and the table data of the new version of BOM is traversed; the table data of the old version of BOM is traversed, and the value of the first column is used as the key and the row number as the value, and stored in the old array; the table data of the old version of BOM is traversed to check whether the value of the first column of each row exists in the new array. If it does not exist, the data of that row is written to the deleted part of the worksheet; the data of the new table is traversed, and matching rows are found in the old table. The data is compared column by column. If a difference is found, the column header of the difference is recorded in the array; if the key of a row does not exist in the old version of the dictionary, it is marked as a new row; finally, the data in the array is written to the last column "Changes" in the new table, and the cells with differences are marked as difference cells.
[0011] Furthermore, the specific method for creating two dictionary objects and iterating through the table data of the new BOM is as follows: Create two dictionary objects, dicNewData and dicOldData. Iterate through the data in the new table, using the value of the old serial number column as the key and the row number as the value, and store it in dicNewData. Iterate through the data in the old table, using the value of the first column as the key and the row number as the value, and store it in dicOldData.
[0012] Furthermore, the method of traversing the data in the new table, finding matching rows in the old table, and comparing the data column by column is as follows: if a difference is found, the column header of the difference is recorded in the array diffArray; if the key of a row does not exist in the dictionary of the old version, it is marked as a new row; finally, the data in the array diffArray is written into the last column "Changes" in the new table, and the cells with differences are marked as difference cells.
[0013] Furthermore, in S6, the missing serial number portion is moved to a separate deletion portion worksheet. Finally, redundant content is deleted to reduce memory usage, and the initial time is restored to record the running time. Specifically, CleanExcessCells is called to clean up redundant cells in the old and new tables, release dictionary objects to reduce memory usage, and record the running time on the calculation page.
[0014] The beneficial effects of this invention are as follows: This invention enables rapid comparison of BOM data changes based on VBA, reducing manual costs, greatly improving the efficiency of data comparison, and reducing the error rate in the data comparison process. Attached Figure Description
[0015] To more clearly illustrate the technical solutions of the embodiments of this invention, the drawings used in the embodiments will be briefly introduced below. It should be understood that the following drawings only show some embodiments of this invention and should not be regarded as a limitation of the scope. For those skilled in the art, other related drawings can be obtained from these drawings without creative effort.
[0016] Figure 1 This is a complete flowchart of the VBA-based method for rapid comparison of BOM data changes described in this invention.
[0017] Figure 2 This is a schematic diagram illustrating the direct comparison of changes between the old and new versions of the table in an embodiment of the VBA-based BOM data change rapid comparison method described in this invention. Detailed Implementation
[0018] This section describes embodiments of the present invention, used to explain and illustrate the technical solutions of the present invention. Unless otherwise specified, the embodiments and features described herein can be combined with each other.
[0019] In the description of this invention, it should be understood that the terms "center," "longitudinal," "lateral," "upper," "lower," "front," "rear," "left," "right," "vertical," "horizontal," "top," "bottom," "inner," and "outer," etc., indicating directions or positional relationships, are given in the accompanying drawings and are used only for the convenience of describing the invention and simplifying the description, and do not indicate or imply that the device or device referred to must have a specific orientation, or be constructed and operated in a specific orientation, and therefore should not be construed as a limitation of the invention. Furthermore, the terms "first," "second," etc., are used for descriptive purposes only and should not be construed as indicating or implying relative importance or implying the number of indicated technical features. Thus, features defined with "first," "second," etc., may explicitly or implicitly include more than one of those features. In the description of this invention, unless otherwise stated, "a plurality of" means two or more.
[0020] In the description of this invention, it should be noted that, unless otherwise explicitly specified and limited, the terms "installation," "connection," and "linking" should be interpreted broadly. For example, they can refer to fixed connections, detachable connections, or integrated connections; they can refer to mechanical connections or point connections; they can refer to direct connections or indirect connections through an intermediate medium; and they can refer to the internal communication between two components. Those skilled in the art can understand the specific meaning of the above terms in this invention based on the specific circumstances.
[0021] Example 1: A VBA-based method for rapid comparison of BOM data changes includes the following steps: S1, set the BOM before the data change to the old version BOM, and the BOM after the data change to the new version BOM, and select the comparison data path between the old version BOM and the new version BOM. S2 calculates the complete BOM path for each row of data in the tables of the new and old versions of BOM by level. S3: For the complete BOM path, compare the data row by row with the data in the old BOM table to determine the change type. Finally, list the original sequence number of the new BOM table in the old BOM table, and save the new BOM table separately. S4, modify the information in the new version of the BOM obtained in S3 according to the change type and actual changes; S5 compares the tables of the old version BOM and the new version BOM to obtain the valid data range, and creates a dictionary using the serial number column of the old version BOM to speed up the search. It also compares the structure data part of the table, records the changes, and marks the cells. S6 moves the missing serial number to a separate worksheet for the deleted section and restores the initial time record running time. Step S2 is as follows: Calculate the complete BOM path for each row of data in the EBOM and old BOM tables according to the hierarchy: if the hierarchy is "1", the complete path is "part number & parent number"; if the hierarchy is "2", the complete path is "part number & complete BOM path of the previous level 1"; if the hierarchy is "3", the complete path is "part number & complete BOM path of the previous level 2"; and so on for subsequent levels.
[0022] In S3, the automatic traversal algorithm API is first called. Then, based on the selected complete BOM path, the information of that row in the EBOM and old BOM tables is traversed to obtain the table of the new BOM and put it into a temporary storage table, and a change type column is generated.
[0023] In S3, compare the data BOM path in the old BOM table with that in the new BOM table. If they match, compare the design version. If the versions are different, mark the change type as "upgrade". If they do not match, determine whether it is a change according to the BOM path part number and the numbering rules. Compared with the part number before the change, only modify the last digit of the changed part number, and replace it according to the last digit from 1 to 9. Finally, list the position number of the new BOM table in the row path in the old BOM table in the serial number column of the old BOM.
[0024] In S5, two dictionary objects are created and the table data of the new version of BOM is traversed. The table data of the old version of BOM is traversed, and the value of the first column is used as the key and the row number as the value, and stored in the old array. The table data of the old version of BOM is traversed again, and the value of the first column of each row is checked to see if it exists in the new array. If it does not exist, the data of that row is written to the deleted part of the worksheet. The data of the new table is traversed, and the matching rows are searched in the old table. The data is compared column by column. If a difference is found, the column header of the difference is recorded in the array. If the key of a row does not exist in the old version of the dictionary, it is marked as a new row. Finally, the data in the array is written to the last column "Changes" in the new table, and the cells with differences are marked as difference cells.
[0025] The specific method for creating two dictionary objects and iterating through the table data of the new BOM is as follows: Create two dictionary objects, dicNewData and dicOldData. Iterate through the data in the new table, using the value of the old serial number column as the key and the row number as the value, and store it in dicNewData. Iterate through the data in the old table, using the value of the first column as the key and the row number as the value, and store it in dicOldData.
[0026] The method for traversing the data in the new table, finding matching rows in the old table, and comparing the data column by column is as follows: if a difference is found, the column header of the difference is recorded in the array diffArray; if the key of a row does not exist in the dictionary of the old version, it is marked as a new row; finally, the data in the array diffArray is written into the last column "Changes" in the new table, and the cells with differences are marked as difference cells.
[0027] In S6, the missing serial numbers are moved to a separate worksheet for deletion. Finally, redundant content is deleted to reduce memory usage, and the initial time is restored to record the running time. Specifically, CleanExcessCells is called to clean up redundant cells in the old and new tables, release dictionary objects to reduce memory usage, and record the running time on the calculation page.
[0028] In step S1, the constructed storage table header includes the original header of the EBOM table, and additionally adds change type and old sequence number columns.
[0029] Example 2: like Figure 1 As shown, the VBA-based method for rapid comparison of BOM data changes described in this invention includes the following steps: Step S100: Design the storage table, select the EBOM and old BOM data paths; the constructed storage table header includes the original header in the EBOM table, keeping it consistent with the old difference table header, and adds a manufacturing configuration record column: including the installation drawing number, installation drawing version, process document number, airborne equipment serial number, remarks and difference description columns, and also adds change type and old serial number columns.
[0030] Step S200: Open the configuration specification list according to the EBOM and old BOM file paths and filter to obtain the required data. Add a new column to obtain the complete BOM path according to the hierarchy. Obtain the required data from the old table according to the path in the EBOM and put it into the temporary storage table. Step S300: Compare the temporarily stored EBOM data with the EBOM portion of the old table to determine the change type. Specifically, this can be done as follows: First, call the automatic traversal algorithm API. Then, based on the selected complete BOM path, traverse the EBOM and the corresponding row information in the old table to obtain the new table, which is then stored in a temporary table, and a change type column is generated. Compare whether the BOM path in the old table is consistent with that in the new difference table. If they are consistent, compare the design version. If the versions are different, mark the change type as "upgrade". If they are inconsistent, replace the BOM path part number by the last digit from 1 to 9. Similarly, if they are consistent, mark it as "number change"; if they are inconsistent, mark it as "new addition". Finally, list the position number of the new table's row path in the old table in the old table in the old sequence number column to mark the change type and the old sequence number.
[0031] Step S400: Modify the corresponding BOM data according to the change type and manufacturing configuration information; Step S500: Compare the new data difference table with the old data difference table, using the old serial numbers as the basis for comparing each row and column of data. Deleted data is placed in a separate worksheet; any changes are highlighted in yellow and the header name of the changed item is marked. Specifically, this can be done by: creating two dictionary objects, dicNewData and dicOldData; iterating through the new table data, using the value of column 26 ("old serial number" column) as the key and the row number as the value, and storing it in dicNewData; iterating through the old table data, using the value of column 1 as the key and the row number as the value, and storing it in dicOldData; iterating through the old table data... Check if the value in the first column of each row exists in dicNewData. If not, store the row data in the delData array and write it to the deleted part of the worksheet. Traverse the data in the new table, find matching rows in the old table, and compare the data column by column. If a difference is found, record the column header of the difference in the array diffArray. If the key of a row does not exist in the old version of the dictionary, mark it as a new row. Finally, write the data in the diffArray array into the last column "Changes" in the new table, and mark the cells with differences as difference cells and set the background color to yellow.
[0032] Step S600: Move the missing serial number to a separate worksheet for the deleted part, and finally delete the redundant content to reduce memory usage, and restore the initial time record to record the running time; specifically, call CleanExcessCells to clean up the redundant cells in the old and new tables, release the dictionary object to reduce memory usage, and record the running time on the calculation page.
[0033] Example 3: By employing the above method, this invention enables rapid comparison of BOM data changes based on VBA, reduces the cost of manual comparison, greatly improves the efficiency of data comparison, and increases the accuracy of the data comparison process.
[0034] The following is an example Figure 2 The illustrated embodiment further illustrates the VBA-based method for rapid comparison of BOM data changes described in this invention by directly comparing new and old versions of tables.
[0035] Step S100: Obtain the new difference comparison data table: Add the columns required for the complete process to the old table, keep the header consistent, sort the old serial number column, and then modify the old table directly according to the changes in the physical configuration. Save the new BOM data table. Step S200: Select the data table path for the new version of BOM and the old version of BOM: Select the new and old BOM table data based on the file location path and check if the path is empty. If the check is empty, return to the previous path selection step with an error message. Step S300: Click "Identify and Calculate Differences" to start the program: Check if the path is empty. If it is empty, report an error. If it is not empty, continue running. Clean up the old data in the "Old Sheet" and "New Sheet" worksheets and initialize the "Deleted Part" worksheet. Open the selected old and new sheet files respectively, read their data range (A1 to the last non-empty cell) and copy them to the "Old Sheet" and "New Sheet" sheets respectively. Step S400: Verify data integrity and process deleted portions: Two dictionary objects, dicNewData and dicOldData, are created. The data in the new table is traversed, and the value of column 26 ("Old Sequence Number") is used as the key, with the row number as the value, and stored in dicNewData. The data in the old table is then traversed, and the value of column 1 is used as the key, with the row number as the value, and stored in dicOldData. The data in the old table is then traversed again, and the value of column 1 for each row is checked to see if it exists in dicNewData. If it does not exist, the row data is stored in the delData array and written to the deleted portion worksheet. This is done to verify the integrity of the BOM (Bill of Materials) and to include the deleted portion in a new table for comparison. Step S500: Compare the data in the new table and the old table, and mark the cells with differences: Iterate through the data in the new table, find matching rows in the old table, and compare the data column by column. If a difference is found, record the column header of the difference into the array diffArray. If the key of a row does not exist in the old version of the dictionary, mark it as a new row. Finally, write the data in the array diffArray into the last column "Changes" in the new table, and mark the cells with differences as difference cells and set the background color to yellow. Step S600: Clean up redundant cells and record the execution time: The CleanExcessCells function cleans up redundant cells in both the old and new tables, releases dictionary objects to reduce memory usage, and records the runtime on the calculation page.
[0036] Step S700: Comment on the changes: The last column of the new difference table lists the names of the column headers corresponding to the highlighted items in the manufacturing configuration record section.
[0037] The resulting "new sheet" is the new BOM difference table, marked in yellow with the changes highlighted. A dedicated information panel showing the differences identified between the old and new sheets enhances visualization.
[0038] The above-disclosed embodiments are merely preferred embodiments of the present invention and should not be construed as limiting the scope of the invention. Therefore, any equivalent variations made in accordance with the claims of the present invention shall still fall within the scope of the invention.
Claims
1. A method for rapid comparison of BOM data changes based on VBA, characterized in that, Includes the following steps: S1, set the BOM before the data change to the old version BOM, and the BOM after the data change to the new version BOM, and select the comparison data path between the old version BOM and the new version BOM. S2 calculates the complete BOM path for each row of data in the tables of the new and old versions of BOM by level. S3: For the complete BOM path, compare the data row by row with the data in the old BOM table to determine the change type. Finally, list the original sequence number of the new BOM table in the old BOM table, and save the new BOM table separately. S4, modify the information in the new version of the BOM obtained in S3 according to the change type and actual changes; S5 compares the tables of the old version BOM and the new version BOM to obtain the valid data range, and creates a dictionary using the serial number column of the old version BOM to speed up the search. It also compares the structure data part of the table, records the changes, and marks the cells. S6 moves the missing serial number to a separate deleted section worksheet and restores the initial time record during runtime.
2. The method for rapid comparison of BOM data changes based on VBA according to claim 1, characterized in that, S2 specifically involves calculating the complete BOM path for each row of data in the EBOM and old BOM tables according to their hierarchy: if the hierarchy is "1", the complete path is "part number & parent number"; if the hierarchy is "2", the complete path is "part number & complete BOM path of the parent level 1"; if the hierarchy is "3", the complete path is "part number & complete BOM path of the parent level 2"; and so on for subsequent levels.
3. The method for rapid comparison of BOM data changes based on VBA according to claim 1, characterized in that, In S3, the automatic traversal algorithm API is first called. Then, based on the selected complete BOM path, the information of that row in the EBOM and old BOM tables is traversed to obtain the table of the new BOM and put it into a temporary storage table, and a change type column is generated.
4. The method for rapid comparison of BOM data changes based on VBA according to claim 3, characterized in that, In S3, compare the data BOM path in the old BOM table with that in the new BOM table. If they match, compare the design version. If the versions are different, mark the change type as "upgrade". If they do not match, determine whether it is a change according to the BOM path part number and the numbering rules. Compared with the part number before the change, only modify the last digit of the changed part number, and replace it according to the last digit from 1 to 9. Finally, list the position number of the new BOM table in the row path in the old BOM table in the serial number column of the old BOM.
5. The method for rapid comparison of BOM data changes based on VBA according to claim 1, characterized in that, In S5, create two dictionary objects and iterate through the table data of the new version of BOM; Iterate through the table data in the old BOM, using the value of the first column as the key and the row number as the value, and store it in the old array; The process iterates through the old BOM table data, checking if the value in the first column of each row exists in the new array. If not, the row data is written to the deleted section worksheet. The process then iterates through the new table data, searching for matching rows in the old table and comparing the data column by column. If differences are found, the column headers of the differences are recorded in the array. If a row's key does not exist in the old version's dictionary, it is marked as a new row. Finally, the data in the array is written to the last column "Changes" in the new table, with differing cells marked as "Difference Cells".
6. The method for rapid comparison of BOM data changes based on VBA according to claim 5, characterized in that, The specific method for creating two dictionary objects and iterating through the table data of the new BOM is as follows: Create two dictionary objects, dicNewData and dicOldData. Iterate through the data in the new table, using the value of the old serial number column as the key and the row number as the value, and store it in dicNewData. Iterate through the data in the old table, using the value of the first column as the key and the row number as the value, and store it in dicOldData.
7. A method for rapid comparison of BOM data changes based on VBA according to claim 5, characterized in that, The method for traversing the data in the new table, finding matching rows in the old table, and comparing the data column by column is as follows: if a difference is found, the column header of the difference is recorded in the array diffArray; if the key of a row does not exist in the dictionary of the old version, it is marked as a new row; finally, the data in the array diffArray is written into the last column "Changes" in the new table, and the cells with differences are marked as difference cells.
8. The method for rapid comparison of BOM data changes based on VBA according to claim 1, characterized in that, In S6, the missing serial numbers are moved to a separate worksheet for deletion. Finally, redundant content is deleted to reduce memory usage, and the initial time is restored to record the running time. Specifically, CleanExcessCells is called to clean up redundant cells in the old and new tables, release dictionary objects to reduce memory usage, and record the running time on the calculation page.