森の株

Introduction In this post, I will share how to set Option Buttons (also known as Radio Buttons) on a UserForm to be unselected (OFF) by default. Environment: OS: Windows 10 Pro Version: 20H2 Background I placed two option buttons, "Yes" ...

Background Unlike Excel, Word does not have the concept of cell addresses like "A1" or "F6". Therefore, I investigated how to extract data from a specific position in a Word table and search for that data within an Excel file. I achieved...

Introduction Excel includes a feature to add "Notes (Legacy Comments)" to cells. By using VBA, the process of automatically inserting these notes can be easily implemented. This article provides a detailed explanation of the basic usage ...

Introduction Placing data retrieved via Power Query into a worksheet as a Table (ListObject) makes it much easier to sort and aggregate. This article explains how to use VBA to deploy an existing query as an Excel Table using an external...

In Excel VBA, you can apply custom formatting to date data and handle it as a pre-formatted string. In scenarios such as creating forms, generating file names, or recording logs, it is common to convert dates into formats like "yyyy/mm/d...

Introduction When dealing with customer lists or product code tables, there are frequent situations where you want to check for duplicate data. Searching manually is difficult, but by using Excel's "Conditional Formatting," you can autom...

Overview When handling string data in Excel, you may encounter a mixture of word-level or unnecessary spaces (blanks). These spaces can cause issues such as search failures or inaccurate data aggregation. In this article, I will introduc...

Overview When you want to aggregate only the visible cells extracted by an AutoFilter, WorksheetFunction.Subtotal is the ideal choice. This article provides sample code to retrieve both the total for the filtered range and the total for ...

Overview When an AutoFilter is applied to a worksheet, retrieving the target cell range (headers + data rows) makes it easier to prevent processing errors outside that range. In this article, I will explain a macro that uses AutoFilter.R...

Introduction When providing a VBA UserForm as an operational interface, there are cases where automatic cleanup processing is required at the moment the form is closed. Examples include writing input content to a sheet or recording an ac...

Introduction When placing multiple text boxes or buttons on a VBA UserForm, aligning their positions and sizes manually using the mouse can be tedious and often results in slight misalignments. By using VBA code, you can bulk-configure p...

Introduction It is a common misconception that VBA (Macros) is exclusive to Excel. In reality, Microsoft Word also features a "Developer" tab and supports VBA functionality. By utilizing Word VBA, you can automate repetitive tasks and st...

Introduction When generating reports automatically with Excel VBA, there are many situations where layout automation is necessary, such as "moving this graph to a specific location" or "fitting a shape exactly to the size of certain cell...

Introduction In Excel VBA, there are frequent scenarios where you need to add a new worksheet during macro execution—whether to output summary results or to create a temporary workspace. While the Worksheets.Add method makes it easy to a...

Directly deploying data obtained via Power Query to a worksheet using VBA can significantly simplify operational workflows. This article demonstrates how to use the QueryTable object to load an internal workbook query, paste the results,...

Introduction When executing a macro in VBA, there are situations where "the processing is too fast to visually track what is happening" or "you want to insert a very brief weight (waiting time) between specific processes." VBA has a buil...

It is common to work across multiple sheets in a single Excel workbook, such as moving between an input sheet and a summary sheet, or a data sheet and its corresponding chart. While you can manually use Excel's "New Window" and "Arrange ...

Introduction There are times when you want to check how much disk space a specific project or backup folder is occupying. While you can check this manually by opening the folder's properties, you can also retrieve this information automa...

Introduction In Excel VBA, it is common to encounter situations where you need to move to or select a cell range located on a different sheet. However, since Range(...).Select can only be used on the currently active sheet, attempting to...

Introduction In VBA, array indices (the numbers that identify each element) start at 0 by default. However, there are many cases where managing data using a 1-based index is more intuitive, such as when dealing with "Months 1 to 12." VBA...

It is common to protect sheets containing formulas or important data to prevent users from accidentally changing them. Using VBA, you can automate the process of protecting and unprotecting worksheets. In this article, I will explain the...

Overview When working with a filtered worksheet, you may want to temporarily view all records without clearing the filter settings. Excel VBA provides the ShowAllData method, which allows you to toggle the visibility of all rows while ma...

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...

Introduction When manipulating specific sheets in VBA, methods like Worksheets("SheetName") or Worksheets(1) are common. However, these methods have a weakness: the macro stops with an error if the user renames the sheet or changes the s...

Introduction In Excel document creation, formatting "Outlines" is essential for emphasizing shapes and creating a unified design. If you can manipulate these styles using VBA, you can automate report formatting and significantly improve ...

When getting cell content using Excel VBA, you usually use the .Value property to retrieve the calculation result. However, if a function or formula is set in the cell, you might want to get the "actual formula itself" rather than the re...

Introduction When you share your VBA macros with others, they might not work as intended depending on the user's PC environment (e.g., Windows 10 vs. 11, or 32-bit vs. 64-bit Excel). Using VBA, you can easily retrieve the OS information ...

Background I am currently working with PowerPoint VBA. I wanted to create a feature that "displays a message box listing the name and type of every shape on a slide." Here is the solution I implemented. The Code Below is the VBA code. Th...

Pasting tables created in Excel into PowerPoint slides is a frequent task in report creation. Using VBA, you can not only automate this copy-and-paste process but also precisely specify the size and position of the pasted table on the sl...

Introduction Excel VBA's text-to-speech feature can do more than just read the values in cells; it can speak any text string you define within your program. By utilizing this feature, you can provide more active feedback to users. For ex...