VBA Programming Research Articles

The Fourth Order Runge-Kutta Spreadsheet Calculator Using VBA Programing for Ordinary Differential Equations

Motivated by the previous literature works of spreadsheet solutions of ordinary differential equations (ODE) and a system of ODEs using fourth-order Runge-Kutta (RK4) method, we have built a spreadsheet calculator for solving ODEs numerically by using the RK4 method and VBA programming. In this spreadsheet calculator, a user interface input form is developed to capture the needed information to solve the ODE via the RK4 method. In the first frame of the user interface input form, users are prompted to enter independent and dependent variables in the ODE. On the other hand, they are needed to input the interval for the independent variables, initial value for the dependent variable, step size h and select the desired accuracy of calculation in the second frame of the user interface input form. Lastly, in the third frame of the user interface input form, users are required to enter the ODE function and exact function of ODE if it exists. Once the Solve button is clicked, the full solution of the ODE will be calculated automatically using the RK4 method. This spreadsheet calculator is user-friendly especially for users who are not familiar with Excel. We hope that this spreadsheet calculator can be used as a marking scheme for educators and students who require the full solutions of the ODEs. Moreover, this spreadsheet calculator reduces calculation time and it is hoped to improve students’ learning ability.

Life Cycle Cost Prediction for Rolling Stocks in Maintenance Phase Based on VBA Language Program

In order to predict and analyze the LCC during rolling stocks’ maintenance phase, provide the important support for the decision during rolling stocks’ design, production, operation and disposal, mitigate the risks for high expenditure, the LCC prediction software is developed with VBA programming language in Microsoft Excel environment based on various rolling stocks’ maintenance scenarios. This software can collect the LCC input and predict the LCC during rolling stocks’ maintenance phase. The data input is based on the history data and RAMS information for each system and approved by the engineers. This software is validated by several systems and a specific metro project. It shows very wide applicability for LCC prediction for comparing and analyzing for customer and design and friendly interface to the users.

AN INSURANCE MODEL FOR THE PROTECTION OF CORPORATIONS AGAINST THE BANKRUPTCY OF SUPPLIERS BY USING THE BLACK-SCHOLES-MERTON MODEL

Many banks provide supply-chain finance solutions that might include insurance services that further mitigate trade risk such as the default of suppliers. This study proposes the development of an insurance model that uses the Black-Scholes-Merton Model (BSM) for default prediction and risk pooling management techniques as a way to reduce the risk due to supplier bankruptcy and estimate an insurance premium that banks can use to charge this service to their customers. In order to demonstrate the use of the proposed insurance model, a sample of companies is selected from the New York Stock exchange and data for historical stock prices from the CRSP database (Center for Research in Security Prices) is collected in order to calculate the probability of bankruptcy of a sample of sup- pliers from different industries by using the BSM model. Twelve pools of companies of different sizes are created and a VBA program for Excel is developed in order to calculate probability of bankruptcy tables of companies be- longing to the different pools. A Monte Carlo simulation to simulate the impact on risk and expected losses on the number of insurance policies sold is implemented with the use of simulation software. The results show that the simulation is useful to estimate the number of sold policies required in order to reduce the risk to a minimum level and predict with a high level of certainty the losses due to bankruptcy of suppliers. The expected losses for a risk pool can be used by a financial institution in order to price an insurance contract that hedges a company against the risk of default of suppliers.

Study on Extract and Repair Traffic Performance Index Data of Beijing

Urban traffic expert developed many methods to judge the performance of urban road to provide driving instruction to both drivers and the local authority. In Beijing, the traffic performance index is widely used by people every day. In order to store the traffic performance index data which is continually changed to local database, our team has tried many different ways to achieve it because we want to do deep analyze of it. By using VBA programming we successfully build a data extract system to store data. We use two different ways to process the data from the system based on data missing type. At last, the data is ready for further study to find the traffic pattern of urban area.

Development and practice evaluation of blood acid-base imbalance analysis software

To develop a blood gas, acid-base imbalance analysis computer software to diagnose systematically, rapidly, accurately and automatically determine acid-base imbalance type, and evaluate the clinical application. Using VBA programming language, a computer aided diagnostic software for the judgment of acid-base balance was developed. The clinical data of 220 patients admitted to the Second Affiliated Hospital of Guangzhou Medical University were retrospectively analyzed. The arterial blood gas [pH value, HCO(3)(-), arterial partial pressure of carbon dioxide (PaCO₂)] and electrolytes included data (Na⁺ and Cl⁻) were collected. Data were entered into the software for acid-base imbalances judgment. At the same time the data generation was calculated manually by H-H compensation formula for determining the type of acid-base imbalance. The consistency of judgment results from software and manual calculation was evaluated, and the judgment time of two methods was compared. The clinical diagnosis of the types of acid-base imbalance for the 220 patients: 65 cases were normal, 90 cases with simple type, mixed type in 41 cases, and triplex type in 24 cases. The accuracy of the judgment results of the normal and triplex types from computer software compared with which were calculated manually was 100%, the accuracy of the simple type judgment was 98.9% and 78.0% for the mixed type, and the total accuracy was 95.5%. The Kappa value of judgment result from software and manual judgment was 0.935, P=0.000. It was demonstrated that the consistency was very good. The time for software to determine acid-base imbalances was significantly shorter than the manual judgment (seconds:18.14 ± 3.80 vs. 43.79 ± 23.86, t=7.466, P=0.000), so the method of software was much faster than the manual method. Software judgment can replace manual judgment with the characteristics of rapid, accurate and convenient, can improve work efficiency and quality of clinical doctors and has great clinical application promotion value.

Electrical Marketing Application System Training's Auxiliary Means that Based on VBA

In this paper, combined with trainees ' locking mistakenly or doing wrong with work order that others initiated and problems that teaching schedule and teaching quality is unable to control ,we use “Macro Program” and “Microsoft ODBC for Oracle” to develop the training auxiliary tools in the EXCEL' s circumstances by VBA programming technology and then we achieved the goal of the teaching results of closed-loop test and operation of information broadcast which is based on the working process.

Research on the 3-D Scene Quick Build Method of Virtual City and Implementation Method of Basic GIS Function

<span><span lang="EN-US">Aiming at the common problems of virtual city modeling,such as low efficiency and large amount of data</span><span lang="EN-US">，</span><span lang="EN-US">this paper proposes a quick method to build a virtual city.The method realizes data read automatically through AutoCAD VBA programming, generated the VRML code automatically, buildthree-dimensional scene by the VRML code integration and realize the Web publishing of virual city via Web.</span>

Application of Excel VBA in Score Analysis for Specialty Accreditation

In the standard target system of specialty accreditation, assessed results of teaching process are required to reflect students level of mastering related knowledge. Examination score is the most direct and important reflection. In this paper, VBA programming was applied to generate score sheet of main curriculums in recent years which is required in self-assessment report for specialty accreditation. Firstly, original data were obtained from information management system of teaching affairs. They are teaching task files and class score sheet, which are all Excel files. Secondly, original data were handled by VBA according to requirement. Compulsory courses for civil engineering class and scores for first exams were selected. Thirdly, average scores and first passing rates were calculated. At last, a summary score sheet was produced which is helpful to analyze students learning effect and to find out reasons. Therefore, the efficiency of analyzing teaching results for specialty accreditation is greatly improved.

Test Paper Materials Auto-Generation System

Nowadays, the computer is increasingly popular, and college examination is developing in the direction of traditional examination means to automation and intelligence ones gradually, all these make it inevitable to construct question bank for courses, and to generate test paper using computers. This paper uses the Delphi technique, to make improvements to existing components, combining with VBA programming, and use of SQL Server to implement the question bank management and test paper auto-generation system, which could generate test paper in Word Document. A large number of tests show that the software is running stably and system features are functioning correctly on Windows 2000/XP/2003 platform with Office XP/2003 environment.

Elastic-Plastic Analysis of Partial Single Long-Span Complex Frame Structure under Rare Earthquake in High Seismic Intensity Zone

The item 6.1.5 in Code for seismic design of buildings(GB50011-2010)[ is for single span frame structure design requirements of regulations. But there may be a partial single long-span frame structure in the actual project. Therefore, it is necessary to study the seismic performance of the structure under rare earthquake, especially in high intensity area. This paper takes a hybrid structure which is in high intensity zone with partial single long-span concrete structures (31m) and the single long-span steel roof as an example, and the seismic performance of partial single long-span structures under rare earthquake is discussed. In the elastic-plastic analysis, reinforcement results of members are automatically merged by VBA program. Material parameters of the Elastic-plastic analysis model are compared to Pseudo-static collapse experiment results of reinforced concrete frame columns and the overall framework in Tsinghua University. Results show that: The partial single long-span frame structure reasonably designed based on the Chinese code can meet the requirement of No Collapsing With Strong Earthquake; The bearing type of the steel roof has certain influence on the inter-story displacement angle of supporting frame structures under rare earthquake.

Time aspects of a fund manager appraisal

We compare yields of the leading Russian managed funds with the MICEX index using different approaches to estimate the evaluation period of a fund manager. It is shown that in some special models the MICEX index can not be considered as a benchmark to compare different funds, and at the end of the paper such universal index is suggested. We also provide a special VBA program for its implementation.

The Allocation and Statistics of Quota of Teaching-Practicing Normal Graduates: Based on Excel Built-in Function and VBA

This paper proposes an VBA add in function for excel for a dynamic statistics and allocation table for the quota of teaching-practicing normal graduates. The add in function initially calls some built-in functions such as SUMPRODUCT to sum all interlaced and partition columns. Then it applies the "protection attribute" of cell and "protect worksheet function" to protect the worksheets layout and existing formulas. After added the menu design by VBA programming, it finishes the dynamic statistical sheet in form of the template. This add in function has been approved that the efficiency of allocating the quota of teaching-practicing graduates can be improved a lot through this powerful add in.

Online Examination System for Microsoft Office Software Operations

online examination is an effective solution to the level evaluation problem for computer basic operations. This paper proposed an online examination system for computer basic operations, especially, Microsoft Office software operations. The system mainly achieved functions including making exams intelligently, collecting and marking documents submitted automatically in the exam by the database, socket, ado and VBA program methods. This system supported some kinds of Microsoft Office software including Word, Excel, PowerPoint and Access. The actual running result showed the system could help teachers to improve work efficiency and students to improve software operations by the way of online actual operation in computers. The system has been running in the USTL university computer lab center for some times which has already proved that it was very valid to solve level evaluation problem for Microsoft Office software operations.

Implementation of Chart Automatically Drawing in Excel Based on VBA

Drawing charts are odd work by adding a data source manually and setting other parameters such as border, legend etc in Excel. The key technologies and implementation methods of chart-automatic-drawing are described using embedded VBA Programming codes.

VBA Programming for Data Reorganization: Take Social Accounting Matrix Construction as an Example

VBA Programming for Data Reorganization: Take Social Accounting Matrix Construction as an Example

Combining simulation to nonlinear fitting for the determination of rate constants of elementary reactions by using Excel spreadsheets

A common problem in chemistry is to determine parameters (constants) in an equation used to represent experimental data. Examples are fitting a set of data to a model equation (straight line or curve) to obtain unknown parameters. In chemical kinetics, a set of data is usually a number of concentrations versus time, but the model equation is not well defined! Instead of a well defined model equation we have a set of coupled ODE’s (ordinary differential equations) which represent rate equations for reactants and products. The analytical integration of these ODE’s is rarely possible. The numerical integration is the alternative. In this work are combined the simulation of chemical reactions, by using numerical integration, and nonlinear regression (curve fitting) by using “Solver add-in” of Microsoft Excel to find rate constants of elementary reactions from experimental data. This method is illustrated on three complex mechanisms. The simulation of chemical reactions in Excel spreadsheets is illustrated with/without VBA programming. The automation (automatic obtaining of rate equations from mechanism: no need of chemical kinetics knowledge from the end user!) of mechanism simulation is demonstrated on many example.

IGA-Based User Evaluation Driven Optimization Design of Forklift Styling

Considering the requirements for efficiency and precision in the forklift styling, the authors developed the forklift optimizing system for its styling based on the interactive genetic algorithms (IGA). The system includes parameter optimization, color optimization and part combination three modules. The parameter optimization module can find the user defined parameters of the forklift 3D models and generate new models. Color optimization module can group the model surfaces and render the groups with different color. New color plans are generated through random changing of the colors in each group. The parts combination module divides forklift into several parts and build lib for each part. The module can pick parts from the lib and assemble them into a whole forklift and demonstrate them. The thesis developed a protosystem on the Solidworks platform with VBA programming tools. Interactive genetic algorithms are applied to realize the three module’s function.

A Data Driven Modeling and Simulation Methodology for Automotive Assembly Plant

Simulation offers a quick, controllable and tunable approach for prototyping complex assembly processes in manufacturing environments. In order to improve the responsiveness of production system, this paper proposes a new approach based on data driven modeling and simulation methodology, it can automatically generate the simulation model of production system and realize rapid modification. The generated model also has high efficiency, readability and reusability. The approach is developed for an automotive assembly line. Through the modeling approach of IDEF1X, a data structure of simulation model was constructed, a model simulator was implemented in Arena by VBA program, and then a simulation model for target production system was built automatically through the attribute values in the data structure. The management and control process of assembly line is divided into different levels by hierarchical modeling approach, modeling element and control logic of simulation are separated. The case study of the automotive assembly line demonstrates the validity of data driven modeling and simulation methodology.

Study on the Rapid Design and Optimization Technology of Forklift Styling

The paper introduced a computer-aided industrial design system: Forklift Truck’s Multi Plan Optimizaion System, and demonstrated the modules’ function and techniques with examples. Considering the requirements for efficiency and precision in the forklift styling, the author developed the forklift optimizing software for its styling, which includes parameter optimization, color optimization and part combination three modules. The parameter optimization module can vary the user defined parameters of the forklift models and generate new models. Color optimization module can group the model surfaces and render the groups with different color. New color plans are generated through random changing of the colors in each group. The parts combination module divides forklift into several parts and build lib for each part. The module can pick parts from the lib and assemble them into a whole forklift and demonstrate them. The thesis developed a proto system on the Solidworks platform with VBA programming tools. Interactive genetic algorithms are applied to realize the three module’s function.

SNAP: A DSS to analyze network service pricing for state networks

This study describes the implementation of a spreadsheet-oriented Decision Support System (DSS) that helps in making telecommunications pricing decisions. The system, SNAP-DSS or Service Network Application and Pricing (SNAP)-based DSS, is developed using Microsoft Excel 2007 that also incorporates VBA programming language. We present this research within the context of OneNet, a non-profit state telecommunications service agency. The SNAP-DSS offers the ability to select the rate card options that best fit the preferred pricing strategies by providing a real-time, user-friendly, graphical user interface. The DSS not only captures the effect of changes in the pricing factors on each rate card option, but also allows the user to analyze various rate card options in different scenarios using different parameters. The paper includes some lessons learned for the DSS community.