職場で使えるVBAの基礎と実践 #Excel

職場で毎週定例業務があり他の作業と並行して処理をすると半日あまり時間を消費していたため、VBAで作業時間短縮+精度UPにつながりましたので備忘録も兼ねてノウハウをシェアさせていただきます。

はじめてVBAを学ばれる方
仕事でVBAを使ってみたいがイメージが湧かない方

にオススメの記事です。

オブジェクトとは

「えっ!!いきなり、用語説明かよっ」と思われた方、すみません。とっても大切なので、辛抱してください。

オブジェクト：セルやシートなどExcelで操作する対象のこと。階層構造になっており、上の階層のオブジェクトを[.]で区切ります。

オブジェクトを上から階層順に並べると以下のようになります。

・Applicationオブジェクト
・Workbook(ブック)オブジェクト
・Worksheet(ワークシート）オブジェクト
・Range（セル）オブジェクト

アプリ名・ファイル名・シート・セル番号を指定しているのだなとイメージをもっていただければ十分です。

階層構造の指定の例

Workbooks("Book1.xlsx").Worksheets("sheet1").Range("A1")

#階層の区切りを.で表す。
#文字列は""で囲う。

メソッドと記述方法

つづいて、メソッドです。メソッドはDo(何をする)と理解してください。
オブジェクトに「.」をつけて、続けてメソッドを記述する。

メソッドの記述例

Range("A2").Select #A2セルを選択する。
Range("A2").Clear #A2セルを消去する。
Range("A2").Copy #A2セルをコピーする。
ActieWorkbook.Close #現在のブックを閉じる。

プロパティ

オブジェクトの状態を表します。1つのオブジェクトには値や表示形式、セルの色情報など様々なプロパティがあります。以下のように記述します。

オブジェクト(操作対象).プロパティ（状態・特徴) = 値

プロパティの使用例


Range("A1").Value #A1セルの値を参照
Range("A1").Font.Size #A1セルのフォントサイズを参照
Range("A1"). = 10     #A1セルのフォントサイズを10に指定

変数の設定方法と使い方

マクロで変数を使う時は、あらかじめ記述して宣言します。

変数の宣言の仕方

Dim 変数名　As データ型

変数の定義と使用例


Dim intRow As Integer #変数intRowを整数型で宣言
Dim intCount As Integer #変数intCountを整数型で宣言
Dim intData As String #変数intDataを文字列型で宣言

定義した変数を使用する

intCount = intCount + 1 #intCountに1足した値をintCountに代入する。 
strData = Range("C1") #セルC1の値をstrDataに代入する。
intData = Rang("D4") #セルD4の値をintDataに代入する。

おつかれさまでした!!以上でVBAの基礎が終了です。
使える知識を説明しながら課題解決コードの詳細にはいっていきます。

オートフィルタ―

オートフィルター


Dim  変数　= ActiveSheet.Range("")

変数.Autofilter Filed:=3, Criterial:="選択するデータの名前" 
#フィルターで絞りこむ時に使うワードをいれる。

覚えておきたいセル選択コード

A1から右端の最終セルまでを選択、ExcelのCtrl＋Endと同じです。

A1から右端の最終セルまでを選択


Range("A1", ActiveCell.SpecialCells(xlLastCell)).Select

セルの値のみを削除する


    Range("A1:D5").Select
    Selection.SpecialCells(xlCellTypeConstants).ClearContents

代入について

オブジェクト変数はAs オブジェクト名のように、オブジェクト名を使って型を指定します。

例

As Worksheet
As Workbook
As Range

As Objectを使うとどんなオブジェクトも格納できます。オブジェクト型で宣言したオブジェクト変数に、オブジェクトを格納するときは、Setを使わなければならないというルールがあります。言葉で理解するのは難しいのでコードをみて感覚をつかんでください。

代入の例


Sub オブジェクトの代入
 Dim ws As WorkSheet
 Set ws = Sheets("Sheet1")
 MsgBox ws.Name
End Sub

メソッドやプロパティがあることをわかっていれば、Setを付けなければいけません。セル範囲を表すRangeオブジェクトはValueプロパティなどがあるので、Rangeオブジェクト変数の代入はSetが必要になります。

Withの使い方

withの使い方

Sheets("Sheet1").Activate
 With Sheets("Sheet2")
  Range("A1") = .Range("B2")
 End With
#.がつくことで .Range("B2")は("Sheet2").Range("B2")を省力している。

データの重複削除

重複を削除するコード

Sub Sample_RemoveDuplicates()

Range("A1").CurrentRegion.RemoveDuplicates _
            Colums:= Array(1,2), Header:=xlYes
En Sub

オートフィルターの活用事例

果物名で絞りこみ、重複を削除して並び替えるコード


  Sheets.Add.Visible = True
    Sheets("リンゴ(シート名)").Select
    ActiveSheet.Range("A1").CurrentRegion.AutoFilter Field:=2, Criteria1:="取引先"
    Columns("D:D").Select
    Selection.SpecialCells(xlCellTypeVisible).Select
    Selection.Copy ActiveSheet.Previous.Range("A1")
    Application.CutCopyMode = False
    ActiveSheet.Previous.Range("A1").CurrentRegion.RemoveDuplicates Columns:=1, Header:= _
        xlNo
    ActiveSheet.Previous.Select
    Range("A3").Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.SpecialCells(xlCellTypeVisible).Select
    Selection.Copy Sheets("りんご_取引先(シート名)").Range("B7")
    Application.DisplayAlerts = False
    ActiveSheet.Previous.Delete
    Application.DisplayAlerts = True

     ActiveWorkbook.Worksheets("りんご_取引先(シート名)").Sort.SortFields.Clear
    ActiveWorkbook.Worksheets("りんご_取引先(シート名)").Sort.SortFields.Add Key:=Range("D7:D85") _
        , SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:=xlSortNormal
    ActiveWorkbook.Worksheets("りんご_取引先(シート名)").Sort.SortFields.Add Key:=Range("B7:B85") _
        , SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
    With ActiveWorkbook.Worksheets("りんご_取引先(シート名)").Sort
        .SetRange Range("B7:D85")
        .Header = xlGuess
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
    Sheets("りんご_取引先(シート名").Select

オートフィルターの設定➡データ選択➡別シートへ貼り付け。


Sub 果物ごとのデータを挿入()

    Dim ws As Worksheet #シートを変数指定する。
    Set ws = Sheets("202105027_Sheet")  #シートの変数に特定のシートを代入。
    ws.Select　#シートを選択する。
    Rows("2:2").Select　#行を選択する。
    Selection.AutoFilter　#オートフィルターを設定。
    ActiveSheet.Range("A1").CurrentRegion.AutoFilter Field:=3, Criteria1:="りんご" #オートフィルターの条件、3行目をフィルターで"りんご"で絞りむ。

    Range("A1").CurrentRegion.Copy Sheets("りんご").Range("A1") #絞り込み結果を、シート名"りんご"にコピーする。

    Application.CutCopyMode = False #カットコピーモードをオフにする。

　#以下、繰り返しです。N種類の絞りこみ条件に対応できます。

    ws.Select
    ActiveSheet.Range("A1").CurrentRegion.AutoFilter Field:=3, Criteria1:="J59C"
    Range("A1").CurrentRegion.Copy Sheets("みかん").Range("A1")
    Application.CutCopyMode = False

    ws.Select
    ActiveSheet.Range("A1").CurrentRegion.AutoFilter Field:=3, Criteria1:="J59T"
    Range("A1").CurrentRegion.Copy Sheets("れもん").Range("A1")
    Application.CutCopyMode = False


End Sub

空白行の挿入


Sub 表の1行おきに3行を挿入()
 Dim i As Long
 For i = Cells(Rows.Count,2).End(xlUp).Row To 5 Step -1
  Rows(i & ":" & i + 1 & ":" & i + 2).Insert
  Next i
End Sub

学んだこと&参考にさせて頂いた資料一覧

プログラミング学習全体にいえることかもしれませんが、まず「つくりたいイメージ」が先にあり、技術を学びながら手を動かしてイメージを形に落とし込む。なんだかんだで、このスタイルが1番学びがおおく、楽しさを感じます。

VBAの活用で大切なことまとめ

先にやりたいことを決める
マクロを使わずに動かす方法を言語化する。（関数名など専門用語を使うと検索にしやすくなります。）
手を動かす（本だけ読んでいても、面白くないし知識も定着しません。）
一挙に書かない、1行づつでもいいので組み立てて動きを確認していく。
（動かない原因がどこにあるのかハッキリさせないと対策できず、時間だけが溶けてモチベーションが下がってしまいます。）

参考資料に関して
きほんは、Office TANAKAで対応できると感じました。

"https://www.excel-chunchun.com/entry/RemoveDuplicates"

"http://officetanaka.net/excel/vba/tips/tips180.htm"