Optimization of material delivery time analysis by using Visual Basic for applications in Excel

[EN] In small and medium enterprises, huge numbers of reports are prepared regarding the procurement, planning, production etc on daily, weekly, monthly, quarterly and yearly basis. If the interval between preparation of same report is longer then there is the greater probability that employee can forget the procedure of preparing the report; thus it is beneficial to automate these types of reports especially when there is no cost required for automation. In the same way, material delivery analysis is a kind of report which is prepared on monthly basis and as per the employees` feedback they usually forget some of the steps of report formation; therefore, there was the chance of mistake at the end of employee. Therefore, this report was automated for to minimize the chance of error and report preparation time. All the manual tasks were enlisted and were programmed for automation by the help of VBA macros. For the execution of macros, userform was designed in visual basic editor (in MS excel) consisted on four command buttons and macros were called on command buttons` click. On pressing ‘ctrl + q’ userform used to appear on the screen. Furthermore, the comparison of old and automated methods was conducted to reflect the best suitable method. The report used to take 1.55 minutes to be made manually. Comparison of time of both methods of report formation indicated that suggested method took 70.86% less time as compared to the old method of preparing material delivery time analysis report.Authors of present research paper (representing the automation of one the routine reports) want to thank the concerned employee of ABC Company at its purchase department for his generous cooperation in the automation period of the report.Khan, MA.; Kalwar, MA.; Chaudhry, AK. (2021). Optimization of material delivery time analysis by using Visual Basic for applications in Excel. Journal of Applied Research in Technology & Engineering. 2(2):89-100. https://doi.org/10.4995/jarte.2021.14786OJS8910022Abidin, I.Z., Juahir, H., Azid, A., Mustafa, A.D., & Azaman, F. (2015). Application of Excel-VBA for computation of water quality index and air pollutant index. Malaysian Journal of Analytical Sciences, 19(5), 1056-1064.Abraham, R., Burnett, M., & Erwig, M. (2009). Spreadsheet Programming. In Wiley Encyclopedia of Computer Science and Engineering, B.W. Wah (Ed.). https://doi.org/10.1002/9780470050118.ecse415Ahmadi, A., Robinson, P.H., Elizondo, F., & Chilibroste, P. (2018). Implementation of CTR dairy model using the visual basic for application language of Microsoft excel. International Journal of Agricultural and Environmental Information Systems, 9(3), 74-86. https://doi.org/10.4018/IJAEIS.2018070105Bartoszewicz, G., & Wdowicz, M. (2019). Automation of the Process of Reporting the Compliance of the Production Plan with Its Execution Based on Integration of SAP ERP System In Connection With Excel Spreadsheet and VBA Application. Digitalization of Supply Chains. https://doi.org/10.17270/b.m.978-83-66017-86-3Belchior Junior, A., Bruel, R.N., Andrade, D.A., Sabundjian, G., Macedo, L.A., Angelo, G., … Conti, T.N. (2011). Development of a Vba Macro-Based Spreadsheet Application for Relap5 Data Post-Processing. In International Nuclear Atlantic Conference (pp. 978-85).Bernard, M., Dwi Minarti, E., & Hutajulu, M. (2018). Constructing Student's Mathematical Understanding Skills and Self Confidence: Math Game with Visual Basic Application for Microsoft Excel in Learning Phytagoras at Junior High School. International Journal of Engineering & Technology, 7(3.2), 732-736. https://doi.org/10.14419/ijet.v7i3.2.18738Blattner, D.E., & Valrico, F. (2007). Automatic Report Generation. United States.Blayney, P.J., & Sun, Z. (2019). Using Excel and Excel VBA for Preliminary Analysis in Big Data Research. In Managerial Perspectives on Intelligent Big Data Analytics. IGI Global (pp. 110-136). https://doi.org/10.4018/978-1-5225-7277-0.ch007Chatvichiencha, S. (2015). Enhancing Computational Thinking by Excel-VBA Based Problem Solving. In The 2nd International Conference on Innovation in Education (pp. 284-290).Chaudhry, A.K., Kalwar, M.A., Khan, M.A., & Shaikh, S.A. (2021). Improving the Efficiency of Small Management Information System by Using VBA. International Journal of Science and Engineering Investigations, 10(111), 7-13.Cirujano, J., & Zhu, Z. (2013). Automatic reporting for manpower resources. Proceedings, Annual Conference - Canadian Society for Civil Engineering, 1(January), 710-719.Ding, H.L., Qi, K.Y., Zhao, X.L., & Xu, G.F. (2017). Tibetan Typographical Specifications and Technical Realization Based on Word VBA. In 4th International Conference on Advanced Education and Management (pp. 407-412). https://doi.org/10.12783/dtssehs/icaem2017/19117Evensen, H.T. (2014). A versatile platform for programming and data acquisition: Excel and Visual Basic for Applications. In ASEE Annual Conference and Exposition. https://doi.org/10.18260/1-2--20017Harahap, M.I. P., & Azmi, M.H. (2017). Development of Excel Vba Program for Small Drainage Network. E-Academia Journal, 6(1), 216-227.Hart-Davis, G. (2005). Making Decisions in Your Code. In Mastering MIcrosoft VBA (2nd ed., p. 202).Hila, R. (2009). Water Quality Data Management Database. Society.Kalwar, M.A., & Khan, M.A. (2020a). Increasing performance of footwear stitching line by installation of auto-trim stitching machines. Journal of Applied Research in Technology & Engineering, 1(1), 31-36. https://doi.org/10.4995/jarte.2020.13788Kalwar, M.A., & Khan, M.A. (2020b). Optimization of Procurement & Purchase Order Process in Foot Wear Industry by Using VBA in Ms Excel. International Journal of Business Education and Management Studies, 5(2), 80-100. Retrieved from https://www.ijbems.com/doc/IJBEMS-124.pdf [Accessed December 2020].Kalwar, M.A., Khan, M.A., & Malik, A.J. (2020). Formulation of Mathematical Model for Maximization of Profit: Case of Leather Fotowear Company. International Research Journal of Computer Science and Technology, 1(1), 54-70.Kalwar, M.A., Khan, M.A., & Shaikh, S.A. (2020). Analysis of Driving Anger: Case Study of Mehran UET, Jamshoro. International Research Journal of Computer Science and Technology, 1(1), 13-37.Kalwar, M.A., Khan, M.A., Shaikh, S.A., Salam, A., Memon, M.S., & Khaskheli, S.A. (2020). Aggressive Driving Behavior: A Case Study of Mehran UET,. In Proceedings of the International Conference on Industrial Engineering and Operations Management Dubai, (pp. 2350-2359). Dubai, UAE. Retrieved from http://www.ieomsociety.org/ieom2020/papers/175.pdf [Accessed December 2020].Kalwar, M.A., Khaskheli, S.A., Khan, M.A., Siddiqui, A.A., & Gopang, M.A. (2018). Comfortable Waiting Time of Patients at the OPD with Varying Demographics. Industrial Engineering Letters, 8(2), 20-27. Retrieved from https://core.ac.uk/download/pdf/234685697.pdf [Accessed December 2020].Kalwar, M.A., Mari, S.I., Memon, M.S., Tanwari, A., & Siddiqui, A.A. (2020). Simulation Based Approach for Improving Outpatient Clinic Operations. Mehran University Research Journal of Engineering and Technology, 39(1), 153-170. https://doi.org/10.22581/muet1982.2001.15Kalwar, M.A., Marri, H.B., & Khan, M.A. (2021). Performance Improvement of Sale Order Detail Preparation by Using Visual Basic for Applications: A Case Study of Footwear Industry. International Journal of Business Education and Management Studies, 3(1), 1-22.Kalwar, M.A., Shaikh, shakeel A., & Khan, M.A. (2020). Optimization of Target Calculation Method for Leather Skiving and Stamping: Case of Leather Footwear Industry. International Journal of Education and Management Studies, 7(1), 15-30.Kalwar, M.A., Shaikh, shakeel A., & Khan, M.A. (2021). Statistical Analysis of Driving Impatience Among Vehicle Drivers: A Case Study. International Journal of Education and Management Studies, 2(1), 1-32.Khaskheli, S.A., Kalwar, M.A., Siddiqui, A.A., Nagar, M.A. K., & Wadho, T.H. (2018). Impatience Among Drivers With Varying Demographics. In Professional Trends in Industrial and Systems Engineering (pp. 465-469). UET, Peshawar.Khaskheli, S.A., Marri, H.B., Nebhwani, M., Khan, M.A., & Ahmed, M. (2020). Compartive Study of Queuing Systems of Medical Out Patient Departments of Two Public Hospitals. In Proceedings of the International Conference on Industrial Engineering and Operations Management (Vol. 1913, pp. 2702-2720). Dubai, UAE. Retrieved from http://www.ieomsociety.org/ieom2020/papers/177.pdf [Accessed December 2020].Kuka, S., & Karamani, B. (2011). Using Excel and VBA for Excel to Learn Numerical Methods. In 1st International Sympsosium on Computing in Informatics and Mathematics (pp. 365-376).Lessa, D.J.R., Lessa, F.P.R., Magalhães Junior, P.A.A, & Guimarães, H.V. (2016). Mathematical Model and Programming in VBA Excel for Package Calculation. International Journal of Engineering Research and Applications, 6(5), 55-61.Minto, C.F. (2009). PKPD Tools for Excel.Mustafa, A., & Hatemi-J, A. (2020). A VBA module simulation for finding optimal lag order in time series models and its use on teaching financial data computation. Applied Computing and Informatics. https://doi.org/10.1016/j.aci.2019.04.003Norton, T., & Tiwari, B. (2013). Aiding the understanding of novel freezing technology through numerical modelling with visual basic for applications (VBA). Computer Applications in Engineering Education, 21(3), 530-538. https://doi.org/10.1002/cae.20498Porter, D., & Stretcher, R. (2012). Automating Markowitz Optimizations Using VBA. Journal of Instructional Techniques in Finance, 4(1), 9-16.Sato, K., & Yokoyama, R. (2001). Teaching Aid for Remote Sensing and Map Imagery Analysis Using Excel Spreadsheet and VBA. In 22nd Asian Conference on Remote Sensing. Retrieved from http://www.crisp.nus.edu.sg/~acrs2001/pdf/015venka.pdf [Accessed December 2020].Walkenbach, J. (2013). Microsoft Excel VBA Programmining.Walkenbach, J. (2015). Controling Program Flow and Making Decisions. In 3rd (Ed.), Excel VBA Programming For Dummies (p. 153).Wettlaufer, G.J. (2010). Merlin.Net Automation of External Reports Verification Process. California Polytechnic State University. https://doi.org/10.1558/jsrnc.v4il.24Yan, Q., & Wan, Y. (2017). Using the special font and VBA program to make bill of materials in the transmission line engineering. Revista de La Facultad de Ingeniería, 32(2), 335-341

Uso da linguagem VBA-Excel para desenvolvimento de um programa de simulação e ajuste de parâmetros em modelos de processos fermentativos alcoólicos

Em função da disponibilidade e facilidade de uso, o Excel está presente nas rotinas de estudantes, profissionais, empresas e universidades. O seu uso para programação apresenta vantagens e diversas possibilidades de aplicação. Com a crescente demanda da aplicação industrial para a fermentação alcoólica, a simulação se faz necessária para a obtenção de respostas rápidas. O objetivo deste trabalho foi desenvolver um programa de simulação e ajuste dos parâmetros de modelos em processos fermentativos alcoólicos, utilizando a linguagem VBA do Excel. O usuário faz a seleção de modelos de crescimento de microrganismo e formação do produto, seguida da inserção de parâmetros vinculados a esses modelos. A simulação é feita por meio de integrações de equações diferenciais ordinárias, método de Runge-Kutta de 4ª ordem, obtendo como respostas, as concentrações de microrganismo, produto e substrato ao longo do tempo. Por meio da ferramenta Solver, é feito o ajuste dos parâmetros para minimizar o erro entre os dados teóricos e experimentais. O programa mostrou-se eficaz para a simulação proposta além de permitir o tratamento das respostas e comparação entre os modelos utilizados. DOI: 244694160102201501

Pseudo-Component Viscoelastic Modeling of Soft Tissues

Understanding the response of human tissues to externally imposed stress and strain is critical to improving quality of life. This work explored regression approaches and modeling that aid discovery and describe mechanisms of soft tissue structures. The scope of the study is to develop a non-linear, multi-component, viscoelastic model using pseudo-component approach and to optimize the model parameters to best match the time-dependent experimental stress-strain data. This study also does qualitative testing to test the appropriateness and utility of the model. The models are developed using nonlinear optimization technique in MS Excel/ VBA for convenient access for others. Six composite models were developed using various combinations of viscoelastic components namely hyper-elastic spring, spring-and-dashpot, reform and retain component. Various optimization techniques such as RLM2, Cyclic and Leapfrogging were tested using the experimental stress time data and it was found that Leapfrogging was the most efficient and robust optimizer. 8 parameter models showed sufficiency in the number of parameters by best modeling PLGA, PCL and SIS data. The appropriateness of the models was studied by testing the models on literature data and also testing the models by generating cyclic stress-strain data. The models proved to be appropriate by returning expected results for the tests. The six composite models are made available for others along with the nonlinear optimization techniques as MS Excel/ VBA code.School of Chemical Engineerin

Optimized solar water heater for Scottish weather conditions

The ICSSWH (Integrated Collector Storage Solar Water Heater), apart from being the oldest is also the most economical means of solar water heating. The effect of modifications in the collector geometry has frequently appeared in the literature, and over the time, has resulted in different designs. These designs differ mainly on the site of installation as well as the target application. A novel design created by Professor T Muneer at Napier University, utilizes fins inside the storage tank to improve the heat transfer. The reported field tests of the fin type ICSSWH have indicated a 10 % improvement in the solar fraction compared to unfinned. As ICSSWH is the lowest cost, it presented a possible solution for the Scottish market where Energy Saving Trust estimates that 50% of hot water demand of a household can be met through solar water heating. In the present research program, two prototype collectors (one with- and one without fins) were fabricated at Napier University by the author. The collectors were then tested in the laboratory on identical experimental schemes to determine their respective performance. Simulations based on analytical modelling were then undertaken, using numerous experimental data for validation that were obtained during the course of the present work. A high degree of conformance was noted between the two. Furthermore, for a deeper insight, Computational Fluid Dynamics (CFD) analysis was performed. This research focuses on the qualitative and quantitative assessment of the heat transfer due to the fins. The difference in the performance with respect to the change in tilt angle was also studied. This study can therefore be utilised for developing solar water heating systems for any given location. Note that there is an intrinsic link between the latitude and inclination angle of the collector plate. The nocturnal loss from the collector is the principal factor that has inhibited the wide spread use of the ICSSWH. The remedies to abate this problem have therefore been attempted. In addition to that, the collectors have to be integrated with the domestic hot water supply system and plumbing considerations have to be incorporated within the overall design. The supply circuits were worked out and the measures for freeze and boil protection were devised. The optimization of any system is an intrinsic task particularly when several variables are involved. On the basis of the obtained results from simulations and experiments, the influential design variables were identified. The values for these variables which lead to the optimal design in terms of thermal performance were assessed while keeping the overall manufacturing cost of the collector as low as possible. Recommendations for future work have also been presented.EThOS - Electronic Theses Online ServiceGBUnited Kingdo

Optimized solar water heater for Scottish weather conditions

The ICSSWH (Integrated Collector Storage Solar Water Heater), apart from being the oldest is also the most economical means of solar water heating. The effect of modifications in the collector geometry has frequently appeared in the literature, and over the time, has resulted in different designs. These designs differ mainly on the site of installation as well as the target application. A novel design created by Professor T Muneer at Napier University, utilizes fins inside the storage tank to improve the heat transfer. The reported field tests of the fin type ICSSWH have indicated a 10 % improvement in the solar fraction compared to unfinned.As ICSSWH is the lowest cost, it presented a possible solution for the Scottish market where Energy Saving Trust estimates that 50% of hot water demand of a household can be met through solar water heating.In the present research program, two prototype collectors (one with- and one without fins) were fabricated at Napier University by the author. The collectors were then tested in the laboratory on identical experimental schemes to determine their respective performance. Simulations based on analytical modelling were then undertaken, using numerous experimental data for validation that were obtained during the course of the present work. A high degree of conformance was noted between the two. Furthermore, for a deeper insight, Computational Fluid Dynamics (CFD) analysis was performed.This research focuses on the qualitative and quantitative assessment of the heat transfer due to the fins. The difference in the performance with respect to the change in tilt angle was also studied. This study can therefore be utilised for developing solar water heating systems for any given location. Note that there is an intrinsic link between the latitude and inclination angle of the collector plate. The nocturnal loss from the collector is the principal factor that has inhibited the wide spread use of the ICSSWH. The remedies to abate this problem have therefore been attempted. In addition to that, the collectors have to be integrated with the domestic hot water supply system and plumbing considerations have to be incorporated within the overall design. The supply circuits were worked out and the measures for freeze and boil protection were devised.The optimization of any system is an intrinsic task particularly when several variables are involved. On the basis of the obtained results from simulations and experiments, the influential design variables were identified. The values for these variables which lead to the optimal design in terms of thermal performance were assessed while keeping the overall manufacturing cost of the collector as low as possible. Recommendations for future work have also been presented

Chromosome segregation and recombination in human meiosis: Clinical applications and insight into disjunction errors

Chromosome copy number errors (or aneuploidy) of gametes and embryos occurs in humans more frequently than in any other studied species, with a spectrum of manifestations from implantation failure to affected live births. It is predominantly problem arising in maternal meiosis with at least 20% of oocytes being aneuploid, a proportion that increases dramatically with advancing maternal age. Currently the only intervention to reduce the chances of transmitting aneuploidy is by invasive embryo biopsy procedures in high-risk groups (mainly patients with advanced maternal age) undergoing in-vitro fertilisation. Despite the severity of this problem, aneuploidy of the human preimplantation embryo is relatively poorly understood. With this in mind the purpose of this thesis is to explore the premise underpinning the use of preimplantation genetic screening (PGS) in human embryos and investigate its clinical applications and current methodologies. A series of published works demonstrate what I believe to be a significant contribution to the development of applications for studying human preimplantation aneuploidy, also providing insight into its origins and mechanisms at the earliest stages of human development. Specifically, I present a novel standard set of protocols as a general reference work from practitioners in the fields of embryo biopsy and array comparative genomic hybridisation (CGH - the current ‘gold standard’ for preimplantation aneuploidy screening). I present a summary of work encapsulated in three published clinical papers using a linkage based analysis of Single Nucleotide Polymorphism (SNP) karyotypes (Karyomapping). Karyomapping was designed as a near-universal approach for the simultaneous detection of chromosomal and monogenic disorders in a PGS setting and these results demonstrate the utility of the technique in three separate scenarios. In order to study the underlying mechanisms of female meiosis I present my findings on the use of a calcium ionophore to activate human oocytes artificially. An algorithm based on Karyomapping (termed MeioMapping) is demonstrated for the first time specifically to investigate human female meiosis. By recovering all three products of human female meiosis (oocyte, and both polar biopsies – herein termed “Trios”) using calcium ionophore, I present a novel protocol (commissioned by Nature Protocols) to allow exploration of the full extent of meiotic chromosome recombination and segregation that occurs in the female germline. Finally I present a published set of experiments using this protocol to provide new insight into meiotic segregation patterns and recombination in human oocytes. This work uncovers a previously undescribed pattern of meiotic segregation (termed Reverse Segregation), providing an association between recombination rates and chromosome mis-segregation (aneuploidy). This work demonstrates that there is selection for higher recombination rates in the female germline and that there is a role for meiotic drive for recombinant chromatids at meiosis II in human female meiosis. The work presented in this thesis provides deeper understanding of meiotically derived maternal aneuploidy and recombination. More importantly it provides a vehicle within an ethical framework to continue to expand our knowledge and uncover new insights into the basis of meiotic errors that may aid future reproductive therapies