An electrical engineering design auxiliary calculation and material statistics system and method
The electrical engineering design auxiliary calculation and material statistics system based on VBA and Excel solves the problems of cumbersome calculations, scattered material statistics, and inconsistent output in electrical engineering design. It achieves efficient and accurate material statistics and standardized output, reduces costs, and improves design efficiency.
Patent Information
- Application Number
- CN202610623669.9
- Authority / Receiving Office
- CN · China
- Patent Type
- Applications(China)
- Current Assignee / Owner
- Filing Date
- 2026-05-08
- Publication Date
- 2026-08-25
AI Technical Summary
In the design of electrical engineering for industrial and civil buildings, existing technologies suffer from problems such as cumbersome calculations that are prone to errors, scattered and easily overlooked material statistics, inconsistent design outputs, low efficiency, and poor connection with upstream data. Furthermore, existing software is expensive and difficult to automate and standardize.
An electrical engineering design auxiliary calculation and material statistics system based on VBA and Excel is adopted. Through the core data input and control module, professional calculation module, core basic database and result summary output module, it realizes automated calculation and standardized output, and uses VBA macro program and Excel data verification function for data verification and processing.
Significantly improves design efficiency and accuracy, reduces costs, enables one-click material statistics, increases efficiency by over 80%, ensures calculation accuracy and output standardization, and supports rapid response to design needs.
Smart Images
Figure CN122634835A_ABST
Abstract
Description
Technical Field
[0001] This invention belongs to the field of computer-aided construction drawing design technology for electrical engineering, and relates to an auxiliary calculation and material statistics system and method for electrical engineering design. Background Technology
[0002] In the design process of electrical engineering for industrial and civil buildings, a significant amount of work, including quantity calculations and material statistics, is required in the later stages of construction drawing design to generate equipment and material lists and project cost estimates. This process has traditionally relied heavily on manual labor, resulting in the following significant drawbacks: 1. The calculation process is tedious and error-prone: The calculations involve multiple professional sub-items such as cable trays, cable trenches, lighting illuminance, and lightning protection grounding. Each sub-item requires manual calculation using complex formulas from the design manual. For example, the cable tray system requires calculating the length and weight of more than ten materials, including the cable tray body, columns, brackets, and connectors, based on parameters such as route length, specifications, and support spacing. The calculation chain is long and the workload is large, making it extremely easy for errors to occur due to human negligence.
[0003] 2. Material statistics are scattered and prone to omission: Material information is scattered across different parts of dozens or even hundreds of drawings. Designers need to manually identify, count, and summarize various steel materials, cables, cabinets, etc. from the drawings, which is time-consuming and prone to omissions or duplicate statistics, directly affecting the accuracy of project costs.
[0004] 3. Inconsistent design output standards: Different designers have different calculation habits and material statistics formats, resulting in inconsistent output file formats within the same design unit, which is not conducive to standardized management and subsequent procurement and construction processes.
[0005] 4. Low design efficiency: The process from completing the drawings to producing a complete bill of materials and cost estimate is lengthy, making it difficult to meet the rapid response requirements of modern engineering projects for design progress and cost control.
[0006] 5. Poor data integration with upstream suppliers: When the design of upstream professionals (such as equipment and process professionals) changes, the Excel files of equipment parameters they provide are updated. Downstream electrical engineering quantity calculations require manual searching and modification of relevant data, which is cumbersome and prone to omissions.
[0007] While some specialized electrical design software (such as EPLAN and CAD) exists, they focus on schematic design, component selection, and automatic wiring. Their capabilities in quickly generating detailed equipment lists that integrate installation materials and are budget-oriented are weak or complex, and software licensing fees are high, as are learning curves. When individuals use general-purpose spreadsheet software (such as Microsoft Excel) for auxiliary calculations, they typically only pile up simple formulas within the spreadsheets. These calculation sheets are independent of each other, lacking unified data management and automated linkage mechanisms, thus failing to fundamentally solve the aforementioned problems. Especially when design specifications or material standards are updated, the formulas and parameters scattered across various locations are difficult to maintain uniformly.
[0008] Therefore, there is an urgent need for a high-efficiency auxiliary tool that can integrate multi-disciplinary calculations in electrical design, realize automatic material statistics and standardized output, and is low in cost, easy to customize and maintain. Summary of the Invention
[0009] In view of this, the purpose of the present invention is to provide an electrical engineering design auxiliary calculation and material statistics system and method.
[0010] To achieve the above objectives, the present invention provides the following technical solution: An electrical engineering design auxiliary calculation and material statistics system, comprising: The core data input and overall control module is used to receive the initial design parameters input by the user and provide a navigation control driven by a macro program. The navigation control is used to trigger the macro program to enable navigation and jump to various professional calculation modules after calculation. Multiple specialized calculation modules, each corresponding to an independent worksheet and associated with at least one calculation macro program, the calculation macro program being configured to read input parameters and perform material calculations for different electrical sub-items; The core foundational database consists of multiple independent basic worksheets used to store structured, standard design data; The results summary output module is associated with a summary macro program, which is configured to automatically collect calculation result data from various professional calculation modules and generate a structured material estimate summary table. The calculation macro program accesses the core basic database during runtime to obtain the standard data required for calculation; the functions of the system are executed sequentially by the user triggering the navigation control, the calculation macro program, and the summary macro program.
[0011] Furthermore, the core data input and control interface provides a standardized drop-down selection list through Excel's data validation function, and performs validation and preprocessing of the input data through a Button_Click event procedure written in VBA.
[0012] Furthermore, the computational macro program uses the lookup functions or calculation methods provided by the macro programming language to query the core basic database to match and obtain standard data.
[0013] Furthermore, the specialized calculation modules include at least three of the following: cable routing calculation module, distribution cabinet configuration calculation module, sliding contact line installation calculation module, MCC cabinet layout module, lightning protection grounding calculation module, and lighting calculation module. The core calculation logic of each module is implemented by VBA functions or subroutines. During calculation, the system reads the routing parameters input by the user, accesses the basic database worksheet, uses loops, conditional statements, and queries to complete the calculation of all component materials, and fills the results into the specified output cell area.
[0014] Furthermore, the calculation macro program associated with the cable routing calculation module is configured to query the core basic database based on the input cable tray and cable trench specifications and routing length to obtain the material usage per unit length and specific specification codes, and calculate the required construction material usage for the cable tray body and cable trench according to the formula: Total material = Routing length × Material usage per unit length.
[0015] Furthermore, the summary macro program extracts data from the predetermined output areas of each professional calculation module and fills it into the material estimate summary table according to the preset material classification rules.
[0016] Furthermore, it also includes a template management macro program, which is used to save the current workbook as a template file without specific project data.
[0017] On the other hand, the present invention provides an auxiliary calculation and material statistics method for electrical engineering design, comprising the following steps: S1: The system receives project design data and global parameters input by the user through the core data input and control module; S2: The system responds to user input operations on the main control interface or specific professional calculation module interface, performs real-time validity verification on the input parameters; and responds to user operations on the navigation controls, jumps to the corresponding professional calculation module; or responds to calculation commands, triggers the calculation macro program of the professional calculation module; S3: The calculation macro program triggered by the current calculation item automatically parses and accesses the corresponding data table in the core basic database module according to the preset scheme matching rules, based on the input parameters of the current calculation item, and retrieves and matches the standard material scheme and related technical parameters of the electrical equipment for that item. S4: The calculation macro program calls the built-in material calculation algorithm, combines the input parameters with the standard data queried from the core basic database, traverses all relevant calculation items, automatically performs the calculation of material usage and weight, and writes the calculation results into the standardized output area of the professional calculation module. S5: The system responds to the summary command and starts the summary macro program; the summary macro program sequentially traverses the standardized output areas of each professional calculation module and extracts the calculation result data; according to the predefined material classification rules, the extracted data is transferred, classified and aggregated, and the total quantity and total weight of each type of material are calculated and generated. S6: The system will fill the aggregated data into a pre-formatted report template, generate a summary table of unit project estimates for equipment and installation materials that includes an overview of the project and a detailed list, and provide an export function.
[0018] The beneficial effects of this invention are as follows: Significantly improves design efficiency and speed: Through one-click operation and automated background calculations, the manual calculation, statistics, and tabulation work that originally took several days can be completed in less than an hour. Users no longer need to switch back and forth between different manuals, drawings, and tables. The system responds quickly, making it particularly suitable for preliminary design and budget estimation stages that require rapid response. It is estimated that in the electrical construction drawing design of typical industrial plants, the efficiency of the material statistics process can be improved by more than 80%.
[0019] High accuracy and standardization: All calculations are performed using pre-written and validated rules, VBA algorithms, and built-in formulas, eliminating errors such as input formula errors, table lookup errors, and summation errors that occur in manual calculations. A unified input interface and basic database ensure that different projects and designers follow the same calculation standards and output formats, resulting in stable and reliable output quality.
[0020] Low maintenance costs and flexible customization: This invention is based on the universal Microsoft Excel platform, eliminating the need to purchase expensive professional electrical design software licenses, significantly reducing usage costs. The core basic database is independent of the calculation logic, making updates and maintenance simple. The openness of VBA code allows users to quickly customize calculation rules, output formats, and extend functionality according to specific project needs or internal enterprise standards.
[0021] The system framework boasts high reusability: the overall architecture, module division, data flow design, and main VBA program framework of the system are universal. When applied to different engineering projects, only the input parameters and some basic data need to be adjusted according to the project characteristics. The core automated calculation and summarization logic of the system can be directly reused, realizing "develop once, reuse many times" and improving the return on investment of the tool.
[0022] Other advantages, objectives, and features of the invention will be set forth in part in the description which follows, and in part will be apparent to those skilled in the art from the following examination, or may be learned from practice of the invention. The objectives and other advantages of the invention can be realized and obtained through the following description. Attached Figure Description
[0023] To make the objectives, technical solutions, and advantages of the present invention clearer, the preferred embodiments of the present invention will be described in detail below with reference to the accompanying drawings, wherein: Figure 1 This is a schematic diagram of the module composition and data flow of the electrical construction drawing design auxiliary calculation and material statistics system in Example 1; Figure 2 This is a logic flowchart of the material-aided calculation of the electrical construction drawing system in Example 2; Figure 3 This is a flowchart illustrating the design example of the cable tray panel foundation in Example 3; Figure 4 This is a schematic diagram of the bill of materials calculated for the cable tray in Example 3; Figure 5 This is a schematic diagram of the reference results in Example 3; Figure 6 This is a schematic diagram of the standardized output area in Example 3; Figure 7 This is a schematic diagram of the output material lists for different sub-regions in Example 3; Figure 8 This is a schematic diagram summarizing the list of each type of material in Example 3; Figure 9 This is a schematic representation of the overall project cost estimate derived in Example 3. Detailed Implementation
[0024] The following specific examples illustrate the implementation of the present invention. Those skilled in the art can easily understand other advantages and effects of the present invention from the content disclosed in this specification. The present invention can also be implemented or applied through other different specific embodiments, and various details in this specification can be modified or changed based on different viewpoints and applications without departing from the spirit of the present invention. It should be noted that the illustrations provided in the following embodiments are only schematic representations of the basic concept of the present invention. Unless otherwise specified, the following embodiments and features can be combined with each other.
[0025] It should be noted that the illustrations provided in the following embodiments are only schematic representations of the basic concept of the present invention. Therefore, the drawings only show the components related to the present invention and are not drawn according to the actual number, shape and size of the components in the actual implementation. In the actual implementation, the form, quantity and proportion of each component can be arbitrarily changed, and the layout of the components may also be more complex.
[0026] In the following description, numerous details are explored to provide a more thorough explanation of embodiments of the invention. However, it will be apparent to those skilled in the art that embodiments of the invention may be practiced without these specific details. In other embodiments, well-known structures and devices are shown in block diagram form rather than in detail to avoid obscuring embodiments of the invention.
[0027] Example 1: This invention provides an auxiliary calculation and material statistics system for electrical engineering construction drawing design based on VBA and Excel, such as... Figure 1 As shown. The system uses a Microsoft Excel workbook as its platform and is implemented through programming using its built-in VBA (Visual Basic for Applications) environment. Its key feature is that the system organically couples VBA macros with structured worksheets, forming the following functional modules: Core Data Input and Central Control Module: Serving as the system's main control interface, this module provides a centralized area for initial parameter input. It utilizes Excel's "Data Validation" function to offer a standardized drop-down list and employs a VBA-written Button_Click event procedure to validate and preprocess the input data. The module also includes embedded VBA-driven graphical button controls; clicking different buttons triggers corresponding macro programs and enables quick navigation and jumps to various professional calculation worksheets after calculation.
[0028] Specialized Calculation Modules: Each specialized calculation module corresponds to an independently configured Excel worksheet and is associated with a specific set of VBA macro programs. These modules include a cable routing calculation module, a special MCC cabinet auxiliary calculation module, a sliding contact line installation calculation module, an electrical equipment layout and installation calculation module, a lightning protection and grounding calculation module, and a lighting calculation module. The core calculation logic of each module is implemented by a VBA function or subroutine. This procedure reads the routing parameters input by the user, accesses the basic database worksheet through VBA code, uses loops, conditional statements, and queries to complete the calculation of all component materials, and fills the results into the specified output cell range.
[0029] The core foundational database module consists of a series of protected Excel worksheets, storing structured standard data in tabular form. This includes: tables showing the unit format, material, specifications, and weight of various steel materials (such as structural steel) for cable routing; national standard cable specification tables; MCC auxiliary foundational data tables; basic data tables related to sliding contact lines; electrical room layout spacing specifications; an electrical room standard library; and a lighting foundational library. This module is separate from the calculation module; all calculation modules dynamically reference data from this module via VBA programs. When material standards or design specifications are updated, only the corresponding data tables need to be modified in this module to achieve a unified update of the system's calculation benchmark, greatly facilitating system maintenance.
[0030] The results summary output module, as a standalone Excel worksheet, is essentially a VBA macro triggered by the "Summary of Auxiliary List" button. This macro is configured to perform the following automated operations: iterate through predefined cell ranges labeled with specific names in all professional calculation worksheets; extract pure numerical results using the program; sort the extracted data according to the built-in material classification dictionary and fill it into the corresponding rows of the preliminary estimate table, automatically generating each category and total.
[0031] Example 2: This embodiment provides a method for auxiliary calculation of equipment and material statistics in electrical construction drawings, applied to the system described above, such as... Figure 2 As shown, the method includes the following steps: S1: Data Reception and Initialization. The system receives project design data and global parameters input by the user through its core data input and control module.
[0032] S2: Parameter Validation and Sub-item Calculation Trigger. The system responds to user input operations on the main control interface or specific professional calculation module interface, performs real-time validity validation on the key input parameters, and responds to user operations on the navigation controls, jumping to the corresponding professional calculation module, or responding to calculation commands, triggering the calculation macro program of that module.
[0033] S3: Scheme Matching and Data Query. The triggered calculation macro program automatically parses the input parameters of the current calculation item and, according to the preset scheme matching rules, accesses the corresponding data table in the core basic database module to retrieve and match the standard material scheme and related technical parameters of the electrical equipment for that item.
[0034] S4: Automated Calculation Traversal. The calculation macro program calls the built-in material calculation algorithm, combines the input parameters with standard data retrieved from the basic database, traverses all relevant calculation items, automatically performs the calculation of material usage and weight, and writes the calculation results to the standardized output area of this specialized calculation module.
[0035] S5: Multi-source result aggregation. In response to the aggregation command, the system starts an aggregation macro program; this aggregation macro program sequentially traverses the standardized output areas of each professional calculation module, extracts the calculation result data; according to predefined material classification rules, the extracted data is transferred, classified and aggregated, and the total quantity and total weight of each type of material are calculated.
[0036] S6: Standardized Report Generation. The system will populate the aggregated data into a pre-formatted report template, automatically generating a summary table of unit project costs for equipment and installation materials that includes a project overview and detailed list, and will also provide an export function.
[0037] Example 3: This embodiment provides a method for auxiliary calculation and material statistics in electrical construction drawings, taking the foundation design of cable tray panels as an example. Figure 3 As shown, the specific technical solution is as follows: S1: For the cable tray design in the electrical construction drawings of a converter project of a certain company, organize the basic design data of the cable tray into the data input and control module.
[0038] like Figure 4 As shown, the list of basic electrical equipment includes: cable tray height, width, type, whether the bracket is a finished product, column type, column installation and adjustment height, cable tray type and number of layers, route length, cover plate length, and partition length.
[0039] S2: The system performs real-time validity checks on key parameters input by users on the main control interface or specific professional calculation module interfaces. For example: Figure 4 In calculating the type of cable tray and the number of supports, the system automatically generates the type name based on the user input in S1. If the input data is incorrect, it will prompt that the cable tray model is entered incorrectly. Users can use the navigation controls to jump to the corresponding cable tray and support details calculation module to view the model and the reference calculated value of the total weight of the cable tray, such as... Figure 5 As shown.
[0040] S3: After confirming the accuracy of the S2 cable tray and support detail calculation module, the system will automatically parse the input parameters and, based on the preset material scheme matching rules, access the basic data table for cable trays, retrieve and match the standard material scheme and related technical parameters for cable trays, and display them in the output intermediate variable table, such as... Figure 6 The material name, specifications, and unit are listed in the column.
[0041] S4: The calculation macro program calls the built-in material calculation algorithm, combines the input parameters with standard data retrieved from the basic database, traverses all relevant calculation items, automatically performs the calculation of material usage and weight, and writes the calculation results to the standardized output area, such as... Figure 6 The quantity and weight are shown in the column.
[0042] S5: The system can initiate a summary procedure for a single sub-region based on the summary command, generating output material lists for different sub-regions, such as... Figure 7 As shown; and extract the calculation results data, sequentially traverse the output material lists of different sub-regions, and according to predefined material classification rules, transfer, classify and aggregate the extracted data to calculate and generate a summary list for each type of material, such as... Figure 8 As shown.
[0043] S6: Standardized Report Generation. The system will populate the aggregated data into a pre-formatted report template, automatically generating a summary table of equipment and installation materials unit project costs that includes a project overview and detailed lists of different sections. It also provides a one-click export function for single tables. Figure 9 As shown. Since this project only considers the standard material scheme and related technical parameters for cable trays, other itemized lists are not detailed. If there are actual engineering needs, the input sections for cable trenches, sliding contact lines, and special MCC cabinets in S1 can be adjusted. The specific technical solutions are similar to those described above and will not be shown in this embodiment.
[0044] In the above embodiments, the reference to "this embodiment" in the specification indicates that a specific feature, structure, or characteristic described in connection with the embodiment is included in at least some embodiments, but not necessarily all embodiments. Multiple appearances of "this embodiment" do not necessarily refer to the same embodiment.
[0045] Finally, it should be noted that the above embodiments are only used to illustrate the technical solutions of the present invention and are not intended to limit it. Although the present invention has been described in detail with reference to preferred embodiments, those skilled in the art should understand that modifications or equivalent substitutions can be made to the technical solutions of the present invention without departing from the spirit and scope of the present invention, and all such modifications or substitutions should be covered within the scope of the claims of the present invention.
Claims
1. An electrical engineering design auxiliary calculation and material statistics system, characterized in that: include: The core data input and overall control module is used to receive the initial design parameters input by the user and provide a navigation control driven by a macro program. The navigation control is used to trigger the macro program to enable navigation and jump to various professional calculation modules after calculation. Multiple specialized calculation modules, each corresponding to an independent worksheet and associated with at least one calculation macro program, the calculation macro program being configured to read input parameters and perform material calculations for different electrical sub-items; The core foundational database consists of multiple independent basic worksheets used to store structured, standard design data; The results summary output module is associated with a summary macro program, which is configured to automatically collect calculation result data from various professional calculation modules and generate a structured material estimate summary table. The computational macro program accesses the core basic database during runtime to obtain the standard data required for computation. The system's functions are executed sequentially by the user triggering the navigation controls, calculation macros, and summary macros.
2. The electrical engineering design auxiliary calculation and material statistics system according to claim 1, characterized in that: The core data input and control interface provides a standardized drop-down selection list through Excel's data validation function, and uses a Button_Click event procedure written in VBA to validate and preprocess the input data.
3. The electrical engineering design auxiliary calculation and material statistics system according to claim 1, characterized in that: The computational macro program uses the lookup functions or calculation methods provided by the macro programming language to query the core basic database to match and obtain standard data.
4. The electrical engineering design auxiliary calculation and material statistics system according to claim 1, characterized in that: The specialized calculation modules include at least three of the following: cable routing calculation module, distribution cabinet configuration calculation module, sliding contact line installation calculation module, MCC cabinet layout module, lightning protection and grounding calculation module, and lighting calculation module. The core calculation logic of each module is implemented by VBA functions or subroutines. During calculation, the module reads the routing parameters input by the user, accesses the basic database worksheet, uses loops, conditional statements, and queries to complete the calculation of all component materials, and fills the results into the specified output cell range.
5. The electrical engineering design auxiliary calculation and material statistics system according to claim 1, characterized in that: The calculation macro program associated with the cable routing calculation module is configured to query the core basic database based on the input specifications and route length of the cable tray and cable trench, obtain the material usage per unit length and the specific specification code, and calculate the required construction material usage for the cable tray body and cable trench according to the formula: Total material = Route length × Material usage per unit length.
6. The electrical engineering design auxiliary calculation and material statistics system according to claim 1, characterized in that: The summary macro program extracts data from the predetermined output areas of each professional calculation module and fills it into the material estimate summary table according to the preset material classification rules.
7. The electrical engineering design auxiliary calculation and material statistics system according to claim 1, characterized in that: It also includes a template management macro program, which is used to save the current workbook as a template file without specific project data.
8. A method for auxiliary calculation and material statistics in electrical engineering design, characterized in that: Includes the following steps: S1: The system receives project design data and global parameters input by the user through the core data input and control module; S2: The system responds to user input operations on the main control interface or specific professional calculation module interface and performs real-time validity verification on the input parameters. And in response to the user's operation of the navigation controls, it jumps to the corresponding professional calculation module; Or, in response to a calculation instruction, it triggers the calculation macro program of a specialized calculation module; S3: The calculation macro program triggered by the current calculation item automatically parses and accesses the corresponding data table in the core basic database module according to the preset scheme matching rules, based on the input parameters of the current calculation item, and retrieves and matches the standard material scheme and related technical parameters of the electrical equipment for that item. S4: The calculation macro program calls the built-in material calculation algorithm, combines the input parameters with the standard data queried from the core basic database, traverses all relevant calculation items, automatically performs the calculation of material usage and weight, and writes the calculation results into the standardized output area of the professional calculation module. S5: The system responds to the summary command and starts the summary macro program; the summary macro program sequentially traverses the standardized output areas of each professional calculation module and extracts the calculation result data; according to the predefined material classification rules, the extracted data is transferred, classified and aggregated, and the total quantity and total weight of each type of material are calculated and generated. S6: The system will fill the aggregated data into a pre-formatted report template, generate a summary table of unit project estimates for equipment and installation materials that includes an overview of the project and a detailed list, and provide an export function.