質問をすることでしか得られない、回答やアドバイスがある。

15分調べてもわからないことは、質問しよう!

ただいまの
回答率

90.53%

  • VBA

    1782questions

    VBAはオブジェクト指向プログラミング言語のひとつで、マクロを作成によりExcelなどのOffice業務を自動化することができます。

エクセルのVBAの動作の高速化について

解決済

回答 1

投稿 編集

  • 評価
  • クリップ 0
  • VIEW 2,912

tomituki

score 3

エクセルのVBAの高速化を行いたいと思い質問させていただきます。

現在、きまった形のデータを作成するために、コピーandペースト作業時のミスをなくすためエクセルとVBAを使いフォーマットを作成しました。
エクセルに決まった通りにデータを入力したら、後はVBAで自動的に決まった形のテキストデータを出力してくれるというマクロであります。
当初、データ数がそこまで多くなく軽快に作業が進んでいたのですが、データ数と繰り返しステップ数が増えると作業終了までにかなりの時間がかかるようになりました。それこそ、列ごとにテキストエディタに書きこんだ方が早いかもしれないと思うようにもなりました。(実際に試したのですが、途中で選択範囲を間違えたり、重複で張り付けたりで散々でしたが…)。

セルに(下記はセル番地を示す)
B2 C2 D2 ・・・ XX2
B3 C3 D3 ・・・ XX3   
B4 C4 D4 ・・・ XX4
・ ・ ・ ・・・ ・
B500000 C500000 D500000 ・・・XX500000
とセル番地に文字が入力されていたら
B2
B3
B4

B500000
-1, 0.,
C2
C3
C4

C500000
-1, 0.,
D1
D2
D3

D500000
-1, 0.,

この間繰り返し

XX1
XX2
XX3

XX500000
-1, 0.,
-1
とテキスト形式の文字が列ごとに繋がっていく感じであります。(各列のデータの区切りに-1, 0.,という終了フラグが付きまして、EOFの時には   -1というフラグが付きます)
各列の終了行数は全部同じであります。(つまり2行目から始まり、5万行データなら5万行まで、70万行なら70万行まで全列データがある矩形型)

今現在のコードを記載します。

Sub 結果作成()
Application.ScreenUpdating = False
Dim ファイル名 As String
ファイル名 = InputBox("出力ファイル名称を入力")

outputPath = ThisWorkbook.Path & "\" '出力ファイルの保存先決定
出力ファイル = outputPath & ファイル名 & ".dat" 'datという拡張子

Dim i As Long, LngLoop As Long
Dim IntFlNo As Integer

IntFlNo = FreeFile '空いているファイル番号取得
Open 出力ファイル For Output As #IntFlNo

'テキストファイルへの書き込み

Worksheets("節点値").Activate 
Dim ν As Long
ν = Range("XFD2").End(xlToLeft).Column '列数取得

Worksheets("節点値").Activate '行数取得
Dim μ As Long
μ = Range("b1048576").End(xlUp).Row

Worksheets("節点値").Activate '時系列結果書き込み
Dim Ξ As Long
Dim Ψ As Long

For Ξ = 2 To ν '時刻歴繰り返し 'B列から開始
For Ψ = 2 To μ '節点数繰り返し '2行目から開始
Worksheets("節点値").Activate
    Print #IntFlNo, Cells(Ψ, Ξ)
Next Ψ
     Print #IntFlNo, "-1, 0.,"   '列ごとの終了フラグ記入
Next Ξ
    Print #IntFlNo, "   " & -1 'EOFのフラグ記入
Close #IntFlNo '書き込み終了
Application.ScreenUpdating = True
End Sub


という状況であり、やっていることは単純に、列ごとにその列の最終行までのデータを
Print #IntFlNo, Cells(Ψ, Ξ)
で書き出してるマクロであります。

しかしながらごくまれに5万行×100列というデータもありすごく時間がかかるものもあります。

そこで、教えて頂きたい点としては、
For Ξ = 2 To ν '時刻歴繰り返し 'B列から開始
For Ψ = 2 To μ '節点数繰り返し '2行目から開始
Worksheets("節点値").Activate
Print #IntFlNo, Cells(Ψ, Ξ)
Next Ψ
Print #IntFlNo, "-1, 0.,"   '列ごとの終了フラグ記入
Next Ξ
Print #IntFlNo, "   " & -1 'EOFのフラグ記入

で「出力ファイル = outputPath & ファイル名 & ".dat"」
に現在記述していますが、
それを列単位で
「出力ファイル = outputPath & ファイル名 & ".dat"」
に書き出せないかという事です。

例えばB列からZ列まで列数があり(25列)
2行目から50000行まで行数があるとすると

For next構文開始
B2からB50000までをコピーして
出力ファイルに書き込み
列ごとの終了フラグである「-1, 0.,」という固定文字を書きこみ
C2からC50000までをコピーして
出力ファイルに書き込み
列ごとの終了フラグである「-1, 0.,」という固定文字を書きこみ


Z2からZ50000までをコピーして
出力ファイルに書き込み
列ごとの終了フラグである「-1, 0.,」という固定文字を書きこみ
For next構文終了
EOFのフラグである「-1 」
を書きこみ。

といった感じにすれば早くなりそうな気がするのですが、上記のような構文の書き方が分かりません。それを教えていただきたいです。

使用しているパソコンで試したところ、B2からJ1048576までにそれぞれのセルに半角8文字を入力した状態でコピーをして、それをテキストエディタに張り付けるという事はできたため、メモリー自体は上記のように作業を行ったとしても耐えることができると思われます。

自分で書いたFor next構文の問題が解消できれば随分早くなると思うのでお知恵をお貸しください。

  • 気になる質問をクリップする

    クリップした質問は、後からいつでもマイページで確認できます。

    またクリップした質問に回答があった際、通知やメールを受け取ることができます。

    クリップを取り消します

  • 良い質問の評価を上げる

    以下のような質問は評価を上げましょう

    • 質問内容が明確
    • 自分も答えを知りたい
    • 質問者以外のユーザにも役立つ

    評価が高い質問は、TOPページの「注目」タブのフィードに表示されやすくなります。

    質問の評価を上げたことを取り消します

  • 評価を下げられる数の上限に達しました

    評価を下げることができません

    • 1日5回まで評価を下げられます
    • 1日に1ユーザに対して2回まで評価を下げられます

    質問の評価を下げる

    teratailでは下記のような質問を「具体的に困っていることがない質問」、「サイトポリシーに違反する質問」と定義し、推奨していません。

    • プログラミングに関係のない質問
    • やってほしいことだけを記載した丸投げの質問
    • 問題・課題が含まれていない質問
    • 意図的に内容が抹消された質問
    • 広告と受け取られるような投稿

    評価が下がると、TOPページの「アクティブ」「注目」タブのフィードに表示されにくくなります。

    質問の評価を下げたことを取り消します

    この機能は開放されていません

    評価を下げる条件を満たしてません

    評価を下げる理由を選択してください

    詳細な説明はこちら

    上記に当てはまらず、質問内容が明確になっていない質問には「情報の追加・修正依頼」機能からコメントをしてください。

    質問の評価を下げる機能の利用条件

    この機能を利用するためには、以下の事項を行う必要があります。

質問への追記・修正、ベストアンサー選択の依頼

  • hatena19

    2017/01/21 11:03

    コードはコードタグで囲んでください。コード部分を選択して、「コード」ボタン(</>)をクリックするとできます。

    キャンセル

回答 1

checkベストアンサー

+1

Print # メソッドだと1行ずつファイルに書き出すことなるので、行数が多くなると遅くなります。
出力するテキストを変数に格納して、それを FileSystemObject で一気に書き出すと改善されるでしょう。

あと、Worksheets("節点値").Activate でシートを指定するのではなく、直接Worksheetオブジェクトを参照するほうがいいでしょう。

上記の点を考慮して、自分なりに、コードを書き直してみました。

Public Sub 結果作成1()
    Dim outStr As String
    Dim C As Long, R As Long

    '出力文字列生成
    With ThisWorkbook.Worksheets("節点値")
        For C = 2 To .Range("XFD2").End(xlToLeft).Column  '時刻歴繰り返し 'B列から開始
            For R = 2 To .Range("b1048576").End(xlUp).Row  '節点数繰り返し '2行目から開始
                outStr = outStr & .Cells(R, C) & vbCrLf
            Next
            outStr = outStr & "-1, 0.," & vbCrLf   '列ごとの終了フラグ記入
        Next
        outStr = outStr & "   " & -1    'EOFのフラグ記入
    End With

    Dim FileName As String
    Dim outputPath As String
    Dim OutFile As String

    FileName = InputBox("出力ファイル名称を入力")
    outputPath = ThisWorkbook.Path & "\"    '出力ファイルの保存先決定
    OutFile = outputPath & FileName & ".txt"    'datという拡張子

    Dim FSO As Object

    Set FSO = CreateObject("Scripting.FileSystemObject")
    With FSO.CreateTextFile(OutFile)
        .Write outStr
        .Close
    End With
    Set FSO = Nothing

End Sub

返信に対する追記

書き込みを行う前にいくらか他のセルに打ち込んだ文字列を
Print 構文を使い事前に書きこませたいです。その場合にはどのようにすれば良いでしょうか?

Print は、
Open 出力ファイル For Output As #IntFlNo
で開いたファイルに1行ずつ書き込んでいくものです。
Open メソッドと Print メソッドはセットになります。
ですので、今回の回答のコードでは Print は使いません。

With FSO.CreateTextFile(OutFile)
    .Write outStr
    .Close
End With

は、変数outStr に格納されている文字列を一気にファイルに書き出すものです。

これを理解しておいてください。

などのようにメモ書きをヘッダーとして書きこんでおきたいという事です。

変数 outStr に事前に希望の文字列を代入しておくだけです。

「メモ書き」がどこにあるのか不明ですが、
「メモ書き」というシートのA1セルにあるなら、

Public Sub 結果作成1()
    Dim outStr As String
    Dim C As Long, R As Long

    outStr = ThisWorkbook.Worksheets("メモ書き").Cells(1, 1) 'これを挿入

    '出力文字列生成
    With ThisWorkbook.Worksheets("節点値")

 '以下略

投稿

編集

  • 回答の評価を上げる

    以下のような回答は評価を上げましょう

    • 正しい回答
    • わかりやすい回答
    • ためになる回答

    評価が高い回答ほどページの上位に表示されます。

  • 回答の評価を下げる

    下記のような回答は推奨されていません。

    • 間違っている回答
    • 質問の回答になっていない投稿
    • スパムや攻撃的な表現を用いた投稿

    評価を下げる際はその理由を明確に伝え、適切な回答に修正してもらいましょう。

  • 2017/01/21 15:02

    ありがとうございます。
    動作がすごく早くなりました。
    変数に格納してくれる方法を教えていただきありがとうございます。

    このように使えば高速化処理ができるという事が分かり、勉強になりました。

    もう少し教えてください。
    今回、
    ___
    With FSO.CreateTextFile(OutFile)
    .Write outStr
    .Close
    End With
    ___
    の部分で書きこみを行っていると思うのですが、
    書き込みを行う前にいくらか他のセルに打ち込んだ文字列を
    Print 構文を使い事前に書きこませたいです。その場合にはどのようにすれば良いでしょうか?

    たとえば
    解析プログラム名
    何月何日作成
    時刻歴
    1
    2
    3
    4
    5

    x秒前

    などのようにメモ書きをヘッダーとして書きこんでおきたいという事です。
    そのため
    .Write outStr
    の前にprint構文であらかじめ処理しておきたいという欲ができてしまいました。
    後から使用を述べてしまい、申し訳ありません。
    なお、自分で
    With FSO.CreateTextFile(OutFile)
    のあとに
    Print #IntFlNo, Cells(x, y)
    というように(xとyは変数として作成してfor next関数で回す)
    をやったのですが、#IntFlNoが不正ですと出てしまいました。

    キャンセル

  • 2017/01/21 17:24

    回答に追記しましたので、参照してください。

    キャンセル

  • 2017/01/21 18:41

    ありがとうございます。今までみていなかった部分を知ることができて助かりました。hatena19様、教えていただき嬉しかったです。

    キャンセル

15分調べてもわからないことは、teratailで質問しよう!

  • ただいまの回答率 90.53%
  • 質問をまとめることで、思考を整理して素早く解決
  • テンプレート機能で、簡単に質問をまとめられる

関連した質問

同じタグがついた質問を見る

  • VBA

    1782questions

    VBAはオブジェクト指向プログラミング言語のひとつで、マクロを作成によりExcelなどのOffice業務を自動化することができます。