DOI: https://doi.
org/10.
33479/jtiumc.
Jurnal Sains dan Aplikasi Keilmuan Teknik Industri (SAKTI) Journal of Industrial Engineering: Application and Research Volume 03 No.
01 - June 2023 Journal homepage: w.
ISSN: 2829-8519 .
Ae ISSN: 2829-8748 .
Designing a Macro-VBA Excel-based Kit List Printing Application for the Supporting Department of PT XYZ Marius Pribadi1, .
Novenda Kartika Putrianto1, .
Purnomo1,.
Author Affiliations 1Program Studi Teknik Industri.
Fakultas Sains dan Teknologi.
Universitas Ma Chung Jalan Villa Puncak Tidar N-01 Malang 65151.
Indonesia Author Emails .
411910009@student.
novenda@msn.
nomo@machung.
Received 11 April 2023 / Revised 16 May 2023 / Accepted 29 May 2023 / Published 06 June 2023 Abstract.
The recording of raw material orders in PT.
XYZ must be done through the embedded ERP system in the company.
Microsoft Dynamics GP.
However, the system is not perfect as it is not suitable for workers who follow traditional practices.
To avoid losing goods.
PT XYZ has started implementing kit recording, but a shortage of workers who enter KIT data can lead to human error.
To overcome this.
PT XYZ can automate the printing of KIT using VBA in Microsoft Excel.
VBA allows applications to work more efficiently by easily manipulating object models.
Excel macros support VBA programming and enhance the flexibility of the programming language.
A well-organized VBA Excel application can produce a KIT list by pulling data from the production order sheet attached to the bus chassis as it moves on the production line.
Once the specifications for a bus body to be produced are obtained, the user can edit and click one button to get the KIT that is suitable for that bus.
The process of developing this application took 4 months, including revisions and user adaptation to the application's interface.
Therefore.
VBA in Excel can help PT XYZ improve the recording of raw material orders and reduce human errors that can cause financial losses.
Keywords: KIT automation.
Macro-VBA Excel.
Order record Introduction PT XYZ's production process has incorporated machine labor to assist workers in their tasks.
There are seven main production processes that are necessary to create a single type of bus:
dismantling the chassis, creating and installing the frame onto the chassis, attaching plates to the frame, puttying, painting the bus body, installing the interior and glass, and finishing and detailing.
Each stall/work unit in the bus production department has a similar work time of about 4 hours per vehicle.
However, there are also some complicated order modifications that require overtime to ensure that each part of the vehicle is completed.
In addition, there are three types of warehouses in the company: the press shop warehouse, component warehouse, and pipe Each warehouse has its own shape and storage type based on its usage requirements.
Citation Format: Pribadi.
Putrianto.
, & Purnomo.
Designing a Macro-VBA Excel-based Kit List Printing Application for the Supporting Department of PT XYZ.
Jurnal Sains dan Aplikasi Keilmuan Teknik Industri (SAKTI), 3.
, 59-66.
https://doi.
org/10.
33479/jtiumc.
Pribadi et al.
Every order in PT XYZ of raw materials from the warehouse to the production department must be recorded in an ERP system that has been embedded in the company, namely Microsoft Dynamics GP.
To address challenges in supply chain management, various strategies can be These include improving supply chain management through enhancing product quality and increasing promotional activities, ensuring prompt order processing, maintaining consistency in meeting consumer needs, ensuring stability in production processes, and evaluating all activities systematically (Martono, 2.
This ERP system should serve as the reference for all administrative systems that take place within the company.
However, the system is still not perfect because the application used is not suitable for workers who still adhere to traditional practices.
The recording of raw material orders is crucial to avoid losing goods, which can cause financial losses to the company.
To address the issue of lost items.
PT.
XYZ has begun implementing the use of kit recording.
Material kitting is a manufacturing process that gathers raw materials and components (Rheude.
However, with a large number of kits, a new problem arises as workers who input data for the kits may experience errors during the input process.
According to Syofyan et al.
material kitting in manufacturing is an activity of creating a container for components and delivering them to workstations in predetermined quantities with special containers.
These errors include forgetting the name of the kit, mistakes in reading the job order, and time-consuming input To avoid human errors, the automatic printing of kits can be carried out by reading the points listed in the job order automatically.
Automation of kit printing can be achieved using VBA, which is available in the Microsoft Excel application.
Microsoft Excel is a popular and user-friendly spreadsheet program (Madcoms, 2.
With Microsoft Excel, you can input, analyze, and manipulate data to create the reports you need.
part of the Microsoft Office suite.
Excel is constantly updated and easy to use.
One of the features that makes Excel particularly powerful is VBA, which provides users with ample space to work at their fullest potential.
VBA, or Visual Basic for Applications, is included in all Microsoft Office applications and even some third-party applications.
VBA requires a parent application to run, such as Excel.
VBA can enhance its parent applications .
uch as Excel, etc.
) to become more interactive (Luwis, 2.
Users can use VBA to customize applications to achieve specific goals required by business stakeholders, including automating a process performed on a computer.
There are multiple benefits to using VBA Macro in Excel, as stated by Wicaksono .
Firstly, automated processes are faster compared to manual processes.
Secondly, quick and automated processes require less Lastly, automated processes help minimize errors, except for errors in the macro code Objects can be easily manipulated in VBA, including objects in Excel.
Word.
Access, and PowerPoint (Tofik, 2.
VBA is an application within an application that helps performance work better.
With VBA, applications can run more efficiently and effectively.
Excel VBA, which stands for Excel Visual Basic for Applications, is the name of the Microsoft Excel programming Excel VBA allows user to automate tasks in Excel using macros (Lee, 2.
VBA in Excel is a standalone application that works seamlessly with Excel.
Excel gives us the space to open, edit, and execute any command or object in VBA.
Excel macros strongly support VBA programming (Tofik, 2.
With the help of macros, programming in dialect or programming language becomes more flexible.
A macro is a set of instructions that automates some Excel functions to work more efficiently and quickly (Lee, 2.
You can create a macro using the Excel macro recorder.
Excel saves the function and converts it into a VBA macro.
The macro code obtained by saving or typing directly into the code window and other objects drawn in the VBA project can function if the macro mode is enabled in Excel settings.
Macros can be created using Excel VBA, for example importing data from text files, formatting and saving it in the workbook, etc.
According to Aryan .
, macros can be used to automate repetitive commands related to data processing and reporting that are performed repeatedly.
Microsoft Excel offers numerous features, and among them is the macro functionality.
Macros are utilized to automate tasks through a series of operations (Yang & Rahardjo, 2.
When a macro is created, it can be run multiple times to execute its commands automatically.
Macros can be Designing a Macro-VBA Excel-based Kit List Printing Application for the Supporting Department of PT XYZ created in Excel VBA by opening a new workbook and pressing Alt F11 to open the Visual Basic Editor (VBE).
The VBE has a code window.
The structure of a macro consists of comment lines, user input, and output.
To run the macro, press the F5 key on your keyboard.
Before closing the Excel file with the macro, the file format must be changed from the default file type .
Methods Data Collection There are two types of data collection conducted in this study.
Primary data is obtained from direct observation in the field, as well as from interviews and discussions with staff members responsible for the production department.
The information gathered includes an explanation of the production process flow, types of machinery used, and issues and maintenance of production machinery and equipment.
On the other hand, secondary data is obtained directly from the company, such as company profiles, organizational structures of the company and departments, types of produced feeds, and average daily production.
Designing an Application with Macro-VBA Excel The application design for ordering a list of KITs begins with the creation of a flowchart as follows (Figure .
Figure 1 Flowchart of The Application creating The process of creating the application begins with a detailed introduction to the new system, which requires several days for a thorough understanding.
Subsequently, the logical sequence and objectives of the application are determined, based on the needs of the users as the application's Pribadi et al.
intended users.
The main objective of this application is to automatically generate KIT lists for printing, in order to save working time compared to manual searching for KITs.
The application development process entails collecting the necessary data as combinations for each KIT that will be ordered.
This data includes the desired components from client orders.
The data is then combined using predefined Excel formulas to generate unique combinations that can be matched with the existing list of KITs from the company.
Once the required data is gathered, the process continues with extracting the relevant data based on the user-inputted SPK number from the company's master data.
Excel will search for the key points from the client's SPK within the master data.
Once these points are identified.
Excel transfers them to a dashboard in a new Excel file.
After all the points are recorded in the new Excel file, a processing step takes place, involving the merging of codes to form unique combinations.
These unique combinations serve as the KIT numbers that will be used as order codes for the Warehouse division, facilitating the delivery of goods to the production floor.
With this application, the automatic printing of KITs can be carried out quickly and efficiently.
It not only saves working time but also reduces human errors that may occur during manual data search and merging.
Therefore, this application enhances efficiency and accuracy in the order creation and delivery process.
Results and Discussion The company has provided data that can be used as a reference for printing KITs in all Here is an example of specifications for one type of vehicle.
From the data in table 1, an application was created that can process the data into KITs that are only known to the user.
Using VBA Excel programming language, the author compiled rules or algorithms where the production order sheet obtained can become KITs automatically.
LINE TYPE
INT
INT
INT
INT
INT
Table 1 Data that will be used in application JENIS
DESKRIPSI
KACA SAMPING
RAY BLUE
LAMPU BELAKANG
DOP 2A
LAMPU DEPAN
DOP 2A
LANTAI
STANDAR
MODEL
BESAR M
PINTU BELAKANG
HANDLE A
PINTU DEPAN RH
HANDLE B
STRIPING
DELUXE LUXURY
TINGGI BODY
BESAR M
DENSA DL9
OPERATOR AC
OPERATOR1
SOUND SYSTEM
CNVT
SOUND SYSTEM
DVD CD
SOUND SYSTEM
INVRT
32 INCH
WIPER
STANDAR
BANGKU
STANDAR
BANGKU GUIDE
STANDAR
DUCTING
STANDAR
GORDYN
COKLAT
INTERIOR
KREM
Designing a Macro-VBA Excel-based Kit List Printing Application for the Supporting Department of PT XYZ The first method is to search for the production order sheet number that will enter the production line in an excel file taken directly from historical marketing data when creating production order sheets.
The excel file contains many parts of the bus followed by the production order sheet number.
After filtering the data using the "FILTER" feature in Excel, the data is then moved to the database in the KIT list creation application.
The production order sheet number, also known as the SPK number, has its own sequence or rules that have been determined by the There are 4 columns or sections that are standard for the company, namely: the bus number when entering the company, the month of bus production, the type of bus or minibus, and the year the bus was produced at PT.
XYZ.
Figure 2 The initial display when looking for the SPK number Figure 3 Input process SPK number The next method employed by the author is to identify the types or components of the bus that are relevant to the production process in the supporting bus department.
This includes the chassis type, brand, model, body height, body style, doors, toilets, suspension, and whether the bus is a sleeper or not.
Pribadi et al.
Figure 4 Display of KIT list results before searching for SPK numbers There are also buttons labeled "PAKET SL", "PAKET SUSPENSI", and "PAKET SLEBOR".
These three buttons can change the sleeper, suspension, or fender that are special orders and usually an addition to the SPK, so they cannot be detected by the parts of the bus that are included in the historical data from marketing.
After the button on the KIT search application is clicked, the results appear as follows after data retrieval.
The reading from the SPK in Figure 3.
4 is that this vehicle is the 19th bus in February 2021.
Judging from its specifications, this bus is powered by a Mercedes-Benz type 1626, which will have two windows and a large height size.
There are also various handle types available in the company.
Additionally, the toilet position can also be adjusted according to the buyer's Usually, the smoking area and toilet are located at the back of the bus.
Figure 5 Screen display of specifications for the SPK number that being searched After obtaining the specifications for the body of the bus to be produced, it is the user's role to edit them in case of any writing or ordering errors made by the marketing department that have been entered into the ERP system.
These specifications can be manually changed as a last step if there are any mistakes.
After that, the user can click on the button labeled "BUAT KIT" to obtain a KIT that is suitable for the bus to be produced.
Designing a Macro-VBA Excel-based Kit List Printing Application for the Supporting Department of PT XYZ Figure 6 KIT list results after clicking BUAT KIT Once the KIT list has been obtained, as shown in Figure 4.
7, the user can proceed with the copy-pasting process into the ERP or Microsoft Dynamics GP system.
There are four lists titled AL.
SJ.
L, and R.
These four table headers represent the delivery couriers who are also responsible for recording the information in the GP system.
Thus, the AL courier is responsible for delivering goods with the KIT codes: Package A 225 A.
Package A 225 W.
Package A 225 B.
Package A 225 T.
Package A 225 L.
The SJ courier, on the other hand, delivers Package S 225 A and Package S 225 T.
As for the L and R couriers, they respectively deliver Package L 225 A and Package R 225 A.
Conclusions Based on the analysis conducted in the supporting production department of PT.
XYZ bus, it can be concluded that the bus production process is carried out after the raw material plates are ordered from the plate cutting department by the admin of the supporting bus department.
There are six types of bus models produced, and each model has its own KIT with many variations depending on customer preferences.
The supporting department itself performs its job by assisting in the production of finished goods that make up a bus body such as doors, luggage compartments, ducting, and others.
The different naming of KITs in each part of the bus body causes a delay in finding the KIT names.
The application that has been created can reduce the search time for KIT names by automating it using Microsoft Excel.
The raw material plate ordering process becomes a crucial production process, so there is a shipment of goods from one department to another, which starts with recording the process in the ERP system at PT.
XYZ.
Errors that occur are due to human error when ordering raw material plates from the press shop department.
This application has been implemented for one month and has received satisfactory results for users who will use it.
This application can help workers by using machines as a substitute for humans in repetitive tasks.
The limitations in this application are rooted in the traditional work practices of the employees, who seem to have a limited understanding of the world of information technology, including Excel.
Users are expected to invest some effort in learning Excel in order to minimize potential errors.
However, error handling has been implemented as a preventive measure against any modifications to the master data or interface appearance resulting from the printing of the KIT.
The development of this application is highly adaptable to changes in the master data, requiring only slight adjustments to the code merging process to create a unique combination tailored to the users' needs.
Pribadi et al.
References