VBA– tag –
-
[Excel VBA] How to Bulk Add Values to a ComboBox | Reading from a Cell Range
Background When setting up a pulldown menu (ComboBox) on a UserForm, adding multiple options manually can be tedious. Previously, I used to repeat the .AddItem method for every single item like this: ComboBox1.AddItem "Tokyo" ComboBox1.A... -
[Excel VBA] Macro to Open a CSV File When a Cell is Selected | Linking Column G Selection to Filenames in Column E
Overview When working in Excel, there are times when you want to automatically open a related file just by selecting a specific cell. In this article, I will introduce a VBA macro that "automatically opens a corresponding CSV file when y... -
【VBA】外部ファイルからデータをランダムに取得する方法｜サンプル抽出の自動化
経緯 Excel VBA（Visual Basic for Applications）は、日々の作業を効率化するための強力なツールです。 今回は、「外部のExcelファイルを開き、そこからランダムにデータを取得して、現在作業中のシートに反映させる方法」をご紹介します。 この技術は、... -
[VBA] Simplify Column Processing with Loops: How to Shorten Formula Copying Code
Introduction When writing Excel VBA, if you repeat similar processes, the code can become very long, making it difficult to manage and modify. In this article, I will introduce how to rewrite "code that performs the same process (copying... -
[PowerPoint VBA] How to Open an Excel File and List All Charts
Background When using VBA to automate tasks in PowerPoint, I encountered a requirement: "I want to open an external Excel file and retrieve a list of all charts contained within it." To achieve this, I wrote a code that controls Excel fr... -
[VBA] How to Select a Folder Using a Dialog Box
Introduction I was creating a VBA program to create a folder and save Excel files inside it. Initially, I fixed the folder path directly in the code. However, I realized that the destination folder might change each time I run the progra... -
[VBA] How to Extract Column D from Multiple Excel Files into a List (Batch Processing)
Background In my daily work, I often encounter situations where I need to summarize common items contained in multiple Excel files. Specifically, I wanted to collect data from Column D (the 4th column) of every file and combine it into a... -
[VBA] How to Split Strings and Extract Values using the Split Function
Background Imagine a situation where a single cell in Excel contains multiple pieces of information in one line, such as "Soccer, August 1, 8:00 PM, School Ground". I wanted to find a way to break this text down into individual parts (Ev... -
[VBA] How to Share Variables Between Modules: Using Global Variables
Background When organizing Excel VBA code into multiple modules, I encountered a situation where I wanted to use a variable obtained in Module 1 for processing in Module 2. Specifically, I used the Call statement to run a subroutine in a... -
[VBA] How to Automatically Fill a Name List in a Word Table (Looping)
I created a VBA script to automatically assign names to a schedule table created in Word. This code automates the repetitive task of manually typing the same names over and over again. What I Want to Achieve Automatically list names in t... -
[VBA] How to Fix Cut and Paste Issues
Introduction In this article, I will share the correct method to "Cut" and "Paste" data using VBA. I attempted to create a process to cut a range of cells in Excel and paste them into a different location. Initially, the code did not wor... -
[Excel VBA Beginners] How to Change Cell Font Color | Specifying Colors with RGB
Introduction Have you ever wanted to change the text color of a specific cell using Excel VBA? Changing the font color to red or blue based on conditions improves visibility and helps with data visualization. In this article, I will intr... -
[VBA] How to Convert Full-Width to Half-Width Characters | Using StrConv and vbNarrow
Environment OS: Windows 10 Pro (Version: 20H2) Software: Microsoft Excel VBA Background When processing values read from a barcode reader using VBA, I encountered a significant issue where the input was being interpreted as Full-Width (Z... -
[VBA Beginners] How to Check if a String Contains Specific Text (Using InStr)
Introduction In Excel VBA, a very common task is checking whether a specific word or phrase exists within a string. For example, you might want to execute a process only if a cell contains the word "Urgent". In this article, I will intro... -
[Excel VBA] How to Protect and Unprotect Worksheets
Introduction I wanted to automate the process of protecting and unprotecting sheets using VBA, so I learned how to do it. Solution: Use Protect and Unprotect In Excel VBA, you can protect or unprotect a sheet with a single line of code. ... -
[VBA] How to Change the Outline Color of a Shape (AutoShape)
Introduction In this article, I will share how to change the outline (border) color of a shape using VBA. I previously learned how to change the fill color, but I also wanted to customize the line color to suit my design. Here is the met... -
[Excel VBA] How to Search Cells from Bottom to Top (Using Step -1)
Introduction When working with Excel VBA, you often encounter situations where you need to search for cell contents from the bottom up. For example, you might want to find the very last occurrence of a specific name, like "Mori," in a lo... -
[Excel VBA] How to Split a String by Underscore and Retrieve Values (Using the Split Function)
Introduction Have you ever wanted to extract specific elements from a string separated by underscores (_), such as filenames or IDs? In this article, I will introduce how to use the VBA Split() function to divide a string by _ and extrac... -
[Excel VBA] How to Automatically Enter a Random Value from a Database into an Adjacent Cell
Introduction With Excel VBA, you can easily extract a random value from a specific cell range (acting as a database) and automatically enter it next to a selected cell. In this article, I will share a macro that randomly picks one item f... -
[VBA] How to Delete Only Specific Characters in a Cell (Using Characters().Delete)
Introduction In this article, I will share how to delete only a specific part of a text string within a cell using VBA. Background I encountered a situation where I needed to extract or delete a specific range of characters from a string... -
[VBA] How to Append Values to the Bottom of a Column: Transferring Calculation Results to Another File
In this article, I explain how to use VBA to calculate values in one Excel file and append the result to the bottom of a specific column in a separate file. Background The goal was to calculate the average value of data in Column E of an... -
[VBA] How to Lock and Unlock Cells: Using the Locked Property
I recently wanted to lock only specific cells using VBA, so I learned the method for locking and unlocking cells programmatically. Solution: Using the Locked Property In Excel VBA, you can control whether cells are locked (editing prohib... -
[VBA] How to Set the Initial Folder for File Dialog Boxes (ChDrive and ChDir)
When using a dialog box to select files in VBA, it can be very tedious to manually navigate to deep folder hierarchies if the initial folder is not set correctly. This article explains how to specify the folder that appears first when a ... -
Excel VBAで表の列を同じ値ごとに結合する方法
Excelでデータを整理する際、同じ文字が連続しているセルをまとめて見やすくするために「セルの結合」が役立つ場合があります。本記事では、VBAを使って列内の同じ値を持つセルを自動的に結合する方法を解説します。 前提条件 このコードを利用するための... -
Excel VBAで表から’-‘だけの列を削除する方法
Excelのデータ整理において、データが'-'で埋め尽くされた列は不要なことがよくあります。このような列を自動で削除する方法をVBAを使って簡単に実現する方法を解説します。 前提条件 このコードを利用する前提条件は以下の通りです。 表の範囲: 削除対象... -
Excel VBAで表から空白行を削除する方法
Excelでデータを整理する際、空白の行が含まれると効率的な作業が妨げられることがあります。この記事では、1つの表に対してVBAを使い、空白行を効率的に削除する方法を解説します。 前提条件 以下の条件でコードを使用できます。 表の構造: 表が1つのシー... -
Excel VBAで日付付きの新しいシートを作成し、色をつける方法
Excelで作業を進める中で、特定の日付付きのシートを自動で作成し、見やすく色をつける機能が欲しいと思ったことはありませんか？この記事では、VBAを使ってこれを簡単に実現する方法を紹介します。 実現すること 以下の3つを自動化します。 今日の日付を... -
Excel VBAで印刷設定を一発で完了！コード解説付き
Excelで作成したシートを印刷する際、「1ページに収めたい」「縦向きにしたい」「A4サイズに設定したい」といった要望はありませんか？手作業でこれらの設定をするのは手間ですが、VBAを使えばワンクリックで完了できます。 今回は、印刷設定に特化したシ... -
Excel VBAでテーブルの値を検索する方法【VBA】
Excel VBAを使って、指定した条件に一致するセルをテーブルから検索し、その値を利用する方法を紹介します。この手順により、大量のデータを含むテーブルから、特定の条件を満たすデータを効率的に抽出できるようになります。 1. 準備するもの テーブル: ... -
Excel VBAで番号に対応した画像を自動挿入＆ボタンを残して図形を削除する方法【VBA】
Excel VBAを使って、フォルダ内の画像を番号に対応した図形に自動で挿入するマクロと、指定したボタンだけを残してシート内の図形を一括で削除するマクロを紹介します。このコードを利用すれば、画像の配置やシート整理がより簡単に行えます。 1. 画像ファ...
