• Title/Summary/Keyword: VBA 응용

Search Result 8, Processing Time 0.01 seconds

Design of Application Module for the Excel File Security Management (엑셀 파일의 보안 관리를 위한 응용 프로그램 모듈 설계)

  • Jang, Seung Ju
    • Journal of the Korea Institute of Information and Communication Engineering
    • /
    • v.23 no.9
    • /
    • pp.1173-1178
    • /
    • 2019
  • In this paper, we design a security management application module for an Excel VBA password file. You will set a password for the important VBA program file. If this password is lost, you set a new password. If you forgot the password after setting the password in the Excel VBA file, you will not be able to change the VBA source code. In this paper, we design a function to modify VBA file passwords conveniently. The VBA password modification module extracts VBA files from Excel files. The password can be modified by modifying specific field information in the extracted VBA program file. This allows you to modify the password for the VBA program file. The experiments were performed by implementing the contents proposed in this paper. As a result of the experiment, we can confirm that the password can be used by modifying the VBA file password.

Excel VBA를 이용한 행렬도 시스템의 구현

  • Yu, Seong-Mo;Seo, Yong-Hwan
    • Proceedings of the Korean Statistical Society Conference
    • /
    • 2002.11a
    • /
    • pp.311-314
    • /
    • 2002
  • 자료행렬에서 개체와 변수간의 관계성을 시각적으로 표현하기 위한 방법 중의 하나가 행렬도이다. 본 논문에서는 전문적인 통계 패키지를 이용한 행렬도 구현이 아니라, 가장 널리 사용되는 응용프로그램 중의 하나인 Excel 에서 VBA를 이용하여 행렬도 시스템을 구현하였다.

  • PDF

Application of MS Excel in Teaching Statics (엑셀 프로그램을 활용한 정역학 교육 사례)

  • Kim, Youngheub
    • Transactions of the KSME C: Technology and Education
    • /
    • v.2 no.1
    • /
    • pp.21-28
    • /
    • 2014
  • As one of the most famous spreadsheet programs that is widely applied to a variety of fields in business, MS Excel has also been used for educational purposes due to its feature of wide accessibility, enabling students to use the program through almost any kind of PC that now exists. The program is mainly used for numerical analysis and formulae applications in the fields of science and engineering. This paper shall provide the key to understanding the application of MS Excel to teaching Statics through the illustration of its essential functions for education. Also, the development process of the analysis program using macros and VBA(Visual Basic for Applications) is described for the deeper comprehension of advanced applications. Students were not only able to solve the Statics problems using basic features of MS Excel, but also discovered new systematic methods of approaching complex problems and developed application programs using macros and VBA.

A Development of Multivariate Analysis System by Using Excel (EXCEL을 이용한 다변량자료분석 시스템 개발)

  • 한상태;강현철;한정훈
    • The Korean Journal of Applied Statistics
    • /
    • v.17 no.1
    • /
    • pp.165-172
    • /
    • 2004
  • Recently, there have been several studies to develop the multivariate data analysis system which can be readily used. The common characteristic of these studies is to develop the GUI system to which advanced statistical methods can be conveniently applied. In an extension of these studies, this study aims to supply users in various fields an interactive system with the convenience of the environment of GUI, which is constructed with the Excel macro and VBA, to apply multivariate data analysis methods easily. This system provides a graphic-oriented and menu-centered user interface in the Microsoft Excel which is widely used spreadsheet and analysis program.

A connection method of LPSolve and Excel for network optimization problem (네트워크 최적화 문제의 해결을 위한 LPSolve와 엑셀의 연동 방안)

  • Kim, Hu-Gon
    • Journal of Korea Society of Industrial Information Systems
    • /
    • v.15 no.5
    • /
    • pp.187-196
    • /
    • 2010
  • We present a link that allows Excel to call the functions in the lp_solve system. lp_solve is free software licensed under the GPL that solves linear and mixed integer linear programs of moderate size. Our link manages the interface between Excel and lp_solve. Excel has a built-in add-in named Solver that is capable of solving mixed integer programs, but only with fewer than 200 variables. This link allows Excel users to handle substantially larger problems at no extra cost. Futhermore, we introduce that a network drawing method in Excel using arc adjacency lists of a network.

A Study on the Automated Generation of Arena Simulation Models Using Conceptual Models (개념 모델을 이용한 Arena 시뮬레이션 모델 자동 생성에 관한 연구)

  • Ra, Hyun-Woo;Choi, Seong-Hoon
    • Journal of the Korea Society for Simulation
    • /
    • v.23 no.4
    • /
    • pp.21-29
    • /
    • 2014
  • In general, a simulation project requires much time and money since we should develop a model that works similarly to the system at a level consistent with the project purposes. Therefore, more active research studies are required to reduce the time needed for the modeling process. This is achievable by minimizing the possible trial and error during the model development process through the appropriate conceptual model design and the automated generation of the simulation model. This paper presents a tool automatically generating an Arena model after developing a conceptual simulation model. Because our proposed tool is based on the popular Microsoft Excel and Visio, it is expected to be practically used at many industrial sites. Finally, we showed the effectiveness of the newly suggested tool by applying it to an imaginary simulation project.

A Collaborative Filtering System Combined with Users' Review Mining : Application to the Recommendation of Smartphone Apps (사용자 리뷰 마이닝을 결합한 협업 필터링 시스템: 스마트폰 앱 추천에의 응용)

  • Jeon, ByeoungKug;Ahn, Hyunchul
    • Journal of Intelligence and Information Systems
    • /
    • v.21 no.2
    • /
    • pp.1-18
    • /
    • 2015
  • Collaborative filtering(CF) algorithm has been popularly used for recommender systems in both academic and practical applications. A general CF system compares users based on how similar they are, and creates recommendation results with the items favored by other people with similar tastes. Thus, it is very important for CF to measure the similarities between users because the recommendation quality depends on it. In most cases, users' explicit numeric ratings of items(i.e. quantitative information) have only been used to calculate the similarities between users in CF. However, several studies indicated that qualitative information such as user's reviews on the items may contribute to measure these similarities more accurately. Considering that a lot of people are likely to share their honest opinion on the items they purchased recently due to the advent of the Web 2.0, user's reviews can be regarded as the informative source for identifying user's preference with accuracy. Under this background, this study proposes a new hybrid recommender system that combines with users' review mining. Our proposed system is based on conventional memory-based CF, but it is designed to use both user's numeric ratings and his/her text reviews on the items when calculating similarities between users. In specific, our system creates not only user-item rating matrix, but also user-item review term matrix. Then, it calculates rating similarity and review similarity from each matrix, and calculates the final user-to-user similarity based on these two similarities(i.e. rating and review similarities). As the methods for calculating review similarity between users, we proposed two alternatives - one is to use the frequency of the commonly used terms, and the other one is to use the sum of the importance weights of the commonly used terms in users' review. In the case of the importance weights of terms, we proposed the use of average TF-IDF(Term Frequency - Inverse Document Frequency) weights. To validate the applicability of the proposed system, we applied it to the implementation of a recommender system for smartphone applications (hereafter, app). At present, over a million apps are offered in each app stores operated by Google and Apple. Due to this information overload, users have difficulty in selecting proper apps that they really want. Furthermore, app store operators like Google and Apple have cumulated huge amount of users' reviews on apps until now. Thus, we chose smartphone app stores as the application domain of our system. In order to collect the experimental data set, we built and operated a Web-based data collection system for about two weeks. As a result, we could obtain 1,246 valid responses(ratings and reviews) from 78 users. The experimental system was implemented using Microsoft Visual Basic for Applications(VBA) and SAS Text Miner. And, to avoid distortion due to human intervention, we did not adopt any refining works by human during the user's review mining process. To examine the effectiveness of the proposed system, we compared its performance to the performance of conventional CF system. The performances of recommender systems were evaluated by using average MAE(mean absolute error). The experimental results showed that our proposed system(MAE = 0.7867 ~ 0.7881) slightly outperformed a conventional CF system(MAE = 0.7939). Also, they showed that the calculation of review similarity between users based on the TF-IDF weights(MAE = 0.7867) leaded to better recommendation accuracy than the calculation based on the frequency of the commonly used terms in reviews(MAE = 0.7881). The results from paired samples t-test presented that our proposed system with review similarity calculation using the frequency of the commonly used terms outperformed conventional CF system with 10% statistical significance level. Our study sheds a light on the application of users' review information for facilitating electronic commerce by recommending proper items to users.

Optimization of Multiclass Support Vector Machine using Genetic Algorithm: Application to the Prediction of Corporate Credit Rating (유전자 알고리즘을 이용한 다분류 SVM의 최적화: 기업신용등급 예측에의 응용)

  • Ahn, Hyunchul
    • Information Systems Review
    • /
    • v.16 no.3
    • /
    • pp.161-177
    • /
    • 2014
  • Corporate credit rating assessment consists of complicated processes in which various factors describing a company are taken into consideration. Such assessment is known to be very expensive since domain experts should be employed to assess the ratings. As a result, the data-driven corporate credit rating prediction using statistical and artificial intelligence (AI) techniques has received considerable attention from researchers and practitioners. In particular, statistical methods such as multiple discriminant analysis (MDA) and multinomial logistic regression analysis (MLOGIT), and AI methods including case-based reasoning (CBR), artificial neural network (ANN), and multiclass support vector machine (MSVM) have been applied to corporate credit rating.2) Among them, MSVM has recently become popular because of its robustness and high prediction accuracy. In this study, we propose a novel optimized MSVM model, and appy it to corporate credit rating prediction in order to enhance the accuracy. Our model, named 'GAMSVM (Genetic Algorithm-optimized Multiclass Support Vector Machine),' is designed to simultaneously optimize the kernel parameters and the feature subset selection. Prior studies like Lorena and de Carvalho (2008), and Chatterjee (2013) show that proper kernel parameters may improve the performance of MSVMs. Also, the results from the studies such as Shieh and Yang (2008) and Chatterjee (2013) imply that appropriate feature selection may lead to higher prediction accuracy. Based on these prior studies, we propose to apply GAMSVM to corporate credit rating prediction. As a tool for optimizing the kernel parameters and the feature subset selection, we suggest genetic algorithm (GA). GA is known as an efficient and effective search method that attempts to simulate the biological evolution phenomenon. By applying genetic operations such as selection, crossover, and mutation, it is designed to gradually improve the search results. Especially, mutation operator prevents GA from falling into the local optima, thus we can find the globally optimal or near-optimal solution using it. GA has popularly been applied to search optimal parameters or feature subset selections of AI techniques including MSVM. With these reasons, we also adopt GA as an optimization tool. To empirically validate the usefulness of GAMSVM, we applied it to a real-world case of credit rating in Korea. Our application is in bond rating, which is the most frequently studied area of credit rating for specific debt issues or other financial obligations. The experimental dataset was collected from a large credit rating company in South Korea. It contained 39 financial ratios of 1,295 companies in the manufacturing industry, and their credit ratings. Using various statistical methods including the one-way ANOVA and the stepwise MDA, we selected 14 financial ratios as the candidate independent variables. The dependent variable, i.e. credit rating, was labeled as four classes: 1(A1); 2(A2); 3(A3); 4(B and C). 80 percent of total data for each class was used for training, and remaining 20 percent was used for validation. And, to overcome small sample size, we applied five-fold cross validation to our dataset. In order to examine the competitiveness of the proposed model, we also experimented several comparative models including MDA, MLOGIT, CBR, ANN and MSVM. In case of MSVM, we adopted One-Against-One (OAO) and DAGSVM (Directed Acyclic Graph SVM) approaches because they are known to be the most accurate approaches among various MSVM approaches. GAMSVM was implemented using LIBSVM-an open-source software, and Evolver 5.5-a commercial software enables GA. Other comparative models were experimented using various statistical and AI packages such as SPSS for Windows, Neuroshell, and Microsoft Excel VBA (Visual Basic for Applications). Experimental results showed that the proposed model-GAMSVM-outperformed all the competitive models. In addition, the model was found to use less independent variables, but to show higher accuracy. In our experiments, five variables such as X7 (total debt), X9 (sales per employee), X13 (years after founded), X15 (accumulated earning to total asset), and X39 (the index related to the cash flows from operating activity) were found to be the most important factors in predicting the corporate credit ratings. However, the values of the finally selected kernel parameters were found to be almost same among the data subsets. To examine whether the predictive performance of GAMSVM was significantly greater than those of other models, we used the McNemar test. As a result, we found that GAMSVM was better than MDA, MLOGIT, CBR, and ANN at the 1% significance level, and better than OAO and DAGSVM at the 5% significance level.