エクセルVBAでTransposeの不思議

質問

MS Officeのエクセル2000です。
下記Sub test01はRange("A1:I1")に文字列を入力し、一旦配列に取り込んでからワークシートに貼り付けるものです。
試験用のコードですので意味はありません。
このコードで255文字まではまったく問題はありません。
ところが、256文字以上の場合、横に貼り付けは問題ないのですが、 Transposeで縦に変換すると型が一致しません。(Error 13)となります。
どうしてでしょうか？
試行錯誤の結果、Sub test02のように一旦横に貼ったデータをコピーしてTransposeして貼り付けるのは大丈夫のようですので不思議でしょがありません。
またこの方法は列数256より要素が多い配列には使えないので解決策にはなりません。
ご教示くださいませ。

Sub test01()
Dim myAr As Variant
Dim i As Integer, n As Integer
n = 256 '文字数
With ActiveSheet
.UsedRange.ClearContents
For i = 1 To 9
.Cells(1, i).Value = Application.Rept(Left(.Cells(1, i).Address(0, 0), 1), n)
Next
myAr = .Range("A1:I1").Value
.Range("A3").Resize(, UBound(myAr, 2)).Value = myAr
.Range("A5").Resize(UBound(myAr, 2)).Value = Application.Transpose(myAr) '256文字の場合エラー
End With
End Sub

Sub test02()
Dim myAr As Variant
Dim i As Integer, n As Integer

n = 256 '文字数
With ActiveSheet
.UsedRange.ClearContents
For i = 1 To 9
.Cells(1, i).Value = Application.Rept(Left(.Cells(1, i).Address(0, 0), 1), n)
Next
myAr = .Range("A1:I1").Value
.Range("A3").Resize(, UBound(myAr, 2)).Value = myAr
.Range("A3").Resize(, UBound(myAr, 2)).Copy
.Range("A5").PasteSpecial Paste:=xlValues, Transpose:=True '256文字の場合もOK
Application.CutCopyMode = False
End With
End Sub

回答No.2

end-u · ベストアンサー

VBAでワークシート関数を使う時、《引数に配列を渡す》場合、要素の文字数及び要素数に制限があります。

『オートメーションを使用して ADO レコードセットのデータを Excel に転送する方法』
http://support.microsoft.com/kb/246335/ja
このkbに簡単に触れてあります。

<引用ここから>
Excel の Transpose 関数を使用して配列を入れ替える場合は、Transpose 関数の次の制限に注意してください。

　　* 配列に 255 文字を超える要素を含めることはできません。
　　* 配列に Null 値を含めることはできません。
　　* 配列内の要素数が 5461 を超えることはできません。
<引用ここまで>

▽ここも参照しておいたほうが良いかもしれません。
『オートメーションを使用して Excel に配列を渡すことの XL の制限事項』
http://support.microsoft.com/kb/177991/ja

また、Transposeに限らず、他のワークシート関数でも該当します。
Sub test()
　　Const n1 As Long = 256
　　Const n2 As Long = 5462
　　Dim v, w, x, y

　　v = Array("a", String(n1, "a"))
　　ReDim w(1 To n2)
　　On Error GoTo errHndr
　　With Application

1:　　　　x = .CountA(v)
2:　　　　x = .Transpose(v)
3:　　　　x = .Index(v, 1)

4:　　　　y = .CountA(w)
5:　　　　y = .Transpose(w)
6:　　　　y = .Index(w, 1)

　　End With
　　Erase w
　　Exit Sub
errHndr:
　　MsgBox "Line " & Erl & vbLf & Err.Number & " : " & Err.Description
　　Resume Next
End Sub

>Sub test02のように一旦横に貼ったデータをコピーしてTransposeして貼り付けるのは大丈夫のようですので不思議でしょがありません。
test02はワークシートTranspose関数ではなく、PasteSpecialメソッドですから、比較にはなりません。
解決策としては、Loop処理で自前で入れ替えてあげれば良いかと。
配列処理なので速度的には大差ないと思います。
>.Range("A5").Resize(UBound(myAr, 2)).Value = Application.Transpose(myAr)　 '256文字の場合エラー
.Range("A5").Resize(UBound(myAr, 2)).Value = vTrans(myAr)
'：

Private Function vTrans(v As Variant) As Variant
　　Dim L1 As Long, U1 As Long
　　Dim L2 As Long, U2 As Long
　　Dim rn As Long, cn As Long
　　Dim i　As Long, j　As Long

　　L1 = LBound(v, 1)
　　U1 = UBound(v, 1)
　　L2 = LBound(v, 2)
　　U2 = UBound(v, 2)
　　cn = 0
　　ReDim x(1 To U2 - L2 + 1, 1 To U1 - L1 + 1)
　　For i = L1 To U1
　　　　cn = cn + 1
　　　　rn = 0
　　　　For j = L2 To U2
　　　　　　rn = rn + 1
　　　　　　x(rn, cn) = v(i, j)
　　　　Next j
　　Next i
　　vTrans = x
End Function

#余談ですが、2003からは要素数5,461の制限は65,536に改善されています。
#要素数というより、次元あたりの要素数のようですが。

お礼

> Excel の Transpose 関数を使用して配列を入れ替える場合は、Transpose 関数の次の制限に注意してください。

存じませんでした。
ありがとうございます。勉強になりました。

回答No.1

WDY

Rangeは255文字までしか対応していないからではないでしょうか？

お礼

さっそくありがとうございます。
Rangeの引数が255文字超でエラーになることは存じておりますが、今回の場合、引数の文字数では超えてませんよね？
それともわたしが何か勘違いしているのでしょうか？