処理のバリエーション
▼条件に応じて実行する処理を分岐
・D10セルが空欄の場合、アクティブシートの見出しの色を赤にする。
D10セルにデータが入っている場合は、アクティブシートを非表示にする。
If Range("D10").Value = "" Then
ActiveSheet.Tab.Color = RGB(255, 0, 0)
Else
ActiveSheet.Visible = False
End If
▼複数の条件を指定して実行する処理を分岐する
・D8セルの値を比較対象にし、D8セルの値に応じてD11セルに評価を入力する。いずれの条件にも一致しない場合はシートの見出しの色を赤にする。
Select Case Range("D8").Value
Case Is >= 500
Range("D11").Value = "ランクA"
Case Is >= 400
Range("D11").Value = "ランクB"
Case Is >= 300
Range("D11").Value = "ランクC"
Case Is >= 200
Range("D11").Value = "ランクD"
Case Is >= 100
Range("D11").Value = "ランクE"
Case Else
ActiveSheet.Tab.ColorIndex = 3
End Select
▼4つおきに処理を実行する
・Integer型の変数(数)を宣言し、C列の変数行目からD列の変数行目までのセルの色を水色にする
Dim amount As Integer
For amount = 7 To 23 Step 4
Range(Cells(amount, 3), Cells(amount, 4)). _
Interior.ColorIndex = 37
Next
▼データ入力画面の表示
・文字を入力する画面を表示し、入力された内容をシート名にする
Dim シート名 As String
シート名 = InputBox("アクティブシートの名前を変更する" & vbCrLf & _
"シート名を入力してください", "シート名の指定")
If シート名 <> "" Then
ActiveSheet.Name = シート名
End If
ExcelVBA学習8日目
▼ブックのコピーを保存する
・アクティブブックのパス名を変数(保存先)に格納し、「20200820.xlsm」ブックをコピーし、保存先のパスに「練習ブック1をコピーしたブック.xlsm」という名前で保存する
Dim 保存先 As String
保存先 = ActiveWorkbook.Path
Workbooks("20200820.xlsm").SaveCopyAs _
Filename:=保存先 & "\練習ブック1をコピーしたブック.xlsm"
▼ファイルを開く
・ブックを開く画面を表示
With Application.FileDialog(msoFileDialogOpen)
.InitialFileName = "C:¥Users¥u-001¥Dobuments¥" →保存先を表示
.FilterIndex = 2 →ファイルの種類は、上から2つ目の項目(すべてのExcelファイル)を選択
If.Show = -1 Then .Execute →ダイアログボックスを表示、「開く」が押されたときは、ファイルを開く
End With
※FileDialogオブジェクトのShowメソッドを使い、ダイアログボックスを表示後、アクション(開くや保存など)をクリックされたときは「-1」、キャンセルがクリックされたときは「0」が返る。
▼様々なダイアログボックスを表示
・ファイルを開く画面を表示
Application.Dialogs(xlDialogOpen).Show
・名前をつけて保存画面を表示
Application.Dialogs(xlDialogSaveAs).Show
・セルの書式設定画面(フォントタブ)を表示する
Application.Dialogs(xlDialogFontProperties).Show
▼ファイルを参照するダイアログボックスを表示
①Variant型の変数を宣言
②マクロが記述されているこのブックと同じ場所を表示
③フィルターの一覧をクリア
④ファイターの一覧にすべてのファイルを追加
⑤フィルターの一覧に画像ファイルを追加
⑥上から一つ目の項目(すべてのファイル)をファイルの種類として選択しておく
⑦ダイアログボックスを表示し、OKが押されたときは、FileDialogSlelectedItemsコレクション内の内容の1つずつに対して、選択項目の内容をメッセージボックスに表示
Dim 選択項目 As Variant
With Application.FileDialog(msoFileDialogFilePicker)
'DialogBoxに関する処理をまとめて記述
.InitialFileName = ThisWorkbook.Path & "\"
.Filters.Clear
.Filters.Add "全てのファイル", "*.*"
.Filters.Add "画像ファイル", "*.gif;*.jpg;*.jpeg"
.FilterIndex = 1
If .Show = -1 Then
For Each 選択項目 In .SelectedItems
MsgBox 選択項目 & "が選択されました"
Next 選択項目
End If
End With
ExcelVBA学習7日目
▼シートをコピーする
・「Sheet1」シートを一番右のシートの前にコピーする
Worksheets("sheet1").Copy _
Before:=Worksheets(Worksheets.Count)
・ワークシートの一番右端にシートを移動(コピー)する場合は、現在のワークシートの数を数えて、その数の後ろにシートを移動する。
Worksheets("1月").Move After:=Worksheets(Worksheets.Count)
・ワークシートの一番左側に移動(コピー)する場合は、左辛い番目のシートの前を指定
Worksheets("1月").Move Before:=Workdheets(1)
▼シートの追加・削除
・最初のシートの左側に2枚のシートを追加し、一番左のシートから、「練習1」、「練習2」と名前を付ける
Worksheets.Add Before:=Worksheets(1), Count:=2
Worksheets(1).Name = "練習1"
Worksheets(1).Name = "練習2"
▼シートを削除する
Worksheets(2).Delete
▼シートをダブルクリックしたときに処理を行う
・イベント一覧から「BeforeDoubleClick」を選択して内容を入力する
・ダブルクリックしたとき、選択しているセルの文字の色を青に、セルの色を水色にする
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
With Selection
.Font.ColorIndex = 5
.Interior.ColorIndex = 34
End With
Cancel = True
End Sub
▼ブックの場所やファイル名を参照する
・アクティブブックの名前を表示、アクティブブックのパス名を表示、アクティブブックのパス名と名前を表示、マクロが書かれているブックの名前を表示
MsgBox "作業中のブック名:" & ActiveWorkbook.Name & vbCrLf & _
"作業中のブックの保存先:" & ActiveWorkbook.Path & vbCrLf & _
"作業中のブックの保存先と名前:" & ActiveWorkbook.FullName & vbCrLf & _
"このマクロが書かれているブック名:" & ThisWorkbook.Name
・Cドライブのカレントフォルダーの場所をメッセージ画面に表示
MsgBox "カレントフォルダーの場所" & vbCrLf & CurDir("C")
・カレントフォルダーを変更し、カレントフォルダーの場所をメッセージ画面に表示
ChDir "C:¥Users¥user01¥Desktop"
MsgBox "カレントフォルダーの場所" & vbCrLf & CurDir("C")
※改行を示す→「vbCrLf」
▼ブックを開く
Workbooks.Open _
Filename:="C:\Users\…"
▼新規のブックの作成
Workbooks.Add
▼ブックを閉じる
・ブック名を指定してブックを閉じる
Workbooks("練習ブック1.xlsx").Close
・ブックを保存し、アクティブブックを閉じる
ActiveWorkbook.Save
ActiveWorkbook.Close
ExcelVBA学習6日目(セルの操作)
▼指定した行や列の操作
・列の選択
Columns("B:E").Select 'B~E列
・選択中のセルを含む行を選択
Selection.EntireRow.Select
※選択しているセルを基準にそのセルを含む行全体や列全体を選択するには、RangeオブジェクトのEntireRowプロパティやEntireColumnプロパティを利用。
※行番号や列番号を取得するには、RangeオブジェクトのRowプロパティやColumnプロパティを利用する。ただし、Rangeオブジェクトにセルの範囲を指定した場合は、指定したセル範囲内の先頭行、または最初の列の番号が返る。
▼列の挿入
・B列~C列まで列を挿入する。その際、右列の書式をコピーする。
Column("B:C").Insert CopyOrigin:xlFormatFromRightOrBelow
▼行の高さと列幅の指定
・6行から9行目までの高さを25ポイントにする
Rows("6:9").RowHeight = 25
・B列~C列の幅を20にする
Columns("B:C").ColumnWidth = 20
・列幅を標準に戻す
Columns("B:C").UseStandardWidth = True
・Rangeオブジェクトで特定のセルを指定すると、そのセルを含む行や列のサイズを取得・指定できる。
Range("A10").RowHeight = 20 ’10行目の行の高さが変わる
▼行の高さや列幅を自動調整(AutoFit)
・A3セル~A3セルを基準にした終端セル(右)を含む列の幅を自動調整
Range("A3", Range("A3").End(xlToRight)) .EntireColumn.AutoFit
・A3セルを含むアクティブセル領域の内容に合わせて列幅を自動調整
Range("A3").CurrentRegion.Columns.AutoFit
※A1セルの文字の長さは無視されている
▼セルの書式設定
・A1セルのフォントを「MS明朝」にして、サイズを「18」にし、太字、斜体設定をONにする
With Range(”A1”).Font
.Name = "MS 明朝"
.Size = 18
.Bold = True
.Italic = True
.UnderLine = True
End With
※FontStyleプロパティ、ThemeFontプロパティを指定することもできる
▼文字の配置
・A3セル~G3セルの配置を中央に揃える
Range("A3:G3").HorizontalAlignment = xlCenter
※xlDistributed(均等割り付け)、xlJustify(両端揃え)、xlLeft(左揃え)、xlRight(右揃え)、xlGeneral(標準)、xlFill(繰り返し)、xlCenterAcrossSelection(選択範囲内で中央)
※セル高さに対する文字配置は「オブジェクト.VerticalAlignment」、設定値はxlTop(上揃え)、xlCenter(中央揃え)、xlBottom(下揃え)、xlJustify(両端揃え)、xlDistributed(均等割り付け)
※文字の向きを指定するには、RangeオブジェクトのOrientationプロパティで指定。
・B3セル~B3セルを基準にした終端セル(下)目までのセルの文字を折り返して表示する
Range("B3", Range("B3").End(xlDown)) _
.WrapText = True
※列幅に収まらない文字を自動的に縮小して表示されるようにするには、RangeオブジェクトのShrinkToFitプロパティを使う。Trueを指定すると文字を縮小設定がオンになる。
・フォントの色をインデックス番号「46」に設定する
.Font.ColorIndex = 46
・セルの塗りつぶしの色をインデックス番号「36」に設定する
.Interior.ColorIndex = 36
※ColorIndexプロパティの設定値は、インデックス番号か、自動(xlColorIndexAutomatic)、色なし(xlColorIndexNone)を指定する。
▼罫線の設定
・A3セルを含むアクティブセル領域に格子状の罫線を引く。線の種類は2本線にする
Range("A3").CurrentRegion.Borders _
.LineStyle = xlDouble
・A3セル~A3セルを基準にした終端セル(右)までのセル範囲を対象に、下の罫線に関する処理を記述
With Range("A3", Range("A3").End(xlToRight)).Borders(xlEdgeBottom)
.LineStyle = xlContinuous ’線の種類を実践に
.Weight = xlThick ’線の太さを太く
.ColorIndex = 5 ’線の色を青に
End With
▼セルのデータの並び替え(Excel2003)
・A3セルを含むアクティブセル領域を対象にデータの並べ替えを行う。並べ替えの条件は登録プランの昇順で、同じプランの場合は、フリガナのあいうえお順に並べる
Option Explicit
Sub データの並べ替え_Excel2003()
Range("A3").Sort _
key1:=Range("E3"), order1:=xlAscending, _
key2:=Range("C3"), order2:=xlAscending, _
Header:=xlYes
End Sub
※Excelの操作で同じことをしたいときは「データ」タブ→並べ替えをクリックする
▼文字の検索をする
・C6セル~C14セルを対象に、B3セルの文字を探し、結果が見つからないときにメッセージを表示する
Dim result As Range
Set result = Range("C6:C14").Find(what:=Range("B3").Value, _
LookAt:=xlWhole)
If Not result Is Nothing Then
result.Select
Else
MsgBox "There is Nothing you want to see."
End If
▼特定の書式が設定されたセルを検索する
・フォントが太字でセルの色が薄い青のセルを検索
With Application.FindFormat
.Font.Bold = True
.Interior.Color = 15773696
End With
Cells.Find(what:="", SerchFormat:=True).Activate
忘れがちなExcelの便利機能・ショートカット
▼便利なエクセルショートカット
・Ctrl+Shift+1
→数値に桁区切りのカンマ
・Ctrl+Shift+5
→数値を%にする
・Ctrl+Shift+Lキー
→データの一覧にフィルターを設定する
・名前を付けて保存
→F12
作業の節目やある程度の時間を続けた後は定期的に実行し、バックアップを作成すること!
・Ctrl+Alt+Vキー
→形式を選択して貼り付け(選択範囲で一気に除算や、行列入れ替えもオプションにある!!)
▼並べ替え
・表のデータの昇順に並べ替えるには、「見出しのセル」を選択して
「データ」タブの「昇順に並べ替え」ボタンをクリック
※票のデータを並べ替える際は、事前に「別名保存」やファイルコピーなどを行い、必ず変更前の元データを残すようにすること!
※日頃から元データを残す癖をつけること!!
・「並べ替え」ダイアログで並べ替え方法を細かく設定する
→「データ」タブの「並べ替え」ボタンをクリックして、「並べ替え」ダイアログを表示し、設定する
▼表の見出し行を各ページの先頭行に記載
・「ページレイアウト」タブの「印刷タイトル」をクリックする。
・表示される「ページ設定」ダイアログで「シート」タブを選択し、「タイトル行」に表の見出し行を指定する
・印刷バックステージビューで各種設定を行った後、「エクスポート」画面に移動し、「PDF/XPEの作成」ボタンをクリック。
。PDFはExcelがインストールされていない環境はもとより、スマートフォンやタブレットなど、あらゆる環境で閲覧できる汎用性の高いファイル形式のため、ペーパーレス化に活用するとよい。
Excelの便利機能
▼トレース機能
・トレース矢印の表示
・「数式」タブの「参照元のトレース」。参照元が一目で確認できるので多用するべき。数式を入力したら、トレース機能でチェックするを徹底すること!
・「参照元トレース」や「参照先のトレース」を複数回押すと、基準のセルから階層を負ってトレース矢印が追加されていくため、計算式が階層化している場合は、複数回押すことで計算の流れを追うことができる。
・確認したら、矢印を削除することを忘れないこと
▼折れ線グラフを利用し、表の異常値を見つけるとよい
▼作業を始める前に、必ずコピーを取る
・作業を始める前に毎回必ずし、日付を付け、別ファイルとして保存しておくこと
▼何度も使う数字に名前を付ける(専用ダイアログ)
・任意のセルやセル範囲に名前を付けることができる。例えば、桁数を表す際に利用する数値や為替の金額、単位など。セルの名前は1枚のシート内にまとめて設定するとよい。
・「数式」タブの「名前の定義」をクリックし、値そのものに名前を付けることも可
・セルに付けた名前の確認編集は、「数式」タブ内の「名前の管理」をクリックし、「名前の管理」ダイアログを表示。
・印刷範囲を定義すると、「Print_Area」という名前が自動的につく。ほかにもテーブル機能を利用したり、外部データを取り込んだりすると自動的に名前が定義されることもある
▼データの入力規制機能
①入力規制を設定したいセル範囲を選択
②「データ」タブの「データの入力規則」をクリックする
③「入りょいうちの種類」で「リスト」を選択する
④「元の値」にリストとして表示したい値をカンマ区切りで列記する→OK
セルに入力済みの値を指定することもできる。対象の値が入力されているセル範囲を絶対参照でしていする。また、セル範囲に付けた名前を指定することも可能
※「データの入力規則」を解除するには、「データの入力規則」ダイアログの左下にある「すべてクリア」ボタンをクリックする
▼「データの入力規則」を設定すると、初期設定では、リスト以外の値は入力できなくなるが、エラーメッセージの「スタイル」を変更することで、リスト以外の値も入力できるように設定する。
①「データの入力規則」ダイアログボックスを表示し、「エラーメッセージ」タブを開く。
②「スタイル」に「情報」を選択
③「OK」ボタンをクリックする
→リストにない文字を入力すると、エラーメッセージが表示されるが、「OK」ボタンをクリックすると、値をそのまま入力できるようになる
ExcelVBA学習5日目(セルを操作する)
▼上下左右のセルを操作(Offsetプロパティ)
VBAでは指定したセル範囲やセル範囲から〇行、〇列ずれたセルを参照することができる。オブジェクトにはRangeを指定する
使用例)
Sub 隣接セルの参照()
With Range("B3")
.Offset(, -1).Value = "左"
.Offset(, 1).Value = "右"
.Offset(-1).Value = "上"
.Offset(1).Value = "下"
.Offset(3, -1).Value = "3つ下1つ左"
End With
Range("D2:D4").Offset(1,2).Value ="1つ下、2つ右"
Range("A2").CurrentRegion.Offset(1,2) = "A2セルを含むアクティブセル領域を基準に、1行下2列右"
End Sub
▼表内のセルの参照(CurrentRegionプロパティ)
・Excelで、アクティブセルを選択するには、「Ctrl+Shift+*」を押す。
・使用例)
Sub A6セルから下のデータを参照()
Range("B6", Range("B6").End(xlDown).End(xlToRight)).Select
End Sub
※Endプロパティを利用すると、データが入っている領域の終端セルを選択できる。
▼数式や空白セルの参照(SpecialCellsメソッド)
・オブジェクト.SpecialCells(Type,[Value])
・空白セルや数式が入ったセルなど、特定の種類のセルを選択する。
・Rangeオブジェクトを指定
・引数Typeに、「xlCellTypeConstants(定数が含まれるセル)」または「xlCellTypeFormulas(数式が含まれるセル)」が指定されているときにValueが指定できる。
使用例)
Option Explicit
Sub 空白セルの選択()
'エラーが発生したときは「エラーメッセージ」の箇所に移動
On Error GoTo エラーメッセージ
'範囲内の空白セルを選択し、マクロを終了
Range("D4:F7").SpecialCells(xlCellTypeBlanks).Select
Exit Sub
エラーメッセージ: 'エラー場発生したときエラー内容を表示
MsgBox Err.Description
End Sub