Category: Excel VBA Coding Patterns

Specify PtrSafe to use both 32bit and 64bit Excel when importing Windows API

Learn about the Windows API PtrSafe declaration. When coding with Excel VBA, you can check the causes and solutions of PtrSafe-related errors that you occasionally encounter while importing and using Windows APIs. 1. Example code for Windows API PtrSafe error This is the code that imports and declares Windows API Sleep, OutputDebugString. (reference:...

Logging Patterns: OutputDebugString , Leverage DebugView

In this article, we look at an effective logging method using the Windows API OutputDebugString and DebugView utilities in Excel VBA. 1. Basic Logging Pattern Excel VBA Course (9): Excel VBA How-To 4.3. How to use the Immediate Window tool Refer to “3. Checking the Output Message “was covered. You can check the contents output by Debug.Print in the Immediate Window...

Creating 32bit Provider Connection String in 64bit OS

Introduces how to create a 32bit Provider Connection String on a 64bit OS. Previous Post VBA Coding Pattern: Importing DB Query Results to Excel_ConnectionString Creation Method 1. I mentioned “How to display 32bit Provider in 64bit OS” in the contents of using UDL file. If you create a connection string with a UDL file on a 64-bit OS, you can basically only create it with the installed 64-bit Provider. Windows 10 in this article...

VBA Coding Pattern: Importing DB Query Results to Excel

Introducing the VBA coding pattern that implements importing DB Query results to Excel. Related Article: VBA Coding Pattern: Creating ADO DB Connection String 1. Excel Screen and Operation Method 1.1. Enter the connection string to connect to the DB in cell “B1” of the Excel screen, and in cell “B2” to extract data...

VBA Coding Pattern: Create ADO DB Connection String

This article examines the implementation of functions for creating and editing ADO DB Connection Strings in Excel VBA. 1. ADO concept and necessity 1.1. Concept of ADO (ActiveX Data Objects) ADO is a library that can access various data stores and CRUD (Create, Read, Update, Delete) data. Microsoft docs...

VBA Coding Patterns: Improving VBA Performance

In this article, we will look at how to improve VBA performance. In the previous article, we compared the VBA Range Loop method and confirmed that the method using Variant Array is the fastest. VBA Coding Pattern: Range Loop - Read 1. Performance-Related VBA Global Variables Here is a summary of all variables....

VBA Coding Pattern: Named Range

Even if the reference address of Range is changed during VBA coding, if you use Named Range (named range), you can prevent the source code from changing regardless of the reference address change. Let's look at the following. 1. Range reference using absolute address The most frequently used method among many methods to refer to a Range in Excel VBA is as follows. In Range(“B2”)...

VBA Coding Pattern: Range Loop - Write

Among the VBA coding patterns, look at the contents of Range Loop-Write. Summary Introducing coding patterns that improve performance when writing a lot of data in an Excel sheet using Variant Array. The article below has already covered how to improve performance when reading a lot of data from an Excel sheet. VBA coding patterns:...

VBA Coding Pattern: Range Loop - Read

Among the VBA coding patterns, look at the contents of Range Loop-Read. Summary Range Loop is a coding pattern often used when reading and processing data entered in an Excel sheet with VBA code. The basic pattern of reading sequentially from the start cell to the last cell, and reading at once without reading sequentially...

en_USEnglish