VBA樹林– category –

Overview When creating reports in Excel, manually adjusting chart ranges every time the data volume increases or decreases is tedious. By utilizing the VBA SetSourceData method, you can update the specified cell range with a single click...

In Excel VBA, you can directly input (assign) values to cells or cell ranges. Writing values to specific cells is a frequently used operation for initializing forms or as part of data processing. This article introduces how to assign val...

Introduction When creating UserForms in VBA, optimizing the display position and size based on the user's screen size is often necessary. For example, centering a form according to the screen resolution or adjusting a window so it does n...

Introduction Have you ever wanted to know how often a shared Excel file is actually viewed? For example, it would be useful to know if "profile" or "guideline" files—which often exist just for the sake of formality—are actually being ope...

There are many situations where large amounts of processing results calculated or processed in VBA macros need to be written to a worksheet. If you use a For loop to write values one cell at a time, the processing speed becomes extremely...

Introduction In VBA UserForms, there are frequent scenarios where specific processing needs to be executed the moment a checkbox is toggled on or off. A common example is interactive behavior, such as "enabling a text box for detailed in...

Introduction In VBA error handling, On Error GoTo jumps to a specific label regardless of the error, which can make it difficult to implement fine-grained responses based on the error type. On the other hand, On Error Resume Next simply ...

Have you ever faced a situation where you need to compare a "Member List" and an "Event Attendee List" to find "Members who did not attend the event"? This task involves comparing two lists to find the "difference" data that exists only ...

Introduction When you launch other applications from a VBA macro using the Shell function or manipulate applications that are already open, the target window may sometimes be hidden behind other windows. In such cases, you can use the Wi...

When saving files to a specific folder or reading files from a folder using VBA macros, if the target folder does not exist, an error will occur, and the macro will stop. To prevent such errors, it is a golden rule to include a process t...

Background When creating tools using UserForms in Excel, I was bothered by the fact that the form remained on the screen even after the program was executed. It looked cluttered, and I wanted to close the form to clearly indicate that th...

Introduction When creating an operation panel using a UserForm in VBA, you will inevitably encounter situations where you want to "execute a main process written in a standard module when a button on the form is clicked." For example, yo...

Introduction Since XML data has a hierarchical structure, using XPath (XML Path Language) is very effective for pinpointing and retrieving specific information. In Excel VBA, by using MSXML2.DOMDocument combined with XPath expressions, y...

In business, there are many situations where you need to know "how many business days later" something is, rather than just simple calendar days. For example, if you want to count the duration of a project excluding weekends and public h...

When managing schedules or logs in Word tables, you often need to delete rows containing dates that have already passed. This article introduces a VBA code snippet that automatically deletes rows with dates earlier than "today." Goal Tar...

Introduction While you can execute Command Prompt commands from VBA using the Shell function, there are times when you want to receive the execution result (text displayed on the screen) as a VBA variable to use within your program. Exam...

Introduction In VBA UserForms, labels play a crucial role not just as static text, but as tool titles or status messages conveying processing results. While label formatting is typically set in the Properties window, changing it dynamica...

Introduction When processing data in Excel, you often encounter situations where you need to "delete rows that contain blank cells." Deleting them manually one by one is time-consuming, but combining VBA's SpecialCells and EntireRow allo...

In Excel Tables (ListObject), columns are referred to as "Fields." Using VBA, you can process only specific columns (fields). This article introduces the basic steps to retrieve and manipulate specific columns in a table using ListColumn...

Environment Edition: Windows 10 Pro Version: 20H2 Background In Excel, you often drag the small square (Fill Handle) at the bottom right of a cell to copy formulas or values continuously. I wanted to automate this operation using VBA. In...

"Event procedures" like Worksheet_Change are very convenient features that allow macros to execute automatically based on specific actions. However, if used incorrectly, they can cause unintended infinite loops (chain reactions), causing...

When integrating with Web APIs or exchanging data with modern web applications, JSON (JavaScript Object Notation) is standard. The need to convert data managed in Excel into this JSON format is increasing year by year. This article expla...

Introduction When handling JSON data using only Excel VBA, using ScriptControl to parse data with JavaScript's eval function is a convenient method. This article explains the steps to read JSON in an array format and write the values of ...

When saving files created by macros or processing multiple files in a specific folder, you often want the user to select the target folder themselves. VBA provides two main ways to display a "Folder Selection" dialog box. This article ex...

When manipulating table data using Excel VBA, you often need to "automatically retrieve the first and last cells of a table." For example, you might need to identify the starting position for data entry or add new data to the end. This a...

Overview The InternetExplorer.Application object used for Internet Explorer is being phased out. For Windows 10 and later, automating Microsoft Edge is recommended. This article explains the steps to launch Edge from Excel VBA using Sele...

Background When working in Excel, you might lose track of which row you were looking at because there are too many columns. This is common with horizontal scrolling or long tables where it is easy to lose sight of the row containing the ...

Introduction In VBA procedures, besides the Sub we have seen so far, there is another type called Function. The biggest difference between Sub and Function is that a Function can return a "return value" as the result of a process. By cre...

Overview Excel's AdvancedFilter allows for flexible data extraction combining AND/OR conditions by setting a "Criteria Range" that reuses column headers. This article provides sample code to execute In-Place Filtering (only changing the ...

Overview When creating reports by product in Excel, you may encounter situations where you want to change the target product name via a cell or VBA and immediately re-run Power Query. This article explains the steps to replace query para...