VBA樹林– category –

When processing multiple cells at once in Excel VBA, one common challenge is "how to handle blank cells." For example, have you ever encountered situations like these? You want to sum only cells where numbers are entered. You want to tra...

When automating data processing in Excel VBA, there are many situations where you want to search for a cell containing specific text and perform operations on it. In such cases, using the Find function allows you to efficiently locate th...

Introduction In this article, we will look at how to branch processing using option buttons placed on a UserForm. Environment: OS: Windows 10 Pro Version: 20H2 Background I wanted to place two option buttons on a UserForm, "Yes" and "No,...

Have you ever experienced text displaying as "???" (garbled text) when uploading a CSV file created in Excel VBA to a web system or opening it on a PC with a different OS? This "Mojibake" occurs because the character encoding standardly ...

There is often a need to obtain the sorted results of an Excel data range or a VBA array in memory without modifying the original data. By calling the SORT function (available in Excel for Microsoft 365 and Excel 2021 or later) from VBA,...

When you want to retrieve HTML or JSON from a website without launching a browser, you can use HTTP communication objects such as MSXML2.XMLHTTP. In this article, I will introduce the procedure to send a GET request to a sample URL (http...

When you try to copy a chart in VBA, you may suddenly encounter the error: "Run-time error -2147221040 (800401d0): Copy method of ChartObject class failed." This error can be confusing and difficult for VBA beginners to handle. In this a...

When building processes in VBA, you often need to create conditional branches based on time, such as "stop processing if the current time is past XX:00." I actually had a requirement to "prevent data writing after 5:00 PM," so I research...

As you proceed with VBA development, you will often face situations where you want to use the same logic across multiple files. Copying and pasting code every time is inefficient. Furthermore, if you need to make corrections, you have to...

Overview Many people use multiple charts in Excel to visualize data and show changes over time. However, if the X-axis represents a "time axis," you often need to change the period or adjust the intervals. Doing this manually for every s...

Sometimes, you may want to paste a part of a table or report as a "snapshot" to another location. This ensures that the values and formatting remain fixed and do not change. By using the .CopyPicture method in VBA, you can copy a cell ra...

Introduction This article explains the procedure to automatically launch the default browser from Excel VBA and open a specific web page. Using morinokabu.com as an example, we will introduce code and tips useful for improving business e...

Introduction Getting data from a "selected row" after a user chooses an item in a UserForm ListBox is one of the most basic operations in VBA tool development. This is especially true for multi-column ListBoxes, where you often need to a...

When organizing data in Excel, "Merge Cells" is useful for grouping consecutive cells with the same text to make them easier to read. In this article, I will explain how to automatically merge cells with the same value in a column using ...

Introduction Excel worksheets allow you to place two types of controls: "Form Controls" and "ActiveX Controls." Among these, Form Controls are generally easier to use. Usually, you link these controls to cells using the LinkedCell proper...

Background When multiple people use an Excel file on a shared server, someone might start editing without knowing that another person already has the file open. This often leads to accidental overwrites or errors. For example, "Mr. Mori ...

Introduction When using VBA macros to output a new file to the same folder as the current workbook or to log the location of an open file, you need to retrieve the workbook's "Full Path" (the complete path combining the file location and...

Environment Edition: Windows 10 Pro Version: 20H2 Background When I tried to close a file using VBA, the following message appeared: "There is a large amount of information on the Clipboard. Do you want to be able to paste this informati...

Overview Excel allows you to specify wildcards (* or ?) in AutoFilter to extract only rows that contain or do not contain specific strings. In this article, I will carefully explain the basics and applications of wildcard specifications ...

Overview In workbooks that utilize Power Query, unnecessary queries and connection information often remain from the trial-and-error process. To save you the trouble of manually deleting unnecessary objects, I will introduce a macro that...

There is a very common business need to batch read data from multiple Excel files stored in a specific folder. In this article, I will introduce a VBA macro that automatically aggregates data from all Excel workbooks in a specified folde...

Background In daily work, I often wanted to "automatically record the date in column A when a value is entered in column B." I thought this would be useful for managing work dates or input dates, so I learned how to automate this recordi...

Introduction When using UserForms created in VBA with default settings, they may appear in unexpected locations. To improve tool usability, it is important to display the form in an easy-to-understand position, such as the center of the ...

Introduction When you declare an array in VBA like Dim myArray(1 To 5), its size is fixed. However, in many cases, you do not know how many elements you need to store until you run the program. For example, you might want to reserve arra...

Introduction When automating with Excel VBA, there is often a need to "automatically execute a process when a value is entered into a cell." In my case, I wanted to automatically enter a date when text was entered in column B, so I resea...

Background When I tried to execute an external VBScript file (.vbs) from VBA, I encountered an error message saying "Unknown runtime error," and the script would not run. At first, I thought there was a mistake in my script code. However...

There are many situations where you want to read external text files (.txt), such as CSV files or system logs, into Excel to use as data. By using the FileSystemObject (FSO), you can easily open text files and read their contents with VB...

When opening another workbook using VBA, if that workbook has a Workbook_Open event set, the macro will execute immediately upon opening. This can be problematic if you only want to read or write data but end up triggering message boxes ...

I will explain the warning message often seen when saving files: "Be careful, as your document may contain personal information that cannot be removed by the Document Inspector." Many people see this and wonder what causes it or if they ...

Background When creating tools in Excel, I often want to simplify user input and operations. Therefore, I use UserForms in my programs. I wanted to be able to open a completed UserForm by clicking a button on the Excel sheet, so I learne...