VBAの実行方法について
▼クイックアクセスバーにマクロを追加する
- 「ファイル」タブ→「オプション」→「クイックアクセスツールバー」
- 「コマンドの選択」欄でマクロを選択し、クイックアクセスツールバーのユーザー設定欄で作業中のブックを選択する。
- 使いたいマクロをクリックし、「追加」し、OKする
※ここでOKする前に「変更」でボタンのアイコンを変更するとさらに使いやすくなる！
▼ショートカットキーからの実行
- 「開発」タブの「マクロ」をクリック
- 割り当てたいマクロを選択し、「オプション」をクリック
- ショートカットキーを入力し、OK
▼ボタンからの実行
- 挿入で任意の図形を作り、図形を右クリック→「マクロの登録」
- または「開発」タブ→「挿入」→「ボタン」をクリックし、マクロを登録する
※マクロを実行した後、「元に戻す」をクリックしても、マクロの実行する前の状態に戻すことはできない。
ExcelVBA学習4日目(ファイル操作)
▼ファイルオープン１
Openステートメントで指定ファイルを開き、内容をメッセージボックスに表示。Openステートメントでファイルを開くと、画面にファイル内容を表示することなく入出力可能な状態になる。
まずGetステートメントで100biteづつバイナリモードでファイルを読み込んで変数に格納する。
Option Explicit
Sub OpenStM1()
Dim MyMoji As String * 100
Open "C:\Users\yuki\Desktop\text.txt" For Binary As #1
Do Until EOF(1) = True
Get #1, , MyMoji
MsgBox MyMoji
Loop
Close #1
End Sub
ExcelVBA学習3日目(データの処理メイン)
▼データエリアに外枠を付ける
セルA1 を含むセル範囲にデータを入力してから実行する
Option Explicit
Sub Dataline()
Range("A1").CurrentRegion.Select
Selection.BorderAround ColorIndex:=5, Weight:=xlMedium
End Sub
▼データエリアから項目行を除く
Resizeプロパティ、OffSetプロパティを使用して、アクティブセル領域から各列のタイトル行(項目行)を除く。データエリアからデータ部分のみを抽出するときに使用する。
Resizeプロパティは、行・列レベルでセル範囲のサイズを、OffSetプロパティは、行・列レベルで指定セル範囲からの相対的な位置を設定する。
セルA1を含むセル範囲にデータを入力してから事項する。
Option Explicit
Sub ResizeOffSet()
Dim RowsCount As Integer
Range("A1").CurrentRegion.Select
MsgBox "select the area of acrivecell!"
RowsCount = Selection.Rows.Count
Selection.Offset(1).Select
MsgBox "shift to the range to 1 row down"
Selection.Resize(RowsCount - 1).Select
MsgBox "delete a extra row"
End Sub
▼オートフィルタでデータを抽出
AutoFilterメソッドの引数fieldは、フィルタの対象となるフィールド番号を整数で指定、引数Criteria1は抽出条件となる文字列を指定する。
データを入力してから実行すると、データを抽出してくれる。
Option Explicit
Sub Dataselect()
Range("A1").AutoFilter field:=1, Criteria1:="福岡支店"
MsgBox "abstruct data"
End Sub
▼検索したセルのフォントカラーを変更する
Findメソッドはセル範囲を対象にして、検索条件に一致したセル（Range型）を返す。条件に一致するセルが複数ある場合は、FindNextメソッドで順次検索を行う。
検索条件に一致するセルのフォントを青にする。
Option Explicit
Sub Datalookup()
Dim RangeObje As Range
Dim FirstRanAddress As String
Set RangeObje = Cells.Find("ball") ’最初に見つあったセルを選択
If Not RangeObje Is Nothing Then ’Findメソッドの返り値がNothingでないことを確認
FirstRanAddress = RangeObje.Address ’そのアドレスを取得
Do
RangeObje.Font.ColorIndex = 5 ’フォントカラーを青にする
Set RangeObje = Cells.FindNext(RangeObje) ’その他検索条件に一致するセルを検索
Loop Until RangeObje Is Nothing Or RangeObje.Address = _
FirstRanAddress ’FindメソッドがNothingを返すか、Findメソッドの返したセル（Range）のアドレスが取得したアドレスとイコールになるまでループ
End If
End Sub
▼ソーティング
Sortメソッドを使用してデータを並べ替える。引数Key1は並べ替えで最優先されるフィールド、引数Order1は昇順、降順の指定。
例では、B列を基準として降順に並べ替える。
Option Explicit
Sub DataLineup()
Range("A1").Sort Key1:=Range("B1"), Order1:=xlDescending
End Sub
▼セル範囲を集計する
Subtotalメソッドは、指定セル範囲についての集計の作成を行う。
Subtotalメソッドには次の引数を設定する
GroupBy→何列目にあるデータを基準に分類するかを設定
Function→集計方法(合計、平均等)
TotalList→何列目を集計の対象するかどうかを設定
Replace→既存の集計表を更新するかどうかを設定
PageBreaks→分類毎に改ページにするかどうかを設定
SummaryBlowData→集計行の位置
Option Explicit
Sub shuukei()
Worksheets("Sheet8").Activate
Range("A1").CurrentRegion.Select ←表全体を選択
'chenge in order(priority key is subject)
Selection.SortSpecial SortMethod:=xlSyllabary, Key1:=Range("B1"), _
Order1:=xlAscending, Header:=xlYes, OrderCustom:=1, _
MatchCase:=False, Orientation:=xlTopToBottom
'do the Sumfuction
Selection.Subtotal GroupBy:=2, Function:=xlSum, _
TotalList:=Array(3, 4, 5), Replace:=True, _
PageBreaks:=False, SummaryBelowData:=True
ActiveSheet.Range("A1").CurrentRegion.Copy ←集計表をコピー
ActiveSheet.Paste Destination:=Worksheets("Sheet9").Range("A1") ←Sheet9に張り付け
ActiveSheet.Range("A1").CurrentRegion.RemoveSubtotal ←集計状態解除
End Sub
▼ピボットテーブルを作成
PivotTableWizardメソッドによって、Sheet1のスポーツ用品店野球用品売り上げデータベースをもとに、ピボットテーブルをSheet2に作成して、”summary sheet”と名付ける。
このピボットテーブルにAddFieldsプロパティで、行フィールド(商品名)、列フィールド(月)、ページフィールド(支店名)を追加する。
Orientationプロパティで、”売上金額”をデータフィールドに配置する。
- Worksheets("シート名").PivotTableWizardメソッド→PivotTable型
- PivotTables(”ピボットテーブル名”).AddFieldsメソッド
- PivotTables(”ピボットテーブル名”).PivotFieldsメソッド(”ピボットテーブル名”)→PivotField型
Option Explicit
Sub PivotTW()
Worksheets("Sheet2").Activate
ActiveSheet.PivotTableWizard _
SourceType:=xlDatabase, _
SourceData:=Worksheets("Sheet1").Range("A1:D16"), _ ←参照データ
TableDestination:=Worksheets("Sheet2").Range("A1"), _ ←ピボットテーブルの配置場所
TableName:="summary sheet" ビボットテーブル名
ActiveSheet.PivotTables("summary sheet").AddFields _ ←フィールドの追加
RowFields:="goods", ColumnFields:="month", _ ←行、列フィールドの追加
PageFields:="store" ←ページフィールドの追加
ActiveSheet.PivotTables("summary sheet").PivotFields("sales"). _
Orientation = xlDataField ”売上金額” をデータフィールドに配置
End Sub
▼エラーの種類に応じて、エラー処理を行う
On Error GoTo ステートメント
On Errorステートメントは、エラーの発生時に指定行ラベルに処理を移行する。
指定行ラベル以下にはエラー処理ルーチンを記述しておく。
ExcelVBA学習2日目
ブックを上書き保存する
Sub WorkbookSave()
Dim Response As Integer
Response = MsgBox("Do you save this " & ActiveWorkbook.FullName & "?", vbYesNo + vbQuestion, "check")
If Response = vbYes Then
ActiveWorkbook.Save
End If
End Sub
▼Rangeオブジェクト
Rangオブジェクトとは、「セル」のことを意味する。
通常は同一のオブジェクトが集まったものをコレクションオブジェクトというが、Rangeオブジェクトにはコレクションは存在しない。
しかし、状況によって単独のセル以外にもセル範囲、行、列などの複数のセルの集まりも1つのRangeオブジェクトとして扱う。
▼セル範囲の選択
Sub RangeObj()
Range("A1").Select
MsgBox "cell"
Range("A1:C3").Select
MsgBox "Range of cells"
Range("A1:C3,D4:F6").Select
MsgBox "not connected Ranges!"
End Sub
▼セル範囲に名前を付ける
セル範囲を指定するときはA1形式、R1C1形式、インデックス番号などを使用するが、セル範囲に名前を付けると直接その名前を参照先にすることができる。
Sub SelectNamedCell()
Worksheets("Sheet4").Activate
Range("A1:C3").Name = "CellArea"
Range("CellArea").Value = "HappyLuckey"
End Sub
VBA入門
▼ショートカット集・便利機能
・VBA起動
→Alt+F11
・[マクロ]ダイアログボックスを表示
→Alt+F８
・マクロシートを挿入
→Ctrl+F11
・オブジェクトブラウザ(オブジェクト、メソッド、変数等に関する情報がまとまっている) →F2
・カレント行を削除
→Ctrl＋Y
・すべてのブレークポイントをクリア
→Ctrl+Shift+F9
・次のプロシージャへ移動
→Ctrl+↓
・前のプロシージャへ移動
→Ctrl+↑
・1画面下へ移動
→Ctrl+PgDn
・1画面上へ移動
→Ctrl+PgUp
・モジュールの先頭に移動
→Ctril+HOME
・メニューバーのツール→オプションで、チェック機能などの設定ができる。
・作成したオブジェクトが自分以外の人間に見られないようにプロジェクトの保護ができる
▼プロジェクトエクスプローラー・モジュールの種類
・MicrosoftExcelObjects
→Excelシートとブックのイベントに関連付けられたイベントプロシージャを記述する。シートアクティブ時に実行させる、ブックオープン時に実行されるイベントを記載
・フォーム
→プロジェクト内に作成したユーザーフォームとコントロールに関連付けられたイベントを記述するためのモジュール。ユーザーフォームとコントロールに関連付けられたコードを記述。
・標準モジュール
→オブジェクトに関連付けられていないマクロやプロジェクト全体でつかうパブリック変数などを記述。きーわーどPrivateの指定がないものはすべてここに記述
・クラスモジュール
→このモジュールは自作オブジェクトとしてコードから自由に利用することができる。また、モジュール内の各プロシージャはそのオブジェクトのメソッドとしてプライベートに機能する。
VBA入門
▼ショートカット集・便利機能
・VBA起動
→Alt+F11
・オブジェクトブラウザ(オブジェクト、メソッド、変数等に関する情報がまとまっている) →F2
・メニューバーのツール→オプションで、チェック機能などの設定ができる。
・作成したオブジェクトが自分以外の人間に見られないようにプロジェクトの保護ができる
・
▼プロジェクトエクスプローラー・モジュールの種類
・MicrosoftExcelObjects
→Excelシートとブックのイベントに関連付けられたイベントプロシージャを記述する。シートアクティブ時に実行させる、ブックオープン時に実行されるイベントを記載
・フォーム
→プロジェクト内に作成したユーザーフォームとコントロールに関連付けられたイベントを記述するためのモジュール。ユーザーフォームとコントロールに関連付けられたコードを記述。
・標準モジュール
→オブジェクトに関連付けられていないマクロやプロジェクト全体でつかうパブリック変数などを記述。きーわーどPrivateの指定がないものはすべてここに記述
・クラスモジュール
→このモジュールは自作オブジェクトとしてコードから自由に利用することができる。また、モジュール内の各プロシージャはそのオブジェクトのメソッドとしてプライベートに機能する。
