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

職場で毎週定例業務があり他の作業と並行して処理をすると半日あまり時間を消費していたため、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"