CN111914526A - Method for calculating and managing bus use by using excelVBA - Google Patents
Method for calculating and managing bus use by using excelVBA Download PDFInfo
- Publication number
- CN111914526A CN111914526A CN202010725382.XA CN202010725382A CN111914526A CN 111914526 A CN111914526 A CN 111914526A CN 202010725382 A CN202010725382 A CN 202010725382A CN 111914526 A CN111914526 A CN 111914526A
- Authority
- CN
- China
- Prior art keywords
- bus
- cells
- worksheets
- bus bar
- 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.)
- Granted
Links
Images
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
Abstract
The invention relates to a method for calculating and managing the use of a bus by utilizing excelVBA, S1: respectively establishing a parameter input worksheet, a calculation result, a busbar use worksheet and an inventory management worksheet in Excel; s2: establishing a module in a visual basic editing area of Excel; s3: establishing a command button control in a calculation result worksheet; s4: and step S1, recording part parameters, related technical parameters and field measurement span in the parameter recording worksheet established in the step S1, and triggering each command button control established in the step S2 to realize corresponding functions of bus calculation, bus calling, bus discharging and warehousing management. The method can complete the automatic calculation of the installation of the rigid contact net busbar, greatly improves the calculation efficiency and reduces the working intensity of the calculation personnel on one hand. On the other hand, the phenomenon of material waste in construction is reduced by automatically allocating the short busbars for use. And moreover, the standing book is automatically arranged according to the calculation result, so that manual recording errors are avoided, and the use and management of the bus are facilitated.
Description
Technical Field
The invention relates to the technical field of bus bar installation of rigid contact networks of urban rail transit, in particular to a method for calculating and managing bus bar use by utilizing excelVBA.
Background
After one anchor section of a suspension supporting device of a rigid contact network of urban rail transit is installed, the actual spans and the anchor section joint lengths of the rigid suspension anchor section need to be measured and matched, the total length of a busbar is calculated, the total length of the busbar is calculated, the number of busbars of the whole anchor section and the length of short busbars are calculated according to the total length of the busbar, when the busbar is calculated, intermediate joints of the busbars are far away from a midspan as far as possible and close to a suspension point, the sag of the midspan is reduced as far as possible, clamping stagnation of a positioning wire clamp is prevented, after calculation is completed, the lengths of the busbars which are not short and are not required are counted and matched with the short busbars meeting the length requirement, and material waste is.
Disclosure of Invention
The present invention is directed to solve the above technical problems, and to provide a method for calculating and managing bus usage by using ExcelVBA.
In order to solve the technical problems, the invention adopts the technical scheme that: a method for calculating and managing bus usage using ExcelVBA, comprising the steps of:
s1: respectively establishing a parameter input worksheet, a calculation result, a busbar use worksheet and an inventory management worksheet in Excel;
setting a calculation condition area and a field measurement area in an input parameter worksheet;
setting a bus arrangement area and a bus arrangement summary area in a calculation result chart;
setting a short bus source area and a residual short bus area in a bus use worksheet;
setting a short bus bar stock area and a whole bus bar stock area in the stock management worksheet;
s2: the following modules are respectively established in a visual basic editing area of Excel:
establishing a bus calculation module for calculating the length and the use position of the short bus;
a bus using module is established to carry out bus allocation and use and reduce the generation of short buses;
a bus later-use module is established to allocate and use the bus and reduce the use of the whole bus;
establishing a 'bus bar manual adjustment module', and replacing the bus bar with an entire bus bar when a constructor wants to reserve part of short bus bars and does not want to use the short bus bars;
establishing a busbar discharging and warehousing module, and subtracting the whole busbar and the short busbar which need to be used in the stock and adding the whole busbar and the short busbar which need to be warehoused;
s3: the following command button controls are established in the calculation results worksheet:
establishing a bus calculation command button control, and calling a bus calculation module in the step S2;
establishing a "bus use" command button control that calls the "bus use module" in step S2; establishing a command button control of ' later use of the bus ', and calling a ' later use module ' of the bus ' in the step S2;
establishing a manual adjustment command button control, and calling a bus manual adjustment module in the step S2;
establishing a command button control of 'warehouse-in and warehouse-out' in the inventory management worksheet, and calling a 'bus bar warehouse-in and warehouse-out module' in the step S2 by the command button control;
s4: and step S1, recording part parameters, related technical parameters and field measurement span in the parameter recording worksheet established in the step S1, and triggering each command button control established in the step S2 to realize corresponding functions of bus calculation, bus calling, bus discharging and warehousing management.
Further optimization as a method of the invention for calculating and managing the use of the bus using ExcelVBA: the part parameters and related technical parameters recorded in step S4 specifically include XXXX.
Further optimization as a method of the invention for calculating and managing the use of the bus using ExcelVBA: the field measurement span entered in step S4 includes an anchor segment number, a span, and an anchor segment length.
Further optimization as a method of the invention for calculating and managing the use of the bus using ExcelVBA: the method for implementing the bus calculation function in step S4 includes: and triggering a bus calculation command button control in the 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 the used buses and the position of the short bus.
Further optimization as a method of the invention for calculating and managing the use of the bus using ExcelVBA: the specific steps of the bus calculation include:
1) resetting the calculation result worksheet and deleting the former result;
2) reading the information of the measurement area of the line being calculated, if the measurement information is empty, not calculating, reading the information of the next line until the reading completion program of 100008 lines is finished, if the data is not empty, filling in the anchor segment number, and entering the next step for carrying out the operation of the bus;
3) establishing an array 1 and an array 3, assigning the array as the distance from each suspension point to the initial position of the 1 st bus, and assigning a maximum value to the array 1 without using elements so as to facilitate later-stage Match function call;
4) establishing an array 2 and an array 4, assigning the array as the distance from each span to the initial position of the 1 st bus, and assigning a maximum value to the array 2 without using elements so as to facilitate later-stage Match function call;
5) calculate last bus allowed shortest length hzh: the maximum value of the sum of the distance from the terminal of the anchor section bus to the nearest suspension point and 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 to the initial position of the 1 st bus;
7) judging whether the distance between the tail end position of the bus bar and the suspension points on the two sides and the distances between the tail end position of the bus bar and the spans on the two sides are larger than a required value or not, if not, carrying out the operation of step 12), and if so, recording the length of the bus bar to a corresponding table for carrying out the next operation;
8) judging whether the arrangement of the anchor section bus bar can be finished by adding another whole bus bar, if so, filling the corresponding table into the last bus bar which is the whole bus bar, and carrying out the operation of the step 20), otherwise, carrying out the next operation;
9) judging whether a long bus with the length of hzh exceeds the total length of the anchor section bus, if so, subtracting hzh from the length of the bus, and starting the operation from the step 7) again, and if not, carrying out the next operation;
10) judging whether the total length of the used anchor segment bus is exceeded by another whole bus, if so, the length of the last bus is the total length of the used bus minus the length of the accumulated bus, filling the length of the accumulated bus into a corresponding table, and performing the operation of the step 20), otherwise, 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 starting 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 performing the next operation;
13) judging whether the distance between the tail end position of the bus bar and the suspension points on the two sides and the distances between the tail end position of the bus bar and the spans on the two sides are larger than a required value or not, if not, carrying out the operation of the step 18), and if so, carrying out the next operation;
14) judging whether the arrangement of the anchor section bus bar can be finished by adding one more whole bus bar, if so, substituting the length of the short bus bar into the calculation, and starting the calculation from the step 7) again, and if not, carrying out the next calculation;
15) judging whether the length of the long bus with hzh exceeds the total length of the anchor section bus, if so, subtracting hzh from the length of the short bus, and starting the operation from the step 13) again, and if not, carrying out the next operation;
16) judging whether the total length of the used anchor segment bus bar is exceeded by adding another whole bus bar, if so, substituting the length of the short bus bar, and starting operation from the step 7) again, and if not, performing next calculation;
17) adding a whole bus to calculate the distance from the tail end of the bus to the starting position of the 1 st bus, and starting operation from the step 13);
18) recording the length of the short busbar, subsequently installing the length of the busbar, and cutting off the length of the busbar to be cut calculated in the step 13) on the basis of the busbar cut in the step 12);
19) judging whether the starting ends of the buses are in the same span and the length of the buses is larger than the length of the shortest bus after the short buses are cut, if not, substituting the length of the short buses, and starting operation from the step 13) again; if yes, the short bus with the longest length of the subsequent installation bus recorded in the step 18) is taken in, and the operation is started from the step 7) again;
20) reading the bus arrangement information to count the number of the return buses, the position and the length of the short bus, entering the next line, and starting to calculate again from the step 2) until 100008 lines of computers finish the program.
Further optimization as a method of the invention for calculating and managing the use of the bus using ExcelVBA: the bus calling function implementation method aiming at the early construction comprises the following steps: triggering a command button control of 'using the bus bar' in a bus bar using worksheet, automatically counting the number of the whole bus bar and the short bus bar calculated in the step S2 by the system, comprehensively comparing the cut residual bus bar and the stored short bus bar, calling the proper short bus bar to calculate the residual bus bar allowance, and automatically marking if the cut bus bar in the current construction is used in another anchor section using system.
Further optimization as a method of the invention for calculating and managing the use of the bus using ExcelVBA: the bus calling function implementation method aiming at later construction comprises the following steps: and triggering a command button control of 'later use of the busbar' in a busbar use worksheet, automatically counting the number of the whole busbars and the short busbars calculated in the step two by the system, mainly comparing the short busbars in stock, comparing the remaining busbars cut this time, calling the proper short busbars to calculate the remaining busbar allowance, and automatically marking if the busbars cut by this time of construction are used in another anchor section by the system.
Further optimization as a method of the invention for calculating and managing the use of the bus using ExcelVBA: the bus calling function implementation method aiming at manual adjustment comprises the following steps: triggering the manual adjustment command button control, the system automatically defaults to using the whole bus and calculates the bus allowance.
Further optimization as a method of the invention for calculating and managing the use of the bus using ExcelVBA: triggering a 'warehouse-in and warehouse-out' command button control in the inventory management worksheet, automatically eliminating the buses required to be used in the calculation in the warehouse-in and warehouse-out accounts by the system, and recording the buses required to be warehoused in the warehouse-in and warehouse-out accounts.
Further optimization as a method of the invention for calculating and managing the use of the bus using ExcelVBA: the new arrival busbar fills the arrival quantity into the warehousing column, and can also be counted into the warehouse ledger through the step.
Advantageous effects
The method can complete the automatic calculation of the installation of the rigid contact net busbar, greatly improves the calculation efficiency and reduces the working intensity of the calculation personnel on one hand. On the other hand, the phenomenon of material waste in construction is reduced by automatically allocating the short busbars for use. And moreover, the standing book is automatically arranged according to the calculation result, so that manual recording errors are avoided, and the use and management of the bus are facilitated.
Drawings
FIG. 1 is an exemplary diagram of a worksheet of entered parameters;
FIG. 2 is an exemplary diagram of a calculation results worksheet;
FIG. 3 is an exemplary diagram of a bus bar usage (pre-construction) worksheet;
FIG. 4 is an exemplary diagram of a bus bar usage (pre-construction) worksheet;
FIG. 5 is a comparison of the use of the manual adjustment module;
FIG. 6 is an example of an inventory management worksheet;
FIG. 7 is a block diagram illustrating the overall operation of the computing and management method of the present invention;
FIG. 8 is a flow chart of bus calculation for the calculation and management method of the present invention.
Detailed Description
The technical solution 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 a parameter input worksheet, a calculation result, a busbar use worksheet and an inventory management worksheet in Excel;
setting a calculation condition area and a field measurement area in an input parameter worksheet;
setting a bus arrangement area and a bus arrangement summary area in a calculation result chart;
setting a short bus source area and a residual short bus area in a bus use worksheet;
setting a short bus bar stock area and a whole bus bar stock area in the stock management worksheet;
s2: the following modules are respectively established in a visual basic editing area of Excel:
establishing a bus calculation module for calculating the length and the use position of the short bus;
a bus using module is established to carry out bus allocation and use and reduce the generation of short buses;
a bus later-use module is established to allocate and use the bus and reduce the use of the whole bus;
establishing a 'bus bar manual adjustment module', and replacing the bus bar with an entire bus bar when a constructor wants to reserve part of short bus bars and does not want to use the short bus bars;
establishing a busbar discharging and warehousing module, and subtracting the whole busbar and the short busbar which need to be used in the stock and adding the whole busbar and the short busbar which need to be warehoused;
s3: the following command button controls are established in the calculation results worksheet:
establishing a bus calculation command button control, and calling a bus calculation module in the step S2;
establishing a "bus use" command button control that calls the "bus use module" in step S2; establishing a command button control of ' later use of the bus ', and calling a ' later use module ' of the bus ' in the step S2;
establishing a manual adjustment command button control, and calling a bus manual adjustment module in the step S2;
establishing a command button control of 'warehouse-in and warehouse-out' in the inventory management worksheet, and calling a 'bus bar warehouse-in and warehouse-out module' in the step S2 by the command button control;
s4: and step S1, recording part parameters, related technical parameters and field measurement span in the parameter recording worksheet established in the step S1, and triggering each command button control established in the step S2 to realize corresponding functions of bus calculation, bus calling, bus discharging and warehousing management.
And recording the bus terminal length, the bus length, the shortest distance of the bus connecting gap from the hanging point and the shortest distance of the bus connecting gap from the span in the parameter recording worksheet calculation condition area. And inputting the number of the anchor section, the length of the anchor section and the span in a field measurement area.
Triggering a command button control of 'bus calculation' in a calculation result worksheet, automatically deleting the previous calculation result by the system, displaying the arrangement result of the current bus calculation, and counting the total number of the used buses and the positions of the short buses, wherein the calculation result is shown in fig. 2.
If the command button control of 'using the bus bar' is triggered in the bus bar using worksheet in the earlier stage of construction, the system automatically counts the number of the whole bus bar and the short bus bar calculated in the second step, comprehensively compares the cut residual bus bar and the stock short bus bar, calls a proper short bus bar to calculate the residual bus bar allowance, and if the cut bus bar in the current construction is automatically marked in another anchor section using system, the calculation result is shown in figure 3;
the VBA procedure is specifically as follows:
sub bus usage ()
Dim m#,n#,z#,X#,y#,h1#
m＝0
n＝0
y＝0
z＝0
h1＝1
Sheets ("bus use"). Select
Cells.Select
Selection.ClearContents
Selection.Interior.Pattern＝xlPatternNone
Range ("a1"). Formula ═ bus bar usage number scale "
Range("A2").Select
Collection. formula is used for "the entire bus bar"
Range("A3").Select
Collection. formula ═ anchor segment number "
Range("C3").Select
Collection. formula ═ using bus length "
Range("D3").Select
Collection. formula ═ using bus sources "
Range("E3").Select
Collection. formula ═ bus bar residue "
Range("B3").Select
Collection. formula ═ cutting bus bar 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
Worksheets (3), Cells (m3,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
Worksheets (3), Cells (m3,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 (m3,4) ═ Worksheets (3). Cells (m6,1) & "anchor section number" & Worksheets (3) & "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 (m3,4) ═ inventory & "mm bus bar" & Worksheets (4), Cells (m9,2) & "mm bus bar"
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
Sheets ("bus use"). Activate
Worksheets(3).Cells(2,4)＝Now
Range("C4:C1000003").Select
Selection.Interior.Pattern＝xlPatternNone
End Sub
If the command button control of 'later-stage use of the busbar' is triggered in the busbar use worksheet in later-stage construction, the system automatically counts the number of the whole busbars and the short busbars calculated in the step two, mainly compares the short busbars in stock, compares the remaining busbars cut this time, calls the appropriate short busbars to calculate the remaining busbar allowance, and if the busbars cut in the construction this time are automatically marked in another anchor section using system, as shown in fig. 4. Compared with 2 calculation modes, the calculation result calculated by the busbar use module uses more integral busbars, but the longer busbars in the stock are stored more, and the busbars are used more frequently in later construction and are better utilized; the use amount of the whole bus calculated by the bus later-stage use module is small, a large number of short buses in stock are used, but a large number of short buses in particular cannot be reused basically, and when the short buses in stock are more and the demand of the buses is small in the later stage of construction, more whole buses can be reserved by using the mode.
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
Sheets ("bus use"). Select
Cells.Select
Selection.ClearContents
Selection.Interior.Pattern＝xlPatternNone
Range ("a1"). Formula ═ bus bar usage number scale "
Range("A2").Select
Collection. formula is used for "the entire bus bar"
Range("A3").Select
Collection. formula ═ anchor segment number "
Range("C3").Select
Collection. formula ═ using bus length "
Range("D3").Select
Collection. formula ═ using bus sources "
Range("E3").Select
Collection. formula ═ bus bar residue "
Range("B3").Select
Collection. formula ═ cutting bus bar 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
Worksheets (3), Cells (m3,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
Worksheets (3), Cells (m3,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 (m3,4) ═ Worksheets (3). Cells (m6,1) & "anchor section number" & Worksheets (3) & "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 (m3,4) ═ inventory & "mm bus bar" & Worksheets (4), Cells (m9,2) & "mm bus bar"
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
Sheets ("bus use"). Activate
Worksheets(3).Cells(2,4)＝Now
Columns("C:C").Select
Range("C4:C1000003").Select
Selection.Interior.Pattern＝xlPatternNone
End Sub
After the system is deployed, because there is no short bus in the stock, the short bus matched with the system is long, and the long short bus is used to cause waste of the bus, the bus left after the whole bus is cut can be well utilized, at this time, the contents of the source of the used bus and the two rows of the bus left need to be deleted manually, the manual adjustment command button control is triggered, the system can automatically default to use the whole bus, the bus allowance is calculated, and the number of the used whole bus is increased, as shown in fig. 5.
The VBA procedure is specifically as follows:
sub bus manual regulation ()
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
Worksheets (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 allocates, triggering the command button control of 'warehouse-in and warehouse-out' in the inventory management worksheet, automatically eliminating the buses required to be used in the calculation in the warehouse-in and warehouse-out accounts by the system, and recording the buses required to be warehoused in the warehouse-in and warehouse-out accounts. The new arrival busbar fills the arrival quantity into the warehousing column, and also can be counted into the in-warehouse ledger through the step to avoid manual calculation errors. The system does not carry out warehouse entry and warehouse exit calculation for using the bus bar and the rest bus bar according to the inconsistency between warehouse exit material reporting time and calculation time in the bus bar use worksheet, so that misoperation of operators is prevented, and the operation of entering the warehouse of the bus bar is facilitated for the operators.
Sub busbar warehouse outlet ()
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 Worksheets (3), Cells (n +3,4) Like busbar "Then
For y＝1To h1
If works sets (3) Cells (n +3,4) ═ inventory & works sets (4) Cells (y +3,2) & "mm bus" Then
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 Worksheets (4), Cells (3,5) <0the MsgBox, "bus stock is insufficient"
End Sub
The implementation method of the bus calculation function comprises the following steps: and triggering a bus calculation command button control in the 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 the used buses and the position of the short bus.
The specific steps of the bus calculation include:
1) resetting the calculation result worksheet and deleting the former result;
2) reading the information of the measurement area of the line being calculated, if the measurement information is empty, not calculating, reading the information of the next line until the reading completion program of 100008 lines is finished, if the data is not empty, filling in the anchor segment number, and entering the next step for carrying out the operation of the bus;
3) establishing an array 1 and an array 3, assigning the array as the distance from each suspension point to the initial position of the 1 st bus, and assigning a maximum value to the array 1 without using elements so as to facilitate later-stage Match function call;
4) establishing an array 2 and an array 4, assigning the array as the distance from each span to the initial position of the 1 st bus, and assigning a maximum value to the array 2 without using elements so as to facilitate later-stage Match function call;
5) calculate last bus allowed length hzh: the maximum value of the sum of the distance from the terminal of the anchor section bus to the nearest suspension point and 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 to the initial position of the 1 st bus;
7) judging whether the distance between the tail end position of the bus bar and the suspension points on the two sides and the distances between the tail end position of the bus bar and the spans on the two sides are larger than a required value or not, if not, carrying out the operation of step 12), and if so, recording the length of the bus bar to a corresponding table for carrying out the next operation;
8) judging whether the arrangement of the anchor section bus bar can be finished by adding another whole bus bar, if so, filling the corresponding table into the last bus bar which is the whole bus bar, and carrying out the operation of the step 20), otherwise, carrying out the next operation;
9) judging whether a long bus with the length of hzh exceeds the total length of the anchor section bus, if so, subtracting hzh from the length of the bus, and starting the operation from the step 7) again, and if not, carrying out the next operation;
10) judging whether the total length of the used anchor segment bus is exceeded by another whole bus, if so, the length of the last bus is the total length of the used bus minus the length of the accumulated bus, filling the length of the accumulated bus into a corresponding table, and performing the operation of the step 20), otherwise, 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 starting 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 performing the next operation;
13) judging whether the distance between the tail end position of the bus bar and the suspension points on the two sides and the distances between the tail end position of the bus bar and the spans on the two sides are larger than a required value or not, if not, carrying out the operation of the step 18), and if so, carrying out the next operation;
14) judging whether the arrangement of the anchor section bus bar can be finished by adding one more whole bus bar, if so, substituting the length of the short bus bar into the calculation, and starting the calculation from the step 7) again, and if not, carrying out the next calculation;
15) judging whether the length of the long bus with hzh exceeds the total length of the anchor section bus, if so, subtracting hzh from the length of the short bus, and starting the operation from the step 13) again, and if not, carrying out the next operation;
16) judging whether the total length of the used anchor segment bus bar is exceeded by adding another whole bus bar, if so, substituting the length of the short bus bar, and starting operation from the step 7) again, and if not, performing next calculation;
17) adding a whole bus to calculate the distance from the tail end of the bus to the starting position of the 1 st bus, and starting operation from the step 13);
18) recording the length of the short busbar, subsequently installing the length of the busbar, and cutting off the length of the busbar to be cut calculated in the step 13) on the basis of the busbar cut in the step 12);
19) judging whether the starting ends of the buses are in the same span and the length of the buses is larger than the length of the shortest bus after the short buses are cut, if not, substituting the length of the short buses, and starting operation from the step 13) again; if yes, the short bus with the longest length of the subsequent installation bus recorded in the step 18) is taken in, and the operation is started from the step 7) again;
20) reading the bus arrangement information to count the number of the return buses, the position and the length of the short bus, entering the next line, and starting to calculate again from the step 2) until 100008 lines of computers finish the program.
The VBA procedure is specifically as follows:
sub bus calculation ()
Sheets ("results of calculation"). Select
Cells.Select
Selection.ClearContents
Range ("a1"). Formula ═ bus bar calculation "
Range("A2").Select
Collection. formula ═ anchor segment number "
Range("B2").Select
Collection. formula ═ summary of bus bar use "
Range("C2").Select
Collection. 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
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 & "th" & nn & "root length is" & 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) ═ in total, "& nn &" root bus was used. No bus bars need to be cut. "
In total, "& nn-1&" root bus was used as Else: works (2). Cells (mdh,2) ═ in. The remaining busbars 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 description of specific embodiments of the present invention has been presented. It is to be understood that the present invention is not limited to the specific embodiments described above, and that various changes and modifications may be made by one skilled in the art within the scope of the appended claims without departing from the spirit of the invention.
Claims (10)
1. A method for calculating and managing bus usage by using excelVBA is characterized in that: the method comprises the following steps:
s1: respectively establishing a parameter input worksheet, a calculation result, a busbar use worksheet and an inventory management worksheet in Excel;
setting a calculation condition area and a field measurement area in an input parameter worksheet;
setting a bus arrangement area and a bus arrangement summary area in a calculation result chart;
setting a short bus source area and a residual short bus area in a bus use worksheet;
setting a short bus bar stock area and a whole bus bar stock area in the stock management worksheet;
s2: the following modules are respectively established in a visual basic editing area of Excel:
establishing a bus calculation module for calculating the length and the use position of the short bus;
a bus using module is established to carry out bus allocation and use and reduce the generation of short buses;
a bus later-use module is established to allocate and use the bus and reduce the use of the whole bus;
establishing a 'bus bar manual adjustment module', and replacing the bus bar with an entire bus bar when a constructor wants to reserve part of short bus bars and does not want to use the short bus bars;
establishing a busbar discharging and warehousing module, and subtracting the whole busbar and the short busbar which need to be used in the stock and adding the whole busbar and the short busbar which need to be warehoused;
s3: the following command button controls are established in the calculation results worksheet:
establishing a bus calculation command button control, and calling a bus calculation module in the step S2;
establishing a "bus use" command button control that calls the "bus use module" in step S2; establishing a command button control of ' later use of the bus ', and calling a ' later use module ' of the bus ' in the step S2;
establishing a manual adjustment command button control, and calling a bus manual adjustment module in the step S2;
establishing a command button control of 'warehouse-in and warehouse-out' in the inventory management worksheet, and calling a 'bus bar warehouse-in and warehouse-out module' in the step S2 by the command button control;
s4: and step S1, recording part parameters, related technical parameters and field measurement span in the parameter recording worksheet established in the step S1, and triggering each command button control established in the step S2 to realize corresponding functions of bus calculation, bus calling, bus discharging and warehousing management.
2. The method for calculating and managing bus usage with ExcelVBA as claimed in claim 1, wherein: the parameters entered in step S4 specifically include the bus bar terminal length, the bus bar shortest length, the bus bar connection gap shortest distance from the suspension point, and the bus bar connection gap shortest distance across.
3. The method for calculating and managing bus usage with ExcelVBA as claimed in claim 1, wherein: the field measurement span entered in step S4 includes an anchor segment number, a span, and an anchor segment length.
4. The method for calculating and managing bus usage with ExcelVBA as claimed in claim 1, wherein: the method for implementing the bus calculation function in step S4 includes: and triggering a bus calculation command button control in the 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 the used buses and the position of the short bus.
5. The method for calculating and managing bus usage with ExcelVBA according to claim 4, wherein: the specific steps of the bus calculation include:
1) resetting the calculation result worksheet and deleting the former result;
2) reading the information of the measurement area of the line being calculated, if the measurement information is empty, not calculating, reading the information of the next line until the reading completion program of 100008 lines is finished, if the data is not empty, filling in the anchor segment number, and entering the next step for carrying out the operation of the bus;
3) establishing an array 1 and an array 3, assigning the array as the distance from each suspension point to the initial position of the 1 st bus, and assigning a maximum value to the array 1 without using elements so as to facilitate later-stage Match function call;
4) establishing an array 2 and an array 4, assigning the array as the distance from each span to the initial position of the 1 st bus, and assigning a maximum value to the array 2 without using elements so as to facilitate later-stage Match function call;
5) calculate last bus allowed shortest length hzh: the maximum value of the sum of the distance from the terminal of the anchor section bus to the nearest suspension point and 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 end of the 1 st bus to the initial position of the 1 st bus;
7) judging whether the distance between the tail end position of the bus bar and the suspension points on the two sides and the distances between the tail end position of the bus bar and the spans on the two sides are larger than a required value or not, if not, carrying out the operation of step 12), and if so, recording the length of the bus bar to a corresponding table for carrying out the next operation;
8) judging whether the arrangement of the anchor section bus bar can be finished by adding another whole bus bar, if so, filling the corresponding table into the last bus bar which is the whole bus bar, and carrying out the operation of the step 20), otherwise, carrying out the next operation;
9) judging whether a long bus with the length of hzh exceeds the total length of the anchor section bus, if so, subtracting hzh from the length of the bus, and starting the operation from the step 7) again, and if not, carrying out the next operation;
10) judging whether the total length of the used anchor segment bus is exceeded by another whole bus, if so, the length of the last bus is the total length of the used bus minus the length of the accumulated bus, filling the length of the accumulated bus into a corresponding table, and performing the operation of the step 20), otherwise, 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 starting operation from the step 7) again;
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 performing the next operation;
13) judging whether the distance between the tail end position of the bus bar and the suspension points on the two sides and the distances between the tail end position of the bus bar and the spans on the two sides are larger than a required value or not, if not, carrying out the operation of the step 18), and if so, carrying out the next operation;
14) judging whether the arrangement of the anchor section bus bar can be finished by adding one more whole bus bar, if so, substituting the length of the short bus bar into the calculation, and starting the calculation from the step 7) again, and if not, carrying out the next calculation;
15) judging whether the length of the long bus with hzh exceeds the total length of the anchor section bus, if so, subtracting hzh from the length of the short bus, and starting the operation from the step 13) again, and if not, carrying out the next operation;
16) judging whether the total length of the used anchor segment bus bar is exceeded by adding another whole bus bar, if so, substituting the length of the short bus bar, and starting operation from the step 7) again, and if not, performing 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 starting operation from the step 13) again;
18) recording the length of the short busbar, subsequently installing the length of the busbar, and cutting off the length of the busbar to be cut calculated in the step 13) on the basis of the busbar cut in the step 12);
19) judging whether the starting ends of the buses are in the same span and the length of the buses is larger than the length of the shortest bus after the short buses are cut, if not, substituting the length of the short buses, and starting operation from the step 13) again; if yes, the short bus with the longest length of the subsequent installation bus recorded in the step 18) is taken in, and the operation is started from the step 7) again;
20) reading the bus arrangement information to count the number of the return buses, the position and the length of the short bus, entering the next line, and starting to calculate again from the step 2) until 100008 lines of computers finish the program.
6. The method for calculating and managing bus usage with ExcelVBA as claimed in claim 1, wherein: the bus calling function implementation method aiming at the early construction comprises the following steps: triggering a command button control of 'using the bus bar' in a bus bar using worksheet, automatically counting the number of the whole bus bar and the short bus bar calculated in the step S2 by the system, comprehensively comparing the cut residual bus bar and the stored short bus bar, calling the proper short bus bar to calculate the residual bus bar allowance, and automatically marking if the cut bus bar in the current construction is used in another anchor section using system.
7. The method for calculating and managing bus usage with ExcelVBA as claimed in claim 1, wherein: the bus calling function implementation method aiming at later construction comprises the following steps: and triggering a command button control of 'later use of the busbar' in a busbar use worksheet, automatically counting the number of the whole busbars and the short busbars calculated in the step two by the system, mainly comparing the short busbars in stock, comparing the remaining busbars cut this time, calling the proper short busbars to calculate the remaining busbar allowance, and automatically marking if the busbars cut by this time of construction are used in another anchor section by the system.
8. The method for calculating and managing bus usage with ExcelVBA as claimed in claim 1, wherein: the bus calling function implementation method aiming at manual adjustment comprises the following steps: triggering the manual adjustment command button control, the system automatically defaults to using the whole bus and calculates the bus allowance.
9. The method for calculating and managing bus usage with ExcelVBA as claimed in claim 1, wherein: triggering a 'warehouse-in and warehouse-out' command button control in the inventory management worksheet, automatically eliminating the buses required to be used in the calculation in the warehouse-in and warehouse-out accounts by the system, and recording the buses required to be warehoused in the warehouse-in and warehouse-out accounts.
10. The method for calculating and managing bus usage with ExcelVBA in accordance with claim 9, wherein: the new arrival busbar fills the arrival quantity into the warehousing column, and can also be counted into the warehouse ledger through the step.
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 true CN111914526A (en) | 2020-11-10 |
| CN111914526B 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 (8)
| Publication number | Priority date | Publication date | Assignee | Title |
|---|---|---|---|---|
| EP0572868A1 (en) * | 1992-06-03 | 1993-12-08 | Siemens Aktiengesellschaft | Catenary wire support |
| US20090177301A1 (en) * | 2007-12-03 | 2009-07-09 | Codentity, Llc | Scalable system and method for an integrated digital media catalog, management and reproduction system |
| 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 |
-
2020
- 2020-07-24 CN CN202010725382.XA patent/CN111914526B/en active Active
Patent Citations (8)
| Publication number | Priority date | Publication date | Assignee | Title |
|---|---|---|---|---|
| EP0572868A1 (en) * | 1992-06-03 | 1993-12-08 | Siemens Aktiengesellschaft | Catenary wire support |
| US20090177301A1 (en) * | 2007-12-03 | 2009-07-09 | Codentity, Llc | Scalable system and method for an integrated digital media catalog, management and reproduction system |
| 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 |
|---|
| 姜岳健;陈实;: "基于EXCEL VBA网络岗位练兵数据统计工具的开发", 数字通信世界, no. 02 * |
| 尹魁元;: "接触网刚性悬挂跨距的选择", 都市快轨交通, no. 04 * |
| 田升平;: "地铁架空刚性接触网平面设计软件的优化", 铁道标准设计, no. 03 * |
| 白凯元;: "刚性接触网拉出值布置与磨耗分析", 城市轨道交通研究, no. 06 * |
| 连瑞梅;: "基于Excel VBA工资条标题的创建方法的研究", 中国科技信息, no. 18 * |
| 黄河;: "刚性接触网预弯汇流排的参数确定", 电气化铁道, no. 05 * |
Also Published As
| Publication number | Publication date |
|---|---|
| CN111914526B (en) | 2023-10-20 |
Similar Documents
| Publication | Publication Date | Title |
|---|---|---|
| CN114037100B (en) | AI technology-based power equipment operation and maintenance method and system | |
| DE102014009492B4 (en) | machine control | |
| JP5661975B1 (en) | Storage battery control device, control method, control program, and power storage system | |
| CN111914526A (en) | Method for calculating and managing bus use by using excelVBA | |
| CN116757877B (en) | Power grid line loss reduction optimization method and system for new energy access power distribution network | |
| KR102050967B1 (en) | Operating system for energy storage device based on korean type power charges and method thereof | |
| EP2109070A1 (en) | A method and device for selecting the work flow route | |
| CN105140905A (en) | Telecommunication equipment, power supply system and power supply realization method | |
| CN115130927B (en) | Data modeling method and system | |
| EP1187397A3 (en) | Reconfiguring an Ad Hoc network | |
| CN104007721B (en) | The defeated coal of thermal power plant program control wiring diagram intelligent generation method | |
| CN103219039A (en) | Programming voltage compensation circuit | |
| CN103184948A (en) | Automatic engine stable state calibration system and automatic calibration method thereof | |
| JP2008299512A (en) | Production planning device and method, and production planning program | |
| CN114442579A (en) | Remote monitoring system of numerical control machine tool based on Internet of things | |
| CN115459258A (en) | Load accurate regulation and control method and system based on distribution automation | |
| EP1357732A1 (en) | Server for a telecommunication system and method for establishing a telecommunication connection | |
| CN110165680B (en) | Passive power distribution network voltage control method, device, control equipment and storage medium | |
| CN112967020A (en) | Order production management system and management method thereof | |
| CN113919687B (en) | Electric energy metering material stock distribution method | |
| EP1830586B1 (en) | Method and mobile terminal for providing a telecommunications connection between two terminals | |
| CN116500995A (en) | MES monitoring system and method with data interaction function | |
| CN102685231A (en) | Charging station control system | |
| CN116029647B (en) | Warehouse site management method and system for supply chain | |
| Marsh | Loop plant modeling: The feeder allocation process |
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 |