【Excel VBA】よく使うコードまとめ

VBA開発をする時、基本的で簡単なコードであれば覚えているのですが、たいていググるか昔のファイルを開いてコピペしちゃってます。

その度に参考サイトにアクセスしたりファイルを開くのがめんどくさいので、この記事にまとめておくことにしました。

スポンサーリンク

別記事に登場するコード

ファイルを開く

Excelブックとしてファイルを開く

Excelブックとして開いて問題のないデータであれば、この方法が最も確実でわかりやすいです。

このマクロが記録されたExcelファイルと同じフォルダに「sample.csv」があれば開き、無ければユーザーがファイルを選択して開きます。プロシージャの最後で開いたファイルは保存せずに閉じます。

GetOpenFilenameメソッドの引数で開くファイル形式を限定することができます。以下の3つだけ使えればいいでしょう。

ちなみにこの引数はMacではエラーとなり指定できません。

ファイル形式引数
Excel(xls、xlsx、xlsm)Excel ブック,*.xls*
CSVファイルCSV(コンマ区切り),*.csv
テキストファイル(TSV)テキスト(タブ区切り),*.txt

CSVファイルをLine Inputで読み込む

Excelでは15桁を超える数値は扱えません。16桁以降はすべて0に変換されてしまいます。

16桁以降も正常に表示するには文字列として扱う必要があるのですが、そういったデータが含まれるCSVファイルをExcelで開いてしまうと変換後のデータしか取り出せません。

そこでLine Inputでテキストデータ読み込み、文字列としてExcelに出力するという方法があります。

なお、数値を文字列として処理するには文字列型で宣言した変数に格納したり、出力先セルの書式設定を文字列にしておくなどがありますが、勝手に数値に変えられてしまいがちです。

もっとも確実なのは頭に「’(シングルクォート)」を付けると強制的に文字列として扱われるので、「’ & strArray(i)」みたいな感じでセルに出力するといいと思います。

カンマ入りのCSVファイルをTSVファイルに変換する

CSVはComma Separated Valueの略で、要は「,(カンマ)」でデータを区切った形式です。

上記の方法ではSplit関数を使ってカンマでデータを分割していたのですが、区切りとしてはでなく文字列としてカンマが含まれていたら、値の途中でデータを区切ってしまうことになります。

エクセルVBAでデータにカンマが含まれてしまっているCSVを取り込む

そこで、こちらの記事を参考にし、区切りのカンマをタブ記号に変換してTSVにします。

文字コードがUTF-8のCSVファイルを開く

Excelで文字コードがUTF-8のファイルを開くと文字化けします。

文字化けよさようなら!エクセルVBAでUTF-8のCSVを読み込む方法

こちらの記事を参考にし、ADODB.Streamというオブジェクトに入れて文字化けを避けます。

ADODB.Streamを使うには事前準備が必要です。

  1. VBEを起動
  2. メニューの「ツール」から「参照設定」を開く
  3. Microsoft ActiveX Data Objects ○.○ Libraryにチェックを入れる

MacでUTF-8のCSVファイルを読み込む

上記のADODB.StreamはMac版ではおそらく対応していません。

VBA CSV ファイルの読み込み (Workbooks.OpenText 関数を使う)

代替方法はこちらの記事を参考にしました。

OpenTextメソッドの第2引数で開く文字コードを指定します。

文字コード
Shift-JIS932
UTF-865001

また、第3引数では区切り文字を指定できます。

区切り文字
カンマComma:=True
タブTab:=True
セミコロンSemicolon:=True
スペースSpace:=True
その他Other:=True, OtherChar:=”a”

ディレクトリ内のファイルを順番に開く

指定のディレクトリ内のファイルを順番に開く方法です。

VBA:フォルダ内のファイルを順次処理

こちらの記事を参考にしています。

マクロを記録したExcelファイルと同じディレクトリ内にある「sample」ディレクトリ内にあるファイルを順番に開きます。

ファイルを新規作成・保存する

新規ブックを作成して保存する

新規ブックを開いて保存します。また、既存のファイルを改めて名前を付けて保存することも可能です。

SaveAsメソッドのFileFormatでファイル形式を指定することができます。

ファイル形式
ExcelxlNormal
CSVxlCSV
テキストxlText

テキストファイルを作成して出力

テキストファイルとしてファイルを作成するのであれば、かなり処理が速いのでこのやり方がベストです。

Printで1行ずつ追記していきます。

ワークシートの操作

シートを探す

指定の名前がついたシートが見つかればそのシートをアクティブにします。

シートの複製

マクロ入りシートを複製しボタンに再登録

シートにマクロが記録されている場合、シートを複製すると登録名が前のシートのままになってしまいますので、複製したシート名で登録し直します。

シートを削除

シートを削除する前に警告メッセージが出てくるのを無効化する処理もしておきます。

シートの保護

マクロ実行時のみシートの保護を解除

テーブル

ソート

テーブルの先頭はヘッダー行ではなくデータが始まる行を指定します。

昇順xlAscending
降順xlDescending

セル

リストを作成する

条件付き書式

FormatCondition.Addメソッドの引数で条件が変わります。

入力規則

変数における処理

現在の日付を文字列として取得

日付の計算

setDateに3日足す例。

DateAddの引き数を変えることで日数や年・月・日・時・分・秒までいける。

特定の文字以下の文字列を取り出す

頭に”$”があるデータから、$より右側を取得。

アルファベットの大文字・小文字を区別しない

大文字・小文字を区別しないというとニュアンスが違いますが、UCase関数はアルファベットをすべて大文字に変換します。

全角を半角にする

StrConv関数はさまざまな変換ができまして、第2引数をvbNarrowにすることで全角を半角にすることができます。

コメント

タイトルとURLをコピーしました