vbaでファイルのオープン・クローズを繰り返すとメモリ不足でEXCELがコケた。

　同じフォーマットのEXCEL シート（ファイル）をマージ（統合）するvbaコードで、たくさんファイルを読ませると、メモリ不足でEXCELがコケた。
　ググってみたら、マイクロソフトのバグ らしく、ファイルのオープン・クローズを繰り返すだけで、マイクロソフトはメモリ解放のタイミングを逃してしまうようだ。
　例によって、それが仕様なのでしょう。これを回避するには、closeの後にDoEventsという呪文をかけておかなくてはならないようだ。

Optoon Explicit
Dim maxRow As Long
Dim filePath As string
Dim fileName As String
Dim i,j,k As Long
Sub MergeALL()
    j = 1  ' the row counter of TO(COPY)
    filePath = ThisWorkbook.Path & "¥"
    fileName = Dir(filePath & "*DATA*.xlsx") ' Get a file to be merged
    Do While fileName <> "" ' Is there a file to be merged ? 
        Workbooks.Open Filename :=filePath & fileName
        maxRow = ActiveWorkbook.Worksheets(1).Cells(Roes.Count,3).End(xlUp).Row
        If j = 1 then k = 1    ' k: the row counter of FROM(COPY)
        If j > 1 then k = 3
        m = maxRow - ( k - 1 ) ' m: the numbers of lines to be copied
        ActiveWorkBook.Worksheets(1).Range(Rows(k),Rows(maxrow)).Copy
        ThisWorkBook.Worksheets("まとめ").Activate
        ThisWorkbook.Worksheets("まとめ").Range(Rows(j),Rows(j+m-1).PasteSpecial (xlPasteAll)
        application.CutCopyMode = False
        j = j + m
        Workbooks(fileName).Close
        DoEvents   ' give a chance to release memory area
        fileName = Dir() ' Get the next file to be merged
    Loop
End Sub

こんな感じで、今度こそいいと思う。たぶん。ここで、一息のコーナーです。NHKの100分で名著で、ル・ボンの「群集心理」を観た。政治家やメディアは、しばしば、精緻な論理などを打ち捨て、「断言」「反復」「感染」という手法を使って、群衆たちに「紋切り型のイメージ」「粗雑な陰謀論」「敵-味方の単純図式」を流布する。まさに、そのとおりだなと感じた。誠に、空恐ろしい。

同じフォーマットのEXCEL シートをマージ（統合）するvbaコードを書いてみた。

　同じフォーマット（1行目と2行目がタイトル行で、データは3行目から始まると仮定、ファイル名にDATAという文字列が含まれているものとする）のEXCEL シートをマージ（統合）するvbaコードを書いてみた。マージするファイルはマクロブックと同じフォルダにあるとものとする。使うのは、DIR関数とCopy,Pasteメソッドで、それをDo WhileでLoopさせる。とてもシンプルだ。このツールは自身のシート（”まとめ”）にマージした結果を抱え込む。menuシートでマクロ起動ボタンを作り、マクロ登録すれば、完成だ。

Option Explicit
Dim maxRow As Long
Dim filePath As string
Dim fileName As String
Dim i,j,k As Long
Sub MergeALL()
    j = 1  ' the row counter of TO(COPY)
    filePath = ThisWorkbook.Path & "¥"
    fileName = Dir(filePath & "*DATA*.xlsx") ' Get a file to be merged
    Do While fileName <> "" ' Is there a file to be merged ? 
        Workbooks.Open Filename :=filePath & fileName
        maxRow = ActiveWorkbook.Worksheets(1).Cells(Rows.Count,1).End(xlUp).Row
        If j = 1 then k = 1    ' k: the row counter of FROM(COPY)
        If j > 1 then k = 3
        m = maxRow - ( k - 1 ) ' m: the numbers of lines to be copied
        ActiveWorkBook.Worksheets(1).Range(Rows(k),Rows(maxrow)).Copy
        ThisWorkBook.Worksheets("まとめ").Activate
        ThisWorkbook.Worksheets("まとめ").Range(Rows(j),Rows(j+m-1)).PasteSpecial (xlPasteAll)
        application.CutCopyMode = False
        j = j + m
        Workbooks(fileName).Close
        fileName = Dir() ' Get the next file to be merged
    Loop
End Sub

こんな感じになると思う。

VBAで使うrangeオブジェクトのautofilterメソッドをマイクロソフトのサイトで調べてみた。

　VBAでプログラミングするとき、いつも悩むのが、どう記述すれば良いのか、いわゆる文法（書き方）というか、そもそもの仕様の調べ方だ。どこにあるの？
　例えば、Rangeオブジェクトで使うautofilterメソッドは、以下のページで解説されている。
　https://docs.microsoft.com/ja-jp/office/vba/api/excel.range.autofilter　

　ググるときは、

 VBA、Rangeオブジェクト、autofilterメソッド site:microsoft.com

でいいようだ。

2021.09-girl

　ここで一息のコーナー。今回は、大根の冷凍について。大根が使い切れず、だめにしてしまうことが多いので、冷凍を試みた。大根おろしにして、一回分ずつをラップして、冷凍してみた。解凍が難しいのだが、電子レンジならば、200wで2分ぐらい。時間は量に応じて加減した方が良い。やり過ぎると、煮えてしまうので、1分やって具合をみて、残りは少しずつやり、お好みの解凍状態にするのがよい。

excelで、ある列の飛び飛びになったセルたちをコピペしようとすると、連続した領域でないため、拒否られる。では、どうしたらいいの？例えば、１回ソートしてみる。

　excelで、フィルターし、ある列の飛び飛びになったセルたち（ワタシは密かにこれを飛び地と呼んでいる）をコピペしようとすると、連続した領域でないため、拒否られたこと（ワタシは密かにこれを飛び地問題と呼んでいる）がないだろうか？。

　では、どうしたら回避できるのだろう？

　例えば、１回、その列をソートしてみるのはいかがだろう。

　フィルターの条件によるかもしれないが、セルの値で昇順なり、降順なり、並べ替えておけば、フィルターしても、結果が飛び飛びのセルたちにならず、連続したセルたちになり、具合がいいのではないかと・・・・。

　ここで、一息のコーナー。シャトレーゼの生ワインを試した。赤（カベルネ）を買ってみた。手頃な値段で、かつ美味しい。何よりも、参加防止剤や保存料が入っていないところがいい。最初にビンを買い、次回は交換して、中身を入れてくれるのだという。これまで、ワインのビンを捨てるのが、心苦し買ったので、しばらく、これで行こうと思う。今回、赤を試したので、次回は、白（シャルドネ）も試してみよう。

If AutoFilterMode is NOT working in VBA, Use Both FilterMode and autoFilterMode

　EXCEL  のVBAでシートを操作（例えば、行コピー）する場合、フィルタがかかったままだと、誤動作する（列がズレてコピーされる）ことがあるので、フィルタを解除するコードを書いてみた。

If ActiveSheet.AutoFilterMode = True then 　' フィルタがかかっているかを判定する
    ActiveSheet.Range("A1").autofilter　' フィルタをクリア
    ActiveSheet.AutoFilterMode = False  ' ▽ボタンを消す
End If

でもこれ（AutoFilterMode)だとフィルターがかかっていても、then節を実行してくれない。AutoFilterModeがTrueにならないのだ。 色々試行錯誤し、わかったのだが、そんな時には、AutoFilterModeではなく、FilterModeを使うと、うまく行くかもしれない。逆もありうるので、両方のパターンを網羅する。なんだか、元々がバグのようでなので、わけわかりませんが・・・

If ActiveSheet.FilterMode = True or ActiveSheet.AutoFilterMode = True then 　' フィルタがかかっているかを判定する
    ActiveSheet.Range("A1").autofilter　' フィルタをクリア
    ActiveSheet.AutoFilterMode = False  ' ▽ボタンを消す
End If

試してみてね。マイクロソフトは気ままな会社なので、ユーザはとても大変ですね。

　ここで一息のコーナー。今回は、エノキの冷凍について。エノキが使い切れず、だめにしてしまうことが多いので、冷凍を試みた。４〜５センチに切り、一回分ずつをラップして、冷凍してみた。解凍はせず、そのまま、味噌汁の具鍋に投入。物凄く使い易い。これはもはや、茹で卵の作り方みたいに、どうでもいい情報だね。

If IsChanged(boolean variable) then is NOT working in EXCEL 2016 vba, but working in EXCEL 2013 vba

sleepinggirl202109

The codes " If IsChanged(boolean variable) then" is NOT working in EXCEL 2016 vba, but working in EXCEL 2013 vba. It should be "If IsChanged= True then".

If IsChanged thenはEXCEL 2016 VBA では何故か動かない、という実行が止まる。F8で進めると動くのだが。それをIf IsChanged=True thenにすれば、文句を言わずに動く。EXCEL 2013 VBAでは文句は言わないのだが、やれやれ。

EXCELをパスワード保護すると出てくる「ファイルは次のユーザーによって保護されています」のユーザー名の文字列に個人名を出したくない場合には、EXCELのオプションで基本設定のMicrosoft Officeのユーザー設定の文字列を個人名でないものへ変更し、その後にパスワード保護を行えば良い。

　EXCELをパスワード保護すると出てくる「ファイルは次のユーザーによって保護されています」のユーザー名の文字列に個人名を出したくない場合には、EXCELのオプションで基本設定のMicrosoft Officeのユーザー設定の文字列を個人名でないものへ変更し、その後にパスワー ド保護を行えば良い。

　一度、設定したものは変更できないので、新たに設定することになるだろう。既にパスワードを設定したファイルの場合、パスワードを変更した時点で、その時点の「EXCELのオプション基本設定のMicrosoft Officeのユーザー設定の文字列に」変更されるのかもしれない。

　例えば、EXCELを第３者に提供する場合、パスワードをかけた個人の名前（図の中の黒塗りの箇所だ。ファイル名が個人名になっていて、ややこしいが、そこじゃないよ）になっていると、個人情報漏洩みたいで、実に具合が悪い。格好悪い。

　そこで、会社の略称なんぞにしたい。

　そんなときには、EXCELのオプションにある、基本設定のMicrosoft Officeのユーザー設定にある個人情報っぽい文字列を会社の略称みたいな文字列に変更した後、EXCELの保存メニューのツールでパスワードを指定し、新規ファイル保存すれば良いのだ。