• ベストアンサー
  • 暇なときにでも

エクセルVBAでTransposeの不思議

  • 質問No.5031943
  • 閲覧数12027
  • ありがとう数6
  • 回答数2

お礼率 93% (1499/1605)

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
  • ベストアンサー

ベストアンサー率 79% (496/625)

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に改善されています。
#要素数というより、次元あたりの要素数のようですが。
お礼コメント
merlionXX

お礼率 93% (1499/1605)

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

存じませんでした。
ありがとうございます。勉強になりました。
投稿日時:2009/06/11 16:08

その他の回答 (全1件)

  • 回答No.1

ベストアンサー率 29% (101/345)

Rangeは255文字までしか対応していないからではないでしょうか?
お礼コメント
merlionXX

お礼率 93% (1499/1605)

さっそくありがとうございます。
Rangeの引数が255文字超でエラーになることは存じておりますが、今回の場合、引数の文字数では超えてませんよね?
それともわたしが何か勘違いしているのでしょうか?
投稿日時:2009/06/10 11:29
関連するQ&A

その他の関連するQ&Aをキーワードで探す

ページ先頭へ