-
[VBA] How to Set Tab Order in UserForms to Improve Usability
Introduction Have you ever felt frustrated when pressing the Tab key on a VBA UserForm because the focus jumps around in a random, unintended order? This sequence in which the cursor moves is called the "Tab Order." When the tab order is... -
[Excel VBA] How to Always Add a New Worksheet to the End (Far Right)
Introduction When you add a new sheet in Excel, it is usually inserted to the left of the currently active sheet. However, there are many cases—such as creating monthly summary sheets or work logs—where you want to "always add the sheet ... -
[VBA] How to Create a Multi-Line TextBox in a UserForm
Introduction In VBA UserForms, there are times when you want users to input free-form text across multiple lines, such as for addresses or remarks. While the default TextBox only accepts a single line, you can easily support multi-line i... -
[Excel VBA] How to Bulk Delete All “Defined Names” in a Workbook [Caution]
Introduction Excel's "Defined Names" feature is very useful for giving specific cell ranges easy-to-understand names for use in formulas. However, as you repeatedly copy sheets or have multiple people edit a file, unnecessary definitions... -
[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... -
[VBA] How to Extract Matches and Values Using Regular Expressions
Overview Using Regular Expressions (RegExp) in Excel VBA allows you to easily extract strings that match specific patterns. In this article, I will explain how to retrieve matched values and their partial matches (groups) using the Match... -
[Excel VBA] Useful Macros to Toggle Display Settings (Formulas, Gridlines, Phonetics, and More)
Introduction When using Excel, there are many situations where you want to switch display settings, such as "checking all formulas temporarily," "hiding gridlines to check the print preview," or "showing phonetics (furigana) all at once.... -
[VBA] Introduction to 2D Arrays | Processing Excel Tabular Data at High Speed
Introduction In VBA, an "Array" usually refers to a "1-dimensional" list of data. However, when handling table-style data consisting of rows and columns—like an Excel worksheet—the more powerful "2-dimensional (2D) array" is extremely us... -
[Excel VBA] How to Move and Reorder Worksheets (.Move)
When creating a new report sheet using VBA, you often want to organize the workbook by moving that sheet to the far left (beginning) or far right (end) so users can find it easily. While you can change the order of sheets manually by dra... -
[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... -
[Excel VBA] How to Automatically Create a PowerPoint Presentation
Creating PowerPoint reports using data or charts analyzed in Excel is a common task in many business scenarios. By using VBA, you can automate the creation of these PowerPoint presentations. In this article, I will explain the most basic... -
[Excel VBA] How to Extract and Reorder Specific Columns Using AdvancedFilter
Overview By using the CopyToRange parameter of AdvancedFilter, you can extract only the necessary columns from your source data and copy them to another sheet in any order you like. You can achieve this simply by arranging the "desired c... -
[VBA] How to Define Methods (Sub/Function) in a Class
Introduction A VBA Class Module is a blueprint for an "Object" that combines data (Properties) and the processing to manipulate that data (Methods). While properties represent the "state" of an object, methods define the object's "behavi... -
[VBA] High-Precision Time Measurement in Milliseconds (GetTickCount API)
Introduction The VBA Timer function is useful for easily measuring processing time in seconds. However, if you want to compare the performance of processes that complete very quickly, the Timer function's precision is often insufficient.... -
[Excel VBA] How to Force Recalculation Before Saving Even in Manual Calculation Mode
In heavy Excel files containing a large number of formulas, users often set the calculation method to "Manual" to make operations smoother. However, this setting carries a significant risk: "forgetting to recalculate (F9 key) and saving ... -
[Excel VBA] How to Add New Records to a Table | Using ListObject.ListRows.Add
Excel Tables (ListObject) are very convenient as structured data. Understanding how to add records (rows) using VBA is incredibly helpful for automating data entry tasks. In this article, I will explain how to add the "next record" to a ... -
【VBA】外部ファイルからデータをランダムに取得する方法｜サンプル抽出の自動化
経緯 Excel VBA（Visual Basic for Applications）は、日々の作業を効率化するための強力なツールです。 今回は、「外部のExcelファイルを開き、そこからランダムにデータを取得して、現在作業中のシートに反映させる方法」をご紹介します。 この技術は、... -
[VBA] How to Retrieve Specific Pivot Table Values (GetPivotData)
Overview In Excel Pivot Tables, you often encounter situations where you want to automatically retrieve only the aggregated results that match specific conditions. The GetPivotData function is very useful for this purpose. In this articl... -
[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... -
[Excel VBA] How to Auto-Set Table Formulas Using Structured References ([@ColumnName])
In Excel Tables (ListObject), using "Structured References" allows you to set formulas that automatically apply calculations to every row. You can also use this feature from VBA to apply formulas like =[@Price]*[@Quantity] to an entire t... -
[VBA] How to Scroll a Specific Cell to the Top-Left of the Screen
When working in Excel, there are times when you want to display a specific cell exactly at the top-left corner of the screen. For example, you might want to instantly jump to a row in the middle of a data list and start working from ther... -
[VBA] Implementing the Quick Sort Algorithm: How to Sort Arrays Fast
Introduction When sorting arrays containing a large amount of data in VBA, processing speed becomes a critical factor. Among the many sorting algorithms available, Quick Sort is known as one of the fastest, as its name suggests. Quick So... -
[VBA] How to Make Function Arguments Optional (The Optional Keyword)
Introduction When creating custom VBA functions, you may want to make them more flexible by allowing certain arguments to be omitted. For example, you might have a value that is usually the same (a default), but you want the option to ch... -
[Excel VBA] How to Bulk Create Sequential Worksheets Using For Loops
Introduction Manually preparing 12 sheets for monthly reports (e.g., "April", "May"...) or separate summary sheets for each branch (e.g., "Branch01", "Branch02"...) can be very tedious. By combining the VBA For...Next loop with the Works... -
[Excel VBA] Fast Counting of Partial Matches Using COUNTIF and Wildcards
When analyzing survey results, you often need to count the number of cells that contain a specific text (partial match), such as "How many answers include the word 'Tokyo'?". While you can achieve this using a For loop and the Like opera... -
[Excel VBA] The Ultimate Technique to Speed Up Print Settings (PrintCommunication)
Have you ever felt that your macro runs surprisingly slowly when changing PageSetup settings, such as the print area, paper orientation, or margins? This slowness is particularly noticeable when using a network printer. The reason for th... -
[VBA] How to Pass Arguments to Subroutines: Basics for Writing Reusable Code
Introduction Splitting VBA processing into subroutines (modularized macros) helps organize your code. However, if you leave them as is, they can only execute processing on "specific cell ranges" or "fixed text strings." To turn a subrout... -
[VBA Beginners] Summary of Methods to Find the Next Available Row for Data Entry
There are many situations where you want to automatically write data to the next blank row in Excel. Adding new information to the end of existing data is a standard task in business automation. In this article, I will introduce multiple... -
[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...
