Published by : http://www.ijert.org International Journal of Engineering Research & Technology (IJERT) ISSN: 2278-0181 Vol. 9 Issue 08, August-2020 Computer Aided Exhaust Valve Design Automation using Creo and Microsoft Excel Spreadsheet Siraj Chabrua, P.S Kulkarnib a b M.Tech, Student, Maharashtra institute of Technology Aurangabad, 431001, India Assistant Professor, Maharashtra institute of Technology Aurangabad, 431001, India Abstract:- 3D Modeling has been widely used because of their ease in understanding, visualization, and generation of manufacturing drawing compared to 2D drafting. However modeling process is a time consuming and many draftsmen do not have the skills of performing such task. Exhaust valve is one of the component reputedly required nearly in all ic engines. It is the nose of the combustion engine to return exhaust gases out at a time of exhaust stroke. There are many tools and computer languages like C, C++, Python, Visual basic, Go language etc useful for task automation. In this paper we are automate the designing and solid modeling task of exhaust valve by creating graphical user tool to take input parameters required for exhaust valve design using visual basic programming language into the excel spreadsheet and integrate this spreadsheet with Creo or Pro-Engineering parametric tool for 3D solid modeling automation. To demonstrate our study we are considered to automate the exhaust valve design and solid model generation made by authors S.S Gawale & Dr S.N Shelke(8). Keywords: Microsoft Excel, Modeling Automation, Parametric Modeling, Graphical user interface, Visual basic for application, Computer aided design. Nomenclature GUI Graphical user interface Pro-E Pro/Engineering MS Microsoft spreadsheet API Application programming interface 1. INTRODUCTION Exhaust valve is the Mechanical component useful to push the exhaust gases out form the combustion chamber at a time of exhaust stroke. The valve should be well design to fit in size with outlet port of the internal combustion engine. The exhaust valve is useful in both petrol and diesel engines. The design of valve is based on various parameters like inlet port diameter, cylinder bore, stroke, and Engine rpm, Mean velocity of gas, type of material used to manufacture, and pressure acting on the valve head and different parameters of the internal combustion engine. Different parameters lead to different valve dimension hence automation is need. By using excel spreadsheet and creo parametric it is possible to automate the 3d solid modeling of the exhaust valve. 2. LITERATURE REVIEW Many attempts has researcher been made in area of parametric Modeling and there relations. [1] Song-Hao Wang et al are presented the unique idea of liquid foot packaging product design using excel spreadsheet and the parametric relation in Pro-Engineering wildfire. [2] Umesh and Laukik are presented the development of 3D model with the graphical user interface integrating with the excel spreadsheet and the Cre-O parametric for 3d modeling by considering CI Engine as study point of view. [3] Yuanyuan and Zailiang are describe the parametric spar gear generation by using pro programming toolkit and relation into pro wildfire. They made a graphical tool to create the spar gear by just entering the parametric valve using visual basic programming language. [4] Patel and Thakkar is presented the idea for interrelating through MS Excel and C language by the case study of pulley design. [5] H.M.A Hussein is demonstrate the blanking die design based on the available data by using Catia v5 scripting and the visual basic. He constructed knowledge based expert system for blanking dies. [6] QingMing Fan et al is presented their work to automate the development of standard die set library by family table in wildfire 2.0 interface by the visual c++ and Pro/Toolkit. [7] Bhaval B Shah an attempt has been made to integrate Autodesk Inventor with Microsoft Excel spreadsheet for creation of modeling and manufacturing drawing. They considered a flange coupling as an example for their study point of view. [8] Snehal and Dr S.N.Shelke are designed the exhaust valve and studied the stress concentration by varying different fillet parameters for the diesel engine. Further best material is recommended. 3. METHODOLOGY FOR MODELING AUTOMATION Cre-O is the most popular Application in the Engineering field. It has verity of applications not only in the engineering but where there is need of 3d solid modeling visualization and simulation of the components. Many feature s are available in the creo one of useful feature is Pro/Program. Pro/program runs at the background during the creo session and it changes and update automatically feature component and the relation added to the part of assembly. The Pro/Program gives parameters dimensions IJERTV9IS080211 www.ijert.org (This work is licensed under a Creative Commons Attribution 4.0 International License.) 490 Published by : http://www.ijert.org International Journal of Engineering Research & Technology (IJERT) ISSN: 2278-0181 Vol. 9 Issue 08, August-2020 and the relation of the part. Pro/Program reflects all the data in the text data form this data can be modified to add new feature, update the model, and delete existing feature and change the dimensions of the feature. With the proper user interface (API) the user can directly create and modify the 3d model by just giving the input value. Here in the Pro/Engineering the Microsoft excel spreadsheet is integrated with the excel analysis feature. Excel analysis is the feature useful to interact to the excel cells text data in the Creo. Excel analysis tool transfer the excel spreadsheet data to the Pro/E database. There data can related to the part feature data. Hence the spreadsheets data is equal to the data of the part that is dimensions of the model. By just changing and upgrading the data of the spreadsheet will upgrade the dimensions of the Part. 3.1 Creating Microsoft Excel Spreadsheets Microsoft excel sheet is the set of empty boxes generally called as cells integrated by different mathematical relations to add, remove and modify the text data from the cells. An excel spreadsheet containing the valve design parameters names with respective to feature and the dimension’s has been prepared. The customized Engine Specification buttons is added near to the dimensions of the valve to open up the graphical user form to take input for further processing. Similarly save button is added to automatically save the all spreadsheet data once user click on the button as show in figure 1. The excel spreadsheet file is saved as macro-enable excel workbook .xlsm file format. Figure 1: Excel Interface. 3.2 Create Graphical User Tool Graphical user tool is the type of user form useful to take engine parameters as input values from the user in the text form. Graphical tool has been create using visual basic editor available In Microsoft excel spreadsheet under the developer option. Graphical tool is combined with engine specification button Figure 1 as soon as user click on engine specification button graphical user tool will be popup Figure 2 represent the smart look named as graphical user tool. It is the real time interaction when user click the Engine specification button. Later manually insert the values of parameters mentioned in the graphical user tool responsible to design the Exhaust valve. We are inserted engine specifications of valve design made by Gawale, S. S., & Shelke D. S (8). The program has been wrote behind the user tool such as to get resultant valve specification values from the program directly into the excel cells when user click on the create button figure 1 that is our Exhaust valve design dimensions. Program Behind Create Button Private Sub CommandButton1_Click () ''''''cylinder_bore = Cb ''''''' ''''''Cylinder_Stroke = Cs ''''' ''''''Area_of_cyliner = Ac ''''' ''''''Mean_Velocity_of_piston = Vp '''''' ''''''Engine_rpm = Erpm '''''' ''''''Exhaust_port_Diameter = d1 ''''''' ''''''Mean_velocity_of_gas = Vg '''''' ''''''thickness_of_valve_Disk = t ''''' '''''' Value_of_K = k '''''' ''''''Maxmum_gas_pressure = Pg '''''' '''''permissible_bending_stress = Sb ''''' '''''valve_head_Diameter = d2 ''''' '''''Diameter_of_valve_head_opining_Area = d3 ''''' '''''Diameter_of_valve_steam = ds ''''' '''''Length_of_valve_steam = Ls''''' '''''Chamfer_Length = CL '''' '''''Fillet Radius = Fr '''' Dim Cb As Double Dim Cs As Double Dim Ac As Double Dim Vp As Double IJERTV9IS080211 Dim Erpm As Double Dim d1 As Double Dim Vg As Double Dim t As Double Dim k As Double Dim Pg As Double Dim Sb As Double Dim d2 As Double Dim d3 As Double Dim A As Double Dim B As Double Dim ds As Double Dim Ls As Double Dim Fr As Double Dim CL As Double If Me.TextBox1.Value = "" Or VBA.IsNumeric(Me.TextBox1.Value) = False Then MsgBox ("Insert Numeric Value of Cylinder Bore") Exit Sub End If If Me.TextBox2.Value = "" Or VBA.IsNumeric(Me.TextBox2.Value) = False Then www.ijert.org (This work is licensed under a Creative Commons Attribution 4.0 International License.) 491 Published by : http://www.ijert.org International Journal of Engineering Research & Technology (IJERT) ISSN: 2278-0181 Vol. 9 Issue 08, August-2020 MsgBox ("Insert Numeric Value of Cylinder Stroke") Exit Sub End If If Me.TextBox3.Value = "" Or VBA.IsNumeric(Me.TextBox3.Value) = False Then MsgBox ("Insert Numeric Value of Engine RPM") Exit Sub End If If Me.TextBox4.Value = "" Or VBA.IsNumeric(Me.TextBox4.Value) = False Then MsgBox (" Insert Numeric Value of Mean velocity ") Exit Sub End If If Me.TextBox5.Value = "" Or VBA.IsNumeric(Me.TextBox5.Value) = False Then MsgBox ("Insert Numeric Value of Maximum Gas Pressure") Exit Sub End If If Me.TextBox6.Value = "" Or VBA.IsNumeric(Me.TextBox6.Value) = False Then MsgBox ("Insert Numeric Value of Maximum Bending stress ") Exit Sub End If If Me.TextBox7.Value = "" Or VBA.IsNumeric(Me.TextBox7.Value) = False Then MsgBox ("Insert Numeric Value of Total Length of valve") Exit Sub End If If Me.TextBox8.Value = "" Or VBA.IsNumeric(Me.TextBox8.Value) = False Then MsgBox ("Insert Numeric Value of Fillet Radius") Exit Sub End If If Me.ComboBox1.Value = "" Then MsgBox (" Please Select Material Type ") Exit Sub End If Cb = TextBox1.Value Cs = TextBox2.Value Erpm = TextBox3.Value Vg = TextBox4.Value Pg = TextBox5.Value Sb = TextBox6.Value Ls = TextBox7.Value Fr = TextBox8.Value d1 = Cb * Sqr((Erpm) / (Vg)) If Me.ComboBox1.Value = "Steel" Then k = 0.41 Else k = 0.54 End If IJERTV9IS080211 t = k * d1 * (Sqr(Pg / Sb)) d2 = d1 + (2 * (t * 0.7071)) since d2 = d1 + (2 * (t * sin(90alpha)))’’ d3 = Sqr((d1) ^ 2 + (d2) ^ 2) A = 0.7854 * ((d3) ^ 2 - (d2) ^ 2) B = 0.7854 * (d1) ^ 2 If A >= B Then MsgBox "Your Design is Safe" Else MsgBox "Your Design is Not Safe" End If ds = ((d1) / 8) + 4 CL = 0.5 * (d2 - d1) ‘‘‘‘Insert parameters into cells''''' Cells (1, 2) = d2 Cells (2, 2) = t Cells (4, 2) = ds Cells (5, 2) = Ls Cells (6, 2) = Fr Cells (3, 2) = CL End Sub Program behind Clear Button Private Sub CommandButton2_Click () TextBox1.Value = "" TextBox2.Value = "" TextBox3.Value = "" TextBox4.Value = "" TextBox5.Value = "" TextBox6.Value = "" TextBox7.Value = "" TextBox8.Value = "" ComboBox1.Value = "" End Sub Program behind Close Button Private Sub CommandButton3_Click () Form_valve.Hide End Sub Private Sub UserForm_Activate() With Me.ComboBox1 .Clear .AddItem "Select" .AddItem "Steel" .AddItem "Cast Iron" End With End Sub www.ijert.org (This work is licensed under a Creative Commons Attribution 4.0 International License.) 492 Published by : http://www.ijert.org International Journal of Engineering Research & Technology (IJERT) ISSN: 2278-0181 Vol. 9 Issue 08, August-2020 The Program has been wrote so that it will automatically calculate the valve dimension from parameters of engine specifications figure 2. Program is well developed considering all the conditions the program automatically clear all the valves from the boxes when user click of the clear button. GUI will be disappear when click on close button. This tool will suggest either the design is safe or not based on the parameters user inserted into the graphical user form. Figure 2: Graphical User Tool 3.3 Creating the 3d Model A 3d part of the component is design with proper feature and dimensions. The accurate design of the 3d solid object leads to better accuracy and visualization. Different constraints has been applied to the model. Figure 3 represent the 3d object with constraints and the dimension obtained from the visual basic program as per the Engine specifications into the excel spreadsheet and this output assigned to the solid part. Figure 3: Valve Terminology 3.4 Transferring the data from Excel spreadsheet to Pro/E To transfer inside data of excel sheet to the Pro/E Excel Analysis option should be selected available in the options menu bar in Pro/E as show in the figure 4. IJERTV9IS080211 www.ijert.org (This work is licensed under a Creative Commons Attribution 4.0 International License.) 493 Published by : http://www.ijert.org International Journal of Engineering Research & Technology (IJERT) ISSN: 2278-0181 Vol. 9 Issue 08, August-2020 Figure 4: Excel Analysis As soon as we click on the excel analysis icon new Excel analysis dialog box will be popup. The excel sheet we have created before is loaded into using “load file” option button from the dialog box. Next click on the “output cells” icon. Small menu manager dialog box will popup. After without touching to menu manager select all the cells range from the excel spreadsheet in which all the parametric dimensions values related to the feature is present then click on “done sel” option from the menu manager dialog box. Later select the compute button Pro/E API will automatically transfer the data from the excel spreadsheet. All the cells range data we selected will be appeared in the result section of the dialog box as show in the Figure 5 with smart numbered bullets in our case. Figure 5: Data Transfer After that click on the add feature option button give the proper name of the features to save this data into the Pro/E database as the feature before closing the Excel analysis dialog box save feature will get automatically add to the design tree as show in the figure 6. IJERTV9IS080211 www.ijert.org (This work is licensed under a Creative Commons Attribution 4.0 International License.) 494 Published by : http://www.ijert.org International Journal of Engineering Research & Technology (IJERT) ISSN: 2278-0181 Vol. 9 Issue 08, August-2020 Figure 6: Save Feature. 3.5 Assigning data with part feature To relate the excel spreadsheet data and the Creo solid part feature data special command exist in the modal option from the intent menu called relations. Select the Relation button relation dialog box will be open then select all feature from the model tree as soon as we select the feature its relative dimension will be flash as show in Figure 7 then select the feature as d3 in our case. then go to insert→from list then parametric dialog box will be appear next click on look in menu and select feature option then click on the feature name as saved previously suddenly all the dimensions added from the excel spreadsheet will be popup now select respective parameter then click on the insert selected icon button as show in Figure 8 in such a way relation will get assigned to respective valve feature. Figure 7: Feature Relation Feature 8: Features with Dimension’s IJERTV9IS080211 www.ijert.org (This work is licensed under a Creative Commons Attribution 4.0 International License.) 495 Published by : http://www.ijert.org International Journal of Engineering Research & Technology (IJERT) ISSN: 2278-0181 Vol. 9 Issue 08, August-2020 Similarly do the same procedure one by one to relate feature and the dimension. such a way new relations has been created between the every feature and the dimension from the excel spreadsheet to the Pro/E solid Part finally verify the relation as per the dimension and click on ok which makes Excel and Pro/E completely integrated Now save all the component setup for future reference Show in Figure 9. Figure 9: Relation with Respective Feature Finally we integrate the excel spreadsheet and the creo parametric to automate the design and solid modeling generation tasks. User is now ready to make a solid 3d object of exhaust valve by just entering the input parameters needed to design exhaust valve into the graphical user tool by click on engine specification button form excel spreadsheet. Graphical user tool will automatically calculate the dimensions of exhaust valve and save it into excel spreadsheet save the sheet by clicking on save button. Now update the model from the Creo feature tree it will automatically assign the valve dimensions as per the MS sheet data. 3D generated solid object of exhaust valve as per the engine specification is show in figure 10. IJERTV9IS080211 www.ijert.org (This work is licensed under a Creative Commons Attribution 4.0 International License.) 496 Published by : http://www.ijert.org International Journal of Engineering Research & Technology (IJERT) ISSN: 2278-0181 Vol. 9 Issue 08, August-2020 Figure 10: 3D Solid Exhaust Valve 4. PROCEDURE FOR MODIFYING THE MODEL 3D solid modelling of valve is totally depends on the parameters provided into the excel spreadsheet. If we need to modify the model then click on edit definition from Creo tree then save and regenerate the model basic procedure is shown in flow chart 1. Flow chart 1. IJERTV9IS080211 www.ijert.org (This work is licensed under a Creative Commons Attribution 4.0 International License.) 497 Published by : http://www.ijert.org International Journal of Engineering Research & Technology (IJERT) ISSN: 2278-0181 Vol. 9 Issue 08, August-2020 5 CONCLUSION A generic solid model of exhaust valve and the Microsoft Excel spreadsheet has been created. Graphical user tool is created inside the Microsoft excel sheet to automate the exhaust valve design procedure. Later all the data from the Microsoft excel sheet has been transferred to Creo by using excel analysis tool. All the transfer data then relate to the respective tool feature using relation tool. In this way Microsoft excel sheet is relate to the component in creo for solid model creation. User can also create their custom valve as per their need by just modifying the excel sheet data. This takes very less time to generate the 3D solid model compared to make them manually. Our study demonstrate automation technique for exhaust valve design calculation and solid model generation. Similar technique is useful to design 3D solid model of exhaust valve based on different engine specification of different dimensions. In future this model is further proceed to export for analysis purpose. Even it would be easy to generate the NC program to manufacture the components. ACKNOWLEDGEMENT All the work is done under guidance of Maharashtra institute of technology Aurangabad, special thanks to Mechanical department for their support. REFERENCES. [1] Wang, S. H., Melendez, S., & Tsai, C. S. (2008). Application of parametric sketching and associability in 3D CAD. Computer-Aided Design and Applications, 5(6), 822-830. [2] Bedse, U. A. Developing a GUI based Design Software in VB Environment to Integrate with CREO for Design and Modeling of CI Engine. International Journal of Latest Trends in Engineering and Technology, 6(4), 373-381. [3] Zheng, Y., & Chen, Z. (2011, August). Parametric design of straight spur gears based on Pro/E. In Proceedings of 2011 International Conference on Electronic & Mechanical Engineering and Information Technology (Vol. 9, pp. 4585-4589). IEEE. [4] Thakkar, A. M., & Patel, Y. D. (2012). Integration of Pro\Engineer with Excel and C Language for design automation. International Journal of Engineering Research & Technology (IJERT), 1(4), 2-4. [5] Hussein, H. M. A. (2014). Computer aided blanking die design using CATIA. Procedia Cirp, 18, 96-101. [6] Fan, Q., Liu, G., & Wang, W. (2011). Development of Die Sets standard parts library based on Pro/E. Procedia Engineering, 15, 3802-3807. [7] Shah, D. B. (2013). Parametric Modeling and Drawing Automation for Flange Coupling using Excel Spreadsheet. International Journal of Research in Engineering and Technology, Impact Journal, 187-192. [8] Gawale, S. S., & Shelke, D. S. (2016). Diesel engine exhaust valve design and optimization. IQSR Journal of Mechanical and Civil Engineering, 13(04). [9] R.S Khurmi and J.K Gupta, Fundamentals of Machine design, 2018, McGraw-Hill Publication. [10] Jelen, Bill and Syrstad, Tracy, Microsoft Excel 2019 VBA and Macros, 2019, Pearson Education Inc. IJERTV9IS080211 www.ijert.org (This work is licensed under a Creative Commons Attribution 4.0 International License.) 498