[VBA] How to Convert Full-Width to Half-Width Characters | Using StrConv and vbNarrow

Environment

• OS: Windows 10 Pro (Version: 20H2)
• Software: Microsoft Excel VBA

Background

When processing values read from a barcode reader using VBA, I encountered a significant issue where the input was being interpreted as Full-Width (Zenkaku) characters. This caused data mismatch errors.

To solve this, I learned how to convert these Full-Width characters into Half-Width (Hankaku) characters.

Solution: Use the StrConv Function (vbNarrow)

In VBA, you can easily convert Full-Width characters to Half-Width using the StrConv function.

Code Example (Converting Barcode Input to Half-Width)

Sub ConvertBarcodeToHalfWidth()
    Dim barcode_raw As String
    Dim barcode As String

    ' Get input from the user (Type:=2 specifies Text)
    barcode_raw = Application.InputBox(Prompt:="Please scan the barcode.", Title:="Barcode Form", Type:=2)
    
    ' Convert the raw input to Half-Width (vbNarrow)
    barcode = StrConv(barcode_raw, vbNarrow)
    
    ' Output the result (for verification)
    MsgBox "Converted Value: " & barcode
End Sub

Code Explanation

• Lines 2-3: Declare string variables barcode_raw (for raw input) and barcode (for the converted result).
• Line 6: Display an InputBox for barcode scanning and store the value in barcode_raw.
  • Note: We use Type:=2 to specify Text input.
• Line 9: Use the StrConv function to convert the content of barcode_raw to Half-Width by specifying the vbNarrow constant. The result is stored in the barcode variable.

This ensures that even if the barcode scanner inputs Full-Width characters, they are automatically normalized to Half-Width.

Lessons Learned from Failed Attempts

Before finding this solution, I attempted to solve the issue using xlIMEMode properties or the CStr() function. However, I faced the following problems:

1. Object-related errors: Setting IME modes works for entering data into cells but does not convert data already received in a variable.
2. Type conversion issues: CStr() changes the data type to a string but does not change the character width (Full-Width remains Full-Width).

I realized that StrConv + vbNarrow is the simplest and most reliable method for this specific task.

Summary

To convert Full-Width characters to Half-Width in VBA, use the following syntax:

StrConv(TargetString, vbNarrow)

This is the easiest and most certain method. It is particularly effective in scenarios requiring strict data standardization, such as barcode processing or database registration.