Excel VBA質問箱 IV

当質問箱は、有志のボランティア精神のおかげで成り立っています。
問題が解決したら、必ずお礼をしましょうね。
本サイトの基本方針をまとめました。こちら をご一読ください。

投稿種別の選択が必要です。ご注意ください。
迷惑投稿防止のため、URLの入力を制限しています。ご了承ください。


7048 / 13644 ツリー ←次へ | 前へ→

【41495】Cells関数の処理時間 パプチ 06/8/12(土) 8:12 発言[未読]
【41496】Re:Cells関数の処理時間 かみちゃん 06/8/12(土) 8:25 発言[未読]
【41509】Re:Cells関数の処理時間 パプチ 06/8/12(土) 15:59 発言[未読]
【41510】Re:Cells関数の処理時間 かみちゃん 06/8/12(土) 16:09 発言[未読]
【41513】Re:Cells関数の処理時間 パプチ 06/8/12(土) 17:33 発言[未読]
【41514】Re:Cells関数の処理時間 かみちゃん 06/8/12(土) 18:01 発言[未読]
【41516】Re:Cells関数の処理時間 Kein 06/8/12(土) 18:11 回答[未読]
【41522】Re:Cells関数の処理時間 パプチ 06/8/12(土) 19:56 お礼[未読]
【41523】Re:Cells関数の処理時間 かみちゃん 06/8/12(土) 20:05 発言[未読]
【41524】Re:Cells関数の処理時間 かみちゃん 06/8/12(土) 20:11 発言[未読]
【41530】Re:Cells関数の処理時間 パプチ 06/8/12(土) 21:36 お礼[未読]
【41525】Re:Cells関数の処理時間 Kein 06/8/12(土) 20:12 発言[未読]
【41520】Re:Cells関数の処理時間 Hirofumi 06/8/12(土) 19:44 回答[未読]
【41527】Re:Cells関数の処理時間 パプチ 06/8/12(土) 20:45 お礼[未読]

【41495】Cells関数の処理時間
発言  パプチ  - 06/8/12(土) 8:12 -

引用なし
パスワード
   システム開発を始めたらCells関数の処理時間が大きいことに気が付きました。
100や200の読み書きなら問題ないですが2000や10000個の読み書きをさせると
待つのが嫌になります。VBに値を渡したり、その計算結果をEXCELに書き込む
ときにCells関数を使っていますが、巧い方法があれば教えて下さい。

【41496】Re:Cells関数の処理時間
発言  かみちゃん  - 06/8/12(土) 8:25 -

引用なし
パスワード
   こんにちは。かみちゃん です。

> 巧い方法があれば教えて下さい。

どういうコードを組むかによりますが、配列変数で一気に読み込んだり、書き込ん
だりするという方法もあると思います。
質問に具体的なものが見えないので、この程度しかコメントできません。

【41509】Re:Cells関数の処理時間
発言  パプチ  - 06/8/12(土) 15:59 -

引用なし
パスワード
   かみちゃん、返信ありがとう
大量のデータは配列変数の内容の伝達のためです。
つまりEXCELの表のデータをVBへ、そしてVBで計算した結果を
またEXCELに渡したいのです。そのときデータが多いものですから
処理時間がとてもかかります。
なお、Cells関数はFor Nextで1個1個のセルを1つづつ処理する
方法をとっています。配列で効率のよい方法があれば教えて下さい。

【41510】Re:Cells関数の処理時間
発言  かみちゃん  - 06/8/12(土) 16:09 -

引用なし
パスワード
   こんにちは。かみちゃん です。

>なお、Cells関数はFor Nextで1個1個のセルを1つづつ処理する
>方法をとっています。配列で効率のよい方法があれば教えて下さい。

それであれば、For〜Nextのコードの提示はできないのでしょうか?
提示いただいたければ、何かしらの解決案は出てくるかもしれません。
なお、VBAではなくVBの連携については、私、よくわかりません。

【41513】Re:Cells関数の処理時間
発言  パプチ  - 06/8/12(土) 17:33 -

引用なし
パスワード
   レスありがとう。ソースがないとイメージがわきませんよね。

EXCELからのデータの読み込み部です

  Dim Xdat(2000,10) as Single, Ydat(2000,10) as Single
  Dim N as Integer, NV as Integer, i as Integer, j as Integer
  Workbooks.Open FileName:=Fname
  With Workbooks(2).Worksheets("Sheet1")
   N=2000
   NV=10
   for i=1 to NV
    for j=1 to N
     Xdat(j,i)=Val(.Cells(j,i))
    next
   next
  End With
    :
  Xdatを入力としてYdatを計算して求めます
    :

EXCELへの書き込み部分は
  With Worksheets("Sheet2")
   for i=1 to NV
    for j=1 to N
     .Cells(j,i)=Ydat(j,i)
    next
   next
  End With

この例では2万個のセルの読み込みと、2万個のセルへの書き込み
があります。メモリ演算だけならなんでもないのですがCells関数
を使うと膨大な処理時間を要します。
以上ですがアドバイスがあればお願い致します。

【41514】Re:Cells関数の処理時間
発言  かみちゃん  - 06/8/12(土) 18:01 -

引用なし
パスワード
   こんにちは。かみちゃん です。

>この例では2万個のセルの読み込みと、2万個のセルへの書き込み
>があります。メモリ演算だけならなんでもないのですがCells関数
>を使うと膨大な処理時間を要します。

一例ですので、これで処理速度が向上するかどうかは定かではありませんが、
以下のような感じだとどうでしょうか?
私は、このようなことしか思いつきません。

Sub Test1()
 Dim Xdat As Variant
 Dim Ydat(2000,10) As Variant
 Dim N As Long
 Dim j As Long
 Dim NV As Integer
 Dim i As Integer
 
 N = 2000
 NV = 10
 Xdat = Sheets("Sheet1").Range("A1").Resize(N, NV)
 
 For i = 1 To NV
  For j = 1 To 1
   Debug.Print Xdat(j, i)
  Next
 Next

 'Xdatを入力としてYdatを計算して求めます

 '書き込み処理
 Sheets("Sheet2").Range("A1").Resize(UBound(Ydat, 1), UBound(Ydat, 2)).Value = Ydat
End Sub

【41516】Re:Cells関数の処理時間
回答  Kein  - 06/8/12(土) 18:11 -

引用なし
パスワード
   読み込みはループ処理にしなければなりませんが、
書き込みは作った配列を一気にセルに流し込む形にすることが
出来ます。むしろそのために配列を作ってるようなものなんだけど・・。

Sub Test_MyArray()
  Dim i As Long, j As Long
  Dim Ary(1 To 2000, 1 To 10)
 
  For i = 1 To 2000
   For j = 1 To 10
     Ary(i, j) = _
     Val(Sheets("Sheet1").Cells(i, j).Value)
   Next j
  Next i
  Worksheets("Sheet2").Range("A1:J2000").Value = Ary
  Erase Ary
End Sub

ちなみにこのマクロの処理時間は、初回の実行時でも2秒以下でしたが・・。

【41520】Re:Cells関数の処理時間
回答  Hirofumi  - 06/8/12(土) 19:44 -

引用なし
パスワード
   こんななのでは?

'EXCELからのデータの読み込み部です

  Const N = 2000
  Const NV = 10
  
  Dim i As Long
  Dim j As Long
  Dim Xdat(2000, 10) As Single
  Dim Ydat(2000, 10) As Single
  Dim vntData As Variant
  
  Workbooks.Open FileName:=Fname
  
  With Workbooks(2).Worksheets("Sheet1")
    vntData = .Cells(1, 1).Resize(N, NV).Value
  End With
  
  For i = 1 To NV
    For j = 1 To N
      Xdat(j, i) = Val(vntData(j, i))
    Nextj
  Nexti

':
'  Xdatを入力としてYdatを計算して求めます
':

'EXCELへの書き込み部分は

  Worksheets("Sheet2").Cells(1, 1).Resize(N, NV).Value = Ydat

【41522】Re:Cells関数の処理時間
お礼  パプチ  - 06/8/12(土) 19:56 -

引用なし
パスワード
   Keinさん、かみちゃんご回答ありがとうございます。
二人が言われた方法でセルへの書き込みはそれぞれ高速にできました。

ただし、セルからの読み取りはKeinさんは出来ないとのご指摘。
かみちゃんはプログラムの提示がありましたが
私が試したところ「配列には割り当てられません」とか
「型が一致しません」というエラーが出て正常動作しませんでした。
かみちゃんはご確認されたのでしょうか?動きましたでしょうか?

とりあえず私の願望は叶いました。
セルからの読み取りも高速に出来てしまえば万万歳なのですが。
ご教示、本当にありがとうございました。

【41523】Re:Cells関数の処理時間
発言  かみちゃん  - 06/8/12(土) 20:05 -

引用なし
パスワード
   こんにちは。かみちゃん です。

>かみちゃんはプログラムの提示がありましたが
>私が試したところ「配列には割り当てられません」とか
>「型が一致しません」というエラーが出て正常動作しませんでした。
>かみちゃんはご確認されたのでしょうか?動きましたでしょうか?

失礼ながら、最初パプチさんが提示されたコードは、こちらでは動作確認できていません。
Ydatの格納処理が不明、Sub 〜 End Sub の記述がないからこれ以外にはコードが
ないことと解釈せざるを得ないなど・・・

それに対して、私は想像を働かせて、動作確認をしたのち提示しています。
変数宣言部を以下のように記述していますか?
 Dim Xdat As Variant
 Dim Ydat(2000, 10) As Variant
私が提示した Sub Test1() をパプチさんがそのまま動作確認していないのではないでしょうか?

そのまま動作確認していただいた上でのご発言なら、調査が必要ですが、いかがでしょうか?何か、ご自身で修正またはご自身のコードに置き換えてエラーならば、
動作確認できません。

【41524】Re:Cells関数の処理時間
発言  かみちゃん  - 06/8/12(土) 20:11 -

引用なし
パスワード
   こんにちは。かみちゃん です。

>かみちゃんはプログラムの提示がありましたが
>私が試したところ「配列には割り当てられません」とか
>「型が一致しません」というエラーが出て正常動作しませんでした。

ちなみに、以下の記述をよく比較してみてください。
A1、B1セルに文字列ではなく数値を適当に入れて試していただけるとわかると思います。
私も同じ悩みを持った経験がありますから・・・・

Option Explicit

'「配列には割り当てられません」のコード例
Sub test()
 Dim Xdat(1, 2) As Variant
 
 Xdat = Range("A1").Resize(, 2).Value
 Debug.Print Xdat(1, 1)
 Debug.Print Xdat(1, 2)
End Sub

'「型が一致しません」のコード例
Sub test2()
 Dim Xdat() As String
 
 Xdat = Range("A1").Resize(, 2).Value
 Debug.Print Xdat(1, 1)
 Debug.Print Xdat(1, 2)
End Sub

'動作確認OK
Sub test3()
 Dim Xdat() As Variant
 
 Xdat = Range("A1").Resize(, 2).Value
 Debug.Print Xdat(1, 1)
 Debug.Print Xdat(1, 2)
End Sub

'動作確認OK
Sub test4()
 Dim Xdat As Variant
 
 Xdat = Range("A1").Resize(, 2).Value
 Debug.Print Xdat(1, 1)
 Debug.Print Xdat(1, 2)
End Sub

【41525】Re:Cells関数の処理時間
発言  Kein  - 06/8/12(土) 20:12 -

引用なし
パスワード
   あー・・いや・・二次元配列なら確かにループで作らなくても、
かんみちゃんさんのように Variant型の配列変数を使えば出来ます。
その点は明らかに私の方が間違ってました。すいません m(_ _)m
ただ、2万個もの要素を持つ配列を一気に作り、それで値の代入をしようとすると
かなりのメモリー負担を生じることが予想されますから、エラーメッセージが出たり
Excelが落ちたりする可能性はあるのではないか、と思われます。
ならばループなら安全なのか ? と突かれると、自信があるわけではないのですが・・。

【41527】Re:Cells関数の処理時間
お礼  パプチ  - 06/8/12(土) 20:45 -

引用なし
パスワード
   Hirohumiさんご回答ありがとうございます。
お蔭様で完璧に問題が解決されました。
色々な遣り方があるのだなとつくづく関心させられます。
本例題は読みに1分半、書き込みに2分、計3分半かかる処理が
読み込みは一瞬、書き込みは2秒で計2秒で処理できるようになりました。
本当にありがとうございます。
これで効率の良いシステム開発の夢が実現できます。

【41530】Re:Cells関数の処理時間
お礼  パプチ  - 06/8/12(土) 21:36 -

引用なし
パスワード
   かみんちゃん、レスありがとうございます。
まず失礼なことを言ってしまったことをお詫び致します。
Xdatの意味を十分把握していなかったため正しく定義していませんでした。

実行結果はセルの読み書きともに高速にできることを確認いたしました。
今まで3分半かかったのが、僅かに2秒で処理できてしまいます。
本当にありがとうございました。
お蔭様でいいシステムが出来そうです。m(__)m

7048 / 13644 ツリー ←次へ | 前へ→
ページ:  ┃  記事番号:
2610219
(SS)C-BOARD v3.8 is Free