VBA樹林– category –
-
[Power Query x VBA] How to Create a Macro to Auto-Apply “F2” + “Enter” to All Cells
Introduction When importing data using Power Query, have you ever encountered a situation where the cell values are not recognized as the intended data type? For example, numbers or dates might not be recognized correctly, preventing cal... -
[Power Query x VBA] How to Create a “Refresh All” Button
Introduction When importing data using Power Query in Excel, do you ever feel that clicking the [Refresh All] button in the "Data" tab every time is a hassle? In this article, I will show you how to improve work efficiency by using VBA t... -
[VBA] How to Output Data Scanned with Pasori RC-S300 to Excel
Background I wanted to see if I could use the data inside a card using a contactless IC card reader, so I studied how to achieve this. Install the Driver First, download the driver from the link below and install the exe file. https://ww... -
[VBA] How to Paste an Excel Table into Outlook
Environment Windows Specification: Windows 10 Pro Version: 20H2 Background I investigated whether it is possible to paste a table created in Excel into an Outlook email using VBA. Using ".GetInspector().WordEditor.Windows(1).Selection.Pa... -
[VBA] How to Set Today’s Date as the Default Value in a UserForm TextBox
Environment Windows Specification: Windows 10 Pro Version: 20H2 Background I decided to create a text box in a UserForm using VBA to enter a date. Initially, it was blank, but there was a request to "insert today's date by default," so I... -
[VBA] How to Add Line Breaks to Outlook Email Body
Environment Windows Specification: Windows 10 Pro Version: 20H2 Background I tried sending an email using VBA, but the line breaks in the body did not work properly, and the text was output as a single continuous line. Therefore, I learn... -
[VBA] How to Open External Excel Files in the Background Using ScreenUpdating
When you implement a process to open an external Excel file using Excel VBA, the target file often fills the screen and gets in the way. In this article, I learned how to open files in the background and suppress screen updates, so I wil... -
[VBA] Error “Outlook Does Not Recognize One or More Names” at objMail.Send
Environment Windows Specification: Windows 10 Pro Version: 20H2 Background When I tried to send an email using VBA, I received the error "Outlook does not recognize one or more names." When debugging, the error pointed to objMail.Send. I... -
[VBA] How to Use “Shift + Ctrl + Down” and “Shift + Ctrl + Right”
Environment Windows Specification: Windows 10 Pro Version: 20H2 Background I wanted to use Excel's shortcuts "Shift + Ctrl + Down" and "Shift + Ctrl + Right" in VBA, so I studied how to implement them. How to Write the Code You can write... -
“Microsoft MonthView Control” is Missing [VBA]
Environment Windows Specification: Windows 10 Pro Version: 20H2 Excel Version: 2108 Background I created a Gantt chart, but entering dates manually was tedious, so I thought of a way to click dates on a calendar. I searched and found tha... -
[Power Query x VBA] How to Create a Macro to Automatically Apply “F2” + “Enter” to All Cells
When importing data with Power Query, cell values sometimes do not end up as the intended data type. For example, numbers or dates may not be recognized correctly, preventing expected calculations or display. I confirmed that manually do... -
[Excel VBA] Complete Print Settings with One Click! With Code Explanation
When printing a sheet created in Excel, do you often want to "fit it to one page," "set it to portrait," or "set it to A4 size"? Manually configuring these settings every time is a hassle, but with VBA, you can complete it with a single ... -
[VBA] How to Attach the Currently Open File in Outlook
Environment Windows Specification: Windows 10 Pro Version: 20H2 Background I was able to send emails using VBA, but I also wanted to attach the file I was currently working on. I studied how to do this and found the solution. Using "Atta... -
[[VBA] How to Automatically Move to a Specified Cell After Pressing Enter]
Background In Excel, when input items are scattered, moving back and forth between cells can be tedious. In my case, the cells to be entered were scattered like "A1", "C4", and "F3", and I felt it was a hassle to move with the mouse or a... -
[Power Query x VBA] How to Create a “Refresh All” Button
When importing data using Power Query in Excel, clicking the [Refresh All] button in the "Data" tab every time can be annoying. In this article, I created a macro using VBA to execute [Refresh All] with a single button click to improve e... -
[How to Automatically Insert and Reset Images in Excel VBA]
Let's create two macros using Excel VBA: one to automatically place images from a folder into specific cells, and another to delete all placed images with a single click. These codes will allow you to easily arrange images on an Excel sh... -
[VBA] Difference Between “Exit Sub” and “End” for Stopping Macros: How to Use Them Correctly
When creating macros in VBA, there are often situations where you want to stop processing midway if specific conditions are met. Common examples include "a required file is not found" or "a mandatory input cell is blank." VBA provides Ex... -
[VBA] How to Reproduce “Ctrl + F” Search Function | Usage of Find Method
Environment OS: Windows 10 Pro (Version: 20H2) Software: Microsoft Excel VBA Background I learned how to reproduce the "Ctrl + F" search function in Excel using VBA to find specific strings within cells. Solution: Use the Find Method In ... -
[Excel VBA] How to Search for Values in a Table (ListObject)
This article introduces how to use Excel VBA to search for cells in a table that match specified conditions and retrieve their values. This procedure allows you to efficiently extract data that meets specific criteria from tables contain... -
[Excel VBA] Two Ways to Decode URL-Encoded Strings
Japanese characters and symbols in URLs are converted into percent-encoding (e.g., %E3%81%AA). In Excel VBA, you can easily revert (decode) these strings to their original form using JScript or PowerShell. This article summarizes code ex... -
[Excel VBA] How to Cancel Cut/Copy Mode [Usage of Application.CutCopyMode]
Introduction When automating tasks with Excel VBA, there are often situations where you want to implement a flow like "Copy a cell -> Paste it into another file -> Close the original file." I was trying to build exactly this proces... -
[VBA] 3 Ways to Check if a Cell is Blank: Using IsEmpty, Len, and Comparison Operators
In Excel VBA, checking whether a cell is blank is a very common task. It is useful in various situations, such as "detecting missing entries" or "branching processing based on whether data exists." This article introduces three represent... -
[Excel VBA] Differences Between the Three Methods to Save a Workbook (Save/SaveAs/SaveCopyAs) and How to Use Them
When you finish processing a workbook in VBA, the "Save" operation is always necessary at the end. However, VBA has three similar save methods: .Save, .SaveAs, and .SaveCopyAs. If you do not understand the differences correctly, it may l... -
[VBA] How to Simulate Pressing “F2” and “Enter” Keys | Usage of SendKeys
Environment OS: Windows 10 Pro (Version: 20H2) Software: Microsoft Excel VBA Background While automating Excel tasks, I wanted to implement the operation of "entering cell edit mode (F2 key) and confirming (Enter key)" using VBA. I learn... -
[Excel VBA] How to Correctly Read CSV Files by Specifying Character Encoding (UTF-8, etc.)
Have you ever experienced garbled text (Mojibake) when opening a CSV file in Excel? Especially when you directly open a CSV file saved in UTF-8 format in a Japanese environment Excel, full-width characters and symbols often get garbled. ... -
[Excel VBA] How to Copy Only Visible Cells to Another Sheet After Filtering
Overview There are many situations where you want to transfer rows filtered by AutoFilter directly to another sheet. If you simply execute Copy, hidden rows are also copied. However, by combining this with SpecialCells(xlCellTypeVisible)... -
[Excel VBA] Common Errors and How to Fix Them
Overview When developing programs using Excel VBA, you may encounter various errors. In this article, I will introduce errors I have actually experienced, along with their causes, explanations, and how to fix them. Runtime Error 1004: Me... -
[Excel VBA] How to Unzip (Extract) Files to a Specified Folder
In VBA macros, you may often need to read and process data files (such as CSV or text files) contained inside ZIP archives received from external sources. In such cases, it is much smarter to automate the unzipping process using VBA rath... -
[VBA] How to Create Macros Compatible with Both 32-bit and 64-bit Office (Vba7, Win64)
Since Excel 2010, Office has been available in both 32-bit and 64-bit versions. Consequently, VBA code—especially Declare statements that call Windows API functions—must be written differently depending on the bitness of the environment.... -
[Excel VBA] How to Execute Macros with a Delay or Repeatedly (OnTime, GetTickCount)
Introduction In VBA, there are many situations where you want to implement time-based processing, such as "saving a file automatically after 5 minutes" or "updating sheet values every 10 seconds." Additionally, for more advanced usage, y...
