CN111914526B - Method for calculating and managing bus use by utilizing excelVBA - Google Patents
Method for calculating and managing bus use by utilizing excelVBA Download PDFInfo
- Publication number
- CN111914526B CN111914526B CN202010725382.XA CN202010725382A CN111914526B CN 111914526 B CN111914526 B CN 111914526B CN 202010725382 A CN202010725382 A CN 202010725382A CN 111914526 B CN111914526 B CN 111914526B
- Authority
- CN
- China
- Prior art keywords
- bus
- cells
- worksheets
- short
- length
- Prior art date
- Legal status (The legal status is an assumption and is not a legal conclusion. Google has not performed a legal analysis and makes no representation as to the accuracy of the status listed.)
- Active
Links
Classifications
-
- G—PHYSICS
- G06—COMPUTING; CALCULATING OR COUNTING
- G06F—ELECTRIC DIGITAL DATA PROCESSING
- G06F40/00—Handling natural language data
- G06F40/10—Text processing
- G06F40/166—Editing, e.g. inserting or deleting
- G06F40/177—Editing, e.g. inserting or deleting of tables; using ruled lines
- G06F40/18—Editing, e.g. inserting or deleting of tables; using ruled lines of spreadsheets
-
- G—PHYSICS
- G06—COMPUTING; CALCULATING OR COUNTING
- G06F—ELECTRIC DIGITAL DATA PROCESSING
- G06F16/00—Information retrieval; Database structures therefor; File system structures therefor
- G06F16/20—Information retrieval; Database structures therefor; File system structures therefor of structured data, e.g. relational data
- G06F16/22—Indexing; Data structures therefor; Storage structures
- G06F16/2282—Tablespace storage structures; Management thereof
-
- G—PHYSICS
- G06—COMPUTING; CALCULATING OR COUNTING
- G06Q—INFORMATION AND COMMUNICATION TECHNOLOGY [ICT] SPECIALLY ADAPTED FOR ADMINISTRATIVE, COMMERCIAL, FINANCIAL, MANAGERIAL OR SUPERVISORY PURPOSES; SYSTEMS OR METHODS SPECIALLY ADAPTED FOR ADMINISTRATIVE, COMMERCIAL, FINANCIAL, MANAGERIAL OR SUPERVISORY PURPOSES, NOT OTHERWISE PROVIDED FOR
- G06Q10/00—Administration; Management
- G06Q10/08—Logistics, e.g. warehousing, loading or distribution; Inventory or stock management
- G06Q10/087—Inventory or stock management, e.g. order filling, procurement or balancing against orders
Landscapes
- Engineering & Computer Science (AREA)
- Theoretical Computer Science (AREA)
- Business, Economics & Management (AREA)
- General Physics & Mathematics (AREA)
- Physics & Mathematics (AREA)
- General Engineering & Computer Science (AREA)
- Economics (AREA)
- Tourism & Hospitality (AREA)
- Finance (AREA)
- Operations Research (AREA)
- Quality & Reliability (AREA)
- Strategic Management (AREA)
- Human Resources & Organizations (AREA)
- Entrepreneurship & Innovation (AREA)
- General Business, Economics & Management (AREA)
- Development Economics (AREA)
- Marketing (AREA)
- Health & Medical Sciences (AREA)
- Artificial Intelligence (AREA)
- Audiology, Speech & Language Pathology (AREA)
- Computational Linguistics (AREA)
- General Health & Medical Sciences (AREA)
- Accounting & Taxation (AREA)
- Software Systems (AREA)
- Data Mining & Analysis (AREA)
- Databases & Information Systems (AREA)
- Management, Administration, Business Operations System, And Electronic Commerce (AREA)
Abstract
The invention relates to a method for calculating and managing bus use by utilizing excelVBA, which comprises the following steps of S1: respectively establishing input parameters, calculation results, bus use and inventory management worksheets in Excel; s2: building a module in a visual basic editing area of Excel; s3: establishing a command button control in a calculation result worksheet; s4: and (3) inputting part parameters, related technical parameters and field measurement spans into the input parameter worksheet established in the step (S1), and realizing corresponding bus calculation, bus calling and bus output and warehouse entry management functions by triggering each command button control established in the step (S2). The method can complete automatic calculation of the rigid contact net busbar installation, on one hand, the calculation efficiency is greatly improved, and the working intensity of the calculator is reduced. On the other hand, the phenomenon of construction waste materials is reduced by automatically allocating the short bus bars. And the account is automatically arranged according to the calculation result, so that recording errors caused by human beings are avoided, and the bus is convenient to use and manage.
Description
Technical Field
The invention relates to the technical field of urban rail transit rigid contact network busbar installation, in particular to a method for calculating and managing busbar use by utilizing excelVBA.
Background
After the installation of one anchor section of the suspension support device of the rigid contact net of urban rail transit is completed, the actual spans of the rigid suspension anchor section and the joint length of the anchor section are required to be measured and matched, the total length of the bus bar is calculated, the number of bus bars of the whole anchor section and the length of the short bus bar are calculated according to the total length of the root distance, and when the bus bar is calculated, the middle joint of the bus bar is far away from the midspan and is close to the suspension point as much as possible, so that the sag of the midspan is reduced as much as possible, the clamping stagnation of a positioning wire clamp is prevented, and after the calculation is completed, the length of the short bus bar which is required is counted and is matched with the current short bus bar meeting the length requirement is calculated, so that the waste of materials is reduced.
Disclosure of Invention
The invention aims to solve the technical problems and provide a method for calculating and managing bus use by using excelVBA.
The invention solves the technical problems, and adopts the following technical scheme: a method for calculating and managing bus usage using ExcelVBA, comprising the steps of:
s1: respectively establishing input parameters, calculation results, bus use and inventory management worksheets in Excel;
setting a calculation condition area and a field measurement area in an input parameter worksheet;
setting a busbar arrangement area and a busbar arrangement summarization area in a calculation result table;
setting a short bus source area and a residual short bus area in a bus use table;
setting a short bus stock area and a whole bus stock area in an inventory management worksheet;
s2: the following modules are respectively built in the visual basic editing area of Excel:
establishing a bus calculation module, and calculating the length and the use position of the short bus;
establishing a bus using module, performing bus allocation and use, and reducing short bus generation;
establishing a bus post-use module, performing bus allocation and use, and reducing the use of the whole bus;
establishing a bus manual adjustment module, and replacing the bus with the whole bus when a constructor wants to reserve part of short buses and does not want to call the short buses;
establishing a bus discharging and warehousing module, namely subtracting the whole bus and the short bus which need to be used in the inventory, and adding the whole bus and the short bus which need to be warehoused;
s3: the following command button controls are built in the calculation results worksheet:
establishing a bus calculation command button control, wherein the command button control invokes a bus calculation module in the step S2;
establishing a 'bus use' command button control which invokes a 'bus use module' in step S2; establishing a 'bus late use' command button control, wherein the command button control invokes a 'bus late use module' in the step S2;
establishing a 'manual adjustment' command button control, wherein the command button control invokes a 'bus manual adjustment module' in the step S2;
establishing an out-in-warehouse command button control in the inventory management worksheet, wherein the command button control invokes a bus out-in-warehouse module in the step S2;
s4: and (3) inputting part parameters, related technical parameters and field measurement spans into the input parameter worksheet established in the step (S1), and realizing corresponding bus calculation, bus calling and bus output and warehouse entry management functions by triggering each command button control established in the step (S2).
As a further optimization of the method of calculating and managing bus usage using ExcelVBA of the present invention: the component parameters and related technical parameters recorded in the step S4 specifically comprise XXXX.
As a further optimization of the method of calculating and managing bus usage using ExcelVBA of the present invention: the field measurement span recorded in the step S4 comprises an anchor segment number, a span and an anchor segment length.
As a further optimization of the method of calculating and managing bus usage using ExcelVBA of the present invention: the implementation method of the bus calculation function in the step S4 is as follows: triggering a bus calculation command button control in a calculation result worksheet, automatically deleting the previous calculation result by the system, displaying the arrangement result of the bus calculation, and counting the total number of buses used and the positions of short buses.
As a further optimization of the method of calculating and managing bus usage using ExcelVBA of the present invention: the bus calculation comprises the following specific steps:
1) Resetting the calculation result worksheet, and deleting the previous result;
2) Reading information of a measuring area of the row under calculation, if the measuring information is empty, not calculating, reading information of the next row until a 100008 row reading completion program is finished, if the data is not empty, filling in an anchor segment number, and entering a next step of bus operation;
3) Establishing an array 1 and an array 3, wherein the array is assigned to be the distance from each suspension point to the starting position of the 1 st bus, and the array 1 does not use element maximization value, so that the subsequent Match function call is facilitated;
4) Establishing an array 2 and an array 4, wherein the array is assigned to be the distance from each span to the starting position of the 1 st bus, and the array 2 does not use element maximization value, so that the subsequent Match function call is facilitated;
5) Calculating the last bus allows the shortest length hzh: the maximum value of the sum of the distance from the bus terminal of the anchor section to the nearest suspension point plus the minimum distance from the suspension point to the bus gap and the minimum length allowed by the bus;
6) Calculating the distance from the tail end of the 1 st bus bar to the starting position of the 1 st bus bar;
7) Judging whether the distance between the tail end position of the bus and the hanging points at two sides and the span distance at two sides is larger than a required value, if not, performing the operation of the step 12), and if so, recording the length of the bus to a corresponding table to perform the next operation;
8) Judging whether the arrangement of the anchor section bus bar can be completed by adding an integral bus bar, if so, filling a corresponding table into the last bus bar, performing the operation of the step 20), and if not, performing the next operation;
9) Judging whether a long bus with the length of hzh is added to exceed the total length of the bus in the anchor section, if so, subtracting hzh from the length of the bus, restarting the operation from the step 7), and if not, performing the next operation;
10 Judging whether the total length of the bus bar of the anchor section exceeds the total length of the bus bar, if so, subtracting the accumulated bus bar length from the total length of the bus bar of the last bus bar, filling the accumulated bus bar length into a corresponding table, performing the operation of the step 20), and if not, performing the next calculation;
11 Adding a whole bus, calculating the distance from the tail end of the bus to the starting position of the 1 st bus, and restarting the operation from the step 7);
12 Calculating the length of the bus bar to be cut, calculating the distance from the tail end of the short bus bar to the starting position of the 1 st bus bar, and carrying out the next operation;
13 Judging whether the distance between the tail end position of the bus and the hanging points at two sides and the span distance at two sides is larger than a required value, if not, performing the operation of the step 18), and if so, performing the next operation;
14 Judging whether adding a whole bus to finish the arrangement of the anchor section bus, if so, carrying the short bus length into calculation, and restarting the operation from the step 7), if not, carrying out the next operation;
15 Judging whether a long bus bar with the length of hzh is added to exceed the total length of the bus bar of the anchor section, if so, subtracting hzh from the length of the short bus bar, restarting the operation from the step 13), and if not, performing the next operation;
16 Judging whether the total length of the bus bar of the anchor section is exceeded by adding the whole bus bar, if so, bringing the length of the short bus bar into the total length, and restarting the operation from the step 7), if not, performing the next calculation;
17 Adding a whole bus to calculate the distance from the bus end to the 1 st bus starting position, and restarting the operation from the step 13);
18 Recording the short bus length, and subsequently installing the bus length, and cutting the bus length to be cut calculated in the step 13) on the basis of the bus cut in the step 12);
19 Judging whether the bus starting ends are in the same span after the short bus is cut and the bus length is larger than the shortest bus length, if not, carrying the short bus length in, and restarting the operation from the step 13); if yes, bringing the short bus with the longest length of the subsequent installation bus recorded in the step 18) into the operation from the step 7);
20 Reading bus arrangement information to count the number of the reflux buses, and counting the positions and the lengths of the short buses, and entering the next row to start calculation from the step 2) until the 100008 row computer finishes the program.
As a further optimization of the method of calculating and managing bus usage using ExcelVBA of the present invention: the bus calling function implementation method for the early construction comprises the following steps: triggering a command button control for 'bus use' in a bus use work table, automatically counting the number of the whole buses and the short buses calculated in the step S2 by the system, comprehensively comparing the current cutting residual buses with the short buses in stock, calling a proper short bus to calculate the residual bus allowance, and automatically marking if the bus after the current construction cutting is finished is used in another anchor section use system.
As a further optimization of the method of calculating and managing bus usage using ExcelVBA of the present invention: the bus calling function implementation method for the later construction comprises the following steps: triggering a command button control for later use of the bus in a bus use work table, automatically counting the number of the whole bus and the short bus calculated in the second step by the system, mainly comparing the short buses in stock, then comparing the residual buses in the current cutting, calling a proper short bus to calculate the residual bus allowance, and automatically marking if the bus after the current construction cutting is finished in another anchor section use system.
As a further optimization of the method of calculating and managing bus usage using ExcelVBA of the present invention: the bus calling function implementation method aiming at manual adjustment comprises the following steps: triggering the "manual adjust" command button control, the system automatically defaults to use the entire bus and calculates the bus margin.
As a further optimization of the method of calculating and managing bus usage using ExcelVBA of the present invention: triggering an 'out-in warehouse' command button control in an inventory management worksheet, automatically subtracting the calculated bus required to be used in the in-warehouse ledger by the system, and counting the bus required to be in-warehouse into the in-warehouse ledger.
As a further optimization of the method of calculating and managing bus usage using ExcelVBA of the present invention: the new arrival bus fills the arrival quantity into the warehouse entry column, and can also count the warehouse entry accounts through the step.
Advantageous effects
The method can complete automatic calculation of the rigid contact net busbar installation, on one hand, the calculation efficiency is greatly improved, and the working intensity of the calculator is reduced. On the other hand, the phenomenon of construction waste materials is reduced by automatically allocating the short bus bars. And the account is automatically arranged according to the calculation result, so that recording errors caused by human beings are avoided, and the bus is convenient to use and manage.
Drawings
FIG. 1 is an exemplary diagram of an input parameter worksheet;
FIG. 2 is an exemplary diagram of a calculation results worksheet;
FIG. 3 is an exemplary diagram of a buss bar use (pre-construction) worksheet;
FIG. 4 is an exemplary diagram of a buss bar use (pre-construction) worksheet;
FIG. 5 is a comparison of manual adjustment module usage;
FIG. 6 is an example of an inventory management worksheet;
FIG. 7 is a block diagram of the overall operation of the computing and management method of the present invention;
FIG. 8 is a flow chart of a bus calculation for the calculation and management method of the present invention.
Detailed Description
The technical solutions in the embodiments of the present invention will be clearly and completely described below with reference to the accompanying drawings in the embodiments of the present invention.
As shown in the figure: a method for calculating and managing bus usage using ExcelVBA, comprising the steps of:
s1: respectively establishing input parameters, calculation results, bus use and inventory management worksheets in Excel;
setting a calculation condition area and a field measurement area in an input parameter worksheet;
setting a busbar arrangement area and a busbar arrangement summarization area in a calculation result table;
setting a short bus source area and a residual short bus area in a bus use table;
setting a short bus stock area and a whole bus stock area in an inventory management worksheet;
s2: the following modules are respectively built in the visual basic editing area of Excel:
establishing a bus calculation module, and calculating the length and the use position of the short bus;
establishing a bus using module, performing bus allocation and use, and reducing short bus generation;
establishing a bus post-use module, performing bus allocation and use, and reducing the use of the whole bus;
establishing a bus manual adjustment module, and replacing the bus with the whole bus when a constructor wants to reserve part of short buses and does not want to call the short buses;
establishing a bus discharging and warehousing module, namely subtracting the whole bus and the short bus which need to be used in the inventory, and adding the whole bus and the short bus which need to be warehoused;
s3: the following command button controls are built in the calculation results worksheet:
establishing a bus calculation command button control, wherein the command button control invokes a bus calculation module in the step S2;
establishing a 'bus use' command button control which invokes a 'bus use module' in step S2; establishing a 'bus late use' command button control, wherein the command button control invokes a 'bus late use module' in the step S2;
establishing a 'manual adjustment' command button control, wherein the command button control invokes a 'bus manual adjustment module' in the step S2;
establishing an out-in-warehouse command button control in the inventory management worksheet, wherein the command button control invokes a bus out-in-warehouse module in the step S2;
s4: and (3) inputting part parameters, related technical parameters and field measurement spans into the input parameter worksheet established in the step (S1), and realizing corresponding bus calculation, bus calling and bus output and warehouse entry management functions by triggering each command button control established in the step (S2).
And recording the bus terminal length, the bus shortest length, the shortest distance between the bus connecting gap and the hanging point and the shortest distance between the bus connecting gap and the midspan in the parameter recording worksheet calculation condition area. And inputting an anchor segment number, an anchor segment length and a span in a field measurement area.
Triggering a bus calculation command button control in a calculation result worksheet, automatically deleting the previous calculation result by the system, displaying the arrangement result of the bus calculation, counting the total number of buses used and the positions of short buses, and calculating the calculation result as shown in figure 2.
If the control of a command button for 'bus use' is triggered in a bus use work table in the earlier stage of construction, the system automatically counts the number of the whole buses and the number of short buses calculated in the second step, comprehensively compares the current cutting residual buses with the short buses in stock, calls the proper short buses to calculate the residual bus allowance, and automatically marks the bus after the current construction cutting is finished in another anchor section use system, wherein the calculation result is shown in figure 3;
the VBA procedure is specifically as follows:
sub bus use ()
Dim m#,n#,z#,X#,y#,h1#
m＝0
n＝0
y＝0
z＝0
h1＝1
Seets ("bus use"). Select
Cells.Select
Selection.ClearContents
Selection.Interior.Pattern＝xlPatternNone
Range ("A1"). Formula= "bus usage quantity table"
Range("A2").Select
Selection. Formulation= "Whole root bus usage"
Range("A3").Select
Selection. Formulation = "anchor segment number"
Range("C3").Select
Selection. Formulation= "use bus Length"
Range("D3").Select
Selection. Formulation= "use bus source"
Range("E3").Select
Selection. Formulation= "bus remaining"
Range("B3").Select
Selection, formulation = "cut bus position"
Do
If Worksheets(4).Cells(h1+3,2)＝0Then Exit Do
h1＝h1+1
Loop
For m＝3To 100008
X＝0
For n＝3To 50
X＝X+1
If Worksheets(2).Cells(m,X+2)＝""Then Exit For
Worksheets(3).Cells(2,2)＝Worksheets(3).Cells(2,2)+1
If Worksheets(2).Cells(m,X+2)<＝Worksheets(1).Cells(3,2)-Worksheets(1).Cells(4,2) Then
y＝y+1
Worksheets(3).Cells(y+3,1)＝Worksheets(2).Cells(m,1)
Worksheets(3).Cells(y+3,2)＝X
Worksheets(3).Cells(y+3,3)＝Worksheets(2).Cells(m,X+2)
Worksheets(3).Cells(y+3,5)＝Worksheets(1).Cells(3,2)-Worksheets(3).Cells(y+3,3)
z＝z+1
End If
Next
Next
Dim arr1(1To 100008),m1#,m2#,m3#,m4#,m5#,m6#,m7#,m8#,m9#
m6＝0
m9＝0
For m1＝1To z
arr1(m1)＝Worksheets(3).Cells(m1+3,3)
Next
For m1＝1To z
m2＝Application.Large(arr1,m1)
For m3＝4To z+3
If Worksheets(3).Cells(m3,3)＝m2 And Worksheets(3).Cells(m3,3).Interior.Color<> 65535Then
Worksheets(3).Cells(m3,3).Interior.Color＝65535
Exit For
End If
Next
If Worksheets(3).Cells(m3,5).Interior.Color＝65535Then
Works (3), cells (m 3, 4) = "stock whole root"
GoTo a1
End If
m5＝Worksheets(1).Cells(3,2)
For m4＝4To z+3
If m2<＝Worksheets(3).Cells(m4,5)And m5>Worksheets(3).Cells(m4,5)And m4<> m3 And Worksheets(3).Cells(m4,5).Interior.Color<>65535Then
m5＝Worksheets(3).Cells(m4,5)
m6＝m4
End If
Next
m8＝Worksheets(1).Cells(3,2)
For m7＝4To h1+3
If m2<＝Worksheets(4).Cells(m7,2)And m8>Worksheets(4).Cells(m7,2)And Worksheets(4).Cells(m7,3)-Worksheets(4).Cells(m7,4)>0Then
m8＝Worksheets(4).Cells(m7,2)
m9＝m7
End If
Next
If m5＝m8 And m5＝Worksheets(1).Cells(3,2)Then
Works (3), cells (m 3, 4) = "stock whole root"
GoTo a1
End If
If m5<＝m8 Then
Worksheets(3).Cells(m6,5).Interior.Color＝65535
Worksheets(3).Cells(m3,5)＝Worksheets(3).Cells(m6,5)-Worksheets(3).Cells(m3,3)
Worksheets (3), cells (m 3, 4) =worksheets (3), cells (m 6, 1) & "Anchor segment number" & Worksheets (3), cells (m 6, 2) & "root"
Worksheets(3).Cells(2,2)＝Worksheets(3).Cells(2,2)-1
Else
Worksheets(3).Cells(m3,5)＝Worksheets(4).Cells(m9,2)-Worksheets(3).Cells(m3,3)
Worksheets(4).Cells(m9,4)＝Worksheets(4).Cells(m9,4)+1
Worksheets (3), cells (m 3, 4) = "stock" & Worksheets (4), cells (m 9, 2) & "mm busbar"
Worksheets(3).Cells(2,2)＝Worksheets(3).Cells(2,2)-1
End If
a1:
Next
Sheets ("inventory management"). Activate
Columns("D:D").Select
Selection.ClearContents
Shift ("bus use"). Active
Worksheets(3).Cells(2,4)＝Now
Range("C4:C1000003").Select
Selection.Interior.Pattern＝xlPatternNone
End Sub
If the post construction triggers a command button control of 'bus post use' in a bus use work table, the system automatically counts the number of the whole buses and the short buses calculated in the second step, mainly compares the short buses in stock, then compares the short buses in stock, calls the proper short buses to calculate the residual bus allowance, and if the buses after the completion of the construction and the cutting are used in another anchor section, the system automatically marks the bus allowance as shown in fig. 4. Compared with the 2 calculation modes, the calculation result calculated by the bus using module uses more overall buses, but longer buses in the stock are stored more, so that the bus use probability in later construction is high and better utilized; the calculated result of the bus post-use module is that the whole bus is less in use amount, a large amount of short buses are stored for use, but a large amount of short buses which are particularly short are generated and cannot be used again basically, and the short buses are stored for a large amount until the later stage of construction, the bus demand is less, and more whole buses can be reserved by using the method.
The VBA procedure is specifically as follows:
sub bus late use ()
Dim m#,n#,z#,X#,y#,h1#
m＝0
n＝0
y＝0
z＝0
h1＝1
Seets ("bus use"). Select
Cells.Select
Selection.ClearContents
Selection.Interior.Pattern＝xlPatternNone
Range ("A1"). Formula= "bus usage quantity table"
Range("A2").Select
Selection. Formulation= "Whole root bus usage"
Range("A3").Select
Selection. Formulation = "anchor segment number"
Range("C3").Select
Selection. Formulation= "use bus Length"
Range("D3").Select
Selection. Formulation= "use bus source"
Range("E3").Select
Selection. Formulation= "bus remaining"
Range("B3").Select
Selection, formulation = "cut bus position"
Do
If Worksheets(4).Cells(h1+3,2)＝0Then Exit Do
h1＝h1+1
Loop
For m＝3To 100008
X＝0
For n＝3To 50
X＝X+1
If Worksheets(2).Cells(m,X+2)＝""Then Exit For
Worksheets(3).Cells(2,2)＝Worksheets(3).Cells(2,2)+1
If Worksheets(2).Cells(m,X+2)<＝Worksheets(1).Cells(3,2)-Worksheets(1).Cells(4,2) Then
y＝y+1
Worksheets(3).Cells(y+3,1)＝Worksheets(2).Cells(m,1)
Worksheets(3).Cells(y+3,2)＝X
Worksheets(3).Cells(y+3,3)＝Worksheets(2).Cells(m,X+2)
z＝z+1
End If
Next
Next
Dim arr1(1To 100008),m1#,m2#,m3#,m4#,m5#,m6#,m7#,m8#,m9#
m6＝0
m9＝0
For m1＝1To z
arr1(m1)＝Worksheets(3).Cells(m1+3,3)
Next
For m1＝1To z
m2＝Application.Large(arr1,m1)
For m3＝4To z+3
If Worksheets(3).Cells(m3,3)＝m2 And Worksheets(3).Cells(m3,3).Interior.Color<> 65535Then
Worksheets(3).Cells(m3,3).Interior.Color＝65535
Exit For
End If
Next
If Worksheets(3).Cells(m3,5).Interior.Color＝65535Then
Works (3), cells (m 3, 4) = "stock whole root"
Worksheets(3).Cells(m3,5)＝Worksheets(1).Cells(3,2)-Cells(m3,3)
GoTo a1
End If
m5＝Worksheets(1).Cells(3,2)
For m4＝4To z+3
If m2<＝Worksheets(3).Cells(m4,5)And m5>Worksheets(3).Cells(m4,5)And m4<> m3 And Worksheets(3).Cells(m4,5).Interior.Color<>65535Then
m5＝Worksheets(3).Cells(m4,5)
m6＝m4
End If
Next
m8＝Worksheets(1).Cells(3,2)
For m7＝4To h1+3
If m2<＝Worksheets(4).Cells(m7,2)And m8>Worksheets(4).Cells(m7,2)And Worksheets(4).Cells(m7,3)-Worksheets(4).Cells(m7,4)>0Then
m8＝Worksheets(4).Cells(m7,2)
m9＝m7
End If
Next
If m5＝m8 And m5＝Worksheets(1).Cells(3,2)Then
Works (3), cells (m 3, 4) = "stock whole root"
Worksheets(3).Cells(m3,5)＝Worksheets(1).Cells(3,2)-Cells(m3,3)
GoTo a1
End If
If m5<＝m8 Then
Worksheets(3).Cells(m6,5).Interior.Color＝65535
Worksheets(3).Cells(m3,5)＝Worksheets(3).Cells(m6,5)-Worksheets(3).Cells(m3,3)
Worksheets (3), cells (m 3, 4) =worksheets (3), cells (m 6, 1) & "Anchor segment number" & Worksheets (3), cells (m 6, 2) & "root"
Worksheets(3).Cells(2,2)＝Worksheets(3).Cells(2,2)-1
Else
Worksheets(3).Cells(m3,5)＝Worksheets(4).Cells(m9,2)-Worksheets(3).Cells(m3,3)
Worksheets(4).Cells(m9,4)＝Worksheets(4).Cells(m9,4)+1
Worksheets (3), cells (m 3, 4) = "stock" & Worksheets (4), cells (m 9, 2) & "mm busbar"
Worksheets(3).Cells(2,2)＝Worksheets(3).Cells(2,2)-1
End If
a1:
Next
Sheets ("inventory management"). Activate
Columns("D:D").Select
Selection.ClearContents
Shift ("bus use"). Active
Worksheets(3).Cells(2,4)＝Now
Columns("C:C").Select
Range("C4:C1000003").Select
Selection.Interior.Pattern＝xlPatternNone
End Sub
After the system is allocated, because there is no short bus in the stock, the short bus matched with the system is longer, the long short bus is used to cause bus waste, the residual bus after the whole bus is cut can be well utilized, at this time, the content of the two rows of the bus source and the bus needs to be manually deleted, the command button control for manual adjustment is triggered, the system can automatically default to use the whole bus, the bus allowance is calculated, and the number of the whole buses is increased, as shown in fig. 5.
The VBA procedure is specifically as follows:
sub busbar manual adjustment ()
Dim m#,n#,z#,X#,y#
m＝4
n＝0
y＝0
z＝0
Do
If Worksheets(3).Cells(m,4)＝""And Worksheets(3).Cells(m,3)>0Then
Works (3), cells (m, 4) = "stock whole root"
Worksheets(3).Cells(m,5)＝Worksheets(1).Cells(3,2)-Worksheets(3).Cells(m,3)
Worksheets(3).Cells(m,5).Interior.Pattern＝xlPatternNone
Worksheets(3).Cells(2,2)＝Worksheets(3).Cells(2,2)+1
End If
If Worksheets(3).Cells(m,4)＝""And Worksheets(3).Cells(m,3)＝""Then Exit Do
m＝m+1
Loop
End Sub
After the system calculates and distributes, a 'warehouse-in and warehouse-out' command button control is triggered in the inventory management worksheet, the system automatically subtracts the bus required to be used in the calculation from the in-warehouse ledger, and the bus required to be warehoused is counted into the in-warehouse ledger. The new arrival bus fills the arrival quantity into the warehouse entry column, and the warehouse entry account can be calculated through the step so as to avoid manual calculation errors. The system with inconsistent delivery time of the warehouse-out materials and calculation time in the bus utilization work table does not carry out warehouse-in and warehouse-out calculation of utilizing the bus and the residual bus, thereby preventing misoperation of operators and facilitating warehouse-in operation of the bus by the operators.
Sub bus discharging warehouse-in ()
If Worksheets(4).Cells(3,6)>0Then
Worksheets(4).Cells(3,5)＝Worksheets(4).Cells(3,5)+Worksheets(4).Cells(3,6)
Worksheets(4).Cells(3,6)＝""
End If
If Worksheets(4).Cells(3,7)＝Worksheets(3).Cells(2,4)Then Exit Sub
Worksheets(4).Cells(3,7)＝Worksheets(3).Cells(2,4)
Worksheets(4).Cells(3,5)＝Worksheets(4).Cells(3,5)-Worksheets(3).Cells(2,2)
Dim m#,n#,z#,X#,y#,h1#
m＝1
n＝0
y＝0
z＝0
h1＝1
Do
If Worksheets(4).Cells(h1+3,2)＝0Then Exit Do
h1＝h1+1
Loop
Do
If Worksheets(3).Cells(m+3,3)＝0Then Exit Do
m＝m+1
Loop
For n＝1To m
If works pieces (3), cells (n+3, 4) Like buss bar th
For y＝1To h1
If workbench (3), cells (n+3, 4) = "inventory" & workbench (4), cells (y+3, 2) & "mm busbar" th
Worksheets(4).Cells(y+3,3)＝Worksheets(4).Cells(y+3,3)-1
Exit For
End If
Next
End If
If Worksheets(3).Cells(n+3,5)>＝Worksheets(1).Cells(4,2)And Worksheets(3).Cells(n+ 3,5).Interior.Color<>65535Then
For y＝1To h1
If Worksheets(3).Cells(n+3,5)＝Worksheets(4).Cells(y+3,2)Then
Worksheets(4).Cells(y+3,3)＝Worksheets(4).Cells(y+3,3)+1
Exit For
End If
If y＝h1 Then
Worksheets(4).Cells(y+3,2)＝Worksheets(3).Cells(n+3,5)
Worksheets(4).Cells(y+3,3)＝Worksheets(4).Cells(y+3,3)+1
Worksheets(4).Cells(y+3,1)＝y
h1＝h1+1
End If
Next
End If
Next
If works sheets (4). Cells (3, 5) <0th MsgBox "busbar inventory shortfall"
End Sub
The bus calculation function is realized by the following steps: triggering a bus calculation command button control in a calculation result worksheet, automatically deleting the previous calculation result by the system, displaying the arrangement result of the bus calculation, and counting the total number of buses used and the positions of short buses.
The bus calculation comprises the following specific steps:
1) Resetting the calculation result worksheet, and deleting the previous result;
2) Reading information of a measuring area of the row under calculation, if the measuring information is empty, not calculating, reading information of the next row until a 100008 row reading completion program is finished, if the data is not empty, filling in an anchor segment number, and entering a next step of bus operation;
3) Establishing an array 1 and an array 3, wherein the array is assigned to be the distance from each suspension point to the starting position of the 1 st bus, and the array 1 does not use element maximization value, so that the subsequent Match function call is facilitated;
4) Establishing an array 2 and an array 4, wherein the array is assigned to be the distance from each span to the starting position of the 1 st bus, and the array 2 does not use element maximization value, so that the subsequent Match function call is facilitated;
5) Calculate last bus allowed length hzh: the maximum value of the sum of the distance from the bus terminal of the anchor section to the nearest suspension point plus the minimum distance from the suspension point to the bus gap and the minimum length allowed by the bus;
6) Calculating the distance from the tail end of the 1 st bus bar to the starting position of the 1 st bus bar;
7) Judging whether the distance between the tail end position of the bus and the hanging points at two sides and the span distance at two sides is larger than a required value, if not, performing the operation of the step 12), and if so, recording the length of the bus to a corresponding table to perform the next operation;
8) Judging whether the arrangement of the anchor section bus bar can be completed by adding an integral bus bar, if so, filling a corresponding table into the last bus bar, performing the operation of the step 20), and if not, performing the next operation;
9) Judging whether a long bus with the length of hzh is added to exceed the total length of the bus in the anchor section, if so, subtracting hzh from the length of the bus, restarting the operation from the step 7), and if not, performing the next operation;
10 Judging whether the total length of the bus bar of the anchor section exceeds the total length of the bus bar, if so, subtracting the accumulated bus bar length from the total length of the bus bar of the last bus bar, filling the accumulated bus bar length into a corresponding table, performing the operation of the step 20), and if not, performing the next calculation;
11 Adding a whole bus, calculating the distance from the tail end of the bus to the starting position of the 1 st bus, and restarting the operation from the step 7);
12 Calculating the length of the bus bar to be cut, calculating the distance from the tail end of the short bus bar to the starting position of the 1 st bus bar, and carrying out the next operation;
13 Judging whether the distance between the tail end position of the bus and the hanging points at two sides and the span distance at two sides is larger than a required value, if not, performing the operation of the step 18), and if so, performing the next operation;
14 Judging whether adding a whole bus to finish the arrangement of the anchor section bus, if so, carrying the short bus length into calculation, and restarting the operation from the step 7), if not, carrying out the next operation;
15 Judging whether a long bus bar with the length of hzh is added to exceed the total length of the bus bar of the anchor section, if so, subtracting hzh from the length of the short bus bar, restarting the operation from the step 13), and if not, performing the next operation;
16 Judging whether the total length of the bus bar of the anchor section is exceeded by adding the whole bus bar, if so, bringing the length of the short bus bar into the total length, and restarting the operation from the step 7), if not, performing the next calculation;
17 Adding a whole bus to calculate the distance from the bus end to the 1 st bus starting position, and restarting the operation from the step 13);
18 Recording the short bus length, and subsequently installing the bus length, and cutting the bus length to be cut calculated in the step 13) on the basis of the bus cut in the step 12);
19 Judging whether the bus starting ends are in the same span after the short bus is cut and the bus length is larger than the shortest bus length, if not, carrying the short bus length in, and restarting the operation from the step 13); if yes, bringing the short bus with the longest length of the subsequent installation bus recorded in the step 18) into the operation from the step 7);
20 Reading bus arrangement information to count the number of the reflux buses, and counting the positions and the lengths of the short buses, and entering the next row to start calculation from the step 2) until the 100008 row computer finishes the program.
The VBA procedure is specifically as follows:
sub bus calculation ()
Seets ("results of calculation"). Select
Cells.Select
Selection.ClearContents
Range ("A1"). Formula= "bus calculation"
Range("A2").Select
Selection. Formulation = "anchor segment number"
Range("B2").Select
Selection, formulation = "bus usage summary"
Range("C2").Select
Selection. Formula = "1 st root"
ActiveWindow.ScrollColumn＝5
Selection.AutoFill Destination:＝Range("C2:AB2"),Type:＝xlFillDefault
Range("C2:AB2").Select
ActiveWindow.ScrollColumn＝1
Dim mdh#
For mdh＝3To 100008
If Worksheets(1).Cells(mdh+5,2)＝0Or Worksheets(1).Cells(mdh+5,2)＝""Then GoTo js
Worksheets(2).Cells(mdh,1)＝Worksheets(1).Cells(mdh+5,1)
Dim h1#,h2#,h3#,h4#,arr1(1To 50),arr2(1To 50),arr3(1To 50),arr4(1To 50)
h1＝0
h2＝0
h3＝0
h4＝0
For h3＝1To 50
h1＝h1+Worksheets(1).Cells(mdh+5,h3+2)
If h1*1000<Worksheets(1).Cells(2,2)Then
h2＝0
h4＝0
ElseIf Worksheets(1).Cells(mdh+5,2)*1000-h1*1000<Worksheets(1).Cells(2,2) Then
h4＝500000
h2＝500000
Else:h2＝h1*1000-Worksheets(1).Cells(2,2)
h4＝h2+Worksheets(1).Cells(mdh+5,h3+3)*1000/2
End If
arr1(h3)＝h2
arr2(h3)＝h4
Next
h1＝0
h2＝0
h3＝0
h4＝0
For h3＝1 To 50
h1＝h1+Worksheets(1).Cells(mdh+5,h3+2)
If h1*1000<Worksheets(1).Cells(2,2)Then
h2＝0
h4＝0
ElseIf Worksheets(1).Cells(mdh+5,2)*1000-h1*1000<Worksheets(1).Cells(2,2) Then
h4＝0
h2＝0
Else:h2＝h1*1000-Worksheets(1).Cells(2,2)
h4＝h2+Worksheets(1).Cells(mdh+5,h3+3)*1000/2
End If
arr3(h3)＝h2
arr4(h3)＝h4
Next
Dim h5#,h6#,hzq#,hzh#
h5＝0
h6＝0
hzq＝0
hzh＝0
For h5＝1 To 50
h6＝h6+Worksheets(1).Cells(mdh+5,h5+2)
hqz＝h6*1000-Worksheets(1).Cells(2,2)
If h6*1000>Worksheets(1).Cells(2,2)Then Exit For
Next
Dim m1#,m2#,m3#,m4#,m5#,m6#,m7#,m8#,m9#,m10#
hzh＝Worksheets(1).Cells(mdh+5,2)*1000-Application.Max(arr3)- Worksheets(1).Cells(2,2)*2
m1＝0
m2＝0
m3＝0
m4＝0
m5＝0
m6＝0
m7＝0
m8＝0
m9＝0
m10＝0
Do
m1＝m1+1
m2＝Worksheets(1).Cells(3,2)
a21:
m3＝m10+m2
If m3+Worksheets(1).Cells(3,2)>Worksheets(1).Cells(mdh+5,2)*1000-2* Worksheets(1).Cells(2,2)And Worksheets(1).Cells(mdh+5,2)*1000-2*Worksheets(1).Cells(2, 2)-m3<Application.Max(hzh,Worksheets(1).Cells(4,2))Then
m2＝Worksheets(1).Cells(mdh+5,2)*1000-2*Worksheets(1).Cells(2,2)-m10- Application.Max(hzh,Worksheets(1).Cells(4,2))
GoTo a21
End If
m4＝Application.Match(m3,arr1,1)
If m3-arr1(m4)<Worksheets(1).Cells(5,2)Then
m5＝Worksheets(1).Cells(5,2)+m3-arr1(m4)
GoTo a1
End If
m6＝m4+1
If arr1(m6)-m3<Worksheets(1).Cells(5,2)Then
m5＝Worksheets(1).Cells(5,2)+m3-arr1(m6)
GoTo a1
End If
m7＝Application.Match(m3,arr2,1)
If m3-arr2(m7)<Worksheets(1).Cells(6,2)Then
m5＝Worksheets(1).Cells(6,2)+m3-arr2(m7)
GoTo a1
End If
m8＝m7+1
If arr2(m8)-m3<Worksheets(1).Cells(6,2)Then
m5＝Worksheets(1).Cells(6,2)+m3-arr2(m8)
GoTo a1
End If
Worksheets(2).Cells(mdh,m1+2)＝Application.Round(m2,0)
If m3+Worksheets(1).Cells(3,2)＝Worksheets(1).Cells(mdh+5,2)*1000-2* Worksheets(1).Cells(2,2)Then
Worksheets(2).Cells(mdh,m1+3)＝Worksheets(1).Cells(3,2)
Exit Do
End If
If m3+Worksheets(1).Cells(3,2)>Worksheets(1).Cells(mdh+5,2)*1000-2* Worksheets(1).Cells(2,2)Then
Worksheets(2).Cells(mdh,m1+3)＝Worksheets(1).Cells(mdh+5,2)*1000-2* Worksheets(1).Cells(2,2)-m3
Exit Do
End If
m10＝m10+m2
Loop
Dim nn％,nnn$,mm％,mmm％
nn＝0
nnn= "wherein"
Do
nn＝nn+1
If Worksheets(2).Cells(mdh,nn+2)>0And Worksheets(2).Cells(mdh,nn+2)< Worksheets(1).Cells(3,2)Then
nnn=nnn & "& nn & gt root length" & Worksheets (2) & Cells (mdh, nn+2) & mm; "
End If
If Worksheets(2).Cells(mdh,nn+2)＝0Or Worksheets(2).Cells(mdh,nn+2)＝""Then
Exit Do
End If
mmm＝mmm+1
Loop
If nnn= "wherein," Then
Worksheets (2), cells (mdh, 2) = "commonly used" & nn & "root bus. No bus bar needs to be cut. "
Else: worksheets (2),. Cells (mdh, 2) = "commonly used" & nn-1& "root bus. "& nnn &", the remaining buses were not cut. "
End If
GoTo js
a1:
Dim m11#,m12#,m13#,m14#,m15#,m16#,m17#,m18#,m19#,m20#,m101#,m102#
m101＝0
m102＝m5
a31:
m20＝0
m20＝m10+m2-m5-Worksheets(1).Cells(3,2)
m11＝0
m12＝0
m13＝0
m14＝0
m15＝0
m16＝0
m17＝0
m18＝0
m19＝0
Do
m11＝m11+1
m12＝Worksheets(1).Cells(3,2)
m13＝m20+m12
If Application.Match(m13,arr1,1)＝Application.Match(m10,arr1,1)Then
m2＝m2-m102
GoTo a21
End If
m14＝Application.Match(m13,arr1,1)
If m13-arr1(m14)<Worksheets(1).Cells(5,2)Then
m15＝Worksheets(1).Cells(5,2)+m13-arr1(m14)
GoTo a11
End If
m16＝m14+1
If arr1(m16)-m13<Worksheets(1).Cells(5,2)Then
m15＝Worksheets(1).Cells(5,2)+m13-arr1(m16)
GoTo a11
End If
m17＝Application.Match(m13,arr2,1)
If m13-arr2(m17)<Worksheets(1).Cells(6,2)Then
m15＝Worksheets(1).Cells(6,2)+m13-arr2(m17)
GoTo a11
End If
m18＝m17+1
If arr2(m18)-m13<Worksheets(1).Cells(6,2)Then
m15＝Worksheets(1).Cells(6,2)+m13-arr2(m18)
GoTo a11
End If
If m13+Worksheets(1).Cells(3,2)＝Worksheets(1).Cells(mdh+5,2)*1000-2* Worksheets(1).Cells(2,2)Then
m2＝m2-m5
GoTo a21
End If
If m13+Worksheets(1).Cells(3,2)>Worksheets(1).Cells(mdh+5,2)*1000-2* Worksheets(1).Cells(2,2)Then GoTo a51
m20＝m20+m12
Loop
GoTo js
a11:
If m11>m101 And m5+m15<Worksheets(1).Cells(3,2)-Worksheets(1).Cells(4,2)And Application.Match(m10,arr1,1)<Application.Match(m10+m2-m5-m15,arr1,1)Then
m101＝m11
m102＝m5
End If
m5＝m5+m15
If m2-m5<＝Worksheets(1).Cells(4,2)Then
m2＝m2-m102
GoTo a21
Else:GoTo a31
End If
a51:
Dim m111#,m112#,m113#,m114#,m115#,m116#,m117#,m118#,m119#,m120#, m121#,m131#,m141#
m115＝Worksheets(1).Cells(3,2)-Worksheets(1).Cells(mdh+5,2)*1000+2* Worksheets(1).Cells(2,2)+m13
If m2-m5-m115<＝Worksheets(1).Cells(3,2)-Worksheets(1).Cells(4,2)Then GoTo a61
m120＝m10+m2-m5-m115-Worksheets(1).Cells(3,2)
m111＝0
m112＝0
m113＝0
m114＝0
m116＝0
m117＝0
m118＝0
m119＝0
Do
m111＝m111+1
m112＝Worksheets(1).Cells(3,2)
m113＝m120+m112
If Application.Match(m113,arr1,1)＝Application.Match(m10,arr1,1)Then
GoTo a61
End If
m114＝Application.Match(m113,arr1,1)
If m113-arr1(m114)<Worksheets(1).Cells(5,2)Then
GoTo a61
End If
m116＝m114+1
If arr1(m116)-m113<Worksheets(1).Cells(5,2)Then
GoTo a61
End If
m117＝Application.Match(m113,arr2,1)
If m113-arr2(m117)<Worksheets(1).Cells(6,2)Then
GoTo a61
End If
m118＝m117+1
If arr2(m118)-m113<Worksheets(1).Cells(6,2)Then
GoTo a61
End If
If m113+Worksheets(1).Cells(3,2)＝Worksheets(1).Cells(mdh+5,2)*1000-2* Worksheets(1).Cells(2,2)Then
m2＝m2-m5-m115
GoTo a21
End If
m120＝m120+m112
Loop
a61:
If Worksheets(1).Cells(mdh+5,2)*1000-2*Worksheets(1).Cells(2,2)-m13>＝ Worksheets(1).Cells(4,2)And Worksheets(1).Cells(mdh+5,2)*1000-2*Worksheets(1).Cells(2, 2)-m13>＝hzh+Worksheets(1).Cells(5,2)Then
m2＝m2-m5
GoTo a21
End If
m15＝Application.Max(Worksheets(1).Cells(4,2),hzh+Worksheets(1).Cells(5,2))- Worksheets(1).Cells(mdh+5,2)*1000+2*Worksheets(1).Cells(2,2)+m13
m141＝0
aa31:
m20＝m10+m2-m5-m15-m141-Worksheets(1).Cells(3,2)
m131＝0
m12＝0
m13＝0
m14＝0
m16＝0
m17＝0
m18＝0
m19＝0
m121＝0
Do
m131＝m131+1
m12＝Worksheets(1).Cells(3,2)
m13＝m20+m12
If Application.Match(m13,arr1,1)＝Application.Match(m10,arr1,1)Then
m2＝m2-m5
GoTo a21
End If
m14＝Application.Match(m13,arr1,1)
If m13-arr1(m14)<Worksheets(1).Cells(5,2)Then
m121＝Worksheets(1).Cells(5,2)+m13-arr1(m14)
m141＝m141+m121
GoTo aa31
End If
m16＝m14+1
If arr1(m16)-m13<Worksheets(1).Cells(5,2)Then
m121＝Worksheets(1).Cells(5,2)+m13-arr1(m16)
m141＝m141+m121
GoTo aa31
End If
m17＝Application.Match(m13,arr2,1)
If m13-arr2(m17)<Worksheets(1).Cells(6,2)Then
m121＝Worksheets(1).Cells(6,2)+m13-arr2(m17)
m141＝m141+m121
GoTo aa31
End If
m18＝m17+1
If arr2(m18)-m13<Worksheets(1).Cells(6,2)Then
m121＝Worksheets(1).Cells(6,2)+m13-arr2(m18)
m141＝m141+m121
GoTo aa31
End If
If m131>m11 Then
m2＝m2-m5
GoTo a21
End If
If m131＝m11 Then
m2＝m2-m5-m15-m141
GoTo a21
End If
m20＝m20+m12
Loop
js:
Next
End Sub
The foregoing describes specific embodiments of the present invention. It is to be understood that the invention is not limited to the particular embodiments described above, and that various changes and modifications may be made by one skilled in the art within the scope of the claims without affecting the spirit of the invention.
Claims (8)
1. A method for calculating and managing bus usage by using ExcelVBA, characterized in that: the method comprises the following steps:
s1: respectively establishing input parameters, calculation results, bus use and inventory management worksheets in Excel;
setting a calculation condition area and a field measurement area in an input parameter worksheet;
setting a busbar arrangement area and a busbar arrangement summarization area in a calculation result worksheet;
setting a short bus source area and a residual short bus area in a bus use table;
setting a short bus stock area and a whole bus stock area in an inventory management worksheet;
s2: the following modules are respectively built in the visual basic editing area of Excel:
establishing a bus calculation module, and calculating the length and the use position of the short bus;
establishing a bus using module, performing bus allocation and use, and reducing short bus generation;
establishing a bus post-use module, performing bus allocation and use, and reducing the use of the whole bus;
establishing a bus manual adjustment module, and replacing the bus with the whole bus when a constructor wants to reserve part of short buses and does not want to call the short buses;
establishing a bus discharging and warehousing module, namely subtracting the whole bus and the short bus required to be used from an inventory standing book, and adding the whole bus and the short bus required to be warehoused;
s3: the following command button controls are built in the calculation results worksheet:
establishing a bus calculation command button control, wherein the command button control uses a bus calculation module in the step S2;
establishing a 'bus use' command button control, wherein the command button control uses a 'bus use module' in the step S2; establishing a command button control of 'bus late use', wherein the command button control uses a 'bus late use module' in the step S2;
establishing a 'manual adjustment' command button control, wherein the command button control uses a 'bus manual adjustment module' in the step S2;
establishing an out-in-warehouse command button control in the inventory management worksheet, wherein the command button control uses a bus out-in-warehouse module in the step S2;
s4: inputting part parameters, related technical parameters and field measurement spans into the input parameter worksheet established in the step S1, and realizing corresponding bus calculation, bus calling and bus output and input management functions by triggering all command button controls established in the step S3;
the implementation method of the bus calculation function in step S4 is as follows: triggering a bus calculation command button control in a calculation result worksheet, automatically deleting the previous calculation result by a system, displaying the arrangement result of the bus calculation, counting the total number of buses used and the positions of short buses, and specifically, calculating the buses, wherein the bus calculation comprises the following steps:
(1) Resetting the calculation result worksheet, and deleting the previous result;
(2) Reading measurement area information of the row being calculated, if the measurement information is empty, not calculating, reading next row information until 100008 rows of reading completion programs are finished, if the data is not empty, filling in anchor segment numbers, and entering next step of bus operation;
(3) Establishing an array 1 and an array 3, assigning values for the array 1 and the array 3, wherein the array assigns a distance from each suspension point to the starting position of the 1 st bus, and assigns a maximum value for elements which are not used in the array 1, so that the subsequent Match function call is facilitated;
(4) Establishing an array 2 and an array 4, assigning values for the array 2 and the array 4, wherein the array assigns a distance from each span to the starting position of the 1 st bus, and assigns a maximum value for the unused elements in the array 2, so that the subsequent Match function call is facilitated;
(5) Calculate the last bus allowable shortest length hzh: the maximum value of the sum of the distance from the bus terminal of the anchor section to the nearest suspension point plus the minimum distance from the suspension point to the bus gap and the minimum length allowed by the bus;
(6) Calculating the distance from the tail end of the 1 st bus bar to the starting position of the 1 st bus bar;
(7) Judging whether the distance between the tail end position of the bus and the hanging points at two sides and the span distance at two sides is larger than a required value, if not, performing the operation of the step (12), and if so, recording the length of the bus to a corresponding table for performing the next operation;
(8) Judging whether the anchor section bus arrangement can be completed by adding an integral bus, if so, filling a corresponding table into the last bus, performing the operation of the step (20), and if not, performing the next operation;
(9) Judging whether a long bus with the length of hzh is added to exceed the total length of the bus in the anchor section, if so, subtracting hzh from the length of the bus, and if not, performing the next operation;
(10) Judging whether the total length of the bus bar of the anchor section is exceeded by adding the whole bus bar, if so, subtracting the accumulated bus bar length from the total length of the bus bar of the last bus bar, filling the accumulated bus bar length into a corresponding table, performing operation in the step (20), and if not, performing next calculation;
(11) Adding a whole bus, calculating the distance from the tail end of the bus to the starting position of the 1 st bus, and restarting the operation from the step (7);
(12) Calculating the length of the bus to be cut, calculating the distance from the tail end of the short bus to the starting position of the 1 st bus, and carrying out the next operation;
(13) Judging whether the distance between the tail end position of the busbar and the hanging points at two sides and the span distance at two sides is larger than a required value, if not, performing the operation of the step (18), and if so, performing the next operation;
(14) Judging whether adding a whole bus to finish the arrangement of the anchor section bus, if so, carrying the short bus length into calculation, and restarting the operation from the step (7), if not, carrying out the next operation;
(15) Judging whether a long bus bar with the length of hzh is added to exceed the total length of the bus bar of the anchor section, if so, subtracting hzh from the length of the short bus bar, restarting the operation from the step (13), and if not, performing the next operation;
(16) Judging whether the total length of the bus bar of the anchor section is exceeded by adding the whole bus bar, if so, bringing the length of the short bus bar into the total length, and restarting the operation from the step (7), if not, performing the next calculation;
(17) Adding a whole bus, calculating the distance from the tail end of the bus to the starting position of the 1 st bus, and restarting the operation from the step (13);
(18) Recording the short bus length, and subsequently installing the bus length, and cutting the bus length to be cut calculated in the step (13) on the basis of the bus cut in the step (12);
(19) Judging whether the bus starting end is in the same span after the short bus is cut and the bus length is larger than the shortest bus length, if not, carrying the short bus length in, and restarting the operation from the step (13); if yes, carrying the short bus with the longest length of the subsequent mounting bus recorded in the step (18) into the short bus, and restarting the operation from the step (7);
(20) And (3) reading bus arrangement information to count the number of buses, short bus positions and lengths, and entering the next row to calculate again from the step (2) until the 100008 row computer finishes the program.
2. The method for calculating and managing bus usage using ExcelVBA according to claim 1, wherein: the parameters recorded in the step S4 specifically include a bus terminal length, a bus shortest length, a shortest distance between a bus connection slit and a suspension point, and a mid-span shortest distance between a bus connection slit and a suspension point.
3. The method for calculating and managing bus usage using ExcelVBA according to claim 1, wherein: the field measurement span recorded in the step S4 comprises an anchor segment number, a span and an anchor segment length.
4. The method for calculating and managing bus usage using ExcelVBA according to claim 1, wherein: the bus calling function implementation method for the early construction comprises the following steps: triggering a command button control for 'bus use' in a bus use work table, automatically counting the number of the whole buses and the short buses calculated in the step S2 by the system, comprehensively comparing the current cutting residual buses with the short buses in stock, calling a proper short bus to calculate the residual bus allowance, and automatically marking if the buses after the current construction cutting are used in another anchor section by the system.
5. The method for calculating and managing bus usage using ExcelVBA according to claim 1, wherein: the bus calling function implementation method for the later construction comprises the following steps: triggering a command button control for later use of the bus in a bus use work table, automatically counting the number of the whole bus and the short bus calculated in the step S2 by the system, mainly comparing the short buses in stock, then comparing the residual buses in the current cutting, calling a proper short bus to calculate the residual bus allowance, and automatically marking if the bus after the current construction cutting is used in another anchor section by the system.
6. The method for calculating and managing bus usage using ExcelVBA according to claim 1, wherein: the bus calling function implementation method aiming at manual adjustment comprises the following steps: triggering the "manual adjust" command button control, the system automatically defaults to use the entire bus and calculates the bus margin.
7. The method for calculating and managing bus usage using ExcelVBA according to claim 1, wherein: triggering an 'out-in warehouse' command button control in an inventory management worksheet, automatically subtracting the calculated bus required to be used in the in-warehouse ledger by the system, and counting the bus required to be in-warehouse into the in-warehouse ledger.
8. The method for calculating and managing bus usage using ExcelVBA according to claim 7, wherein: the new arrival bus fills the arrival amount into the warehouse entry column or counts the arrival amount into the warehouse account.
Priority Applications (1)
| Application Number | Priority Date | Filing Date | Title |
|---|---|---|---|
| CN202010725382.XA CN111914526B (en) | 2020-07-24 | 2020-07-24 | Method for calculating and managing bus use by utilizing excelVBA |
Applications Claiming Priority (1)
| Application Number | Priority Date | Filing Date | Title |
|---|---|---|---|
| CN202010725382.XA CN111914526B (en) | 2020-07-24 | 2020-07-24 | Method for calculating and managing bus use by utilizing excelVBA |
Publications (2)
| Publication Number | Publication Date |
|---|---|
| CN111914526A CN111914526A (en) | 2020-11-10 |
| CN111914526B true CN111914526B (en) | 2023-10-20 |
Family
ID=73280777
Family Applications (1)
| Application Number | Title | Priority Date | Filing Date |
|---|---|---|---|
| CN202010725382.XA Active CN111914526B (en) | 2020-07-24 | 2020-07-24 | Method for calculating and managing bus use by utilizing excelVBA |
Country Status (1)
| Country | Link |
|---|---|
| CN (1) | CN111914526B (en) |
Citations (7)
| Publication number | Priority date | Publication date | Assignee | Title |
|---|---|---|---|---|
| EP0572868A1 (en) * | 1992-06-03 | 1993-12-08 | Siemens Aktiengesellschaft | Catenary wire support |
| CN104391832A (en) * | 2014-11-18 | 2015-03-04 | 浪潮电子信息产业股份有限公司 | Method for combining data in multiple EXCEL data sheets by using VBA (Visual Basic for Applications) |
| CN105095598A (en) * | 2015-08-28 | 2015-11-25 | 中铁第一勘察设计院集团有限公司 | Construction method for graphic design drawing of overhead rigid catenary of metro |
| CN205381911U (en) * | 2016-01-06 | 2016-07-13 | 中铁武汉电气化局集团有限公司 | A hydraulic lifting platform for installation of subway busbar |
| CN105868212A (en) * | 2015-01-22 | 2016-08-17 | 天津金牛电源材料有限责任公司 | VBA(Visual Basic for Applications)-based electrolyte packaging bucket management system and operation method |
| CN205871785U (en) * | 2016-07-25 | 2017-01-11 | 中国建筑第六工程局有限公司 | Subway rigid suspension contact net bearing structure |
| CN110606001A (en) * | 2019-07-05 | 2019-12-24 | 中铁十二局集团有限公司 | Mounting construction method for rigid contact network busbar of subway at 160km per hour |
Family Cites Families (1)
| Publication number | Priority date | Publication date | Assignee | Title |
|---|---|---|---|---|
| US20090177301A1 (en) * | 2007-12-03 | 2009-07-09 | Codentity, Llc | Scalable system and method for an integrated digital media catalog, management and reproduction system |
-
2020
- 2020-07-24 CN CN202010725382.XA patent/CN111914526B/en active Active
Patent Citations (7)
| Publication number | Priority date | Publication date | Assignee | Title |
|---|---|---|---|---|
| EP0572868A1 (en) * | 1992-06-03 | 1993-12-08 | Siemens Aktiengesellschaft | Catenary wire support |
| CN104391832A (en) * | 2014-11-18 | 2015-03-04 | 浪潮电子信息产业股份有限公司 | Method for combining data in multiple EXCEL data sheets by using VBA (Visual Basic for Applications) |
| CN105868212A (en) * | 2015-01-22 | 2016-08-17 | 天津金牛电源材料有限责任公司 | VBA(Visual Basic for Applications)-based electrolyte packaging bucket management system and operation method |
| CN105095598A (en) * | 2015-08-28 | 2015-11-25 | 中铁第一勘察设计院集团有限公司 | Construction method for graphic design drawing of overhead rigid catenary of metro |
| CN205381911U (en) * | 2016-01-06 | 2016-07-13 | 中铁武汉电气化局集团有限公司 | A hydraulic lifting platform for installation of subway busbar |
| CN205871785U (en) * | 2016-07-25 | 2017-01-11 | 中国建筑第六工程局有限公司 | Subway rigid suspension contact net bearing structure |
| CN110606001A (en) * | 2019-07-05 | 2019-12-24 | 中铁十二局集团有限公司 | Mounting construction method for rigid contact network busbar of subway at 160km per hour |
Non-Patent Citations (6)
| Title |
|---|
| 刚性接触网拉出值布置与磨耗分析;白凯元;;城市轨道交通研究(第06期);全文 * |
| 刚性接触网预弯汇流排的参数确定;黄河;;电气化铁道(第05期);全文 * |
| 地铁架空刚性接触网平面设计软件的优化;田升平;;铁道标准设计(第03期);全文 * |
| 基于Excel VBA工资条标题的创建方法的研究;连瑞梅;;中国科技信息(第18期);全文 * |
| 基于EXCEL VBA网络岗位练兵数据统计工具的开发;姜岳健;陈实;;数字通信世界(第02期);全文 * |
| 接触网刚性悬挂跨距的选择;尹魁元;;都市快轨交通(第04期);全文 * |
Also Published As
| Publication number | Publication date |
|---|---|
| CN111914526A (en) | 2020-11-10 |
Similar Documents
| Publication | Publication Date | Title |
|---|---|---|
| CN108845881A (en) | The method and device of server capacity dynamic adjustment | |
| US4525601A (en) | Telephone call accounting system | |
| CN108667654A (en) | The automatic expansion method of server cluster and relevant device | |
| CN111914526B (en) | Method for calculating and managing bus use by utilizing excelVBA | |
| KR102594950B1 (en) | Energy control method and power generation and energy storage system | |
| CN107608870A (en) | A kind of statistical method and system of system resource utilization rate | |
| EP2109070A1 (en) | A method and device for selecting the work flow route | |
| CN114819406A (en) | Water-light complementary scheduling large system decomposition coordination optimization method | |
| KR20180103516A (en) | Operating system for energy storage device based on korean type power charges and method thereof | |
| CN106600154A (en) | Method and system for precise personnel allocation of server product | |
| CN101662778B (en) | Method and system for mass base station establishment | |
| CN102981842A (en) | Method of generating interactive Gantt chart | |
| CN115130927B (en) | Data modeling method and system | |
| CN115940301A (en) | Power factor control method, device, equipment and storage medium | |
| CN114442579A (en) | Remote monitoring system of numerical control machine tool based on Internet of things | |
| CN116822841B (en) | Management method of sharing station, computer storage medium and equipment | |
| CN107567102A (en) | A kind of resource allocation methods and device | |
| CN103139887A (en) | Power distributive method and system of high-speed shared control channel | |
| CN101515833B (en) | Method and device for realizing protection switching | |
| DE19858218B4 (en) | A method for maintaining an upper total power limit for a group of electrical consumers in the supply of electrical energy from a utility and a device for carrying it out | |
| CN113220668B (en) | Load curve data access method based on grouping | |
| CN117291384B (en) | Project progress adjustment method, device, equipment and medium based on resource allocation | |
| CN116757760B (en) | Method, system, terminal and storage medium for checking electric charge of business user | |
| CN110991807A (en) | Power failure quota setting method, computer device, and medium | |
| CN111275382A (en) | Intelligent checking management method and system for tobacco lamina stock |
Legal Events
| Date | Code | Title | Description |
|---|---|---|---|
| PB01 | Publication | ||
| PB01 | Publication | ||
| SE01 | Entry into force of request for substantive examination | ||
| SE01 | Entry into force of request for substantive examination | ||
| GR01 | Patent grant | ||
| GR01 | Patent grant |