2020/7/30 追記

『Udemy』向けExcelVBA講座を作成しましたので、
ぜひご活用いただければと思います。

関連講座

もんプロ～問題発見と解決のためのプログラミング〜

【ExcelVBA】初心者向け講座をリリースしました。【Udemy】

https://coinbaby8.com/udemy_excelvba.html

こちらの記事から約1か月。ようやく、『Udemy』向け第4弾動画を全て撮影し終わりました。こちらからご覧いただけます。最新クーポン情報はこちら定価5,400円ですが、クーポン経由で大幅割引になります。動画約100本で約9時間。当初思っていたよりボリュームある内容になりました。ExcelVBA初心者向け動画を撮影したきっかけオフィスワーカー、デスクワーカーの方ならほとんどの方が使っているであろう『Excel(エクセル)』。その『Excel(エクセル)』でできる事を自動化することができるのが、『ExcelVBA』という専用プログラミング言語...

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

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

アオキ

プログラミングって書き方がいろいろあるので、

アオキ

これがベストとは限らないのでご注意くださいませ。

ExcelVBA セル関係

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

本田
長友
香川

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

Dim last_row as integer

last_row = Range("A100000").End(xlUp).Row

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

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

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

Dim test_sheet as string
Dim test_sheet_last_row as integer

test_sheet = "シート名"

test_sheet_last_row = Worksheets(test_sheet).Range("A1000000").End(xlUp).Row

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

セル位置に変数を混ぜる

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

Dim test_row_a as integer
Dim test_row_b as integer
Dim test_col_a as integer
Dim test_col_b as integer

test_row_a = 1
test_row_b = 2
test_col_a = 1
test_col_b = 2

range(cells(test_row_a,test_col_a),cells(test_col_b, test_col_b))

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

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

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

Range("A1") = "=TEXT(RC[-1],""aaa"")"
Range("A1").Copy Range(Cells(2, 1), Cells(last_row, 1))

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

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

Range(Cells(3, 6), Cells(last_row, 6)).Copy Range(Cells(3, 23), Cells(last_row, 23))

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

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

セルを別シートにコピー

Dim before_sheet as string
Dim after_sheet as string

before_sheet = "シート名1"
after_sheet = "シート名2"

Sheets(before_sheet).Cells(1, 1).Copy Destination:=Sheets(after_sheet).Cells(1, 1)

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

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

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

Dim test_sheet_test_range as string
Dim test_sheet = "シート名"

test_sheet_test_range = test_sheet & "!" & Cells(3, 1).Address & ":" & Cells(last_row, 22).Address

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

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

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

Cells(first_row, 2).Address(rowabsolute:=False)

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

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

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

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

sum関数の場合

dim first_row as integer
dim last_row as integer

first_row = 1
last_row = Range("A100000").End(xlUp).Row


Cells(1, 1).Formula = "=sum(" & Cells(first_row, 3).Address & ":" & Cells(last_row, 3).Address & ")"

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

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

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

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

この場合は、

Cells(sum_row, 3).Formula = "=sum(" & Cells(first_row, 3).Address & ":" & Cells(last_row, 3).Address & ")"

'解説
Cells(sum_row, 3).Formula = "文字列" & セル位置は囲まない & ":は囲む" & セル位置は囲まない & ")最後のかっこは文字列なので囲む"

といった具合です。

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

countif関数・sumif関数

Dim target_sheet as string
Dim referrence_sheet as string

target_sheet = "対象シート名"
referrence_sheet = "参照シート名"

Dim referrence_sheet_range As String
referrence_sheet_range = referrence_sheet & "!" & Cells(3, 1).Address & ":" & Cells(last_row, 1).Address

Dim referrence_sum_range as string
referrence_sum_range = referrence_sheet & "!" & Cells(3, 9).Address & ":" & Cells(last_row, 9).Address

'CountIf関数
Worksheets(test_sheet).Cells(test_row, 3).Formula = "=CountIf(" & referrence_sheet_range _
& "," & target_sheet & "!" & Cells(first_row, 2).Address(rowabsolute:=False) & ")"

'SumIf関数
Worksheets(test_sheet).Cells(first_row, 4).Formula = "=sumIf(" & referrence_sheet_range _
& "," & target_sheet & "!" & Cells(first_row, 2).Address(rowabsolute:=False) & "," & referrence_sum_range & ")"

アオキ

長い・・多分もっといい書き方があるはず・・きっと。。

ExcelVBA シート関係

シートを追加する

Dim before_sheet As String

before_sheet = "シート名"

Worksheets.Add(after:=Worksheets(Worksheets.Count)).Name = before_sheet 'シート名の後ろにシートが追加される

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

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

Dim test_sheet as string
Dim ws As Worksheet
Dim flag as boolean

test_sheet = "シート名"


For Each ws In Worksheets
    If ws.Name = test_sheet Then flag = True
Next ws

If flag = True Then
    Worksheets(test_sheet).Delete
End If

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

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

Dim mySht As Worksheet

With Application
    
    .DisplayAlerts = False
    
    For Each mySht In Worksheets
        If mySht.Name <> ActiveSheet.Name Then mySht.Delete
    Next
    
    .DisplayAlerts = True
End With

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

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

Worksheets(test_sheet).Copy before:=Workbooks(myBook).Worksheets(3)

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

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

ExcelVBA 検索・置換

文字の置換

Dim test_sheet as string
Dim last_row as integer

test_sheet = "置換情報を書いているシート名"

last_row = Range("C100000").End(xlUp).Row  

For replace_sheet_row = 1 To last_row '1行目から最終行まで

    before_replace = Worksheets(test_sheet).Cells(replace_sheet_row, 1) '置換前(1列目)
    after_replace = Worksheets(test_sheet).Cells(replace_sheet_row, 2) '置換後(2列目)

    Columns("A:A").Select ' A列を置き換えると想定

    Selection.Replace what:=before_replace, Replacement:=after_replace, lookat:= _
    xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
    ReplaceFormat:=False

before_replace = Empty
after_replace = Empty

Next

今回の場合は、

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

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

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

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

Dim FindName as string
Dim last_row as integer
Dim target_range As Range
Dim target_name_row as integer

FindName = "本田"
last_row = Range("A100000").End(xlUp).Row
        
Set target_range = Range(Cells(1, 1), Cells(last_row, 1)).Find(what:=FindName, lookat:=xlWhole)

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

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

Dim FindName as string
Dim last_row as integer
Dim target_range As Range
Dim target_name_row as integer

FindName = "本田"
last_row = Range("A100000").End(xlUp).Row
        
Set target_range = Range(Cells(1, 1), Cells(last_row, 1)).Find(what:=FindName, lookat:=xlWhole)

target_name_row = target_range.Row

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

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

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

本田
香川
本田
乾
長友
長友

となっていたら、

本田
香川
乾
長友

としたい場合。

Dim Dic, dic_row As Long, dic_name As String, Keys  'dic_nameに要素が挿入される
Set Dic = CreateObject("Scripting.Dictionary") 'オブジェクト作成


For dic_row = 1 To last_row '1行目から最下行までチェック
    dic_name = Worksheets(test_sheet).Cells(dic_row, 1).Value 'test_sheetの1行目からの値を取得
    If Not Dic.exists(dic_name) Then 'もしDicの中に存在してなかったら
        Dic.Add dic_name, dic_name 'Dicに値を取得する
    End If
Next dic_row

Keys = Dic.Keys
For i = 0 To Dic.Count - 1
    Cells(first_row + i, 2) = Keys(i)
Next i

Set Dic = Nothing

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

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

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

その他

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

Dim msg As String
    msg = "メッセージ内容です"

MsgBox msg, vbOKOnly + vbInformation, "再チェックをお願いします。"

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

ファイル選択

 varFileName = Application.GetOpenFilename(FileFilter:="CSVファイル(*.csv),*.csv", _
                                        Title:="CSVファイルを選択してね")
    If varFileName = False Then
        Exit Sub
    End If

Workbooks.Open Filename:=varFileName

ActiveWorkbook.Close savechanges:=False

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

ウィンドウ枠の固定

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

ActiveWindow.LargeScroll Up:=10000
ActiveWindow.LargeScroll ToLeft:=10000

Cells(4, 1).Select
ActiveWindow.FreezePanes = True

印刷関連

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

ActiveWindow.View = xlPageBreakPreview

ActiveSheet.VPageBreaks(1).DragOff Direction:=xlToRight, RegionIndex:=1
ActiveWindow.SmallScroll Down:=30

'印刷タイトル設定
With ActiveSheet.PageSetup
        .PrintTitleRows = "$1:$3"
End With

さいごに

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

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

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

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

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

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

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

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

アオキ

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

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

アオキ

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