Visual Basic For Applications Research Articles

Development of a new function in ms Excel to assess differences between sample groups

Statistical analysis plays a crucial role in several areas, including engineering, where it helps in making impartial decisions to optimize production processes. Analysis of Variance (ANOVA) is often used to compare sample groups, allowing the simultaneous evaluation of several means, although it does not identify which groups differ from each other. Post-hoc tests, such as Tukey's test, solve this problem, but their reliability decreases with larger amounts of groups. In this work, a new function was developed for Microsoft (MS) Excel, taking advantage of integration with Visual Basic for Applications (VBA), to simplify direct comparisons between sample groups. This function makes it possible to perform an ANOVA for two sample groups based on the provided size, mean, and standard deviation, generating the P value as a result, which can be directly compared to the significance level adopted by each user. The developed function proved to be effective and resulted in significant productivity gains due to its integration with native MS Excel functions.

AN ALGORITHM FOR CALCULATING THE STEREO FACTOR

Information on wood volume is essential in forestry activities, especially in the sale of wood products. However, measuring solid volume during a harvest period is challenging. The viable parameter for measurement is stereo volume, which refers to the stacked quantity of wood, including the empty spaces between the logs. However, the stereo volume–cubic volume (solid) ratio, which is influenced by the taper, uniformity, and size of the trees, needs to be obtained. In most cases, if a field tester does not choose a reference value, the result is less than reliable. In this work, an algorithm is presented that estimates the stereo–cubic volume relationship from forest inventory and tree stacking data. It was implemented in Visual Basic for Applications (VBA) to simulate virtual log stacking. Its accuracy was evaluated in three field trials: the first and second in a eucalyptus stand and the third in a bamboo stand. Implementing the three calculation methods, the errors ranged from -12.34 to 8.33% for the cubing factor and from -8.94 to 15.64% for the stacking factor, which was the first degree of reliability for a parameter that requires a high level of accuracy because of the impact on wood buying and selling. The algorithm is easy to use but needs further validation in forest harvesting areas.

Decision support system (DSS) for facilities rehabilitation and management (part 2): development of optimized intervention strategy

PurposeThis paper (Part 2 of 2) aims to expand the previously developed performance assessment model (PAM) into a decision support system (DSS) to produce optimized decisions on rehabilitation of facilities.Design/methodology/approachThe research includes a comprehensive PAM that integrates analytical hierarchy process-multi attribute utility theory methods for the physical, operational, and environmental aspects of the mosque, as described in Part 1 of the study. An optimized intervention tool is added to the PAM to complete the DSS development. The tool determines the optimum maintenance decisions that are in line with the budget and condition criticality. The deterioration measures are created using a deterministic straight-line extrapolation technique, and the intervention actions used in the optimization are specified through literature review and in-depth interviews with 11 experts. The optimization model was simplified into a user-friendly visual basic application and applied to an existing facility case study depicting its useful functionality.FindingsThe optimization model recommendation of interventions recovered the overall condition rating index (CRI) of the facility while maintaining budget constraints. The CRI in the first approach (minimum budget to achieve acceptable overall performance) was improved from 6.5 to 7.76, costing 275,850 SAR. While the second approach (Maximum recovery with a selected budget constraint, 300,000 SAR in this case) CRI was improved from 6.5 to 8.3, costing 299,850 SAR.Originality/valueThe presented DSS in this paper offers a tool that enables the rehabilitation and overall management of any facility by its managers. The DSS can also be further customized to fit the facility manager’s specific strategies and achieve desired objectives.

FEATURES OF USING VBA IN TEACHING ACTUARIAL MATHEMATICS

The article is devoted to the analysis of practice of application of specific computational techniques within MS Excel, including VBA, for teaching selected topics in actuarial mathematics. Formulation of the problem. With the evolution of financial and actuarial models towards complexity and reliance on machine learning and data science, there is a growing demand for modern mathematicians, particularly actuaries, to acquire new skills and knowledge. This demand is being driven by new fintech trends in banking and insurance, such as smart payments, real-time credit risk assessment, automated reputation management, customer analytics, fraud detection and cryptocurrency trading. As a result, the training of future actuaries must meet high standards to ensure they are equipped to handle the sophisticated mathematical calculations required by insurance companies and other financial institutions. Materials and methods. To yield results, both theoretical methods, which involve analyzing books and publications, in particular in professional journals in the domains of finance and actuarial mathematics, and empirical methods, entailing the observation of the educational process aimed at training prospective actuaries, are employed in this study. Results. A study aimed at tackling the challenges encountered in teaching actuarial mathematics topics, such as life expectancy and mortality tables, revealed that incorporating a balanced blend of theoretical concepts and hands-on applications, coupled with illustrating mathematical models through real-world examples and reinforcing diverse computational techniques, constitutes a cornerstone of successful training for aspiring actuaries. Leveraging a versatile and user-friendly tool like VBA (Visual Basic for Applications) integrated into MS Excel enables educators to customize examples and assignments to match the intricacy and emphasis of their syllabus, thereby offering students a more targeted learning journey. Conclusions. The teaching of mathematical disciplines, especially financial and actuarial mathematics, in academic programmes for future actuaries requires the adaptation of traditional methods and approaches. In particular, the integration of VBA is key to teaching specific actuarial mathematics concepts, which aims to equip students with the necessary practical competencies. VBA facilitates interactive learning by enabling students to manipulate variables and observe changes in calculations in real time, which improves their understanding of actuarial concepts. VBA also allows you to automate repetitive tasks involving complex mathematical calculations, which saves time and reduces errors. Therefore, its use is recommended when teaching topics involving tabular data, especially life expectancy.

Statistical Application Using Visual Basic For Application (VBA) Excel

BandiStats, a statistical application, was developed with the aim of being a simple and easy-to-use statistical analysis tool thanks to its base on Microsoft Excel, a well-known platform. The development method used the Visual Basic for Application (VBA) programming language. The application test results showed the success of all implemented menus. This research produced an application that can facilitate users in analyzing statistical data. It is hoped that this application can be a useful tool for those who need statistical analysis in their daily work without having to have in-depth knowledge of statistics or computer programming. With its easy-to-use interface and comprehensive features, BandiStats provides an efficient and effective solution for statistical data analysis.

DESIGN OF INFORMATION SYSTEM FOR PAYMENT OF WORKERS' WAGES AND PREPARATION OF FINANCIAL REPORTS ON GARMENT UD KELAMBI ANYAR

Information technology developments are beneficial to businesses worldwide, particularly in Indonesia. Businesses that use information technology effectively can store and manage data, expedite business operations, and manage data. Information systems used in accounting are one area where technology is crucial. The purpose of this project is to develop and construct an information system application for the UD Kelambi Anyar clothing firm that will be used for financial report preparation and worker salary payment. For worker salaries, this company historically used manual records, which presented a number of challenges including the possibility of errors, information delays, and difficulty in finding data. This application was developed using the Software Development Life Cycle (SDLC) approach using the Waterfall paradigm. Planning, analyzing, designing, implementing, testing, and implementing are the stages of system development. Microsoft Access 2019 was used to create the database for this application, while Visual Basic Applications (VBA) was used to write the code. An information system application that facilitates the recording of worker service payments and the preparation of financial reports, such as profit and loss statements and balance sheets, is the outcome of this research project for UD Kelambi Anyar. In addition to a filter menu to make data searching for employee salary reports easier, this program has a username and password feature to ensure user security. It is envisaged that using this application will improve the financial transaction process' accuracy and efficiency and speed up the process of making management choices.

A 10-year retrospective study of antibacterial-induced thrombocytopenia in a women and children hospital using China Hospital Pharmacovigilance System and Visual Basic for Applications.

We aimed to investigate antibacterial-induced thrombocytopenia using the China Hospital Pharmacovigilance System (CHPS) in conjunction with Visual Basic for Applications (VBA). Between September 2011 and December 2022, a 2-phase workflow was employed to identify antibacterial-induced thrombocytopenia, including preliminary screening in phase (I) conducted by CHPS algorithms and causality assessment by trained pharmacists in phase (II) using VBA. The incidence of thrombocytopenia in each antibacterial was calculated, and comparisons were performed between paediatric and adult patients. CHPS algorithms identified 4080 cases from 485 238 admissions (including 223 735 admissions receiving at least 1 antibacterial treatment). After ruling out cases with chemotherapy and abnormal platelet count at admission, 3832 cases were available. Using VBA, pharmacists identified 1039 cases (1246 antibacterial treatments, 28 agents) as potential thrombocytopenia instances (κ =0.89), with an incidence of 0.46%. All antibacterial treatments correlated temporally with thrombocytopenia. Carbapenems (meropenem 1.77%), glycopeptides (vancomycin 1.55%) and lincosamides (clindamycin 0.44%) were prominent causal groups. The highest incidences of thrombocytopenia in the cephalosporins and penicillins groups were ceftazidime (2.04%) and piperacillin/tazobactam (1.24%), respectively. Among all antibacterial treatments, clindamycin showed the shortest time to onset (TTO), and erythromycin showed the longest TTO. Paediatric patients exhibited a longer TTO (61 vs. 29 h), extended time to nadir (83 vs. 37 h), lower platelet nadir count values (110 vs. 92 ×109/L), and a higher severe case proportion (12.37 vs. 3.86%) when compared with adults. Different antibacterial agents exhibit varying incidences of thrombocytopenia, with notable disparities between adults and children in the characteristics of thrombocytopenia.

Utilizing VBA in MS Excel for Simplified Solutions in Chemical Engineering: Single-Effect Evaporator Study

Solutions for an evaporator system involve equations that incorporate both mass and energy balances. Solving equations for a single-effect evaporator is simpler compared to those for multi-effect evaporator systems. However, due to the need for extensive analysis of diverse data from various sources, many prefer using simulation programs for convenience. These programs conduct calculations using their own data libraries, which contain thermodynamic and LLE-VLE data. To emulate the functionality of expensive simulation programs, an alternative interface was designed using Visual Basic for Applications (VBA) in MS Excel. In this study, we chose a single-effect evaporator system used for concentrating NaOH solution and solved its equations using a program created in VBA within MS Excel. Moreover, databases for saturated water and superheated steam data were developed within the MS Excel environment to facilitate the program. Ultimately, the program was employed to scrutinize the influence of selected parameters on other parameters, and the results were analyzed. This study serves as an illustration of how VBA in MS Excel can efficiently handle calculations pertaining to chemical engineering processes. By focusing on the single-effect evaporator, this study demonstrates how VBA in Excel can simplify and effectively conduct investigations into chemical engineering process calculations.

Numerical and Experimental Analysis of the Vacuum Corn Seed Degassing System

Vacuum degassing of seeds is a basic preliminary stage of the treatment process to improve the viability of seeds of various crops. In this work, the degassing process of corn seeds was experimentally and numerically analyzed by removing air or other gases from around the seeds, specifically from the seed coating, in a rough vacuum chamber. Two complementary variants were employed to understand and optimize this process to improve the quality and germination rate of the seeds. The average germination percentage on the first day was about 98%, and the germination speed of 5.0 days. Several experiments were conducted with well-established durations of 10 min and masses of 5 kg and masses of corn seeds at different temperatures to observe and record the behavior of the system, facilitating the modeling of the degasification process in the vacuum compartment. Modeling the degasification operation in the vacuum chamber allowed for determining the pressure profiles on the vacuum chamber and its lid. Numerical simulations were either conducted using a simulation program developed in the Visual Basic Applications (VBA) language for Microsoft Excel to model the degassing process in the vacuum chamber or with the assistance of specialized software (transient structural analysis and simulation program in the ANSYS Workbench environment). Statistical analysis of the correlation between experimental and estimated pressure values revealed that both the proposed mathematical model and the solution method are well-chosen, with differences expressed through the absolute error (EA) being very small, only 1.425 mbar. Structural dynamic analysis carried through the Finite Element Method (FEM) highlights that the chosen materials for manufacturing the vacuum chamber vessel (316 stainless steel—yield strength 225 MPa and tangent modulus 2091 MPa) or the chamber lid (transparent acrylic plastic—yield strength 62.35 MPa and shear modulus 1445.3 MPa) are durable and capable of withstanding the desired pressure and temperature demands in the seed treatment process. Additionally, through structural dynamic analysis, it was possible to study the deformation of system components, providing a detailed perspective on their structural distribution. Thus, the paper aims to improve the quality and survival/germination rate of corn seeds as an important step to improve corn yield through simulations and analyses (numerical and experimental) of the vacuum corn seed degassing system. The degassing process of the vacuum chamber was simulated with a simulation program developed for Microsoft Excel for Microsoft 365 MSO (Version 2401 Build 16.0.17231.20236) 64-bit in the VBA language and software (transient structural dynamic analysis in the ANSYS environment through FEM). Vacuum degassing of corn seeds involves the removal of air or other gases around the seeds or products, which is crucial in various fields such as the food, pharmaceutical, or space technology industries.

Modeling arithmetic systems of elliptic curve cryptography using Microsoft Excel VBA

Purpose. This study aims to develop a new teaching module to illustrate the arithmetic systems of Elliptic Curve Cryptography, a powerful yet simple algorithm for information security, by exploring the capability of the Visual Basic Applications of Microsoft Excel in user friendly way. Methodology. The research is performed using research and development approach, which is divided into five steps utilizing VBA features of Microsoft Excel. It starts with modeling arithmetic in Microsoft Excel spreadsheet, then testing the validity through calculation and setup of the actual arithmetic of Elliptic Curve Cryptography using VBA Excel, before performing the test of the VBA application and finally visualizes the results in graphical mode. Findings. Novel teaching software based on of Microsoft Excel Visual Basic Applications is produced that is able to simulate arithmetic system behind Elliptic Curve Cryptography in an easy way for students. Originality. To the best of the authors’ knowledge, this is the first simulation based on Excel VBA to illustrate the arithmetic systems of Elliptic Curve Cryptography for teaching purposes. Practical value. In general, mastering cryptography will need a steep learning curve; however, using Microsoft Excel as a simulation platform will accelerate learning. The main practical value is the ease of Microsoft Excel, which will turn cryptography learning which was commonly very difficult for student to become easier and user friendly.

An automated process for bulk downloading optical coherence tomography scans

ObjectiveTo develop an automated method for efficiently downloading a large number of optical coherence tomography (OCT) scans obtained using the Heidelberg Spectralis (Heidelberg Engineering, Heidelberg, Germany) platform.MethodsThe electronic medical records and OCT scans were extracted for all patients with age-related macular degeneration treated at the Hadassah University Hospital Retina Clinic between 2010 and 2021. A macro was created using Visual Basic for Applications (VBA) and Microsoft Excel to automate the export process and anonymize the OCT scans in accordance with hospital policy. OCT scans were extracted as proprietary Heidelberg E2E files.ResultsThe VBA macro was used to export a total of 94,789 E2E files from 2807 patient records, with an average processing time of 4.32 min per volume scan (SD: 3.57 min). The entire export process took a total of approximately 202 h to complete over a period of 24 days. In a smaller sample, using the macro to download the scans was significantly faster than manually downloading the scans, averaging 3.88 vs. 11.08 min/file, respectively (t = 8.59, p < 0.001). Finally, we found that exporting the files during both off-clinic and working hours resulted in significantly faster processing times compared to exporting the files solely during working hours (t = 5.77, p < 0.001).ConclusionsThis study demonstrates the feasibility of using VBA and Excel to automate the process for bulk downloading data from a specific medical imaging platform. The specific steps and techniques will likely vary depending on the software used and hospital constraints and should be determined for each application.

Software application for predicting the efficacy of rehabilitation treatment in patients with discirculatory encephalopathy

Software application for predicting the efficacy of rehabilitation treatment in patients with discirculatory encephalopathy

DESIGN OF INVENTORY MONITORING INFORMATION SYSTEM OF OFFICE EQUIPMENT AND SUPPLIES AT BAZNAS CITY OF BANDUNG BASED ON MACRO EXCEL (VBA)

BAZNAS, the National Amil Zakat Agency, is the official institution overseeing zakat management at the national level. In Bandung City, BAZNAS runs programs such as Healthy Bandung, Smart Bandung, Taqwa Bandung, and Caring Bandung, contributing to the city's prosperity. To streamline office operations, related institutions must conduct inventory activities for office equipment and supplies. However, BAZNAS Bandung City currently employs a manual inventory management system, involving paper-based recording, recapitulation, and individual input into Excel sheets, leading to inefficiency, security concerns, and data loss risks. Recognizing the need for improvement, a new information system has been designed using Microsoft Excel, Macros, and Visual Basic Applications. The design includes Data Flow Diagrams and Entity Relationship Data to enhance the management and monitoring of inventory data, ensuring data integrity and structured inventory activities. Project evaluations, following the End User Computing Satisfaction (EUCS) principle, show that the system meets institutional needs and is user-friendly. Users are provided with a manual book during testing to enhance system understanding. While the trial results are positive, constructive feedback suggests the need for further feature enhancement to ensure smoother system operation and greater overall benefits. This information system aims to speed up and simplify inventory data management, bringing efficiency to BAZNAS Bandung City's operations.

Effect of Interactive Learning Media Using Visual Basic for Application Excel Spreadsheet to Reduce Misconception in Physics Learning

Misconceptions in physics learning are often caused by students’ preconceptions that are not in accordance with the actual physics theory. The use of interactive digital technology-based learning media makes it easier for students to understands physics concepts. This research aims to apply interactive learning media based on Visual Basic for Application (VBA) excel spreadsheets to remediate misconceptions in traveling wave material. The research method is a pre-experimental design, with one group for pretest and posttest. A total of 21 samples were selected using purposive sampling technique. Data collection was carried out twice, namely during the pretest and posttest using a five-tier multiple choice diagnostic test with 10 questions. The results of the misconception remediation analysis showed a decrease in misconceptions from 28.5% to 11.3% with an average N-Gain score (-0.31). This means that there was a decrease in the percentage of misconceptions before and after treatment. This data is also supported by the results of the paired sample t-test analysis, namely 0.000 &lt; 0.5, which means that the use of interactive learning media based on Visual Basic for Application (VBA) Excel spreadsheets has a significant effect on remediating students' misconceptions about traveling wave material. This interactive learning media is expected to be an alternative for teachers in remediating misconceptions in learning physics.

A Process for Creating and Designing Automated CAD Models Through the SolidWorks API

This study proposes a novel method for speeding up CAD modelling procedures by combining parametric modelling strategies, Knowledge-Based Systems (KBS), and Visual Basic for Applications (VBA) inside the SolidWorks environment. The suggested system accepts client design requirements through a user-friendly graphical user interface (GUI), utilising the power of knowledge representation and automation to speed up the design process. This technique greatly decreases the amount of time needed to model CAD, improves design correctness, and equips engineers to quickly convert client demands into real components by utilising KBS and parametric modelling. This research shows the viability and efficacy of this unique technique, highlighting its potential to transform the processes of product design and development.

Shelf-life prediction of nutmeg candy (myristica fragrans houtt) using the Arrhenius equation approach’s accelerated shelf-life testing (ASLT) method

Nutmeg (Myristica fragrans Houtt) is one of the spices utilized as food. The processing of nutmeg into sweets makes one of the snacks known as nutmeg candy. The nutmeg candy found in the market still does not have the identity product shelf life or expiration date, so buyers are hesitant; in this study, a simulation of estimating the shelf life of nutmeg candy with the Accelerated Shelf-Life Testing (ASLT) method was made and applied using Visual Basic applications which can later be used by industry and enterprise. Samples were taken from UMKM UD. Cahaya in Tapaktuan, South Aceh. This research was conducted by formulating temperatures of 35, 45, and 55°C with water and sugar content measurements; in this study, one critical point was taken to determine the shelf life. The results showed that the water content was 6.85 – 13.69 (%b/b), and the sugar content was 5.10-7° brix. The shelf life measured by the critical point shows that dried candied nutmeg can be stored for 50.89 days.

Finding the Most Dominant Color in an Image using K-Means Algorithm Based on Machine Learning

Abstract: The research project, titled "Finding the Most Dominant Color in an Image Using K-Means Algorithm," leverages machine learning techniques to extract fundamental color information from digital photographs. Employing the K-Means clustering methodology, this research identifies the most prominent colors in an image by grouping pixels with similar color values. This innovative approach holds significant real-world implications across various domains, including text analysis, graphic design, and image processing. By automating the color extraction process, this study enhances the efficiency of tasks such as image classification, object detection, and the generation of visual content. It demonstrates the power of machine learning in simplifying complex image processing procedures, offering the potential to enhance a wide range of visual-based applications.

Music Genre Classification by Machine Learning Techniques

Abstract: Music plays a very important role in people’s lives. Music brings like-minded people together and is the glue that holds communities together. Communities can be recognized by the type of songs that they compose, or even listen to the purpose of our project and research is to find a better machine learning algorithm than the pre-existing models that predicts the genre of songs. Genres can be defined as categorical labels created by humans to identify or characterize the style of music. The concept of automatic music genre classification has become very popular in recent years as a result of the rapid growth of the digital entertainment industry. offering the potential to enhance a wide range of visual-based applications.

DESIGN OF APPLICATION INFORMATION SECURITY SELF-ASSESSMENT USING VBA AND MSXML2.XMLHTTP CASE STUDY: DISKOMINFO KABUPATEN KAMPAR

Information security includes the issues that may threaten accountability, reliability, trustworthiness, privacy, authenticity, and trustworthiness of information in an agency. Data and information are very risky things in Information Security, and therefore it is important to do information security governance. The process of evaluating information security using the Index KAMI and ISO 27001: 2013 will be carried out in this study by recording data using Microsoft Excel which has been provided by the National Cyber and Crypto Agency (BSSN). To make it easier to conduct information security assessments and simplify the Excel display, Visual Basic for Applications (VBA) will be utilized as a medium for adding ISO 27001: 2013, then it will be connected using MSXML2.XMLHTTP. The results of the self-assessment carried out show that the Communication, Information, and Signaling Service of Kampar Regency has a level of completeness in implementing the ISO 27001 standard at the "Inadequate" level with a score of 151 only reaching level I+. Meanwhile, the results of the ISO 27001: 2013 Annex control evaluation show that there are still clauses that have not been fulfilled. Therefore, the Communication, Informatics, and Coding Office of Kampar Regency urgently needs improvement in order to fulfill the clauses of ISO 27001: 2013.

Моделирование технического обслуживания и ремонта высокоскоростных электропоездов с использованием данных бортовых систем диагностики

Purpose: High–speed railway lines (HSRL) are the most promising direction of railway transport development in the 21st century, increasing the mobility of the population at a distance of up to 1000 km, contributing to the development of urban conglomerations. Reliable rolling stock is necessary for the functioning of the HSRL. The article describes the performed mathematical simulation of the operation and maintenance and repair system (MRS) of high-speed electric trains in relation to the St. Petersburg — Moscow landfill. The purpose of the simulation is to substantiate the requirements for MRS, including when using on-board automated systems for technical diagnostics. Methods: It is based on the principles of passenger traffic organization, technical requirements for domestic high-speed rolling stock, queue theory methods, as well as probabilistic mathematical modeling methods in MS Excel in the algorithmic language Visual Basic for Applications (VBA). The corresponding software has been developed. Results: A mathematical model and software have been developed, multifactor modeling has been performed, the degree of failure impact on the required fleet of high-speed electric trains has been determined. Methods of optimization of maintenance to reduce the required fleet are proposed. The requirements for the mathematical model itself are defined. A factor analysis of operation and maintenance has been performed by mathematical modeling. Practical significance: The guarantee of safe and reliable high-speed traffic is the creation of a modern system of maintenance and repair of rolling stock. The study of the interaction of operation and maintenance is very important in preparation for the launch of domestic high-speed traffic.