Open AccessOptimization of material delivery time analysis by using Visual Basic for applications in Excel
Author(s) -
Muhammad Ali Khan,
Muhammad Ahmed Kalwar,
Abdul Karim Chaudhry
Publication year - 2021
Publication title -
journal of applied research in technology and engineering
Language(s) - English
Resource type - Journals
ISSN - 2695-8821
DOI - 10.4995/jarte.2021.14786
Subject(s) - macro , visual basic for applications , computer science , automation , visual basic , ms excel , mistake , procurement , software engineering , operations research , software , operating system , programming language , engineering , mechanical engineering , marketing , law , political science , business
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.