Excel VBA Research Articles

Retention prediction and separation optimization under multilinear gradient elution in liquid chromatography with Microsoft Excel macros

A package of Excel VBA macros have been developed for modeling multilinear gradient retention data obtained in single or double gradient elution mode by changing organic modifier(s) content and/or eluent pH. For this purpose, ten chromatographic models were used and four methods were adopted for their application. The methods were based on (a) the analytical expression of the retention time, provided that this expression is available, (b) the retention times estimated using the Nikitas-Pappa approach, (c) the stepwise approximation, and (d) a simple numerical approximation involving the trapezoid rule for integration of the fundamental equation for gradient elution. For all these methods, Excel VBA macros have been written and implemented using two different platforms; the fitting and the optimization platform. The fitting platform calculates not only the adjustable parameters of the chromatographic models, but also the significance of these parameters and furthermore predicts the analyte elution times. The optimization platform determines the gradient conditions that lead to the optimum separation of a mixture of analytes by using the Solver evolutionary mode, provided that proper constraints are set in order to obtain the optimum gradient profile in the minimum gradient time. The performance of the two platforms was tested using experimental and artificial data. It was found that using the proposed spreadsheets, fitting, prediction, and optimization can be performed easily and effectively under all conditions. Overall, the best performance is exhibited by the analytical and Nikitas-Pappa's methods, although the former cannot be used under all circumstances.

Four-state domestic building occupancy model for energy demand simulations

Stochastic building occupancy models are increasingly used to underpin building energy demand models, especially those providing high-resolution electricity demand profiles. This paper describes the development of an established two-state active-occupancy model into a four-state model in which the absent/present state and the active/inactive state are treated separately. This provides a distinction between sleeping and absence and so offers an improved basis for demand modelling, particularly high-resolution thermal modelling. The model uses a first-order Markov chain technique and the paper illustrates the value of this approach in duly representing the naturally occurring correlation of occupancy states in multiply occupied dwellings. The paper also describes how the model has been enhanced to avoid under-representation of dwellings with 24h occupancy. The model has been implemented in Excel VBA and made available to download for free. The model is constructed from and verified against UK time-use survey data but could readily be adapted to use similar data from elsewhere.

Design and Implementation of Excel VBA-Based Combination Charts in Analyzing Mineral Resources Data

In Mineral resource economics, a large amount of data stored in Excel files usually need to be shown in various charts for analyzing. Excel VBA provides a fast and easy way to create charts. This paper illustrates how to create batched combination charts of several mineral resources and their annual increments based on Excel VBA. Every step of creating process is illustrated in details and the Excel VBA codes are analyzed carefully. And the resulting combination charts are displayed to emphasize the different chart types and to prove the effectiveness of Excel VBA.

An application of the S-P Chart analysis in examination item exam on fundamental nursing

Objective To obtain a two-dimensional evaluation result of the quality of examination paper on fundamental nursing and the students' performance,so as to find the weakness and problems in teaching and learning,and to put forward some suggestions.Methods With the S-P chart analytic system which was designed by using the technology of Excel VBA,we made an analysis of 164 papers of fundamental nursing.Results Both the P-curve and S-curve were in the lower right corner.There wasn't any long vertical line in P-curve.There wasn't any long horizontal one in S-curve either.Caution index of 4 questions and 15 students exceed 0.75.The totality difficulty index of the paper was 0.79.The suitability index was 0.43.The stability index was 0.48.Conclusions Propositional contents about physician orders management,gastric lavage,administering medication,observation of disease and pain need to be reformed into both wide coverage and clear focuses.There are no faults in students' grades.Different learning levels do exist,but with little difference.S-P chart affords multiplex and huge amount of information.Its graphics and parameters help to analyze both the students' and teachers' performance from the macroscopic and microcosmic angle,so as to make teachers self-reflective on teaching fundamental nursing and producing papers from various aspects. Key words: Educational measurement: S-P scale; Fundamental nursing

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.

엑셀 VBA를 이용한 이분형 로지스틱 회귀모형 교육도구 개발

이분형 로지스틱 회귀분석은 양적 혹은 질적 설명변수를 이용해서 이분형 반응변수를 설명하는 하나의 통계적인 기법이다. 이 모형에서는 반응변수가 1이 될 확률을 설명변수들의 선형결합의 변환(혹은 함수)으로 설명하고자 한다. 이 개념에 대한 이해가 비통계학자들이 이분형 로지스틱 회귀모형을 이해하는데 있어서 넘어야 할 커다란 장벽 중의 하나이다. 이 연구에서는 이분형 로지스틱 회귀모형의 필요성을 엑셀 VBA를 이용하여 설명하는 교육도구를 개발하고자 한다. 반응변수가 1이 될 확률을 설명변수의 선형함수로 모형화 할 때의 문제점과 선형결합에 대한 변환을 통해 이 문제점이 어떻게 해소되는지 보여준다. Binary logistic regression analysis is a statistical technique that explains binary response variable by quantitative or qualitative explanatory variables. In the binary logistic regression model, the probability that the response variable equals, say 1, one of the binary values is to be explained as a transformation of linear combination of explanatory variables. This is one of big barriers that non-statisticians have to overcome in order to understand the model. In this study, an educational tool is developed that explains the need of the binary logistic regression analysis using Excel VBA. More precisely, this tool explains the problems related to modeling the probability of the response variable equal to 1 as a linear combination of explanatory variables and then shows how these problems can be solved through some transformations of the linear combination.

Adaptive inventory control system for material items with continuous non-stationary demand

Abstract This paper describes an algorithm of dynamic inventory control system for large numbers of material items with continuous non-stationary demand. It uses principles of pull inventory control systems, statistical inventory analysis and joint replenishment inventory systems. This algorithm was processed in software module (VBA for Excel) and applied in practice. The target of described algorithm is to keep an optimum inventory level and optimum customer service level in terms of inventory control of items with non-stationary demand.

Software for the demonstration of the fundaments of portfolio selection

Financial liquidity interconnections are close to be a portfolio investment problem. The following article is a result of the Slovak–Polish cooperation, between partners from University of Economics in Bratislava and Wroclaw University of Economics. We have created a set of three programs in MS Excel which calculate the approximation of the border of the investment opportunities. The applications are continually developed. All programs are written in VBA for Excel. The following article introduces the second and the third program in which we have coded the fundaments of the portfolio selection to the VBA Excel. Their names are FINV and IDPORT. The FINV calculates the border of investment opportunities by using matrix algebra. FINV works with the enabled short sales. IDPORT calculates the border of investment opportunities by using SOLVER, which is the optimization library. The software has many settings which will be described in the article. It demonstrates the fundaments of the theory of investment portfolio and it is suitable for the teaching purposes at this stage of the development.

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.

Research of Excel VBA Teaching Based on Computational Thinking Ability Training

Recently, higher education has seen an increasing emphasis on the prominent role of computational thinking in computer fundamental education. Computational thinking is taken for the fundamental skills for everyone, not just computer scientist, to learn and use. How to develop students capacity for computational thinking during the teaching process is one of the basic goals of computer fundamental education. Using Excel VBA course teaching as example, in this paper, we discuss how to understand the essence of computational thinking and how cultivate the computational thinking ability of students.

An Algorithm for Influence-Zone Overlap Competition Indices

Evaluating competition for individual tree growth simulation is significant to forest management. However, few existing software were developed for practical use. To analyze the competition of spruce-fir conifer and broadleaf mixed forests in Changbai Mountain, China, an algorithm for Influence-zone overlap competition indices was developed. Firstly, based on different positions of subjected tree crown and competition tree crown, the overlap area and length were calculated separately in their related equations. Subsequently, a drawing flow chart, the user interface and fields of sheet were produced to construct concept models. Finally, the contribution of competition indices to individual tree basal area growth were tested by non-linear extra sum of squares method. Keyword: Competition Indices, Competitor Selection, Excel VBA,

A Microsoft Excel Application for Automatically Building Historical Geography GIS Maps

AbstractThe ability to visualize location and time information on satellite‐generated maps using geographic information systems (GIS) such as Google Earth has enabled researchers with limited mapping expertise to use GIS for a wide range of applications. GIS holds special benefits for research in historical geography involving visualization of events and time‐based changes in geographical information. This article describes a Microsoft Excel VBA application which automatically converts information contained in a Microsoft Excel workbook into a.kml (Keyhole Markup Language) file suitable for display by mapping systems such as Google Earth. The application requires no knowledge of KML or Excel VBA. An example illustrates how the application can be used to trace the Mongol invasion of Eastern Europe.

A Simple Data Acquisition Software for Serial Devices Based on Excel VBA and its Application in Rotary Kilns

In order to collect field data of rotary kilns for detailed analysis before automatic control system were designed and implemented, a simple data acquisition software for serial devices on the basis of Excel VBA was introduced in this paper. The serial communication module was coded with MSComm control. Data were presented both in tables and curves by the charting tools of Excel. A modified 53H algorithm was presented for online abnormal value detection while several data filtering functions were provided. Organized workbooks and worksheets were used as storage structures rather than a database system. Its application in a Lithopone rotary kiln proved its effectiveness in data acquisition, storage, query and preprocessing. This kind of simple data acquisition software has advantages in economical efficiency and simplicity, and is suitable for data acquisition applications with low sampling rates.

The Resource Leveling Problem with multiple resources using an adaptive genetic algorithm

article i nfo Article history: Accepted 3 October 2012 Available online 29 October 2012 Resource management ensures that a project is completed on time and at cost, and that its quality is as previously defined; nevertheless, resources are scarce and their use in the activities of the project leads to conflicts in the schedule. Resource leveling problems consider how to make the resource consumption as efficient as possible. This paper presents an Adaptive Genetic Algorithm for the Resource Leveling Problem, and its novelty lies in using the Weibull distribution to establish an estimation of the global optimum as a ter- mination condition. The extension of the project deadline with a penalty is allowed, avoiding the increase in the project criticality. The algorithm is tested with the Project Scheduling Problem Library PSPLIB. The pro- posed algorithm is implemented using VBA for Excel 2010 to provide a flexible and powerful decision support system that enables practitioners to choose between different feasible solutions to a problem in realistic environments.

Excel VBA를 활용한 중소 물류기업 창고 관리 시스템 적용 사례 연구

This research studies the present status of utilization of IT in SMB, Small & Medium Business, logistics companies, and analyzes the examples of utilizing Excel VBA for building and operating IT system in SMB logistics companies. Through the study, this research suggests main issues of building and operating logistic systems and the effective way of utilizing the systems in SMB logistics companies.

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.

Excel VBA による制御系設計ソフトウェアの開発

Excel VBA による制御系設計ソフトウェアの開発

Measuring and Managing Foreign Exchange Risk at a Large Pharmaceutical Firm: Application of Value at Risk

The present study has been carried out on the express request of a major pharmaceutical firm. The firm has substantial foreign exchange exposure and it needs a tool which can be used for managing foreign exchange risk. In this paper as the calculation of Value at Risk (VaR) is to deal with the risk involved with the options, their non-linear payoff nature, it is more challenging to incorporate all the option sensitivities in the model that we adopt to measure VaR. As the portfolio consists of currency pairs, it was more important to preserve correlation of the currency pairs while measuring VaR using any approach. We calculated delta of each option of the portfolio by using Black-Scholes option pricing formula and thereby calculated the unhedged amount for each asset in the portfolio. We have used variance-covariance approach, historical simulation and Monte Carlo simulation to calculate VaR. After comparison of results by weighing their advantages and disadvantages, historical simulation was ruled out to implement as it will not consider correlation between the assets. We have chosen to implement Variance-Covariance approach against the Monte Carlo simulation method mainly because it takes correlation into consideration and the latter is less transparent. Variance-Covariance approach is also faster and relatively easy to implement when compared to the Monte Carlo simulation. We designed a template using Excel VBA and MATLAB to calculate weekly VaR with the input variables (Current price of the underlying, Strike rate, Time to maturity, Volatility, Risk free interest rates of the currency pairs) and from the result we can interpret whether we are over hedged or under hedged using options as our hedging strategy. This study provides the insight of what can be the value still at risk when the firm is hundred percent hedged using options because of the option sensitivities. It also provides a lucid interpretation to the risk management executive to understand his position on the option hedging strategies.

Using Integer Programming for Airport Service Planning in Staff Scheduling

Reliability and safety in flight is extremely necessary and that depend on the adoption of proper maintenance system. Therefore, it is essential for aircraft maintenance companies to perform the manpower scheduling efficiently. One of the objectives of this paper is to provide an Integer Programming approach to determine the optimal solutions to aircraft maintenance planning and scheduling and hence the planning and scheduling processes can become more efficient and effective. Another objective is to develop a set of computational schedules for maintenance manpower to cover all scheduled flights. In this paper, a sequential methodology consisting of 3 stages is proposed. They are initial maintenance demand schedule, the maintenance pairing and the maintenance group(s) assignment. Since scheduling would split up into different stages, different mathematical techniques have been adopted to cater for their own problem characteristics. Microsoft Excel would be used. Results from the first stage and second stage would be inputted into integer programming model using Microsoft Excel Solver to find the optimal solution. Also, Microsoft Excel VBA is used for devising a scheduling system in order to reduce the manual process and provide a user friendly interface. For the results, all can be obtained optimal solution and the computation time is reasonable and acceptable. Besides, the comparison of the peak time and non-peak time is discussed.

육류의 품질과 Time Temperature Integrator(TTI) 온도의존성에 대한 수학적 Simulation

The temperature dependence of time temperature integrator (TTI) was investigated in terms of the Arrhenius activation energy (Ea) to determine TTI requirements to accurately predict meat quality during storage. Mathematical simulation was conducted using a numerical analysis. First, using Euler's method and MS Excel VBA, the TTI color change was kinetically modeled and numerically calculated under several storage conditions. From the TTI color variable profiles calculated from the storage time-temperature profiles, , which is a constant temperature representing the whole temperature profiles, was calculated. Upon predicting Pseudomonas spp. concentrations (one of the meat qualities) from , it was found that if be true, then Pseudomonas concentrations were calculated to be constant with the same TTI color values, regardless of time-temperature profiles, whereas if then Pseudomonas concentrations varied even with the same TTI color values. This indicates that each TTI color value represents its own fixed degree of meat quality, only if .