出産前後の痔にはご注意!

こんにちは、配列がいくつまで取得できるのか計算するマクロを作っている時に変なことに気が付きました。
エクセルでフォームとラベルを1つ用意し、下記のようなプログラムをモジュールに書きます。

実行すると、10万ずつ数字が増えていき、数字が1000~2000万超えたあたりで、メモリ不足ですという
表示がでます。
ここで、ReDim PreserveのPreserveをとって、ReDimだけにすると、数字の増え方が目に見えて遅くなります。
ReDim Preserveとしたときは9.5秒ぐらいですが
ReDim の時は 30秒以上かかっています
3台のマシンでテストしましたが、どれも似たような結果になりました
普通に値を保持するredim Preserveの方が遅いと思うのですがなぜこのような結果になるのでしょうか?
ご教授お願いいたします。

Public Sub 配列上限取得計算()
On Error GoTo ErrEnd
Dim i As Long
Const kankaku As Long = 100000

ReDim ans(1 To kankaku) As String
ans(1) = 1
i = 2
UserForm1.Show vbModeless

Do
If i Mod kankaku = 0 Then
DoEvents
UserForm1.Label1 = i
'ここのPreserveをなくすと明らかに遅くなる
ReDim Preserve ans(1 To i + kankaku) As String
End If
ans(i) = i
i = i + 1
Loop
Erase ans
Unload UserForm1
Exit Sub
ErrEnd:
MsgBox "これ以上の配列を設定できません。" & vbCrLf & "上限は" & i & "です。" & vbCrLf & Err.Description
Erase ans
Unload UserForm1
End Sub

このQ&Aに関連する最新のQ&A

A 回答 (1件)

こんにちは、



> ここで、ReDim PreserveのPreserveをとって、ReDimだけにすると、数字の増え方が目に見えて遅くなります。
> ReDim Preserveとしたときは9.5秒ぐらいですが
> ReDim の時は 30秒以上かかっています

 ReDim は 新築
 ReDim Preserve は 増築
という喩で説明になっているかと思います。

実際には、Eraseして、メモリの再割り当てをしつつ、配列のサイズ(ディメンション)を再定義するのがReDim
既存のメモリを維持しつつ、メモリを拡張、サイズを再定義するのがReDim Preserve
ということになります。
必然的にReDimを繰り返したら遅くなります。

ご提示のコードでPreserve キーワードを抜くと、
kankakuごとに、ReDimしている訳ですから、
せっかく格納したそれまでの値を消去していることになります。
可変長のString型変数の場合は特殊で、割り当てられるメモリサイズが固定ではありませんから
格納したり消去(長さ0の文字列を格納)したりするのに特に時間が掛かります。

ご提示のコードでPreserve キーワードを抜くと、
それまでに格納した文字長に比例したメモリサイズ、は無視されてしまいます。
本来の目的から外れたテストになってしまいます。

可変長の変数なのですから、要素数、だけではなくて、
要素数と文字長、の2点について、色々パラメータを変えながら
テストしてみた方がより有意義なものになるのではないかと思います。

 Dim ans() As String
のように一旦、文字列型として、動的配列変数を予め定義しておいた方が
テストの精度が高まりますし、ReDimに掛かる時間も短くなります。

以上です。
    • good
    • 0
この回答へのお礼

詳しい解説をありがとうございます

>>可変長のString型変数の場合は特殊で、割り当てられるメモリサイズが固定ではありませんから
格納したり消去(長さ0の文字列を格納)したりするのに特に時間が掛かります。

ここで納得できました。試しにstringでlongにしてみると、普通のredimでも高速になりましたし、string型のときに要素数が増えれば増えるほどだんだん遅くなっていったのも、redimする度にすべての要素に長さ0の文字列を格納していたせいだと考えると、つじつまがあいます。

>>可変長の変数なのですから、要素数、だけではなくて、
要素数と文字長、の2点について、色々パラメータを変えながら
テストしてみた方がより有意義なものになるのではないかと思います。

その通りですね。ただ、実際にパラメータを変えてトライしてみたら、新たな疑問が発生してしまったんですが、この質問自体はもう答えてもらってるので、新しい質問として載せることにします。

ありがとうございました

お礼日時:2013/07/28 21:27

このQ&Aに関連する人気のQ&A

お探しのQ&Aが見つからない時は、教えて!gooで質問しましょう!

このQ&Aを見た人はこんなQ&Aも見ています

このQ&Aを見た人が検索しているワード

このQ&Aと関連する良く見られている質問

Qメモリ不足になってしまう。

教えてください。
現在陥っている現象:「メモリ不足になってしまう」配列の領域をRedimで動的に確保しようとしています。
例えば、
dim A() as byte
dim B() as byte
dim C() as byte
redim A(8000,60000)
redim B(8000,60000)
redim C(8000,60000)
上記のように配列を確保する予定なのですが、
メモリ不足になってしまいます。
ひとつの変数の容量が480MB程度であると思います。
3つ全部でも1440MBであるのですが、物理的な
メモリ容量は、512M*4を実装しています。
上記の変数名で言いますと、
Aの変数の領域確保には問題なく成功するのですが、
その次の行のBのところでエラーになります。
現在、物理的なメモリには空きがあるにもかかわらず
このような現象になってしまい、困っています。
どなたか原因解決の糸口になるような回答をお持ちの方、
教えてください。

Aベストアンサー

>メモリ容量は、512M*4を実装しています。
>物理的なメモリには空きがあるにもかかわらず
配列だけ考えれば確かにそうですが、その他にも物理メモリを使用しているモノがたくさんあるので、足りなくなることがあります。
ハードディスクの空きはありますか?
十分あるのに不足になる原因として考えられるのは、動的に大きな連続した領域を確保しようとしているからだと思います。
動的に確保しなければならないのには理由があるのですか?
静的な確保に変更可能ならば、多少多めに確保しても仮想メモリに割り当てるので確保できるはずです。
また、一度に多くの配列を割り当てるのではなく、細かく分割した方が、メモリ待避・解放のレスポンスも向上しますし、メモリ不足を回避するのにも役立ちます。
最後に単なる思いつきですが、3つのredimの間に適当な処理をはさむとうまくいくかもしれません。

QVBA オブジェクトが空かどうか判定する

皆様のお知恵を拝借させてください。

エクセルVBAでオブジェクトを入れる変数を定義し、その変数にオブジェクト
が入っているかどうか検査したいのですがどうしたらいいでしょうか。

例えば---
Dim a As Workbook
If a <> nothing then ←この部分が分からない。このままだとエラー。
処理
End if
---------
環境
エクセル2003
WinXPsp1

Aベストアンサー

もし、aが空だったら
If a Is Nothing Then 

もし、aが空じゃなかったら
If Not a Is Nothing Then

QDoEvents関数って何?

こんにちは。

VBAやプログラミングに詳しい皆様に
教えていただきたい質問があります。

cells(1,1)からcells(5000,1)までの値を消去するときに
処理の進行状況を表示するためにuserform上にプログレスバーを表示したいと思います。

そこで下記のようなコードを入力しました。

userform1.show
for i =1 to 5000
cells(i,1)=""
userform1.progressbar1.value=i/5000*100
next i
unload userform1

しかしこれだとuserformの背景が真っ白になってしまい
ラベルの文字も消えてしまいます。
そこで「EXCEL VBA パーフェクトマスター」という本を見たら

for i =1 to 5000
cells(i,1)=""
userform1.progressbar1.value=i/5000*100
DoEvents
next i
unload userform1
と入力すれば解決することがわかりました。

しかし「DoEvents」についてあまり詳しく書いていなかったのでDoEvents関数をヘルプで見ると、
「発生したイベントがオペレーティング システムによって処理されるように、プログラムで占有していた制御をオペレーティング システムに渡すフロー制御関数です。」

と書いてあるのですが正直、書いてあることがよくわかりません。

どなたかDoEvents関数について、
もう少しわかりやすく教えていただけませんか。
それから、最初に書いたコードで実行すると
ユーザーフォームの背景が真っ白になってしまう原因も
教えていただけませんか?

よろしくお願いいたします。

こんにちは。

VBAやプログラミングに詳しい皆様に
教えていただきたい質問があります。

cells(1,1)からcells(5000,1)までの値を消去するときに
処理の進行状況を表示するためにuserform上にプログレスバーを表示したいと思います。

そこで下記のようなコードを入力しました。

userform1.show
for i =1 to 5000
cells(i,1)=""
userform1.progressbar1.value=i/5000*100
next i
unload userform1

しかしこれだとuserformの背景が真っ白になってしまい
ラベルの文字も消えてしまいます。
そ...続きを読む

Aベストアンサー

簡単に言うと、
OS に制御を渡すってことです。(ヘルプそのまんま)
時間が掛かるループ処理などの場合、ループが終わるまで制御は独占されてしまいます。
ですのでループ中は OS や Excel そのものにも再描画をさせる暇さえ与えません。
途中に DoEvents を入れると制御が OS に渡るので、OS は溜まっていた処理をそこで行うことができます。
結果、フォームの再描画などが行われることになります。

注意点ですが、
Private Sub CommandButton1_Click()
  Dim i As Long

  For i = 1 To 50000
    DoEvents
    Cells(i,1) = ""
  Next i
End Sub

Private Sub CommandButton2_Click()
  MsgBox "hoge"
End Sub

っていうフォームのコードがあった場合、
DoEvents を入れることによって、ループ中にユーザーがCommandButton2 を押すことによって CommandButton2 のクリック イベントも動いちゃいます。
CommandButton1 のクリック イベントではループの前に
CommandButton1.Enabled = False
CommandButton2.Enabled = False
を書いてフォーム上の CommandButton を無効にしておき、ループが終わったら
CommandButton1.Enabled = True
CommandButton2.Enabled = True
と書いて CommandButton を有効に戻してください。

これを工夫すれば、CommandButton2 で CommandButton1 のループを途中キャンセルする処理もすることができます。

Private Canceled As Boolean

Private Sub CommandButton1_Click()

  CommandButton2.Enabled = False

  Dim i As Long
  For i = 1 To 50000
    DoEvents

    If Canceled = True Then
      MsgBox "キャンセルしました"
      Exit Sub
    End If

    Cells(i, 1).Value = ""
  Next i
End Sub

Private CommandButton2_Click()
  Canceled = True
End Sub



コードの行頭にあるスペースは見易さのために全角スペースで作成していますので、これをこのままコピペするとエラーになるかもしれません。
コピペするなら行頭の全角スペースを半角スペースに直してください。

簡単に言うと、
OS に制御を渡すってことです。(ヘルプそのまんま)
時間が掛かるループ処理などの場合、ループが終わるまで制御は独占されてしまいます。
ですのでループ中は OS や Excel そのものにも再描画をさせる暇さえ与えません。
途中に DoEvents を入れると制御が OS に渡るので、OS は溜まっていた処理をそこで行うことができます。
結果、フォームの再描画などが行われることになります。

注意点ですが、
Private Sub CommandButton1_Click()
  Dim i As Long

  For i = 1 To 50000
...続きを読む

QEXCEL VBAで計算値を四捨五入、切り上げ、切捨てする方法

ネットで探してみたのですが、計算結果を四捨五入して特定のセルを
返すにはどうしたらいいのでしょうか?

Sub hokangosa()

Dim ZPS As Double
Dim ZPOS As Double
Dim DMN As Double
MsgBox (" >>> 補間誤差自動計算 <<< ")
MsgBox (" >>> 初期値入力します <<< ")
ZPS = InputBox(">>> ステップを入力してください<<<")
ZPOS = Sheet1.Cells(22, 4).Value
DMN = ZPOS / ZPS
Sheet1.Cells(23, 6).Value = DMN
End Sub

ここでDMNの値を四捨五入したいです。

またこれとは別に切上げ、切捨ても教えていただけるとありがたいです。

Aベストアンサー

DMN = Application.WorksheetFunction.Round(ZPOS / ZPS, 0)
で、四捨五入
DMN = Application.RoundDown(ZPOS / ZPS, 0)
で切り捨て
DMN = Application.RoundUp(ZPOS / ZPS, 0)
で切り上げです。

引数で、対象桁を変更できます。

QRedim とEraseの違いは?

VBA フォームのボタン クリックイベント時に書き込んだ記述で、次のような記述があります。
Dim temp() as string
ReDim temp(0)
(next processing)

一度、イベントを処理した後(フォームは閉じません。)
再度ボタンをクリックして同じコードを実行すると前に行ったデータが消去されずに残っているようです。
ReDimで Preserve宣言を使っていないので内容が消去されると考えていました。
ReDimのまえに、
Erase temp
という処理を設けて対応しますが、
ローカルウィンドウやウオッチウィンドウでメモリがクリアされたか確認できていません。
ReDimとEraseの処理でどのような違いがあるのでしょうか?

どなたかよろしくお願いします。

Aベストアンサー

----ヘルプより引用----
Eraseステートメント
固定サイズの配列の場合は要素を再初期化し、動的配列の場合は割り当てたメモリを解放します。
----------------------

今回は、動的配列なので、メモリ解放ですね。

----ヘルプより引用----
ReDim ステートメント
動的配列変数に対するメモリ領域の再割り当てを行います。プロシージャ レベルで使用します。
----------------------

通常は動的配列のサイズを変更する時に使います。
また、Preserve を付けない場合は値は残りません。

ReDim temp(0) ですが、Option Base 1 なら、エラーになります。
Option Base 0 (又は、省略)の場合、添え字 0 の要素が利用可能です。

UBound(temp) は 0 となります。
つまり、ReDimで配列の要素を 0 個にすることは出来ません。

ちなみに、Eraseした場合、UBound()はエラーになります。

Erase temp
Debug.Print UBound(temp) ' この行でエラー



>再度ボタンをクリックして同じコードを実行すると前に行ったデータが消去されずに残っているようです。

この部分が疑問ですね。

プロシージャ内で宣言した変数なら、プロシージャが終了した時点で捨てられるので、次のイベントに値が残ることはありません。

どこか別の場所に値が残っているのでは?

----ヘルプより引用----
Eraseステートメント
固定サイズの配列の場合は要素を再初期化し、動的配列の場合は割り当てたメモリを解放します。
----------------------

今回は、動的配列なので、メモリ解放ですね。

----ヘルプより引用----
ReDim ステートメント
動的配列変数に対するメモリ領域の再割り当てを行います。プロシージャ レベルで使用します。
----------------------

通常は動的配列のサイズを変更する時に使います。
また、Preserve を付けない場合は値は残りません。

ReDim temp...続きを読む

QVB6 配列を初期化したい

VB6でループさせて配列に値を入れて、計算させて最終的に求めたい値をRとします。そのときループで繰り返すためか同じ配列に値を入れてどんどん値がでかくなりRの値がおかしくなってしまいます;
おそらく問題は一回前に入れた配列がそのままのこってしまってるからなのだと思うのですが;
配列の中の値をクリアする方法はないものでしょうか?
一応、配列=0として初期化しようとしても値は変わらず前のが残ったままになってしまっています;
どなたかわかる方いらっしゃいましたらご回答宜しくお願いします

その他何かいい方法があればそれも教えていただけたらと思います

Aベストアンサー

Eraceステートメントを使用

  Dim a() as Long
  Dim s(100) as String
  Dim x() as Long

  Erase a     ’要素が0になる
  Erase s     ’要素が""になる

  Redim x(100) as Long

  Erase x      ’メモリを解放

注)VB2005の場合は動作が異なるので注意して下さい。

QExcelのメモリ(配列)の上限は2Gではないのか

こんにちは、
現在ExcelのVBAで大量に配列を必要なマクロを作成しています。

その為、計算量が増えるとどうしても、
メモリ不足というエラーが発生してしまい困っています。

そこで、現在使っているExcelがどれだけの配列とメモリを使用できるのか
下記コードを使用してテストしてみました。

そうすると、下記のような値の時メモリ不足というエラーが発生してマクロが終了しました
(下図参照)
・メモリ(プライベートワーキングセット)  :1249716 K (約1G?)
・String型配列数(各要素"01,02,03,04,05,06") :約28000000(2800万個)

私の知識では、32bitアプリケーションのメモリの上限は2Gだと聞いています。
ですが、実際にはその半分しか使われていません。
そこで質問となるのですが

・32bitアプリケーションの上限が2Gと言われているのはプラベートワーキングセットの値のことではないのか?

・32bit版Excelを使用して、これ以上のメモリ(配列)を使用することは可能か

・可能であれば、その方法はどんな方法か?

以上のことについてお聞きしたいと思っております。
上のどれか一つでもいいです。知っていることがあれば教えてください。

補足となりますが、テストしたPCの簡単な環境を下に記載して置きます。
どのPCでも上記結果とほぼ変わりはありませんでした。

PC1
Windows7 32bit メモリ 4G Excel2013(32bit)

PC2
Windows7 64bit メモリ 8G Excel2010(32bit)

以下は使用したプログラムコードです

---------------------------------------------------------------


Public Sub 配列上限取得計算()
On Error GoTo ErrEnd
Dim i As Long
Const kankaku As Long = 1000000


Dim Moji As String
Moji = "01,02,03,04"

Dim ans() As String
ReDim ans(1 To kankaku) As String
i = 1
Do
If i Mod kankaku = 0 Then
ReDim Preserve ans(1 To i + kankaku) As String
End If

ans(i) = Moji
i = i + 1
Loop
Erase ans
Exit Sub
ErrEnd:
MsgBox Err.Description & vbCrLf & "これ以上の配列を設定できません。" & vbCrLf & "上限は" & i & "です。"
Erase ans
Err.Clear

End Sub

こんにちは、
現在ExcelのVBAで大量に配列を必要なマクロを作成しています。

その為、計算量が増えるとどうしても、
メモリ不足というエラーが発生してしまい困っています。

そこで、現在使っているExcelがどれだけの配列とメモリを使用できるのか
下記コードを使用してテストしてみました。

そうすると、下記のような値の時メモリ不足というエラーが発生してマクロが終了しました
(下図参照)
・メモリ(プライベートワーキングセット)  :1249716 K (約1G?)
・String型配列数(各要素"01,02,03,04,05...続きを読む

Aベストアンサー

#2の回答者です。
>何百万どころか数千万単位で必要です。
>やっていることは、50個あるサンプルで複数の実験を行います。

そういう理由で、配列を使うという所に疑問を感じます。

>50個あるうち6個の組み合わせを考えた場合、
>15890700(1589万700個)通りの文字列を一度配列に格納し、それから各条件に合うかどうか各要素ごとに確認していく作業をしています

時々、ここの掲示板でも、年に一度ぐらい、似たような話は出会いますが、果たして一覧を配列の中にすべて収める必要があるのでしょうか?その都度、組み合わせていって、必要なものを取り出せば済む話だと思うのです。まさか、出来上がってみなければ分からないというような話ではないと思います。もちろん、人間の判断を要するものだとしても、1500万件もの量を、個人でこなしきれるものではないとは思います。

何十年と、こうした掲示板を見ている私でも、組み合わせをすべて配列に入れるという話は、初めてです。もちろん、#1の人の書いた、配列の分散という方法もあるはずだとは思うのですが、今度は、本体のExcel側が果たして要求に応えるか分かりません。

ただし、Excelのアドインの"Solver"の開発元の会社のツールで、組み合わせを解決するという話は聞いたことがあります。ただ、10数万円もするアプリですので、容易には手が出ません。その代わり、VB6時代で、いくつかのアルゴリズムは公開されているはずです。

#1のお礼欄
>64bitOfficeはまだ一般的でなく、また、32bitOfficeと同時にインストールはできなかったと記憶しております。

64bit Officeは、現在の最新バージョン(2013)でも、Microsoft 側は、使用をお勧めしていません。

#2の回答者です。
>何百万どころか数千万単位で必要です。
>やっていることは、50個あるサンプルで複数の実験を行います。

そういう理由で、配列を使うという所に疑問を感じます。

>50個あるうち6個の組み合わせを考えた場合、
>15890700(1589万700個)通りの文字列を一度配列に格納し、それから各条件に合うかどうか各要素ごとに確認していく作業をしています

時々、ここの掲示板でも、年に一度ぐらい、似たような話は出会いますが、果たして一覧を配列の中にすべて収める必要があるのでしょうか?その都度、組...続きを読む

QFunctionの戻り値を配列にしたいのですが

vbを始めたばかりですがよろしくお願いします。

Functionの戻り値を配列にしたいのですが

Function fnc(ByVal a As Byte, ByVal b As Byte) As Integer()
fnc(0) = a + b
fnc(1) = a - b
End Function
というような使い方はできないのでしょうか?
一つのFunctionで二つの計算結果をかえすには
どうしたらよいのでしょうか?
お願いします。

Aベストアンサー

ローカル変数を使えば可能だと思いますよ

VB6.0の場合
Function fnc( byVal a as Byte, Byval b as Byte) as Integer
  dim ar(1) as Integer
  ar(0) = a + b
  ar(1) = a - b
  fnc = ar
End Function

VB.NETなら
Function fnc( byVal a as Byte, Byval b as Byte) as Integer
  dim ar(1) as Integer
  ar(0) = a + b
  ar(1) = a - b
  return ar
End Function

VB.NETでも fnc = ar と言った記述も出来ます

呼び出し側では 動的配列として返り値を受けます
dim results() as Integer
results = fnc( 5, 3 )
と言った具合です

QEXCEL VBA で現在開いているブックのファイル名を取得する方法

EXCEL2003 VBAで業務を簡素化するために、現在開いているブックのファイル名を取得する方法が分かりません。
作業手順をマクロを使って処理していますが、オリジナルのワークブックをファイル名を変えて保存し、以後、このワークブックを読み込んで使用しています。
このときのVBAは、オリジナルのファイル名を使っているため、ファイル名を変更するとエラーになり、以後の業務に使用できません。
常にファイル名を取得出来るVBAをどなたか、教えて下さい。

Aベストアンサー

>現在開いているブックのファイル名
 ちょっと曖昧な表現かなぁという気もいたしますが、VBAが書いてあるブックのブック名は
ThisWorkbook.Name
で、現在 "アクティブにして" 操作対象になっているブックの名前は
ActiveWorkbook.Name
ですね。

 しかし、
>VBAは、オリジナルのファイル名を使っているため、ファイル名を変更するとエラーになり
というような文脈からすると、
ThisWorkbook.Name
の方ですかね。

QVBA コレクションに2次元配列を追加して取り出す方法

2次元配列を作成し、コレクションにAddし、
ループで処理をしたいと考えています。

Addしたものの、取り出す方法がわかりません。
ローカルウインドウで確認すると、値が表示されているので、 test に 2次元配列は追加されているようです。


  Dim test As Collection
Set test = New Collection
Dim tmp(0, 9) As String

tmp(0, 0) = "test"
tmp(0, 1) = "56"


test.Add tmp

tmp(0, 0) = "test2"
tmp(0, 1) = "tttt"

test.Add tmp

この後 test の中の2次元配列の値をループで取り出したいです。
取り出して、セルに代入する予定です。

どうかアドバイスをお願いいたします。

Aベストアンサー

こんな感じですかね。
Sub Sample()
  Dim test As Collection
  Dim tmp(0, 9) As String
  Dim ary As Variant
  Dim i As Integer

  Set test = New Collection

  tmp(0, 0) = "test"
  tmp(0, 1) = "56"
  test.Add tmp

  tmp(0, 0) = "test2"
  tmp(0, 1) = "tttt"
   test.Add tmp

  tmp(0, 0) = "test3"
  tmp(0, 1) = "zzz"
  test.Add tmp

  Debug.Print "データ数 : " & test.Count

  For Each ary In test
    Debug.Print ary(0, 0)
    Debug.Print ary(0, 1)
  Next

  Worksheets.Add Before:=Worksheets(1)

  For i = 1 To test.Count
    Range(Cells(i, 1), Cells(i, 10)) = test(i)
  Next

  Worksheets.Add Before:=Worksheets(1)

  For i = 1 To test.Count
    ary = test(i)
    Range(Cells(1, i * 2), Cells(10, i * 2)) = WorksheetFunction.Transpose(ary)
  Next
End Sub

こんな感じですかね。
Sub Sample()
  Dim test As Collection
  Dim tmp(0, 9) As String
  Dim ary As Variant
  Dim i As Integer

  Set test = New Collection

  tmp(0, 0) = "test"
  tmp(0, 1) = "56"
  test.Add tmp

  tmp(0, 0) = "test2"
  tmp(0, 1) = "tttt"
   test.Add tmp

  tmp(0, 0) = "test3"
  tmp(0, 1) = "zzz"
  test.Add tmp

  Debug.Print "データ数 : " & test.Count

  For Each ary In test
    Debug.Print ary(0, 0)...続きを読む


このQ&Aを見た人がよく見るQ&A

人気Q&Aランキング