VBA Macro Research Articles

Rancang Bangun Sistem Informasi Administrasi Santri Pondok Pesantren Darul Musthofa Sangatta dengan Menggunakan Macro VBA Excel

The rapid advancement of information technology, particularly Microsoft Excel, facilitates the management of administrative data in educational institutions. Information systems are crucial for enhancing efficiency and effectiveness. The administration at Pondok Pesantren Darul Musthofa Sangatta is conducted manually, resulting in deficiencies such as inaccurate data, difficulties in information retrieval, limitations in student data management, and inefficient operations. The student administration information system is considered a solution to address these challenges. This system facilitates the management of student administrative data for administrators. The system is developed using the waterfall methodology, encompassing phases of requirement analysis, design, coding with VBA Macro in Microsoft Excel, testing, and maintenance. This study has produced a web-based student administration information system at Pondok Pesantren Darul Musthofa Sangatta. The system streamlines the administration of student data within the institution, conserving time, effort, and resources. Furthermore, it allows students and parents/guardians to access administrative information, including monthly payments, re-registration, and remaining funds. The system has been customized to meet the specific requirements of Pondok Pesantren Darul Musthofa Sangatta

Alarm probabilities of simple tests of merger control: An analytic derivation

<abstract> <p>For ten simple horizontal merger tests, I analytically derive the probability of blocking a merger. The assumptions are the symmetry of diversion ratios, price-cost margins, and efficiency gains, and the uniform joint distribution of these variables. The robustness analyses suggest that the limitations arising from the simplifying assumptions are only moderate. Based on these results, I provide an easy-to-use Excel calculator (without VBA macro), with which the probabilities associated with any product market and price increase threshold can be quickly calculated. Additionally, I propose a paired testing protocol for unknown demand systems by linking linear- and isoelastic-demand indicators through the threshold or the alarm probability.</p> </abstract>

Enhancing Cybersecurity With P-Code Analysis and XGBoost: A Novel Approach for Malicious VBA Macro Detection in Office Documents

Enhancing Cybersecurity With P-Code Analysis and XGBoost: A Novel Approach for Malicious VBA Macro Detection in Office Documents

Maximum Likelihood Instead of Least Squares in Fracture Analysis by Means of a Simple Excel Sheet with VBA Macro

This technical note illustrates a linear regression algorithm based on the Maximum Likelihood Estimation (MLE), with a related Excel spreadsheet and VBA program, adapted to the case of fracture aperture data sets in which sampling of the smallest values is problematic. The method has been tested by means of Monte Carlo simulations and exhibits significantly better convergence against Least Squares criterion (LSM). As the method is conceptually simple and, following the indications illustrated here, the relative spreadsheet can be easily designed, it may be routinely used, instead of the Least Squares, in fracture analysis. Furthermore, the proposed method, with the appropriate modifications, might be potentially extended to other cases in geology and geophysics, in which significant biases at the lower limits of the sampling scale occur.

PELATIHAN PENGOPRASIAN APLIKASI KEUANGAN BERBASIS VBA MACRO EXCEL PADA IKM RENGGINANG DI DESA PRAMBONTERGAYANG KECAMATAN SOKO KABUPATEN TUBAN

Prambontergayang Village, located in Soko District, Tuban Regency, is a village that is well known for the production of Rengginang snacks in the Soko sub-district. has the Urnika brand, the IRT has been established since 2015 and received PIRT (Household Industry Food) legality in 2018. The problems that occur with these IRTs are financial and bookkeeping management which are still carried out by recording in books or manually, repair solutions that offered is VBA Macro excel-based bookkeeping and finance training. The implementation method carried out in community service first examines the problems found in IRT by means of surveys to IRT locations, then examines solutions to these problems and is implemented with training. The results of community service are proven to be able to increase the productivity of IRT rengginang craftsmen in Prambonterga village, which is in Soko District, Tuban Regency, and IRT are able to independently manage finances well and improve time efficiency when reporting finances.

Development of Computer Accounting Applications with Microsoft Excel VBA (Macro) in The Preparation of Financial Statements of Micro, Small and Medium Enterprises (MSMES) Coconut Wood Furniture

Micro, small and medium enterprises (MSMEs) are one of the economic activities carried out by most Indonesians. MSMEs have a very significant contribution in employment, forming Gross Domestic Product, national export value and national investment. Until 2019, the number of MSMEs in Indonesia reached 56.5 million. In this study, it found that the accounting information system was weak and not in accordance with Financial Accounting Standards at the Technical Education Training Center (BLPT) GMIM Kaaten Tomohon. Seeing the importance of accounting information systems today to the progress of business organizations, and the number of orders for goods at BLPT GMIM Kaaten Tomohon is currently increasing, a fast, accurate and accountable recording process is needed in order to make decisions appropriately based on Financial Accounting Standards. The purpose of this study is to help MSMEs develop the application of a Microsoft Excel VBA (Macro) based accounting information system on BLPT GMIM Kaaten Tomohon. The development method in this study uses the Borg and Gall Research and Development (R&D) model, through this model aims to produce a computer application-based Accounting information system. Data is obtained directly from the company through interviews and documentation. Based on the data obtained, it is stated that the BLPT GMIM Kaaten Tomohon accounting information system has several weaknesses, such as the non-implementation of a computer-based accounting information system. From these weaknesses, researchers provide advice to use a Microsoft Excel VBA (Macro) based accounting information system that is easy to operate and in accordance with SAK EMKM.

Quantitative definition of strength of chromophores in gemstones and the impact on color change in pyralspite garnets

AbstractThe color of most garnets is derived from multiple color‐producing elements (chromophores). In this study we selectively isolate the ultraviolet–visible–near infrared (UV–Vis–NIR) spectra of four major chromophores (Fe2+, Mn2+, Cr3+ and V3+) by collecting and extrapolating the spectra of four representative pyrope‐almandine‐spessartine (pyralspite) garnets. In order to study the color strength of these isolated chromophores and their impact on color change in garnet, we have proposed a new quantitative definition of strength of a chromophore. With the isolated garnet chromophore spectra, hypothetical garnet spectra with various chemical compositions can be simulated. By using these hypothetical garnet spectra, the impact of each chromophore on the color change behavior in pyralspite garnet was also quantitatively studied. Most pyralspite garnets can be successfully modeled using these four chromophores, however, this model cannot adequately describe those garnets containing the Fe3+/Fe2+ intervalence charge‐transfer transitions. Finally, a comprehensive excel data processing template (Appendix 3) written with VBA macro code was generated to help other researchers calculate colorimetric parameters and color panels from UV–Vis–NIR spectrum. In the template, we provided multiple types of conversion matrices and power distribution curves of different illuminants, so that different colorimetric parameter conversion and color panel calculation from UV–Vis–NIR spectrum can be selected with different combinations based on people's preference. The user guide of the excel template can be found in Appendix 4. The detailed calculation procedures and formulae used for the excel template can be found in Appendix 5.

Solving Classic Discrete Facility Location Problems Using Excel Spreadsheets

There are a variety of discrete facility location models that have practical relevance for operations management and management science courses. Integer linear programming (ILP) is the standard technique for solving such problems. An alternative approach that is often conceptually appealing to students is to pose the problem as one of finding the best possible subset of p facilities out of n possible candidates. I developed an Excel workbook that allows students to interactively evaluate the quality of different subsets, to run a VBA macro that finds the optimal subset, or to solve an ILP formulation that finds the optimal subset. Spreadsheets are available for five classic discrete location models: (1) the location set-covering problem, (2) the maximal covering location problem, (3) the p-median problem, (4) the p-centers problem, and (5) the simple plant location problem. The results from an assignment in a master’s-level business analytics course indicate that the workbook facilitates a better conceptual understanding of the precise nature of the discrete facility location problems by showing that they can be solved via enumeration of all possible combinations of p subsets that can be drawn from n candidate locations. More important, students directly observe the superiority of ILP as a solution approach as n increases and as p approaches n/2.

A collaborative method to survey and store urban components

Urban components are the important part of the city, and the rapid and efficient survey of urban components is a key requirement of urban digitization. In this paper, a method of the collaborative survey and storage of urban components is proposed. The national standard code for urban components survey is optimized to accelerate the survey and storage of urban components, and the time cost of error discovery is shorten by using AutoCAD to check the spatial location and employing a VBA macro programme to search for the attribute data errors. At the same time, a collaborative processing flow of components data is constructed through ArcPy to further speed up the storage of urban components. The experiment of urban components survey in Wanxiu District of Wuzhou City shows that this method can effectively reduce the complexity of urban component data storage procedure and the error rate. Compared with the traditional method, the proposed method is about 2 times more efficient to input the urban component data into the database.

Perancangan Sistem Informasi Pergudangan dengan Metode Framework for The Application System Thinking (Fast) Berbasis VBA Macro Excel (Studi Kasus PT. Meidoh Indonesia)

The development of information technology is growing very rapidly, as evidenced by the birth of Industry 4.0. Industry 4.0 has entered all aspects of life, the manufacturing industry and the service industry. One of the purposes of the use of information technology is to increase the effectiveness and efficiency of a process and the company's competitive advantage. PT. Meidoh Indonesia, which is located in Karawang, has begun to apply Industry 4.0 technology, especially in the field of improving material flow. The process of taking employee work equipment is still conventionally using the material request paper. This study aims to design an employee work equipment warehousing information system using the Framework For The Application System Thinking (FAST) method based on VBA Macro Excel. The results showed that this information system makes it easier for employees to make requests for work equipment to support their work. Information systems can reduce times of request material up to 60%. The fastest material request time conventionally takes 15 minutes, while material request time using this warehousing information system only takes 5 minutes.

Macro excel (VBA) implementation in designing booking information systems in uniform convection (Case Study: Kholidi Taylor SME, Medan Denai)

The development of information technology today is very influential in every field of work, especially in Small and Medium Enterprises (SME) which often require the implications of these technologies to support their work. This information technology is expected to help work become faster, more accurate, flexible, and efficient. Kholidi Taylor Convection SME is a business that produces uniforms or printed screen printing that can be customized by its buyers. Therefore, this SME needs an information system that can provide a place for buyers to order the uniform they want easily. This study uses the VBA Macro Excel application where this application will be used by buyers to order the desired uniform. Data Flow Diagrams (DFD) are needed to determine the most important entities and their correlation in order to create a good VBE ordering application.

Mitigation of Supply Chain Risk using HOR Model at PT. Sumber Karya Indah

In supply chain activities has always been a potential risk arising, therefore risk management is very necessary for the handling of risk. In the company that produces women handbags such as PT. Sumber Karya Indah, in its supply chain activities, has the opportunity to arise risk. Therefore, it is necessary to do risk analysis and design of mitigation action, to reduce the risk of interference that is likely to arise in its the supply chain. The research was conducted using a house of risk model consisting of 2 phases. The first phase is risk identification and risk agent, which is then done a measurement of severity and occurrence level as well as a calculation of aggregate risk priority (ARP) value. The second phase is risk mitigation planning. Data retrieval methods are performed by interviews, observations, and historical data as supporting data. After the research has been obtained results that there are 28 risk events and 32 risk agents. There is 7 mitigation action that can be used and expected to mitigate risk in its supply chain. The result is the House of Risk Assessments Management Template using VBA Macro Excel.

Energy and cost optimization in the life cycle of nearly zero energy buildings using parametric calculations

Possible cost saving potentials in the planning and construction of future building standards are often not sufficiently assessed, as only a few possible variants are considered in the traditional planning process. Often planning and analysis are not carried out in parallel, and the various possibilities are discarded at an early stage. If, on the other hand, several variants are realistically compared in the planning phase, including life-cycle costs, a profound decision can be made. Therefore parameter studies were carried out as part of the national research project “KoPro LZK+" (cost and process optimization in the life cycle of nearly zero energy buildings) for seven buildings, five multi-family buildings, a school and an office building. For this purpose, a VBA macro was programmed in MS-Excel©, which automatically carries out energy demand calculations in the “Passive House Planning Package” (PHPP) and life-cycle cost calculations in the tool “econ calc”. A total of more than 216, 000 variants were investigated in this way, whereby on the one hand a variety of technologies, such as insulation of the building envelope, ventilation or electricity and heat supply, and on the other hand a variation of the boundary conditions (such as observation period, user behaviour, energy price increases or CO2 costs) were carried out. The results were analysed energetically and economically over the life cycle (separately from each other and combined) with the objectives of identifying coherences, deriving trends and optimizations over the life cycle.

Minimizing the carbon footprint of material handling equipment: Comparison of electric and LPG forklifts

Purpose: The aim of this study is to identify the best Material Handling Equipment (MHE) to minimize the carbon footprint of inbound logistic activities, based on the type of the warehouse (layout, facilities and order-picking strategy) as well as the weight of the loads to be handled.Design/methodology/approach: A model to select the best environmental MHE for inbound logistic activities has been developed. Environmental performance of the MHE has been evaluated in terms of carbon Footprint (CF). The model is tested with a tool adopting a VBA macro as well as a simulation software allowing the evaluation of energy and time required by the forklift in each phase of the material handling cycle: picking, sorting and storing of the items.Findings: Nowadays, it is not possible to identify ‘a priori’ a particular engine equipped forklift performing better than others under an environmental perspective. Consistently, the application of the developed model allows to identify the best MHE tailored to each case analyzed. Originality/value: This work gives a contribution to the disagreement between environmental performances of forklifts equipped with different engines. The developed model can be considered a valid support for decision makers to identify the best MHE minimizing the carbon footprint of inbound logistic activities.

A Simple Technique for Obtaining Future Climate Data Inputs for Natural Resource Models

<abstract> <b><i>Abstract. </i></b> Those conducting impact studies using natural resource models need to be able to quickly and easily obtain downscaled future climate data from multiple General Circulation Models (GCMs), future scenarios, and timescales for multiple locations. This article describes a method of quickly obtaining future climate data over a wide range of scenarios, GCMs, and timescales from the Intergovernmental Panel on Climate Change AR4 and AR5 model families using the MarkSim DSSAT Weather Generator and a Microsoft Excel VBA Macro, the final result being a properly formatted parameter (.par) file which can be used by CLIGEN (CLImate GENerator) within the Water Erosion Prediction Project (WEPP) model. We developed a fast and simple method to create WEPP climate input files by using software which already exists on most computers that does not require climatological or modeling knowledge to operate. Ultimately, the method was modified to create continuous daily data for use with the Soil and Water Assessment Tool (SWAT) as well. The final product is an automated spreadsheet with a simple user interface which imports, analyzes, and generates climate input files for the WEPP and SWAT models. This article describes the methods, development, and testing of the tool for use with CLIGEN and WEPP model simulations.

Research on Subjective Items Grading Strategy of Network Examination System Based on VBA Macro

Along with the rapid development of computer technology, application of technology of network, communication, database and so on to the daily teaching activities become very practical method. The network examination is an attempt to replace the traditional examination mode, more and more subject’s examination using a paperless network examination mode. In this paper, we take basic computer course as an example, investigate the related evaluation technology, and give the basic design principle and coding method for automatic scoring function of basic computer course papers.

Technical note: A significance test for data-sparse zones in scatter plots

Abstract. Data-sparse zones in scatter plots of hydrological variables can be of interest in various contexts. For example, a well-defined data-sparse zone may indicate inhibition of one variable by another. It is of interest therefore to determine whether data-sparse regions in scatter plots are of sufficient extent to be beyond random chance. We consider the specific situation of data-sparse regions defined by a linear internal boundary within a scatter plot defined over a rectangular region. An Excel VBA macro is provided for carrying out a randomisation-based significance test of the data-sparse region, taking into account both the within-region number of data points and the extent of the region. Example applications are given with respect to a rainfall time series from Israel and also to validation scatter plots from a seasonal forecasting model for lake inflows in New Zealand.

Microsoft Access 2010 VBA Macro Programming * Practical Software Project Estimation * Privacy Law Handbook

Microsoft Access 2010 VBA Macro Programming Get access ITNOW, Volume 53, Issue 3, May 2011, Page 28, https://doi.org/10.1093/itnow/bwr012 Published: 04 May 2011

Herramienta de Análisis y Diseño de Sistemas de Control Basada en Hojas de Cálculo Excel

This paper presents a toolbox for control systems analysis and design using Excel spreadsheets. The toolbox is a set of routines and function written in VBA macro programming language. At present, the toolbox is limited to linear systems represented by transfer functions. The user can specify a model, plot its time or frequency responses, perform a feedback, and tune interactively the controller parameters. The tool is intended to offer good level of productivity maintaining multiple simulations in a unique worksheet, and allows users to switch easily from one simulation to another. It is also intended to be highly interactive, where the user can perform “what-if” experiments in a straightforward manner by changing the parameters values and immediately see how these changes influence the system behavior. To illustrate the scope and the principal features of this tool, several examples are provided.

Spreadsheet analysis to show effect to customer service level on inventory

This paper describes the use of a spreadsheet for simulating inventory replenishment systems for a multi-echelon supply chain model. A spreadsheet model was built using Excel VBA Macro to explore the replenishment policies and inventory carrying units of the branches and the distribution centre of a multi-echelon model by considering a demand that is normally distributed. Managing inventory and keeping stock at optimum levels are two of the foremost considerations in this paper. This paper shows how the customer service levels vary according to the replenishment policies given the specific capacity restrictions and demand patterns in a multi-echelon supply chain. As such, the spreadsheet can be used as a tool to gain a clear insight into the use of inventory control policies and demand patterns in relation to the customer service.