[今日のトリビア][Excel VBA]vbNullChar区切りの配列をJoinすると、Null文字が勝手に空白になる vbNullChar Separeted array can make in vba, but cannot show in the cell

VBAでもvbNullChar区切り配列は成功する

Chr(0)を区切り文字として配列を作ることが可能。

Sub ZeroDelimiterArray()
Dim ar
Dim str As String
Debug.Print "vbNull", vbNull = Chr(0)
Debug.Print "vbNullString", vbNullString = Chr(0)
Debug.Print "vbNullChar", vbNullChar = Chr(0)
Debug.Print """"" = Chr(0)", "" = Chr(0)
Debug.Print "Len(vbNullChar)", Len(vbNullChar)
Debug.Print "LenB(vbNullChar)", LenB(vbNullChar)
str = "A" & Chr(0) & "B" & Chr(0) & "C " & Chr(0) & Chr(0)
ar = Split(str, Chr(0), -1, vbBinaryCompare)
Debug.Print UBound(ar)
Dim I
For I = LBound(ar) To UBound(ar)
Debug.Print ar(I)
Next
ActiveCell.Value = Join(ar)
End Sub

ImmidiateWindowの表示

イミディエイトウインドウには以下のように表示される

vbNull     False
vbNullString  False
vbNullChar  True
"" = Chr(0)  False
Len(vbNullChar)          1 
LenB(vbNullChar)        2 
4 
A
B
C 

なのでvbNullCharがChr(0)ということがわかる。
その他の定数
CHAR 関数

ExcelにJoinで値をいれる。

間が空いている。

調べてみるとCの後ろにはスペースのようなものが4つある。
一つは"C "だとわかるが、他に3つ存在する。さらに、AとBとCの間も隙間がある。そこで文字をコピーアンドペーストで取り出し下のセルに入力する。

CHARで空白とわかる

驚くべきことに、Null文字は空白'Char(32)`に勝手に変更されている！
つまり、Null文字区切りは半角区切りになってしまったのである。
なので、Cの後ろにスペースが4つあるのは
C空白 区切り空白 空白 区切空白
という並びになっているのである。

vbNullCharはセルに入らない

それでは=Char(0)でセルにvbNullCharは入力できないのか。

値のエラーになってしまう。

挿入->記号と特殊文字->記号と特殊文字でも入力できない

まず特殊文字の一覧に存在しない。

制御文字はジャンルとして示されないので、強制的に0000といれて挿入をクリックする。
これを自動記録したマクロで見てみる。

Sub MacroInsertTest()
' マクロの自動記録で記録
    ActiveCell.FormulaR1C1 = " "
    Range("B1").Select
    ActiveCell.FormulaR1C1 = "=CODE(RC[-1])"
    Range("B2").Select
End Sub

このように空白が入力されたことになる。また、Codeの結果は32で半角スペースであることがわかる。

Char関数は0を使うことができないのでこれでも入力できない

CHAR 関数

説明

数値で指定された文字を返します。 CHAR 関数は、他機種のコンピューター上にあるファイルから取得したコード ページ番号を文字に変換する場合などに使用します。

システム環境	文字セット
Macintosh	Macintosh 文字セット
Windows	ANSI ANSI 文字セットは、Windows オペレーティング システムで Windows 95 および Windows NT を通じて使用される標準的な文字セットであり、その後は Unicode が採用されました。 ANSI は 218 文字で構成され、その多くは ASCII/Unicode 形式と同じ数値コードを共有します。 https://www.techopedia.com/definition/932/ansi-character-set

書式

CHAR(数値) CHAR 関数の書式には、次の引数があります。 数値 必ず指定します。 変換する文字を表す 1 ～ 255 の範囲内の数値を指定します。 文字は、コンピューターで使用されている文字セットから返されます。

とこのように1からなのでWorksheetFunction.Char(0)はエラーとなるので、VBAのChr(0)は得ることができない。

空白Chr(32)はChr(0)で代用できる場合がある

配列をChr(0)で区切ると、セルの中に入れたり、表示させれば空白になる以上、いちいちChr(32)としなくても良いのである。
またイミディエイトに表示させた場合も、同様に空白Chr(32)が表示される。
面白いのはこのnull文字区切りはJoin等では最終的にスペース区切りと同じになるのにもかかわらず、それまでは空白と別な文字として扱われる。
つまり空白を含む文字列の配列はNull文字区切りで区切るとよいということになる。
なお、文字数としてはLenの結果を見るとおりゼロではない。半角1字、2バイトを必要とする。

Trimは無駄無駄無駄無駄アァッ！！！

Len(Trim(vbNullChar)) = 1
vbNullCharはvbNullStringと異なり、Trimが効かない。
このため、値に混じった空白を除去しても、配列の区切りとしてのChr(0)は砕けないのである。

WorksheetFunction.Cleanは有効

データをクリーンアップする方法トップ 10
CLEAN 7 ビット ASCII コードの先頭から 32 個の印刷されない文字 (値 0 から 31) をテキストから削除します。
Excelを参照設定で持ってくるか、ExcelのVBAにおいてはこの方法も可能だ。ただ、これは32の空白まで削除している。

結論 git

in VBA, You can make array with vbNullChar.
But Show On Immidiate Or Cell, vbNullChar change Space Automatically.
VBAでもNull文字区切り配列を作成可能である。
しかし、セル内ではNull文字は勝手に空白に変換される。イミディエイトでも同様である。
ただし、単独でVBAで'Chr(0)で代入した場合、長さ0の文字列""`に変換される。

しかしここでもう一つきがついた

CleanがChr(0)をのぞけるなら、入力できるのではないか？
そこでChr(0)のみを表示形式を文字列と、標準でやってみた。

Sub Try_Insert_Char_0()
Dim wb As Workbook: Set wb = ThisWorkbook
Dim ws As Worksheet: Set ws = ActiveSheet
Dim r As Range
Set r = ws.Range(ActiveCell.Address)
r.Clear
Debug.Print "表示形式文字列'chr(0)"
r.NumberFormatLocal = "@"
r.Value = "'" & Chr(0)
Debug.Print "r.Value = Chr(0)  ", r.Value = Chr(0)
Debug.Print "r.Value = Chr(32)  ", r.Value = Chr(32)
Debug.Print "r.Value = vbNullChar", r.Value = vbNullChar
Debug.Print "r.Value = """"", r.Value = ""
Debug.Print "r.Value = vbNullString ", r.Value = vbNullString
Debug.Print "Len(r.Value)  ", Len(r.Value)
Debug.Print "LenB(r.Value)  ", LenB(r.Value)
Debug.Print "StrPtr(r.Value) <> 0 ", StrPtr(r.Value) <> 0
Debug.Print "StrPtr(r.Value)=", StrPtr(r.Value)
r.ClearContents
r.Value = ""
Debug.Print "StrPtr("""")", StrPtr(r.Value)
r.Clear
r.NumberFormatLocal = "@"
Debug.Print "表示形式文字列そのままchr(0)"
r.Value = Chr(0)
Debug.Print "r.Value = Chr(0)  ", r.Value = Chr(0)
Debug.Print "r.Value = Chr(32)  ", r.Value = Chr(32)
Debug.Print "r.Value = vbNullChar", r.Value = vbNullChar
Debug.Print "r.Value = """"", r.Value = ""
Debug.Print "r.Value = vbNullString ", r.Value = vbNullString
Debug.Print "Len(r.Value)  ", Len(r.Value)
Debug.Print "LenB(r.Value)  ", LenB(r.Value)
Debug.Print "StrPtr(r.Value) <> 0 ", StrPtr(r.Value) <> 0
Debug.Print "StrPtr(r.Value)=", StrPtr(r.Value)
r.ClearContents
r.Value = ""
Debug.Print "StrPtr("""")", StrPtr(r.Value)
r.Clear
r.NumberFormatLocal = "G/標準"
Debug.Print "表示形式標準 'chr(0)"
r.Value = "'" & Chr(0)
Debug.Print "r.Value = Chr(0)  ", r.Value = Chr(0)
Debug.Print "r.Value = Chr(32)  ", r.Value = Chr(32)
Debug.Print "r.Value = vbNullChar", r.Value = vbNullChar
Debug.Print "r.Value = """"", r.Value = ""
Debug.Print "r.Value = vbNullString ", r.Value = vbNullString
Debug.Print "Len(r.Value)  ", Len(r.Value)
Debug.Print "LenB(r.Value)  ", LenB(r.Value)
Debug.Print "StrPtr(r.Value) <> 0 ", StrPtr(r.Value) <> 0
Debug.Print "StrPtr(r.Value)=", StrPtr(r.Value)
r.Clear
Debug.Print "StrPtr(r.Value)", StrPtr(r.Value)
r.Value = ""
Debug.Print "StrPtr("""")", StrPtr(r.Value)
r.Clear
r.NumberFormatLocal = "G/標準"
Debug.Print "表示形式文字列そのままchr(0)"
r.Value = Chr(0)
Debug.Print "r.Value = Chr(0)  ", r.Value = Chr(0)
Debug.Print "r.Value = Chr(32)  ", r.Value = Chr(32)
Debug.Print "r.Value = vbNullChar", r.Value = vbNullChar
Debug.Print "r.Value = """"", r.Value = ""
Debug.Print "r.Value = vbNullString ", r.Value = vbNullString
Debug.Print "Len(r.Value)  ", Len(r.Value)
Debug.Print "LenB(r.Value)  ", LenB(r.Value)
Debug.Print "StrPtr(r.Value) <> 0 ", StrPtr(r.Value) <> 0
Debug.Print "StrPtr(r.Value)=", StrPtr(r.Value)
r.Clear
Debug.Print "StrPtr(r.Value)", StrPtr(r.Value)
r.Value = ""
Debug.Print "StrPtr("""")", StrPtr(r.Value)
r.Clear
End Sub

Chr(0)は空文字列""に変換された！

一見vbNullStringのようだが、StrPtrはゼロにならない。
なので長さゼロの文字列""に変換されている。
次にならばNullcharを代入する。まずVBAの代入

Function 長さ0を代入(cnt As Long) As Double
    Dim s As String
    Dim i As Long
    Dim sTime As Double
    sTime = Timer
    For i = 1 To cnt
        s = ""
    Next
    長さ0を代入 = Timer - sTime
End Function
Function NullCharを代入(cnt As Long) As Double
    Dim s As String
    Dim i As Long
    Dim sTime As Double
    sTime = Timer
    For i = 1 To cnt
        s = vbNullChar
    Next
    NullCharを代入 = Timer - sTime
End Function

Function NullStringを代入(cnt As Long) As Double
    Dim s As String
    Dim i As Long
    Dim sTime As Double
    sTime = Timer
    For i = 1 To cnt
        s = vbNullString
    Next
    NullStringを代入 = Timer - sTime
End Function
Sub test()
Debug.Print 長さ0を代入(1000000)
Debug.Print NullCharを代入(1000000)
Debug.Print NullStringを代入(1000000)
End Sub

 0.3671875 
 0.35546875 
 0.1328125 

若干の揺れがあったが、64bitのVBAではNullStringが早い。

セルをクリアするときはどうか

それでは表示形式、標準ので文字列を""、vbNullChar、vbNullStrigでセルのクリアではどうか

Function 長さ0を代入(cnt As Long) As Double
    Dim s As String
    Dim i As Long
    Dim sTime As Double
    sTime = Timer
    For i = 1 To cnt
        ActiveCell.Value = ""
    Next
    長さ0を代入 = Timer - sTime
End Function
Function クリアをかける(cnt As Long) As Double
    Dim s As String
    Dim i As Long
    Dim sTime As Double
    sTime = Timer
    For i = 1 To cnt
        ActiveCell.Clear
    Next
    クリアをかける = Timer - sTime
End Function
Function Cクリアをかける(cnt As Long) As Double
    Dim s As String
    Dim i As Long
    Dim sTime As Double
    sTime = Timer
    For i = 1 To cnt
        ActiveCell.Clear
    Next
    Cクリアをかける = Timer - sTime
End Function
Function NullCharを代入(cnt As Long) As Double
    Dim s As String
    Dim i As Long
    Dim sTime As Double
    sTime = Timer
    For i = 1 To cnt
        ActiveCell.Value = vbNullChar
    Next
    NullCharを代入 = Timer - sTime
End Function

Function NullStringを代入(cnt As Long) As Double
    Dim s As String
    Dim i As Long
    Dim sTime As Double
    sTime = Timer
    For i = 1 To cnt
        ActiveCell.Value = vbNullString
    Next
    NullStringを代入 = Timer - sTime
End Function
Sub test()
Debug.Print 長さ0を代入(10)
Debug.Print NullCharを代入(10)
Debug.Print NullStringを代入(10)
Debug.Print クリアをかける(10)
Debug.Print Cクリアをかける(10)
End Sub

 0.22265625 
 0.15234375 
 0.140625 
 0.14453125 
 0.12109375 

 0.15625 
 0.171875 
 0.140625 
 0.1328125 
 0.125 

 0.1953125 
 0.18359375 
 0.15625 
 0.1328125 
 0.12890625 

これは揺れがあるが、ClearContentsが早かった。
長さゼロ、NullChar、NullStrigは平均的にはvbNullStringが最速だが、やはりClearContentsが早いし、わかりやすい。セルの文字列のクリアはCleaContentsを使うべきである。また一度全部消してしまうClearより早いのでその点でもこれを使ったほうが良いと思われる。