Excel VBA Course (10): Tools developed and used with Excel VBA

This article looks at examples of tools that I developed with Excel VBA myself and are using.

This is a continuation of the previous article.

Excel VBA Courses (9): Excel VBA How-To

5. Tools developed with Excel VBA

5.1. Naver Korean Dictionary, English Dictionary Search Tool

It is an automated tool that searches Naver Korean and English dictionaries and imports the search results into Excel. This tool can be of great help when creating standard words and descriptions (definitions) of standard terms during the data standardization work process. 

Overview of Naver Korean Dictionary/English Dictionary Search Tool

5.2. Work allocation optimization tool using one-dimensional bin packing algorithm

It is a tool that implements the one-dimensional bin packing algorithm, Next Fit, First Fit, Worst Fit, and Best Fit. It provides a way to optimize many work items with the goal of the minimum number of work groups and the minimum execution time. It can be used for data conversion (migration) job execution and batch program scheduling.

Work distribution optimization tool using one-dimensional bin packing algorithm Full Contents, Download

5.3. Data standard check tool

It is a tool that helps in the process of building a dictionary of standard words, standard domains, and standard terms. Standards Check -> Add Word -> Standards Check -> Add Domain -> Standards Check -> Add Term -> Standards Check -> Add Word... This can greatly increase productivity when repeatedly performing standard checks on a large number of attribute names, such as

Data Standard Check Tool Description Contents , Download

5.4. DA# Macro Tool

It is a macro tool that increases the utilization of DA#, a domestic data modeling tool. The DA# provides a great function called 'batch editing', but it is possible only in one model unit. This tool enables entity/property information retrieval (Get), modification (Set), and reverse functions to be processed for multiple models at once.

DA# Macro Description Contents , Download

5.5. PowerDesigner Macro tool

It is a PowerDesigner Macro tool that has some functions of DA# Macro. PowerDesigner v15.6, which was used at the time of making this tool, did not have a model unit 'batch editing' function.

I made the 'batch editing' function with Excel VBA and used it well, and I plan to use it again if needed in the future.

After the release of the DA# Macro, I will post it briefly.

5.6. Data Migration (Migration) Tools

It is a tool that creates a conversion/verification program (UNiX Shell) from a column mapping definition. It was developed for the purpose of automatically creating a conversion/verification program when the data conversion design (mapping definition) is written according to the rules.

It was developed in 2009 and has been directly used in 5 customer projects since that time including the project. There were about 7 participants who provided ideas for developing this tool or provided the source code by directly coding the necessary functions. Some of the participants were told that they continue to use the tool to this day, adapting it to their own uses. Among the tools I developed myself, this tool took the most time and effort to make, and I was satisfied with myself as I felt the effect in real time while making it.

This tool is not well documented, so it is necessary to open the source code again and to document the concept and function description. I will write it slowly and post it on my blog.

5.7. Excel Data Downloader

This is a tool that executes SQL queries in the database and downloads the results as Excel files. Define multiple databases and multiple SQL queries in advance and click the 'Download' button to execute each SQL query in the database in turn and create an Excel file. It is used for purposes such as collecting performance information from a database and periodically downloading business data as an Excel file.

This tool will also be posted on the blog soon.

5.8. Excel Data Uploader

Contrary to the 'Excel Data Downloader' above, it is a tool that uploads Excel files to the database. The initial version was created by a junior in the company, and I am using it by adding the necessary functions.

---

In this article, we looked at the tools developed with Excel VBA and used. All of these are tools I developed myself because I needed them at the project site. I hope that it will be of some help to various roles such as engineers who deal with data, data architects, and DBAs.

After completing all of the Excel VBA courses above, I will post in a separate article about VBA coding patterns, etc.

---

<< Related article list >>

• Start the Excel VBA course. (Lecture notice, feat. Why we recommend Excel VBA)
• Excel VBA Course(1): Overview of Excel VBA
• Excel VBA Course (2): Excel VBA Basics
• Excel VBA Course (3): Excel Object Model
• Excel VBA Course (4): Working with Excel Object Model
• Excel VBA Course (5): Excel File Extension, VBE, Font Settings
• Excel VBA Course (6): Excel VBA Language Basics - Variables
• Excel VBA Course (7): Excel VBA Language Basics - Syntax
• Excel VBA Course (8): Excel VBA Language Basics - Data Types, Data Structures
• Excel VBA Courses (9): Excel VBA How-To
• Excel VBA Course (10): Tools developed and used with Excel VBA
• Full Table of Contents for Excel VBA Courses