VBA将双字节字符选择性转换为单字节字符 [英] VBA selective conversion of double-byte to single-bye characters

问题描述

我已经写了我的第一个VBA子程序，它按照应有的方式工作，但是我无法弄清楚哪一部分是错误的。如果存在一串双字节日文和拉丁字母字符和空格，则可以有选择地将双字节空格，字母，数字和标点符号转换为单字节。

在此图片中，第一行

表示输入，最下面一行表示空格，字母，数字和标点符号的期望输出，它们转换为单字节。

但是，这是运行子程序时发生的情况。显然，它可以正常工作，但我的连接也有问题。

下面的代码基于捕获并转换 UTF-与有问题的全角字符相对应的16个代码。它只能在本地计算机上运行（例如，当语言/地区设置为日本时），但是我认为代码问题与本地化功能无关。任何对我做错事的帮助将不胜感激！

  Public Sub Converter（）
 Dim objRange作为范围
 ActiveSheet.UsedRange 
中的每个objRange调用Alphanumeric（objRange）
下一页
 End Sub 
 
 Private Sub Alphanumeric（ByRef objRange As Range ）
 Dim strIn作为字符串
 Dim strOut作为字符串
 Dim str字母数字作为字符串
 Dim i作为整数
 
如果objRange.HasFormula或_ 
 VarType（objRange.Value）<> vbString然后
退出子
结束如果
 
 strIn = objRange.Value 
 strOut = 
 strAlphanumeric = 
 
对于i = 1到Len（strIn）
如果AscW（Mid（strIn，i，2））+ 65536> = 65280和_ 
 AscW（Mid（strIn，i，2））+ 65536< = 65370然后
 strAlphanumeric = strAlphanumeric& Mid（strIn，i，1）
其他
如果strAlphanumeric<> 然后
 strOut = strOut& StrConv（strIn，vbNarrow）
 strAlphanumeric = 
如果
 strOut = strOut& Mid（strIn，i，1）
如果
下一个
 
 objRange.Value = strOut 
 
 End Sub 
  

解决方案

我怀疑这行

strOut = strOut& StrConv（strIn，vbNarrow）

应该在我的眼中

  strOut = strOut& StrConv（strAlphanumeric，vbNarrow）
  

I've written my first VBA sub, and it's KIND OF working the way it's supposed to, but I cannot figure out the part that's wrong. It's supposed to selectively convert double-byte spaces, letters, numbers, and punctuation to single-byte when there is a string of double-byte Japanese and Latinate characters and spaces.

In this picture, the top row represents the input and the bottom row the desired output of spaces, letters, numbers, and punctuation converted to single-byte while the Japanese characters remain intact.

However, this is what is happening when I run the sub. Clearly it's working, but also something is off with my concatenation.

The code is below and works based on "catching and converting" the range of UTF-16 codes that correspond to the problematic full-width characters. It only functions on localized machines (i.e. when language/region is set to Japan) but I don't think the issue with my code has to do with localized functions. Any help on what I'm doing wrong would be greatly, greatly appreciated!

Public Sub Converter()
    Dim objRange As Range
        For Each objRange In ActiveSheet.UsedRange
        Call Alphanumeric(objRange)
    Next
End Sub

Private Sub Alphanumeric(ByRef objRange As Range)
    Dim strIn As String
    Dim strOut As String
    Dim strAlphanumeric As String
    Dim i As Integer

    If objRange.HasFormula Or _
        VarType(objRange.Value) <> vbString Then
        Exit Sub
    End If

    strIn = objRange.Value
    strOut = ""
    strAlphanumeric = ""

    For i = 1 To Len(strIn)
        If AscW(Mid(strIn, i, 2)) + 65536 >= 65280 And _
           AscW(Mid(strIn, i, 2)) + 65536 <= 65370 Then
           strAlphanumeric = strAlphanumeric & Mid(strIn, i, 1)
        Else
            If strAlphanumeric <> "" Then
                strOut = strOut & StrConv(strIn, vbNarrow)
                strAlphanumeric = ""
            End If
            strOut = strOut & Mid(strIn, i, 1)
        End If
    Next

    objRange.Value = strOut

End Sub

解决方案

I suspect the line

strOut = strOut & StrConv(strIn, vbNarrow)

Should be to my eyes

strOut = strOut & StrConv(strAlphanumeric, vbNarrow)

这篇关于VBA将双字节字符选择性转换为单字节字符的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！