Excelマクロの常識集！スキルアップの為に学ぶべき機能

どうも、Clmb(クライム)です。

Climb

私は、社会人になって約10年近く、ひたすらExcelマクロ(VBA)を組み続けています。その経験から、初心者が最初に学ぶべきExcelマクロの常識をご紹介します。

普段はサラリーマン向けの副業について、情報を発信していますが、

今回は、副業の話ではなく、本業で学んだExcelマクロ(VBA)の常識について紹介します。

ぶっちゃけ、この記事の内容さえ、マスターしてしまえばExcelシートを使った事務作業の自動化は超・楽勝になるでしょう。

それでは、見ていきますよ！

処理を高速化する方法

まずは、Excelマクロの処理を高速化するために知っておくべき常識を紹介しますね。

画面の描画を止める！とか超初歩的な事は言いませんのでご安心ください。笑

セルへの値の入力回数を減らす

恐らくあまり意識してない方が多いと思いますが、Excelマクロは、セルへの入力処理が苦手なんです。

セルへの入力回数が多ければ多いほど、Excelマクロの処理スピードは低下します。

セルの値を参照する処理は、そんなに遅くありません。

Climb

信用できない方は、以下の記事を確認してみてください。

【検証】Excelマクロ高速化！セルへの入力回数を減らせば早くなる

for文で繰り返しセルに値を入力する処理は遅い原因になります。

Excelシート関数を使わない

ExcelにはVlookupやCountAなど便利な関数があらかじめ備わっていますね。

しかし、この便利な関数をExcelマクロで使うと処理が遅くなります。

Vlookupは便利な関数ですが、Dictonary型など連想配列を活用すれば、より高速に安全なプログラムを組む事ができます。

Climb

詳しい実装方法は以下の記事で解説されています。

【検証】ExcelマクロでVlookupを使うと遅い！辞書(Dictionary)で高速化しよう

Dictionaryを使えると、Excelマクロの実装の幅がグンと広がりますので、初心者の方は、是非マスターするようにしてください！

コーディングスピードを上げる方法

続いて、知らないと使えない便利な変数宣言方法を紹介します。

深くプログラムを学んでない、なんちゃってプログラマ向けです。

列挙型（Enum）を活用する

Excelで作られた台帳(データベース)に対して処理を実装する際、列を特定するための変数を次のように宣言してませんか？

Sub Test()

　Const colA as Integer = 1 'A列 
　Const colB as Integer = 2 'B列 
　Const colC as Integer = 3 'C列 
　Const colD as Integer = 4 'D列 
'　(中略)
　Const colZ as Integer = 26 'Z列 

' (処理--省略--)
End Sub

Z列までの宣言をするのは大変ですし、使い辛いですよね。

列挙型で宣言すると次のようになります。

Enum col
  A = 1
  B　
  C　
  D　
  E
  F
  G
 (中略)
　Y
  Z
End Enum

Sub Test()
  Debug.Print col.Z '[26]が出力される　（★）
End Sub

列挙型「col」を作成し、その要素に「A~Z」までを宣言します。

A＝１とする事で、B移行は自動で１つ前の値に＋１された数値が入力されています。

そのため、（★)の部分でcol.Zの値を確認すると「26」が出力されます。

実際に動かしてみると理解しやすいので、是非、上記サンプルをそのままVBE(マクロを組む画面）に貼り付けて、実行してください。

WorkBooks.Openに関する小ネタ

新たにExcelファイルを開いて、処理を開始する際に注意すべき内容をまとめています。

これらの対策をしっかり行っていれば、予期せぬ実行時エラーを防止できます。

ファイル名から不正な文字列を消す

ExcelマクロでExcelファイルを開こうとした際、マクロで処理できない文字列がファイル名に含まれていると、例外が発生し処理が止まります。

それを防止するために、ファイルを開く前に、ファイル名から不正な文字列を排除しておきましょう。

これらがファイル名に含まれていると、マクロがエラーを吐きます。

なので、ファイルを開く前にrepalceで置換してあげましょう。

Function repFileName (target as String) as String

　target = Replace(target,"\","_")
　target = Replace(target,"/","_")
　target = Replace(target,"""","_")
　target = Replace(target,"<","_")
　target = Replace(target,">","_")
　target = Replace(target,"?","_")
　target = Replace(target,"[","_")
　target = Replace(target,"]","_")
　target = Replace(target,":","_")
　target = Replace(target,"|","_")
　target = Replace(target,"*","_")

  repFileName = target

End Function

既に開いてないか確認する

既に対象のExcelファイルが開かれていないか確認するのも、大変重要ですね。

もし、確認せずに同じファイルを開いたりすると、エラーや予期せぬ不具合の原因になります。

読み取り専用か確認する

ファイル更新が必要な場合は、「読み取り専用」で開くのはNGですよね。

必ず書き込み権限を持った状態でファイルを開くようにしましょう。

開く前に処理をCPUに戻す

何言ってるか分からない人も多いと思いますが、ファイルを開くという処理はExcelアプリではなく、WindowsOSの仕事です。

なので、複雑なプログラムを組む際には、かならずファイルオープンとクローズ処理の直前に「DoEvents()」を宣言しておきましょう。

処理対象シートのフィルタを外す

そして、最後になりますが、編集対象のExcelシートのフィルタを外しておきましょう。

フィルタは非常に厄介で、隠れた行は無かったものとして扱われ、フィルタ状態のExcelファイルにマクロで書き込みすると、元データがひっちゃかめっちゃかになってしまいます。

まじで危険なので、フィルタを外すという処理は必ず実装しておいてください。

まとめ

Excelマクロ初心者向けに、ノウハウを列挙致しましたが、如何でしたか？具体的なソースについては各々リンク先で学んで頂き、あなたのスキルとして身に着けてくださいね。

▼こちらの記事も人気です▼

2020年6月11日Excelマクロでお小遣いを稼ぐ方法！VBAで月５万円以上の副収入を得よう