Visual Basic For Applications Research Articles

Monte Carlo Simulation Via Visual Basic Application

Objective: The objective of this study is to implement the Monte Carlo Simulation method using Microsoft Excel's Visual Basic for Applications (VBA). This approach enables users to simulate complex systems easily and obtain statistical outputs for decision-making. Theoretical Framework: Implementing the results of the changes we desire on a real-life system through trial and error can lead to costly and risky outcomes. Simulation allows us to develop a computer model of an existing system, enabling us to understand the system's behavior. Method: Monte Carlo Simulation, a widely used simulation technique, utilizes random sampling to estimate possible outcomes of a process or experiment. Monte Carlo Simulation utilizes random sampling to estimate the outcomes of an experiment. Results and Discussion: The purpose of this study is to implement the Monte Carlo Simulation method using MS Excel Visual Basic Application. When the user interacts with the Excel interface by clicking the "Calculate" button, the system generates random numbers based on predefined probability distributions. Subsequently, statistical measures such as the mean, variance, standard deviation, median, mode, range, and extreme values (maximum and minimum) are calculated using VBA. Research Implications: Thanks to the developed application, Monte Carlo Simulation applications can be calculated easily. Originality/Value: This study contributes to the literature by implementing the Monte Carlo Simulation method using Microsoft Excel's Visual Basic for Applications (VBA). This approach enables users to simulate complex systems easily and obtain statistical outputs for decision-making. These statistics provide insights into the underlying randomness and variability of the system, which aids in decision-making processes.

The Impact of the Approach to Accounting for Age and Sex in Economic Models on Predicted Quality-Adjusted Life-Years.

The method used to model general population mortality estimates in cohort models can make a meaningful difference in appraisals; particularly in scenarios involving potentially curative treatments where a prior National Institute for Health and Care Excellence (NICE) appraisal demonstrated that this assumption alone could make a difference of ~£10,000 to the incremental cost-effectiveness ratio. Our objective was to evaluate the impact of different methods for calculating general population mortality estimates on the predicted total quality-adjusted life expectancy (QALE) as well as absolute and proportional quality-adjusted life year (QALY) shortfall calculations. We employed three distinct methods for deriving general population mortality estimates: firstly, utilizing the population mean age at baseline; secondly, modelling the distribution of mean age at baseline by fitting a parametric distribution to patient-level data sourced from the Health Survey for England (HSE); and thirdly, modelling the empirical age distribution. Subsequently, we simulated patient age distributions to explore the effects of mean starting age and variance levels on the predicted QALE and applicable severity modifiers. Provided sample code in R and Visual Basic for Applications (VBA) facilitates the utilization of individual patient age and sex data to generate weighted average survival and health-related quality of life (utility) outputs. We observed differences of up to 10.4% (equivalent to a difference of 1.01 QALYs in quality-adjusted life-expectancy) between methods using the HSE dataset. In our simulation study, increasing variance in baseline age diminished the accuracy of predictions relying solely on mean age estimation. Differences of -0.30 to 2.24 QALYs were found at a standard deviation of 20%; commonly observed in trials. For potentially curative treatments this would represent a difference in economically justifiable price of -£4,500-+£33,600 at a cost-effectiveness threshold of £30,000 per QALY for a treatment with a 50% cure rate. For lower baseline ages, the population mean method tended to overestimate QALE, whereas for higher baseline ages, it tended to underestimate QALE compared with individual patient age-based approaches. The severity modifier assigned did not vary, however, apart from simulations with means at the extremes of the age distribution or with very high variance. Our analysis underscores the necessity of accounting for the distribution of mean age at baseline, as failure to do so can lead to inaccurate QALE estimates, thereby affecting calculations of incremental costs and QALYs in models, which base survival and quality of life predictions on general population expectations. We would recommend that patient age and sex distribution should be accounted for when incorporating general population mortality in economic models. Provided sufficient sample size, utilizing the observed empirical distribution for the expected population in clinical practice is likely to yield the most accurate results. However, in the absence of patient-level data, selecting a suitable parametric distribution is recommended.

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.

New Model for the Estimation of Solar Radiation on Façades in Urban Environments

This paper presents a new solar irradiance model for the estimation of the solar radiation on façades of buildings in urban environments. A model and a computer tool, using Visual Basic Application of Excel, have been developed to analyse in detail the radiation on façades. It can be used to determine, in a very intuitive, easy and fast way, using solar radiation contour maps, the best areas for the installation of photovoltaic systems. Finally, using the data obtained by the solar irradiance model for an application example, the electricity production of a photovoltaic system located at the selected façade is analyzed and discussed.

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.