[ 初めての方へ | 一覧(最新更新順) | 全文検索 | 過去ログ ]
『VBAのワークシート関数について』（マリオ）
VBAのワークシート関数で、65536を越えた配列要素を取り扱うと、結果がおかしくなります(下記test1～test5参照)｡ Worksheet関数(VBA)で取り扱える 配列の上限は、65536までみたいです。
★Worksheet関数（VBA）で、気を付けるべき点が他にあれば、ご教授願います。
下記URLでも、同じようなことが掲載されています。 http://excel-ubara.com/excelvba4/EXCEL226.html
'******************************************************************************************
Option Explicit
Sub test1()
Const x As Long = 65536 '★★★
'----------------------------------------------------------
'xが、1～65536では問題ない → A列=「1」,B列=「2」,C列=「3」
'xが、65537で、次のようになる。→ A列=「1」,B列=「1」,C列=「1」
'（セルに表示される値がおかしいが、エラーにはならない。）
'----------------------------------------------------------
Dim sh As Worksheet, i As Long, j As Long, k As Long
ReDim Data(1 To 3, 1 To 1) As Variant
Set sh = Sheets("Sheet1")
For j = 1 To x
k = k + 1
ReDim Preserve Data(1 To 3, 1 To k)
For i = 1 To 3
Data(i, k) = i
Next i
Next j
sh.UsedRange.Clear
sh.Range(sh.Cells(1, 1), sh.Cells(x, 3)) = _
WorksheetFunction.Transpose(Data) '★
End Sub
Sub test2()
Const x As Long = 65536 '★★★
'----------------------------------------------------------
'xが、1～65536では問題ない（次行参照）
'3
'65536
'******************
'65536
'3
'----------------------------------------------------------
'xが、65537で Debugの結果がおかしくなる。
'また、エラー（インデックスが有効範囲にありません）になる。
'3
'65537（問題ない）
'******************
'3（Debugの結果がおかしい。65537にならない。）
'（エラーとなるDebugの結果が返ってこない）
'----------------------------------------------------------
Dim sh As Worksheet, i As Long, j As Long, k As Long
ReDim Data(1 To 3, 1 To 1) As Variant
Set sh = Sheets("Sheet1")
For j = 1 To x
k = k + 1
ReDim Preserve Data(1 To 3, 1 To k)
For i = 1 To 3
Data(i, k) = i
Next i
Next j
Debug.Print UBound(Data, 1)
Debug.Print UBound(Data, 2)
Debug.Print "******************"
ReDim Data2(1 To UBound(Data, 2), 1 To 3)
Data2 = WorksheetFunction.Transpose(Data) '★
Debug.Print UBound(Data2, 1) '★
Debug.Print UBound(Data2, 2) '★
Debug.Print vbCrLf
End Sub
Sub test3()
Const x As Long = 65536 '★★★
'----------------------------------------------------------
'xが、1～65536では問題ない（次行参照）
'65536
'3
'******************
'3
'65536
'----------------------------------------------------------
'xが、65537で Debugの結果がおかしくなる。エラーにはならない。
'65537（問題ない）
'3
'******************
'3
'1（Debugの結果がおかしい。65537にならない。）
'----------------------------------------------------------
Dim sh As Worksheet, i As Long, j As Long, k As Long
ReDim Data(1 To x, 1 To 3) As Variant
Set sh = Sheets("Sheet1")
For j = 1 To x
For i = 1 To 3
Data(j, i) = i
Next i
Next j
Debug.Print UBound(Data, 1)
Debug.Print UBound(Data, 2)
Debug.Print "******************"
ReDim Data2(1 To 3, 1 To UBound(Data, 1))
Data2 = WorksheetFunction.Transpose(Data) '★
Debug.Print UBound(Data2, 1)
Debug.Print UBound(Data2, 2) '★
Debug.Print vbCrLf
End Sub
Sub test4()
Const x As Long = 65536 '★★★
'---------------------------------------------------------- 'xが、1～65536では問題ない 'Debugの結果：「65536」
'---------------------------------------------------------- 'xが、65537だと、Debugの結果がおかしい。 'Debugの結果：「1」 '----------------------------------------------------------
Dim sh As Worksheet, i As Long, Data2 As Long
ReDim Data(1 To x) As Variant
Set sh = Sheets("Sheet1")
For i = 1 To x
Data(i) = 1
Next i
Data2 = WorksheetFunction.Sum(Data) '★
Debug.Print Data2
End Sub
Sub test5()
Const x As Long = 65537 '★★★
'----------------------------------------------------------
'xが、65536では問題ない
'9
'-5
'---------------------------------------------------------- 'xが、65537だと、Debugの結果がおかしい。 '-5 '-5 '----------------------------------------------------------
Dim sh As Worksheet, i As Long, Data2 As Long, Data3 As Long
ReDim Data(1 To x) As Variant
Set sh = Sheets("Sheet1")
Data(1) = -5
Data(2) = 9
For i = 3 To x
Data(i) = 1
Next i
Data2 = WorksheetFunction.Max(Data) '★
Data3 = WorksheetFunction.Min(Data) '★
Debug.Print Data2 '★
Debug.Print Data3 '★
End Sub
＜ 使用 Excel：Excel2013、使用 OS：Windows 8.1 ＞
きっと、そうだと思います。 たとえば TRANSPOSE関数は 65536 すらカバーしていないと思いますね。 しかも、項目数が多ければ多いほど、限界が小さくなる 不思議な現象ですね）
Sub Test()
Dim w As Variant
w = WorksheetFunction.Transpose(Range("A1:A100000"))
MsgBox UBound(w, 1)
w = WorksheetFunction.Transpose(Range("A1:A1000000"))
MsgBox UBound(w, 1)
End Sub
（β） 2016/03/27(日) 16:55
To βさん
65536まで、カバーしてませんか？
Sub Test6()
Dim w As Variant
w = WorksheetFunction.Transpose(Range("A1:A65536"))
MsgBox UBound(w, 1)'ちゃんとした結果「65536」が返る。
w = WorksheetFunction.Transpose(Range("A1:A65537"))
MsgBox UBound(w, 1)'「65537」を返してほしいのに、結果が「1」になる。
End Sub
（マリオ） 2016/03/27(日) 17:07
衝突
配列に対するTranspose Methodには制限(2^16)があり, それを超えると2^16分を下方から差し引いた要素数の配列になるはず。
[[20141026141028]] 『必要な行データだけを配列へ格納』（やっぱり初歩） >>
[[20121004235255]] 『配列について』（MAKO） >>BOT
（seiya） 2016/03/27(日) 17:16
To seiyaさん
あぁ、そうだったんですか！ 勉強になります。
To マリオさん
65536 （2＾16）以上の項目数で、100000 とか 1000000 で試してみてくださいね。
（β） 2016/03/27(日) 17:28
To seiya さん
＞配列を使用して確実な結果を期待するなら、配列に対してのTranspose関数の使用は極力避けた方が無難でしょう。 ＞vbaの Transpose method には制限があります。 ＞配列に255を超える長さの文字列が存在したり、 ＞配列のどの次元でも要素数（Index数）が65536(2^16)を超えると機能しません。
情報ありがとうございます。 255を超える文字を、配列に格納して、transposeするとエラーになっちゃうんですね。 （マリオ） 2016/03/28(月) 11:40
[ 一覧(最新更新順) ]
YukiWiki 1.6.7 Copyright (C) 2000,2001 by Hiroshi Yuki.
Modified by kazu.