エクセルマクロの基本操作特集【仕事でよく使われる操作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スキルの取得を目指してみてください。