ファイナルアンラッキー

VBSを非推奨アナウンスはVBAも視野に入れたアナウンスなのかもしれない 先月の11月位から、Excelのアップデート毎に、割と複雑な処理を持っているExcelのVBAにて、今まで通りの正常処理が行えないトラブルが増えている。 Onedriveが起因するトラブルは度々起きているが、それ以外のトラブルも増えてきた。 そういえば昨年の10月から、VBS（Visual Basic Script）が非推奨となった。 レガシーVBの生き残りは、後はVBAのみとなった。 しかし、VBAは、Excelではおまけでついているような機能だとして、ACCESSではVBAでの実装がほぼメインであり、こちらまで非推奨になる事は無いとは思いたいが、そもそもレガシーVBを今から習得する若者も、市場シェア的には随分減ってきている。 最近のEXCELでのVBAの挙動がおかしくなる件は、EXCELマクロをMicrosoftが終了させてがっているのかもしれない。 最近は、自動化ツールでの代用や、Asteria、Dataspiderといったノーコード、ローコードを代わりに使用する現場も増えている。 Excelマクロを使用する理由は今や、ITに予算を掛けたくない企業と、個人の為にだけある機能となってきているのかもしれない。 VBAだけを軸にやっていこうと考えている人は、少しだけ軸の調整も検討した方が良さそうだ。 TIOBE INDEXでも示している通り、コードで実装するならシェアが増加中のC#を選択するのが良いのは一目瞭然だが、ノーコード、ローコードを組み合わせるのもマクロ処理の代わりとなるので、Excel以外でのデータ処理も視野にそちらに軸をずらすとどちらの案件も拾っていけそうではある。

Recordsetの内容をFor分で回さずにシートへ反映する 例：ACCESSファイルのテーブル情報を取得して一括でEXCELへ反映させる 使用するACCESSファイルには下記のような商品マスターテーブルがある これをExcelのVBAからADOでデータ取得し、下記のように用せずに一括で反映させる。 使用するADOのバージョンは迷いがちだが、今ではすっかりOFFICE365のExcelを使用している企業が殆なので、古いバージョンの事は余程配慮が必要でなければ、【Microsoft ActiveX Data Object 6.1 Library】を選択すれば良いだろう。 稀に、32ビット版をまだ使用している場合は【Microsoft ActiveX Data Object 2.8 Library】を使用する事になる。 SQLでデータを取得し、Excelのワークシートへ一括反映させるコードのサンプルは下記となる。 Dim sql_con As New ADODB.Connection Dim sql_rs As New ADODB.Recordset sql_con.Open "Provider=Microsoft.ACE.OLEDB.16.0;Data Source=P:\OneDrive\Download\test\test.accdb" Set sql_rs = sql_con.Execute("SELECT * FROM 商品マスター") Range("A1").CopyFromRecordset Data:=sql_rs sql_rs.Close sql_con.Close Set sql_rs = Nothing Set sql_con = Nothing Rangeが持つCopyFromRecordsetメソッドで、指定したセルから、一括で表形式でデータベースから取得した内容を反映させる事が出来る。 EXCELのVBAでデータベースを取得してか

配列化しても処理速度が落ちてしまう実装方法 折角配列化して処理速度改善を行っても、処理速度が改善しなくなる処理 図のように、配列化しても、データ取得の特定のデータに行きつく為に、アクセスを判別する処理が、毎回必要とする処理を実装してしまうと処理速度が大幅に落ちてしまう事がある。 データベースのようにインデックス化されたデータベースから引用する場合には問題ないが、Excelシートの情報をそのまま配列化した場合に、データベースのようなアクセス方法で実装してしまうと、処理速度の改善がされない場合がある。 何故なら、特定のデータにアクセスする為に、毎回全件データを走査していく必要があるからだ。 Excelシート情報を配列化した処理で、処理速度改善を実現する方法とは？ 何も事前処理されていないExcelシートの情報をそのまま配列化して、特定の加工を施し、データを転記する場合、加工の為に配列化された情報か特定のデータに辿り着くまで毎回アクセスする必要が出てしまい、この特定のデータに辿り着く振る舞いを、1万件、2万件のデータで行うと、少量では気が付きにくかった処理時のオーバーヘッドが積み上げられ、非常に遅くなってしまう結果となる。 処理に必要なデータ順に並び替え、特定のキーが変わったら（キーブレイク）したら、次種類のデータとなったと判別するようにすると、データアクセスの度に全件アクセスを行う必要が無くなり、処理速度がかなり改善される。 配列化されたデータアクセスの処理改善まとめ 処理の度に、毎回全件データアクセスを必要とする構造を廃止し、処理が必要な順にデータを並び替え、データの上から順に一巡するだけで処理が完結する構造とすると、処理速度が随分改善する。 数十分単位だったものが、数秒で終わるケースもこれまでに経験しており、処理を便利に高機能にしたいと走ってしまった場合、大量のデータに遭遇した場合、処理速度の問題にぶつかるであろう前に、ユーザーエクスペリエンスを優先させるかを事前によく検討した方が良いと思われる事案の一件を紹介しました。

通常のコピーを使用しないセルのコピー方法 Copyを使用しないセル値のコピーや転記を行う方法 EXCELのVBA（マクロ）でクリップボードを使用した実装を行うと、マクロ実行中に他の作業で、コピー＆ペースト等を行うと正しく動かなくなる場合がある。 もう一度マクロを実行し直すか、マクロ実行中は操作を控えるかの選択となるが、マクロを使用するユーザーが、マクロ作成者であれば回避も可能だが、そうではない他のユーザーが利用する場合、意図しない動作をしてしまう場合がある。 セルのコピーは、Copyメソッドを利用しない転記方法もある為、今回はクリップボードを使用しない方法で、セルの範囲をコピーする方法を紹介 この方法は、クリップボードが使用されてしまう。 Range("B3:D7").Copy Range("B10:Z13").PasteSpecial Paste:=xlPasteAll 実行結果 Value(XLRangeValueXmlSpreadsheet)を使用すると、クリップボードは使用されない Range("B10:D13").Value(xlRangeValueXMLSpreadsheet) = Range("B3:D7").Value(xlRangeValueXMLSpreadsheet) 実行結果 実際にクリップボードの使用、不使用を確認したい場合は、メモ帳などで、文字をコピー後、マクロを実行し、メモ帳で、貼り付け（ペースト）を行って、コピーした文字が貼り付けば、クリップボードが使用されているかいないかが確認出来る。 クリップボードの使用をマクロで実行している場合、貼付け時に、マクロでコピーした内容が貼り付けられたり、コピーしたクリップボードの内容がクリアーされている事が分かる。

マクロ付きExcelファイルを実行する際に、VBScriptで警告を抑制する方法 VBScriptでマクロ付きExcelファイルを起動する際に、マクロの実行警告を表示させないコード 下図のようなメッセージだけ表示するマクロ付きExcelファイルがあるとする。 何の害も無いこのマクロ付きExcelファイルのxlsmファイルを開こうとした場合でも、警告が表示される。 このファイルを開こうとすると・・・ マクロ付きExcelファイルを実行する為の警告メッセージで、「セキュリティの警告：マクロが無効にされました。」と表示され、【コンテンツの有効化】ボタンを押さなければ、マクロが実行出来ない状態となる。 確実にそのファイルが安全だと分かっており、頻繁に開くファイルであった場合には、いちいちボタンを押下する作業が煩わしくなる。 また、マクロ付きExcelファイルである、xslmファイルを自動実行するバッチ処理であったりする場合等は、人がこのボタンを押さないといけない状態だと、無人での自動化の妨げとなる。 このメッセージを抑制、非表示とし、VBScriptで実行する場合は、下記のようなコードで実現できる。 仮に、AutoMacro.vbsとして保存 Dim objExcel Dim objBook Set objExcel = CreateObject("Excel.Application") 'イベント抑制 objExcel.EnableEvents = False set objBook = objExcel.Workbooks.Open("P:\OneDrive\Download\test\testbook.xlsm")) objBook.Close() objExcel.Quit() Set objBook = Nothing Set objExcel = Nothing 共有フォルダーからコピーしてきたファイルは、あらかじめファイルのプロパティで、セキュリティに別途チェックを入れておく必要がある事をお忘れなく。

ExcelのVBA高速化の3要素 １．一括でデータを読み取る(データ取得でForは使わない) データ取得時に下記のように、セルの範囲のデータを取得する際に、For分で回しながら一つづつ値を取得をしている場合、Excelオブジェクトは内部で様々な処理（検証や、変換等）が行われる為、それを、セル一つづつの単位で回すと、データ量が少ない場合は対して問題にならないが、データ量が増えてくると分単位でかかる場合がある。 この表を下の枠に写す悪いコード例 実行前 実行後 Sub もっさり動作コード() Dim iRow As Long Dim iCol As Long For iRow = 2 To 4 For iCol = 2 To 5 Sheet1.Cells(iRow + 4, iCol).Value = Sheet1.Cells(iRow, iCol) Next Next End Sub 高速化後 Sub 高速化コード() Dim iRow As Long Dim iCol As Long Dim OutputLayout() As Variant '一括でデータを取得し OutputLayout = Sheet1.Range(Cells(2, 2), Cells(4, 4)) '一括でデータを出力 Sheet1.Range("B6:D8").Value = OutputLayout End Sub ２．自動計算機能のOFF EXCELシートのセル内に、沢山の数式が埋め込まれている場合、シートの複製や、値をセットした際に、毎回式が評価され、自動的に再計算されてしまいます。 データが大量になってくると、この評価にものすごく時間が掛かってしまう為、OFFにすると良いでしょう。 Sub 処理() Dim iRow As Long Dim iCol As Long

PostgreSQL12とExcelを連携させて検証 Excelのデータベース連携機能の相性が良くなった 特にUnicodeが相性が良い 　 なんだかんだで、Microsoft Office（マイクロソフトオフィス）製品とデータベースを直接続する場合は、SQL Server以外だと、テーブルやら、データやら、データベース名に使われている文字コードエラー的なものでスムーズにいかない成約が多く、結局VBAでデータ取得で安定化みたいな事をやる事が多かった。 　今回はPostgreSQLもバージョン11から12にした事だし、少しはマシになっているかと思って連携してみたら・・・・　なんとスムーズ！！ 　Excelのデータベース接続機能を使えば、データベース接続時に「Power Queryエディター」というエディターが立ち上がって、ここで集計単位とかを調整すれば・・・なんと、SQLを組まなくても勝手にクエリーが生成されて保存できてしまうのだ！ 　Power Queryエディターで保存した結果は、データベースが更新された後に、Excelのデータ更新ボタンを押すと、すぐさまデータが反映される！ おさらい EXCELを直接PostgreSQLに接続する方法。 Excelのメニューから... [データ]→ [データの取得]→ [その他のデータソースから]→ [ODBCから] の順に選択します。 ※あらかじめODBCの設定が必要で、PostgreSQLは今は64ビット版しか出ていない為、x64のCPUが搭載されているPC（64ビット版）で操作する方が良い。 　（この時、ODBCもPostgreSQLの64ビット版でUnicodeが相性が良い） 後はPower Queryで加工したい形にデータを調整するだけ。