仕事効率化

【ExcelVBA(マクロ)】(エクセル)よく使う書き方をまとめてみた【随時更新】

最近よく『ExcelVBA(エクセルブイビーエー)』を使う機会があったので、

個人的によく使っている『ExcelVBA』の書き方をまとめることにしました。

アオキ
プログラミングって書き方がいろいろあるので、
アオキ
これがベストとは限らないのでご注意くださいませ。
Sponsored link

ExcelVBA セル関係

最後の行の位置(縦の位置)を取得したい

本田
長友
香川

とあったら香川の位置(3番目) を取得。

A列の100000行目から上に向かって最初にひっかかったセル位置を取得します。
→もし100000行目に値があると last_row は 100000 になります。

(2003年頃までのエクセルは最下行が65536 だったので注意)

最後の行位置を取得(別シート)

worksheets(シート名)をつけてあげると、別のシートの値が取得できます。

セル位置に変数を混ぜる

情報が更新されたりするとセルの範囲が変わってくるので、
始めから変数で指定しておくと楽です。

セル範囲に変数を含める場合は、

range(cells(縦,横),cells(縦,横)) という書き方を使います。

1行目の値(数式)を2行目から最下行までコピペ

1つセルだけならRange(“A1”)じゃなくてCells(“A1”) でもいけると思います。

Sponsored link

あるセル範囲を別のセル範囲にコピペする

これも range(cells(縦,横),cells(縦,横)) の書き方です。

コピー元がRangeの場合、コピー先のRange範囲も同じ行数(または列数)でないとエラーでたと思います。

セルを別シートにコピー

Destination:= で宛先シートを指定してますが、

シート指定は worksheets(シート名) でもいける気がします。

変数の中にセル範囲を設定する

Cellの後にAddressをくっつける事で、セル位置を設定できます。

Addressだけだと絶対参照になる($A$1 など)ので、

相対参照にしたいときはAddressの後に(rowabsolute:=False)などをつけます。

Sponsored link

中級編 セルに変数が入った関数をつくる

セルの中に関数を入れて、しかも関数の中に変数が入って、しかも別のシートを参照して・・・

さらにcountif関数やsumif関数を使ったりすると どんどん行数が増えてしまうので、

事前に変数を作っておくと多少なりとも短く、後から編集しやすくなるかとは思います。(これでも長いけど)

sum関数の場合

セル内に関数を入れる場合は Formulaをつけます。

文字列は “” で囲う必要があるけれど、
変数や関数は “” で囲んじゃダメなので、

“” で囲む箇所と、””で囲まない場所を使い分ける必要があります。

また、 “” で囲った前後は & をつけることで結びつける事ができます。

この場合は、

といった具合です。

引数が増えるcounif関数(引数2つ)、sumif関数(引数3つ)ではさらにややこしくなるのでテストしながら書くと吉です。

countif関数・sumif関数

アオキ
長い・・多分もっといい書き方があるはず・・きっと。。
Sponsored link

ExcelVBA シート関係

シートを追加する

前使っていたシートを削除して新しく作り直したりします。

特定のシートがあるかチェックしてあったら削除

シート名がかぶってたらシートを新規作成できないので、先に削除しておくことが多いです。

選択中のシート以外を全部削除

全部消すときはこっちのほうが早いです。

シートを別のブックに追加

開いたシートを別のブックに保存したり。

(開いて、別のブックにコピーして、元のシートを閉じてから作業なんて時によく使います。)

Sponsored link

ExcelVBA 検索・置換

文字の置換

今回の場合は、

test_sheetのセルの中に、置き換え前の文字列と、置き換えた後の文字列を書いておいて、

A列を置き換えるような想定をしています。

置き換えにはいろんな条件指定があるようです。

特定セル範囲の、特定の値を検索して、あるかどうか

“本田” という値があったら処理するなど。

Sponsored link

特定セル範囲の、特定の値を検索して、その行を取得

セル範囲(1,1)から(最下行,1)の間の、”本田”と書かれているセルの行番号を取得します。

Range型に .Row とつけると 行番号が取得できます。

中級編 特定セル範囲に含まれている要素を取得(重複していたら1つだけ取得)

本田
香川
本田

長友
長友

となっていたら、

本田
香川

長友

としたい場合。

Dicというオブジェクトを作って、
1行目ずつチェックして、
重複していなかったら追加させた状態で、

Dicに追加された値を
1行ずつ吐き出す、というような流れになります。

この後に countifだったりsumifをかけたりする事が多いです。

その他

ポップアップ表示(OKボタンのみ)

読み込んだファイルが想定外のものだったりした時に使います。

ファイル選択

エクセルなのでCSVファイルかExcelファイルを読み込ませることがほとんどかなと思います。

ウィンドウ枠の固定

現在表示されている画面で固定されるようで、
一旦セル位置を左上(cells(1,1))が見える状態) に移動してから固定させた方がいいようです。

印刷関連

改ページプレビュー後、一番右のセルまで印刷範囲を拡大しています。
また、ウィンドウ枠固定を使っている場合に、2ページ目にもウィンドウ枠を印刷させる設定です。
(今回は1行目~3行目を固定としています。)

Sponsored link

さいごに

業種にもよるかもですが、

エクセルってよく使う機能は限られているので、

  • シート追加・削除
  • セル・レンジ(セル範囲)のコピペ
  • 関数(sum, average, if, sumif, countif)
  • 検索・置換

などを自動化できるようになると

だいぶ効率よくなるんじゃないかなと思います。

毎日エクセル触ってるって場合はぜひ、

『ExcelVBA』も使ってちょっとずつ効率あげて、

定時ダッシュできるようになるといいですね。

アオキ
ExcelVBA使うとかなり効率化できて便利ですよー

『エクセルVBA・分析・統計』関係ではこんな記事も読まれています。

1. 【ExcelVBA(マクロ)】(エクセル)よく使う書き方をまとめてみた【随時更新】

2. 【Excel(エクセル)】よく使う関数をまとめてみた(組み合わせもあり)

3. 【ピボットテーブル】の使い方をわかりやすくまとめてみた【初心者向け】

4. 【Excel(エクセル)】確率・統計でよく使う関数をまとめてみた【随時更新】

5. 【分散】【標準偏差】とは わかりやすくまとめてみた【ExcelとPython】

6. 【データドリブンマーケティング】の本を頼りにデータ分析力を高めていくべし【統計学】

7. 【正規分布】とは わかりやすくまとめてみた【ExcelとPython】

8. 【確率】当たりがでる確率を計算する方法【二項分布】【Excel/Python】

9. 【条件付き確率】とは わかりやすくまとめてみた

10. 【エクセルvba(マクロ)】でできる事を増やし使える人になったら女の子に「すごぃ!」って言われた時の話

アオキ
ツイッターでも記事ネタ含めちょろちょろ書いていくので、よろしければぜひフォローお願いしますm(_ _ )m

アオキのツイッターアカウント

  


関連記事一覧 (一部広告あり)

コメント

  1. この記事へのコメントはありません。

  1. この記事へのトラックバックはありません。

CAPTCHA


最近の記事

  1. 学び・教育

    『プログラミング教育』より大事な事を考えてみる~AI時代を見据えて~
  2. 音楽

    コードの転回形の練習方法をまとめてみた【大人のピアノ】
  3. IoT関連

    【Arduino】ラジコンをつくってみた 〜今ドキのプログラムの覚え方
  4. CG関連

    【WebGL】入門 わかりやすく【図解】してみた
  5. バックエンド

    【Python】手軽に試す方法2つとwebフレームワークについて【初心者向け】
PAGE TOP