エクセルマクロの基本操作特集【仕事でよく使われる操作23選】
この記事ではマクロで罫線を引く、色を変える、関数Ifを使う、ような利用頻度の高いエクセルマクロをまとめて紹介しています。
仕事でエクセルマクロの開発をお願いされたときに、基本的なマクロをド忘れして思い出すのに時間がかかることが多々あります。
｢あれ、罫線を引くマクロってどうやって作るんだっけ？前に作った、表を自動作成するエクセルファイルを参考に思い出そう｣
以前作成したエクセルファイルを引っ張りだして、罫線を引く部分のマクロだけをコピーして利用する！という方法でマクロの開発を進めます。
このような効率の悪い方法ではマクロの開発にかなりの時間を費やしてしまいます。
そこで思いついたのが、利用頻度の高いマクロをまとめた記事を作成してすぐに調べられるようにする！ということです。
この記事をお気に入り登録しておけば、忘れてしまったマクロをすぐに思い出すこともできるし、コピーしてすぐに使うこともできるようになります。
エクセルマクロの作成が多い方やよく基本的なマクロの書き方をド忘れしてしまう方におすすめの記事になっています。コピーしてすぐに使えるサンプルマクロを多数紹介しているので、この記事をすぐに閲覧できるようお気に入り登録することをおすすめします。
この記事は、ド忘れしたマクロを思い出すきっかけを！コンセプトに作成したので、一つ一つのマクロの詳しい説明を省略しているのでご了承してください。
目次
エクセルマクロの基本的な関数
関数Count
サンプル内容①(1列目に入力された最後の行数を取得)
あらかじめA10セルに｢テスト｣というテキストを入力。最後の行数(10)を変数gyoに入れる。
サンプルマクロ
| 1 | Sub 行数取得() |
| 2 | gyo = Cells(Rows.Count, 1).End(xlUp).Row |
| 3 | End Sub |
サンプル内容②(3行目に入力された最後の列数を取得)
あらかじめE3セルに｢テスト｣というテキストを入力。最後の列数(5)を変数retuに入れる。
サンプルマクロ
| 1 | Sub 列数取得() |
| 2 | retu = Cells(3, Columns.Count).End(xlToLeft).Column |
| 3 | End Sub |
関数If【もし〇〇なら××】
サンプル内容
もしA1セルに｢〇｣が入力されていたら、B1セルに｢×｣、それ以外だったらC1セルに｢△｣を入力する
サンプルマクロ
| 1 | Sub IF関数() |
| 2 | If Cells(1.1) = “〇" Then |
| 3 | Cells(1, 2) = “×" |
| 4 | Else |
| 5 | Cells(1, 3) = “△" |
| 6 | End If |
| 7 | End Sub |
関数Miｄ
サンプル内容
｢エクセルマクロの基本プログラム特集【利用頻度の高いマクロ15選】」というテキストの【】内を関数Midを使って抜き取る
サンプルマクロ
| 1 | Sub Mid関数() |
| 2 | Cells(1, 1) = Mid(“エクセルマクロの基本プログラム特集【利用頻度の高いマクロ15選】", 19, 13) |
| 3 | End Sub |
関数Midの構文と概要
構文
Mid( String , Start , Length )
概要
テキスト(String)の内、指定する場所(Start)以降のテキストを表示する。なお表示する長さ(Length)で指定することができる
関数LEFT
サンプル内容
｢エクセルマクロの基本プログラム特集【利用頻度の高いマクロ15選】」というテキストの「エクセルマクロの基本」を関数Leftを使って抜き取る
サンプルマクロ
| 1 | Sub Left関数() |
| 2 | Cells(1, 1) = Left(“エクセルマクロの基本プログラム特集【利用頻度の高いマクロ15選】", 10) |
| 3 | End Sub |
関数LEFTの構文と概要
構文
Left( String , Length )
概要
テキスト(String)の内、テキストの一番左から指定する長さ(Length)分のテキストを表示させる
関数Right
サンプル内容
｢エクセルマクロの基本プログラム特集【利用頻度の高いマクロ15選】」というテキストの「【利用頻度の高いマクロ15選】」を関数Rightを使って抜き取る
サンプルマクロ
| 1 | Sub Right関数() |
| 2 | Cells(1, 1) = Right(“エクセルマクロの基本プログラム特集【利用頻度の高いマクロ15選】", 15) |
| 3 | End Sub |
関数Rightの構文と概要
構文
Right( String , Length )
概要
テキスト(String)の内、テキストの一番右から指定する長さ(Length)分のテキストを表示させる
関数InStr
サンプル内容
A1セルに入力された｢エクセルマクロの基本プログラム特集【利用頻度の高いマクロ15選】」というテキストの【】内を関数InStrを使って抜き取り、A2セルに表示する
サンプルマクロ
| 1 | Sub InStr() |
| 2 | S1 = InStr(Cells(1, 1), “【") |
| 3 | S2 = InStr(Cells(1, 1), “】") |
| 4 | Cells(1, 2) = Mid(Cells(1, 1), S1 + 1, S2 – (S1 + 1)) |
| 5 | End Sub |
関数InStrの構文と概要
構文
InStr( String1 , String2 )
概要
テキスト(String1)の内、一番左からテキスト(String2)の場所を取得する
半角数字を抽出する関数【Functionプロシージャで作成】
サンプル内容
A1セルに入力された｢あいうえお12345かきくけこ１２３４５｣というテキストで、半角文字を抽出し、B1セルに表示する
サンプルマクロ①
| 1 | Sub 半角数字を抽出() |
| 2 | gyo = Cells(Rows.Count, 1).End(xlUp).Row |
| 3 | Dim moji As String |
| 4 | For i = 2 To r |
| 5 | moji = Cells(i, 1) |
| 6 | Cells(i, 2) = test(Range(Cells(i, 1), Cells(i, 1))) |
| 7 | Next |
| 8 | End Sub |
サンプルマクロ②
| 1 | Function test(r As Range) |
| 2 | Dim T1 As String |
| 3 | Dim T2 As String |
| 4 | For i = 1 To Len(r.Value) |
| 5 | T1 = Mid(r.Value, i, 1) |
| 6 | If T1 Like “[0-9]" Then |
| 7 | T2 = T2 & T1 |
| 8 | End If |
| 9 | Next i |
| 10 | If IsNumeric(T2) Then |
| 11 | test = T2 |
| 12 | Else |
| 13 | test = “半角数字ではない" |
| 14 | End If |
| 15 | End Function |
全角数字を抽出する関数【Functionプロシージャで作成】
サンプル内容
A1セルに入力された｢あいうえお12345かきくけこ１２３４５｣というテキストで、全角数字を抽出し、B1セルに表示する
サンプルマクロ①
| 1 | Sub 全角数字を抽出() |
| 2 | gyo = Cells(Rows.Count, 1).End(xlUp).Row |
| 3 | Dim moji As String |
| 4 | For i = 2 To r |
| 5 | moji = Cells(i, 1) |
| 6 | Cells(i, 2) = test(Range(Cells(i, 1), Cells(i, 1))) |
| 7 | Next |
| 8 | End Sub |
サンプルマクロ②
| 1 | Function test(r As Range) |
| 2 | Dim T1 As String |
| 3 | Dim T2 As String |
| 4 | For i = 1 To Len(r.Value) |
| 5 | T1 = Mid(r.Value, i, 1) |
| 6 | If T1 Like “[０-９]" Then |
| 7 | T2 = T2 & T1 |
| 8 | End If |
| 9 | Next i |
| 10 | If IsNumeric(T2) Then |
| 11 | test = T2 |
| 12 | Else |
| 13 | test = “全角数字ではない" |
| 14 | End If |
| 15 | End Function |
色を操作する基本マクロ
テキストの色を操作
サンプル内容
A1セルに入力されたテキストの文字色を赤にする
サンプルマクロ
| 1 | Sub テキストカラー() |
| 2 | Cells(1, 1).Font.Color = RGB(255, 0, 0) |
| 3 | End Sub |
背景色を操作
サンプル内容
A1セルを赤に塗りつぶす
サンプルマクロ
| 1 | Sub 背景色カラー() |
| 2 | Cells(1, 1).Interior.Color = RGB(255, 0, 0) |
| 3 | End Sub |
罫線の色を操作
サンプル内容
A1からD10の罫線を引き、引いた罫線の色を赤にする
サンプルマクロ
| 1 | Sub 罫線カラー () |
| 2 | Range(Cells(1, 1), Cells(10, 4)).Borders(xlEdgeTop).LineStyle = xlContinuous |
| 3 | Range(Cells(1, 1), Cells(10, 4)).Borders(xlEdgeTop).Color = RGB(255, 0, 0) |
| 4 | Range(Cells(1, 1), Cells(10, 4)).Borders(xlEdgeLeft).LineStyle = xlContinuous |
| 5 | Range(Cells(1, 1), Cells(10, 4)).Borders(xlEdgeLeft).Color = RGB(255, 0, 0) |
| 6 | Range(Cells(1, 1), Cells(10, 4)).Borders(xlEdgeRight).LineStyle = xlContinuous |
| 7 | Range(Cells(1, 1), Cells(10, 4)).Borders(xlEdgeRight).Color = RGB(255, 0, 0) |
| 8 | Range(Cells(1, 1), Cells(10, 4)).Borders(xlEdgeBottom).LineStyle = xlContinuous |
| 9 | Range(Cells(1, 1), Cells(10, 4)).Borders(xlEdgeBottom).Color = RGB(255, 0, 0) |
| 10 | End Sub |
罫線を操作する基本マクロ
セルを指定して罫線を引く
サンプル内容
A1セルからD10セルの範囲で、外枠は太い罫線、内側は細い罫線を引く
サンプルマクロ
| 1 | Sub 罫線() |
| 2 | Range(Cells(1, 1), Cells(10, 4)).Borders(xlEdgeTop).LineStyle = xlContinuous |
| 3 | Range(Cells(1, 1), Cells(10, 4)).Borders(xlEdgeTop).Weight = xlMedium |
| 4 | Range(Cells(1, 1), Cells(10, 4)).Borders(xlEdgeLeft).LineStyle = xlContinuous |
| 5 | Range(Cells(1, 1), Cells(10, 4)).Borders(xlEdgeLeft).Weight = xlMedium |
| 6 | Range(Cells(1, 1), Cells(10, 4)).Borders(xlEdgeRight).LineStyle = xlContinuous |
| 7 | Range(Cells(1, 1), Cells(10, 4)).Borders(xlEdgeRight).Weight = xlMedium |
| 8 | Range(Cells(1, 1), Cells(10, 4)).Borders(xlEdgeBottom).LineStyle = xlContinuous |
| 9 | Range(Cells(1, 1), Cells(10, 4)).Borders(xlEdgeBottom).Weight = xlMedium |
| 10 | Range(Cells(1, 1), Cells(10, 4)).Borders(xlInsideHorizontal).LineStyle = xlContinuous |
| 11 | Range(Cells(1, 1), Cells(10, 4)).Borders(xlInsideHorizontal).Weight = xlThin |
| 12 | Range(Cells(1, 1), Cells(10, 4)).Borders(xlInsideVertical).LineStyle = xlContinuous |
| 13 | Range(Cells(1, 1), Cells(10, 4)).Borders(xlInsideVertical).Weight = xlThin |
| 14 | End Sub |
表の増減を判別し罫線を引く
サンプル内容
1行目、A列目から始まる一覧表で、行数や列数が増えたり、減ったりしても自動的に対応する罫線を引く
サンプルマクロ
| 1 | Sub 罫線() |
| 2 | gyo = Cells(Rows.Count, 1).End(xlUp).Row |
| 3 | retu = Cells(1, Columns.Count).End(xlToLeft).Column |
| 4 | Range(Cells(1, 1), Ash.Cells(gyo, retu)).Borders(xlEdgeTop).LineStyle = xlNone |
| 5 | Range(Cells(1, 1), Ash.Cells(gyo, retu)).Borders(xlEdgeLeft).LineStyle = xlNone |
| 6 | Range(Cells(1, 1), Ash.Cells(gyo, retu)).Borders(xlEdgeRight).LineStyle = xlNone |
| 7 | Range(Cells(1, 1), Ash.Cells(gyo, retu)).Borders(xlEdgeBottom).LineStyle = xlNone |
| 8 | Range(Cells(1, 1), Ash.Cells(gyo, retu)).Borders(xlInsideHorizontal).LineStyle = xlNone |
| 9 | Range(Cells(1, 1), Ash.Cells(gyo, retu)).Borders(xlInsideVertical).LineStyle = xlNone |
| 10 | Range(Cells(1, 1), Ash.Cells(gyo, retu)).Borders(xlEdgeTop).LineStyle = xlContinuous |
| 11 | Range(Cells(1, 1), Ash.Cells(gyo, retu)).Borders(xlEdgeTop).Weight = xlMedium |
| 12 | Range(Cells(1, 1), Ash.Cells(gyo, retu)).Borders(xlEdgeLeft).LineStyle = xlContinuous |
| 13 | Range(Cells(1, 1), Ash.Cells(gyo, retu)).Borders(xlEdgeLeft).Weight = xlMedium |
| 14 | Range(Cells(1, 1), Ash.Cells(gyo, retu)).Borders(xlEdgeRight).LineStyle = xlContinuous |
| 15 | Range(Cells(1, 1), Ash.Cells(gyo, retu)).Borders(xlEdgeRight).Weight = xlMedium |
| 16 | Range(Cells(1, 1), Ash.Cells(gyo, retu)).Borders(xlEdgeBottom).LineStyle = xlContinuous |
| 17 | Range(Cells(1, 1), Ash.Cells(gyo, retu)).Borders(xlEdgeBottom).Weight = xlMedium |
| 18 | Range(Cells(1, 1), Ash.Cells(gyo, retu)).Borders(xlInsideHorizontal).LineStyle = xlContinuous |
| 19 | Range(Cells(1, 1), Ash.Cells(gyo, retu)).Borders(xlInsideHorizontal).Weight = xlThin |
| 20 | Range(Cells(1, 1), Ash.Cells(gyo, retu)).Borders(xlInsideVertical).LineStyle = xlContinuous |
| 21 | Range(Cells(1, 1), Ash.Cells(gyo, retu)).Borders(xlInsideVertical).Weight = xlThin |
| 22 | End Sub |
Targetを使ったVBAの実行
セルをクリックするだけでテキストを変化させる
セルをクリックすると、クリックしたセルのテキストを｢〇｣→｢×｣→｢△｣→｢ ｣に変化させる
サンプルマクロ
| 1 | Private Sub Worksheet_SelectionChange(ByVal Target As Range) |
| 2 | On Error Resume Next |
| 3 | If Target.Value = “" Then |
| 4 | Target.Value = “〇" |
| 5 | ElseIf Target.Value = “〇" Then |
| 6 | Target.Value = “×" |
| 7 | ElseIf Target.Value = “×" Then |
| 8 | Target.Value = “△" |
| 9 | ElseIf Target.Value = “△" Then |
| 10 | Target.Cells.ClearContents |
| 11 | Else |
| 12 | End If |
| 13 | If Err <> 0 Then |
| 14 | Err.Clear |
| 15 | End If |
| 16 | End Sub |
指定したセルをクリックするだけで背景色を変化させる
サンプル内容
C3セルをクリックすると、背景色を緑に変化させる。ただし、C3セル以外のセルをクリックしても何も起こらない。
サンプルマクロ
| 1 | Private Sub Worksheet_SelectionChange(ByVal Target As Range) |
| 2 | On Error Resume Next |
| 3 | If (Target.Row = 3) And (Target.Column = 3) Then |
| 4 | Target.Cells.Interior.Color = RGB(255, 255, 0) |
| 5 | Else |
| 6 | End If |
| 7 | If Err <> 0 Then |
| 8 | Err.Clear |
| 9 | End If |
| 10 | End Sub |
プルダウンリストを作るエクセルマクロ
サンプル内容
マクロを使ってプルダウンリストを表示させる。リスト内容は｢〇｣｢×｣という文字を表示
サンプルマクロ
| 1 | Private Sub Worksheet_SelectionChange(ByVal Target As Range) |
| 2 | ActiveSheet.Cells.Validation.Delete |
| 3 | With Target.Validation |
| 4 | .Add Type:=xlValidateList, Formula1:="〇,×" |
| 5 | .ShowError = False |
| 6 | End With |
| 7 | If Err <> 0 Then |
| 8 | Err.Clear |
| 9 | End If |
| 10 | End Sub |
並び替えのエクセルマクロ
サンプル内容
A1セルからT20セルの範囲、B列を昇順、1行目は表題として判別、の条件で並び替えを行う
サンプルマクロ
| 1 | Sub 並び替え() |
| 2 | Range(Cells(1, 1), Cells(20, 20)).Sort Key1:=Cells(1, 2), Order1:=xlAscending, Header:=xlYes |
| 3 | End Sub |
エクセルマクロでシートを区別する基本設定
シート名から区別する方法
サンプル内容
シート名からシートの区別を行い、テキストを転記する
サンプルマクロ
| 1 | Sub シート名で区別() |
| 2 | Dim Ash As Worksheet |
| 3 | Dim Bsh As Worksheet |
| 4 | Set Ash = ThisWorkbook.Worksheets(“シート名①") |
| 5 | Set Bsh = ThisWorkbook.Worksheets(“シート名②") |
| 6 | Ash.Cells(1,1)=Bsh.Cells(1,1) |
| 7 | EndSub |
シート順番から区別する方法
サンプル内容
シートの順番からシートの区別を行い、テキストを転記する
サンプルマクロ
| 1 | Sub シート順番で区別() |
| 2 | Dim Ash As Worksheet |
| 3 | Dim Bsh As Worksheet |
| 4 | Set Ash = ThisWorkbook.Worksheets(1) |
| 5 | Set Bsh = ThisWorkbook.Worksheets(2) |
| 6 | Ash.Cells(1,1)=Bsh.Cells(1,1) |
| 7 | EndSub |
図形を編集するエクセルマクロ
図形を挿入するエクセルマクロ
サンプル内容
赤色の円(図形)と黒色の△を挿入する
サンプルマクロ
| 1 | Sub 図形の挿入() |
| 2 | With ActiveSheet.Shapes.AddShape(msoShapeOval, 100, 100, 17, 17) |
| 3 | .Fill.Visible = msoFalse |
| 4 | .Line.Weight = 1 |
| 5 | .Line.ForeColor.RGB = vbRed |
| 6 | End With |
| 7 | With ActiveSheet.Shapes.AddShape(msoShapeIsoscelesTriangle, 200, 100, 17, 17) |
| 8 | .Fill.Visible = msoFalse |
| 9 | .Line.Weight = 1 |
| 10 | .Line.ForeColor.RGB = vbBlock |
| 11 | End With |
| 12 | End Sub |
図形を削除するエクセルマクロ
サンプル内容
シート内にある全ての図形(オブジェクト)を削除する
サンプルマクロ
| 1 | Sub 図形削除() |
| 2 | Dim zu As Shape |
| 3 | For Each zu In ActiveSheet.Shapes |
| 4 | zu.Delete |
| 5 | Next zu |
| 6 | End Sub |
VBA初心者が効率化マクロを導入する方法
VBAなんて扱ったことないけどエクセル作業を自動化するマクロを導入したい！
このような問題を抱えながら日々のエクセル業務をこなしている方がおおくいらっしゃるのではないでしょうか。
こんな問題を解決する方法がこちら
- マクロの開発を外注に依頼する
- マクロが設定されたエクセルファイルをダウンロードする
- 参考書やネットでVBAスキルを身に付ける
VBAの勉強を始める前は上記の方法で効率化マクロを導入していました。
完成されたマクロを使ってエクセル業務を処理する
↓
作業効率が２倍以上アップ！！
↓
便利すぎて自分でもマクロを設定したいと考え始める
↓
参考書やネットの情報でVBAスキルを身に付ける
↓
マクロ開発を代行できる程のスキルを習得
VBAは
他のプログラミングに比べて参考書やネットで紹介されている情報量が多いので初心者でも導入しやすい
という特徴があります。
毎日のルーティーン作業にうんざりしている、だれでもできる単純作業に時間を費やしている、という方はあなたに合った方法でVBAを導入しちゃいましょう。
マクロの開発を外注に依頼する
マクロの開発を外注に依頼する導入方法は
VBAを全く扱えない方でもエクセル作業を自動化できる最も簡単な方法
です。
「こんなエクセル作業を自動化したい」「作った資料を自動的にPDFで保存したい」「決まった様式に写真を自動で貼り付けたい」
こんな感じで実現したい内容を依頼するだけで業務内容に合ったマクロを開発してくれます。
ガッツポーズの人
デメリットは、費用がかかることだけ。
開発内容、依頼先によってかかる費用は大きく異なります。
私が会社員の頃利用していた業者さんは１マクロあたり2万円～3万円の費用がかかりました。
疑問がある人
ちなみに、マメBlogでもエクセルマクロ開発代行サービスを承っています。
開発内容の確認、VBAコードの設定、動作確認後の調整、など全ての工程を私（マメ父ちゃん）が行っているので、
費用が安い！スピード納期！！
でやらせてもらっています。
ほとんど独学で身に付けたVBAスキルなので、内容によっては開発できないこともあるのでご了承ください。
ご相談、見積もり依頼は完全無料ですので気になる方は下記のリンクよりお問合せ下さい。
マクロが設定されたエクセルファイルをダウンロードする
VBA初心者でも『マクロが設定されたエクセルファイルをダウンロード』ですぐにエクセル作業を自動化することができます。
たとえば、
A４用紙に写真を貼り付けてコメントを入力する作業を自動化したい！という場合、
『写真を貼り付けるマクロ』が設定されたエクセルファイルをダウンロードすれば問題解決です。
実際に、『写真を貼り付けるマクロ』をダウンロードして使っている様子がこちら
ガッツポーズの人
『マクロが設定されたエクセルファイルをダウンロード』する導入方法は、
VBA初心者でもマクロを導入できる、費用が安い、
というメリットがあります。
しかし
VBAに関する情報が多く公開されているとはいえ、ネット上で想定するマクロは見つけることが難しい
というデメリットもあります。
ガッツポーズの人
個人ブログやファイルを販売できるサイト「note」や「Tips」ではいろんなマクロの情報が掲載されているので是非参考にしてみてください。
今までマメBlogで受注した効率化マクロを下記のnoteで掲載していますので併せてチェックしてみてください。
参考書やネットでVBAスキルを身に付ける
先程も紹介しましたが、
VBAは他のプログラミングに比べて参考書やネットで紹介されている情報量が多いので初心者でも導入しやすい
プログラミングです。
言い換えると、
情報量が豊富なので自分で勉強できちゃう
ということです。
私のVBAスキルはほとんど独学で身に付けたもので、
考えた処理のほとんどをVBAで表現できるレベルにまで到達した！
と思っています。
私の作ったマクロをプロのプログラマーが見れば、
お前の書くコードはインチキだ！「変数の宣言」とか「引数」を正しく使えよ！！
と感じるはずです。
でも、
私からすればどうでもいい！！！想定した内容を正しく処理できればよくないですか！？
独学であっても『写真を貼り付けるマクロ』『全てのシートをPDF出力するマクロ』『連番を振るマクロ』など、
業務内容に合ったマクロを開発できるようになれます。
独学でVBAスキルを身に付けるコツは、
エラーが発生してもいいからひたすらコードを書く、なんでもいいからエクセル作業をマクロ化してみる
です。
とは言っても、全くの知識0の方は何をすればいいかわからないですよね。
VBA知識0の初心者の方は、参考書『たった1秒で仕事が片づくExcel自動化の教科書【増強完全版】』を読んでみてください。
VBAの知識0の方でもすいすい読み進められる内容になっています。
やさしい内容にも関わらず「え！！VBAを使えばこんなことできるの！？」と感動すると思います。
実際に私はこの参考書を読んでからVBAの勉強を始めました。
ひらめく人
VBAの魅力を発見した後は、
ひたすら自動化したい処理を実現するコードを書くだけ
です。
【VBA 写真を貼る】、【VBA PDF出力】、のようにネット検索すれば知りたいコードをすぐにゲットできます。
実現したい処理に向かって、「調べる→書いてみる→エラーを改善する→調べる」を繰り返していると
自然といろんなマクロが作れるようになっているはずです。
VBAに興味が湧いてきたという方は今からVBA学習をスタートさせちゃいましょう。