VBA樹林– category –
-
[VBA] How to Extract a List of Macro Names from a Module Using Regular Expressions
Introduction As a VBA project grows larger, you might find yourself thinking, "I forgot what macros I wrote in this module," or "I need a list of procedure names to create documentation." By combining the VBA VBProject object with Regula... -
[Excel VBA] How to Use VLOOKUP for Table Search (Including Error Handling)
Introduction In Excel data processing, the VLOOKUP function is one of the most commonly used tools. You can also utilize this powerful function within VBA macros via the WorksheetFunction object. This allows you to easily automate table ... -
[Excel VBA] Macro to Paste All Except Borders
Introduction Have you ever ruined the design of a carefully formatted report by pasting cells with thick borders from another location? Using "Paste Special" and selecting "All except borders" is one solution, but it is tedious to do thi... -
[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... -
[Excel VBA] How to Set UserForm Title and Size via Code
Introduction When creating UserForms in VBA, you typically set the text on the title bar (Caption) and the size of the form (Width and Height) using the Properties window. However, there are times when you want to change these values dyn... -
[Excel VBA] How to Set Detailed Cell Borders (Using Edge vs. Inside)
Overview In Excel, adding borders to cells helps clarify the structure of a table. There are many situations where you need to apply different settings to specific parts, such as the outer frame, inner lines, top, bottom, left, or right.... -
[Excel VBA] How to Add Callout Shapes to a Worksheet (Beginner’s Guide)
Introduction When creating documents in Excel, do you ever feel the need to highlight specific cell contents or automatically add annotations to a report? Adding shapes manually can be time-consuming, but with VBA macros, you can instant... -
[Excel VBA] How to Add a New Workbook and Control It Reliably Using Object Variables
Introduction Aggregating data in VBA and saving the results to a new workbook is a very common task. However, developers often face a problem: "I created a new workbook using Workbooks.Add, but how do I control that specific workbook acc... -
[VBA] Change Only the Outline Color of a Clicked Shape
Overview In this article, I will introduce a minimal Excel VBA code that changes only the outline color of a shape (button) when you click it. This code targets the specific shape you clicked and does not affect other elements like chart... -
[Excel VBA] How to Open All Hyperlinks in a Worksheet at Once
It is common to create lists of links in Excel, such as websites, files, or shortcuts to other sheets. However, clicking these links one by one can be time-consuming. This article introduces a method using VBA to automatically open all h... -
[Excel VBA] How to Change PivotTable Calculation Methods and Number Formats
In Excel PivotTables, numeric fields are automatically summed by default. However, in many business scenarios, you need to calculate averages, counts, or distinct counts instead of sums. Additionally, applying proper number formatting (s... -
[Excel VBA] Getting the Current Folder (CurDir) and Its Caveats
When handling files in VBA, you may want to base operations on the current working directory, also known as the "Current Folder." For example, if you save a file using only a filename without specifying a full path, it is saved in this c... -
[VBA Beginners] Preventing Input Errors: How to Set Up Warning Message Boxes for Invalid Data
In Excel, you can use the Data Validation feature to restrict the range or type of values entered into a cell to prevent errors. This article explains how to use VBA to automate this process, specifically focusing on how to display a cus... -
[Excel VBA] Automating Word Document Formatting: Styles and Alignment
When creating Word documents from Excel using VBA, simply pasting data isn't always enough. You often need to format the document—applying heading styles, centering text, or styling tables—to make it look professional. With VBA, you can ... -
[VBA] How to Disable (Uninstall) an Excel Add-in Using a Macro
If you distribute specific tools as Excel Add-ins, it is very user-friendly to provide a feature that allows users to temporarily turn off the functionality or easily disable it when it is no longer needed. Asking users to manually open ... -
[Excel VBA] How to Delete All Sheets Except One Specific Sheet
There are many situations where you want to clear all report sheets created during a previous process before running a macro again, while keeping a specific "Template" or "Master" sheet intact. When deleting multiple sheets in VBA, the m... -
[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... -
[Excel VBA] How to Aggregate Data from Multiple Closed Excel Files
Aggregating data from multiple Excel files is a common business task, but manually opening, copying, and pasting each file is extremely time-consuming. This article explains how to create a VBA macro that automatically opens closed Excel... -
[Excel VBA] How to Manipulate Table Data by Record: Using ListRow
When working with Excel Tables (ListObject) in VBA, there are often times when you want to retrieve, edit, or delete data one row (record) at a time. In VBA, you can use ListRows(row_number) to easily manipulate specific records. This ar... -
[VBA] How to Determine Which OptionButton is Selected in a UserForm
In VBA UserForms, there are many situations where you want the user to choose only one item from multiple options (e.g., selecting gender or survey ratings). The OptionButton control is perfect for this purpose. OptionButtons within the ... -
[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 Display Long Scrollable Text in a UserForm TextBox (Read-Only)
In VBA UserForms, there are times when you need to display long text—such as Terms of Service, application help, or detailed notes—that the user should read but not edit. In such cases, using a multi-line TextBox as a "read-only display ... -
[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 ... -
【VBA】ボタン（図形）を押すと、その外枠の色だけを変更する
概要 本記事では、ワークシート上の図形（ボタン）をクリックしたときに、その図形自身の外枠線の色だけを変更する、最小構成の Excel VBA をご紹介いたします。グラフや系列など、他要素には一切影響を与えません。色は Windows の「色の選択」ダイアログ... -
【VBA】ボタン一つで散布図の系列色を変更する
概要 本記事では、ワークシート上のボタンを一つクリックするだけで、散布図の特定系列の線色を変更する方法をご説明いたします。系列の種類やマーカー設定には一切触れず、凡例の並びを変えないことを重視した構成です。 前提条件 Microsoft 365 版 Excel... -
【VBA】ファイルの拡張子に関連付けられたプログラムのパスを取得する方法 (API)
はじめに VBAマクロから特定のファイルを開く際、通常は Shell 関数に直接ファイルパスを渡せば、Windowsが自動的に関連付けられたプログラムで開いてくれます。しかし、「.pdf ファイルを開くプログラムが何であるか（Acrobat Readerなのか、ブラウザなの... -
【VBA】マクロの処理をミリ秒（ms）単位で一時停止する方法 (Sleep API)
はじめに VBAでマクロを実行する際、「処理が速すぎて、何が起きているか目で追えない」「特定の処理の間に、ほんの少しだけウェイト（待ち時間）を入れたい」といった場面があります。 VBAには Application.Wait という待機命令がありますが、これは秒単... -
【VBA】マクロの処理時間をミリ秒（ms）単位で高精度に計測する方法 (GetTickCount API)
はじめに VBAの Timer 関数は、マクロの処理時間を秒単位で手軽に計測できますが、非常に高速に完了する処理のパフォーマンスを比較したい場合、秒単位では差が分からず、より精密な計測が必要になります。 このような高精度な時間計測には、Windows APIの... -
【VBA】ユーザーフォームを最大化・最小化・リサイズ可能にする方法 (Windows API)
はじめに VBAのユーザーフォームは、デフォルトではウィンドウサイズの変更ができず、タイトルバーには「閉じる」ボタンしかありません。しかし、表示するデータ量に応じてユーザーが自由に大きさを変えられたり、一時的に最小化してシートの作業に戻れた... -
【VBA】ユーザーフォームのタイトルバーと「閉じる」ボタンを完全に消去する方法
はじめに VBAのユーザーフォームで、ユーザーにフォーム上のボタン以外の操作を一切させたくない場合、タイトルバー自体（タイトル、アイコン、「閉じる」ボタンを含む全て）を非表示にしたい、という究極のカスタマイズ要求があります。これにより、フォ...
