Write a sub procedure named Calculate_ TPM_Values that calculates availability ,

Question

Write a sub procedure named Calculate_TPM_Values that calculates availability,performance, quality, and overall equipment effectiveness (OEE) based on the total productive maintenance (TPM) method. TPM is a method to increase the effectiveness of production environments, especially through methods for increasing the effectiveness of equipment. The following formulas should be used to calculate the required outputs[1]:

Actual Operating Time Planned Production Time Availability = Actual Operating Time:0 Number of hours in which plant was actually operating.+' Planed Production Time:A Plant operating hours-plant shutdown time (in hours). Total Output Potential Output at Rated Speed Performance = fotal Outpu: Total quantity of units produced during the running * time including good, bad, reworked, QC samples, and product during changeover.+ Potential Output at Rated Speed Potential quantity (in units) given the designed speed of the equipment. Good Output Total output, Quality Good Output:42 Total quantity of good product (in units) produced by the process and that are available for sale without any type ofrework or re-processing. OEE = Availability × Performance × Quality,

Explanation / Answer

Procedure Calculate_TPM_Values

Sub Calculate_TPM_Values( )

Dim Actual_Operating_Time, Planned_Production_Time, Plant_Shutdown_Time As Integer

Dim Plant_Operating_Hours, Total_Output, Potential_Output_At_Rated_Speed, Good_Output As Integer

Dim Performance, Total_Output, Availability, Quality,Overall_Equipment_Effectiveness As Double

Dim MyArray(1 To 4, 1 To 4) As Array

Plant_Operating_Hours = MsgBox Sheets(“Prob1”).Range(“A1”)

Plant_Shutdown_Time =MsgBox Sheets(“Prob1”).Range(“A2”)

Total_Output = MsgBox Sheets(“Prob1”).Range(“A3”)

Potential_Output_At_Rated_Speed = MsgBox Sheets(“Prob1”).Range(“A4”)

Good_Output = MsgBox Sheets(“Prob1”).Range(“A5”)

Actual_Operating_Time= Plant_Operating_Hours

Planned_Production_Time = Actual_Operating_Time – Plant_Shutdown_Time

Availability= Actual_Operating_Time / Planned_Production_Time

Performance = Total_Output / Potential_Output_At_Rated_Speed

Quality =Good_Output / Total_Output

Overall_Equipment_Effectiveness= Availability *Performance * Quality

MyArray(1, 1) = Format(CStr(Availability), “Percent”)

MyArray(1, 2) = Format(CStr(Performance), “Percent”)

MyArray(1, 3) = Format(CStr(Quality), “Percent”)

MyArray(1, 4) = Format(CStr(Overall_Equipment_Effectiveness), “Percent”)

PrintArray MyArray, "Prob1", 10, 10

' Select the Button button, then draw a rectangle in the worksheet where you want to place the button . The 'Assign Macro dialog pops up with a list of macros in the active worksheet for you to select from. Select the 'sub procedure Calculate_TPM_Values and click OK. Rename the button as “ Calculate TPM Values” after 'you dismiss the dialog, or right click on the button any time.

End Sub

Sub Clear_TPM_Values( )

Sheets("Prob1").Cells.Clear

' Select the Button button, then draw a rectangle in the worksheet where you want to place the button . The 'Assign Macro dialog pops up with a list of macros in the active worksheet for you to select from. Select the 'sub procedure Clear_TPM_Values and click OK. Rename the button as “ ClearTPM Data” after 'you dismiss the dialog, or right click on the button any time.

End Sub

Related Questions

Navigate

• Browse (All)
• Browse W
• Subjects

---

---

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.