ExcelのセルにVBAで"001"をそのまま代入する方法

はじめに

Excelは多少変な形式のデータでもいい感じに解釈して、Excelが扱いやすい形式に変換してくれます。

この挙動は役に立つ場面も多いですが、お節介に感じるときもあります。
例えば以下のコードを実行したとき、Excelは"001"を数字の1として認識し画像のような結果となります。

普通に"001"を設定

ActiveCell.Value() = "001"

上記の場合であれば、以下のように表示形式を指定することで意図した表示にすることが出来ます。
基本的にはこちらの方法を使用するかと思います。

With ActiveCell
    .NumberFormat = "000"
    .Value() = 1
End With

しかし、運用都合上その値をデータ（数値・日付）として扱う必要が一切無くただの文字列として使用したい場合というのがあったりします。
そういった場合の方法の紹介となります。

String型配列を代入する

上記のように表示形式を変更してNumberFormat = "@"とする方法もありますが、それとは別に「String型配列」に値を入れて代入するといった方法もあります。

以下のコードを実行すると、画像のように文字列として値が設定されます。

String型配列として"001"を設定

ActiveCell.Value = Split("001", "") '->String(0 To 0)

この時セルの表示形式自体は変更されておらず、再度手入力などをすると元に戻ります。

String型配列へ変換する関数

スカラー値や一次元配列ならSplitで何とかなりますが、二次元配列を変換するのはちょっと面倒なため、変換用の関数を作成してみました。

'引数の値・配列を二次元String型配列へ変換する。
Function ToString2DimArray(ByRef Value As Variant) As String()
    '処理速度向上のため
    Call internalToStringArray(Value, ToString2DimArray)
End Function

'処理速度向上のため分割
Private Sub internalToStringArray( _
        ByRef iValue As Variant, _
        ByRef oArr() As String)
    
    '特に指定が無かった場合の添え字の下限
    Const DEF_LB = 1
    
    'スカラー値の場合はString型配列に入れる
    If Not IsArray(iValue) Then
        ReDim oArr(DEF_LB To DEF_LB, DEF_LB To DEF_LB)
        Let oArr(DEF_LB, DEF_LB) = CStr(iValue)
        Exit Sub
    End If
    
    '次元数取得
    Dim dimCount As Long
    dimCount = countDimensions(iValue)
    
    Dim c As Long, r As Long
    If dimCount = 1 Then
        
        'Excelの動作に合わせるため横長の配列にする
        ReDim oArr(DEF_LB To DEF_LB, _
                   LBound(iValue, 1) To UBound(iValue, 1))
        
        For c = LBound(oArr, 2) To UBound(oArr, 2)
            Let oArr(DEF_LB, c) = CStr(iValue(c))
        Next c
        
        
    ElseIf dimCount = 2 Then
        
        ReDim oArr(LBound(iValue, 1) To UBound(iValue, 1), _
                   LBound(iValue, 2) To UBound(iValue, 2))
        
        'VBAの配列の構造の関係で、この順番の方が処理が早い
        For c = LBound(oArr, 2) To UBound(oArr, 2)
            For r = LBound(oArr, 1) To UBound(oArr, 1)
                Let oArr(r, c) = CStr(iValue(r, c))
            Next r
        Next c
        
        
    Else
        Err.Raise 5, Description:= _
                        "対応していない次元数の配列です（次元数=" & dimCount & "）。" & vbLf & _
                        "一次元配列か二次元配列を指定してください。"
    End If
    
End Sub

'配列の次元数を取得する。初期化していない動的配列の場合は0
Private Function countDimensions(ByRef iArray As Variant) As Long
    If Not VBA.IsArray(iArray) Then Err.Raise 13
    
    'VBAの多次元配列の次元の上限
    Const MAX_DIMENSION = 64
    
    Dim d As Long, no_mean_var As Long
    On Error Resume Next
        For d = 1 To MAX_DIMENSION
            no_mean_var = LBound(iArray, d)
            If Err.Number <> 0 Then Exit For
        Next d
    On Error GoTo 0
    
    'VBAのFor文は完走すると指定した数値+1になる
    Let countDimensions = (d - 1) '0 To 64
    
End Function

いくつかのプロシージャが含まれていますが、メインはToString2DimArrayで残りはそれの補助処理です。

サンプル

Sub SampleToStrArray()
    Dim ws As Excel.Worksheet
    Set ws = Excel.ActiveSheet
    
    With ws.Range("A1")
        
        'スカラー値の場合
        .Value() = "スカラー値"
        .Offset(1).Value() = ToString2DimArray("001")
        
        '一次元配列の場合
        .Offset(3).Value() = "一次元配列"
        Dim arr() As Variant
        arr = Array("001", "1.00", 1)
        
        Dim strArr() As String
        strArr = ToString2DimArray(arr)
        
        With .Offset(4).Resize(getArrayLength(strArr, 1), getArrayLength(strArr, 2))
            'String型配列
            .Value() = strArr
            
            '確認用。1個下に`Value()`でコピー
            .Offset(1).Value() = .Value()
            
            
            '二次元配列
            .Offset(3).Resize(1, 1).Value() = "二次元配列"
            With .Resize(2)
                Dim arr2Dim() As Variant
                arr2Dim = .Value()
                .Offset(4).Value() = ToString2DimArray(arr2Dim)
            End With
        End With
    
    End With 'ws.Range("A1")
    Stop
End Sub

Private Function getArrayLength( _
        ByRef iArray As Variant, _
        Optional iDimension As Long = 1) As Long
    
    Let getArrayLength = UBound(iArray, iDimension) - LBound(iArray, iDimension) + 1
    
End Function

実行すると以下のような状態になります。

A2・A5:C5・A9:C10がString型配列で値を設定した部分になります。

その他

逆にセルに表示されている値そのままを取得したい場合は、RangeオブジェクトのTextプロパティから取得できます。
注意点として、Textプロパティは読み取り専用のため値の設定には使用できず、セル範囲参照時も使用できません。

JScriptのVBArrayオブジェクトは、dimensions メソッドで次元数を取得できるのに、VBAには該当する関数が無いのはなぜなのでしょうか…。

まとめ

1を001として入力する方法は大きく3パターンあります。

表示形式に000を指定する

• セルの左上に緑の三角が表示されない
• 内部的には数字として扱われる（.Valueは1、.Textは"001"）
• ユーザーが手入力しても形式が保持される

表示形式に@（文字列）を指定する

• セルの左上に緑の三角が表示される
• 内部的には文字列として扱われる（.Valueも.Textも"001"）
• ユーザーが手入力しても形式が保持される

String型配列や、値の先頭に'をつけて値を設定する

• セルの左上に緑の三角が表示される
• 内部的には文字列として扱われる（.Valueも.Textも"001"）
• ユーザーが手入力すると形式が元に戻る