VBA 基本

Visual Basic for Applicationの略。
ExcelやAccessなどで使えるプログラミング言語。
本記事ではExcelで使用する場合のVBAについて記述する。

Excelで立ち上げる場合

開発、Visual Basicをクリックする。
開発タブがない場合は、ファイル、オプション、リボンのユーザー設定、開発にチェックを入れると開発タブが表示される。
また、ALT+F11でもVBAを表示することができる。

左側に出てくるプロジェクトウィンドウは、表示、プロジェクトエクスプローラーをクリックすれば開くことができる。

標準モジュールの追加、削除

プロジェクトウィンドウの中の、「標準モジュール」にプログラムを記述していく。
標準モジュールは、挿入タブ、標準モジュールをクリックすれば表示できる。

標準モジュールを削除するときは、プロジェクトタブのモジュール名を右クリックし、「(モジュール名)の解放」をクリックする。
エクスポートしますか?のダイアログが出て、はいを選択すると、そのモジュールの内容を保存できる。いいえを選べばそのまま削除できる。

マクロの作成

マクロとは、コンピュータの操作を自動化すること。

VBAで、
sub マクロ名
と書き、エンターを押すと
Sub マクロ名()

End Sub
という形が勝手に作られる。
この中に処理内容を書いていくことで、マクロが作られる。
この1つのマクロのことを、VBAではプロシージャという。

Sub マクロ名(変数名 As Integer)
と書くと、Integer型の引数を持ったプロシージャが作れる。

Sub マクロ名(Optional 変数名 As String =”あああ”)
と書くと、入力が必須でない引数を設定できる。引数無しで呼ばれた場合、上のコードだと「あああ」という初期値を持った引数になる。

Sub マクロ名() As String
と書けば、返り値を持たせることができる。
マクロ内に、マクロ名 = “ああああ”
と書くことで、「ああああ」が返り値として渡される。

マクロの実行

実行タブ、Sub/ユーザーフォームの実行をクリックする、
もしくはF5キーを押すことで実行できる。

また、Excelのシートのほうから、開発タブ、マクロをクリックして、実行したいプロシージャを選択することでも実行できる。

Range

Range(“セル番地”)で操作を行いたいセルを指定する。
Range(“A1”)だったらA1のセルに操作を加えることができる。
Range(“A1:E10”)とすれば、範囲選択もできる。A1:E10はA1~E10までのセル。
A:Aとすれば、A列全体。
A:Eとすれば、A列からE列までの全体。
Range(“A1,E10”)と書けば、A1とE10だけを選択できる。
Range(“A:A,E:E”)と書けば、A列とE列を選択できる。
Range(“名前”)と書くことで、名前定義で定めたセルを選択することもできる。

Rangeの範囲指定では、変数を使うこともできる。
i=10という変数があるとき、
Range(“A” & i & “:E10”)
と書けば、Range(A10:E10)と書いたこと同じになる。

ただし、Rangeで変数を使って範囲選択する場合は、Cellsを使うのがいいらしい。
Range(Cells(i, 1), Cells(10, 5)).Select
と書けば、上に書いた結果と同じになる。
ただ、Range(Cells(1,1))と、1セルだけ選択しようとするとエラーになる。

セルの名前定義
セルを選択し、左上のセル番地が記されているテキストボックスに文字を入力することで、選択したセルの名前を決めることができる。

Range(“セル番地”).Value=”入力したい文字”
指定したセル番地に、指定した文字を入力する。
.Valueは省略してもいい。

Range(“セル番地”).Font.Size=11
指定したセルのフォントサイズを11にする。

Range(“セル番地”).NumberFormatLocal=”G/標準”
指定したセルの書式を標準に設定する。
“0_”と書けば、数値。
“yyyy/mm/dd”と書けば、日付。
“@”と書けば、文字列。
“0%”と書けば、パーセント表示。

Cells

Rangeと同じように、セル番地を指定して、操作を行う記述。
Rangeは範囲を指定するのに使うのに対して、Cellsは単一のセルを使う場合に使う。
(Rangeも単一のセル指定ができるけど、Cellsでやったほうがいいらしい)

Cells(行,列)で指定できる。
Rangeと同じように
Cells(1,1).Value=”aaaa”
と書けば、1行目の1列目に「aaaa」という文字を入力する。

A列、B列など、アルファベットで指定したい場合は、
Cells(行,”A”)と、ダブルコーテーションで囲わなきゃいけない。

入力されたセルの最終行を取得

値が入力されたセルが何行か続く場合、その最後の行を取得する記述がある。
Cells(1,1).End(xlDown).Row
以上は、セル番地(1,1)から下方向に、入力されたセルの最後の行の番地を取得する記述。
End(xlDown)は、最終行まで行くことを示す。
1行空白があり、その下にさらに入力されたセルが続く場合は、空白前のセルで止まる。
.Rowは、そのセルの番地を取得することを示す。
Cells(1,1).End(xlDown)だけだと、最終行の中身を取得することになる。
.Rowをつけることで、番地の取得ができるようになる。

また、以下の記述でも最終行を取得することができる。
Cells(Rows.Count,1).End(xlUp).Row
Rows.Countは、エクセルのシート自体の最終行を表す。
最終行から上方向に進み、入力されたセルがあった場合、そこを参照する。
つまり、最下部の入力されたセルを参照することができる。

左方向に進むときはEnd(xlToLeft)

変数の定義

Dim 変数名 As データ型
上の記述で変数を定義する。
As以降は省略することができ、省いた場合はVariant型になり、なんでも入れられる変数になる。

Dim aaaa As Long
aaaa = 1
上記は「aaaa」という変数を定義し、1を代入している。

日付型を入力する場合、値を#で囲む。
Dim Hiduke As Date = #2020/03/30#

複数の変数の値をつなげて表示したい場合は、
Range(“セル番地”)=変数名1 & 変数名2
と書く(&の前後に空白がないとエラーになる?)。

Rows.Countと書くと、シートの行数を表す。
Excel(Office365)の行数は、1,048,576行。

String型にNULLは代入できない。
Variant型にならNULLを代入できる。

定数の定義

const 定数名 As データ型 = 値
=値まで書かないとエラーになる。
コードの先頭に書けば、どのプロシージャ、関数からでも参照できる定数にできる。

For Next文 繰り返し処理

Dim 変数名1 As Long
For 変数名1 = 1 To 10 Step 1
  処理内容
Next 変数名1
以上が、VBAの繰り返し処理の書き方。
1 To 10の部分で、変数名1が1から始まり10になるまで繰り返すことを表している。
Step 1は、ループするたびに、変数名1を1ずつ上げていくことを表している。
Stepと数字の間に空白がないとエラーになる。

Exit Forと書けば、ループを強制終了させることができる。

For Each文

コレクションの中身をすべて参照できる文。
Dim var As Variant
For Each var In グループ
  処理
Next var
これで、指定したグループの要素を1つ1つvarに代入して処理を行える。
Dim var As Worksheet
For Each var In Worksheets
と書いたら、エクセルのシート1つ1つを参照して処理を行える。

Do Loop 繰り返し処理

Dim 変数名1 As Long
Do While 変数名1 <= 10
  処理内容
  変数名1 = 変数名1 + 1
Loop
上記は、変数名1が10以下のとき、処理を繰り返すという記述。

Dim 変数名2 As Long
Do Until 変数名2 >= 10
  処理内容
  変数名2 = 変数名2 +1
Loop
上記は、変数名2が10以上になるまで、処理を繰り返すという記述。

If文 条件式

If Cells(1,1)=”aaaa” THEN
  Cells(1,2)=”〇”
ElseIf Cells(1,1)=”bbbb” THEN
  Cells(1,2)=”△”
ELSE
  Cells(1,2)=”×”
END If

上記は、1,1のセルに「aaaa」が入力されていたら、1,2のセルに〇を入力する、
「bbbb」が入力されていたら、1,2のセルに△を入力する、
「aaaa」でも「bbbb」でもない場合は、1,2のセルに×を入力するという記述。

別のシートの情報を取得

Sheets(“シート名”).Select
と書くことで、操作を行うシートを選択することができる。

Cells(1,1)
という記述は、今アクティブになっているシートの番地(1,1)のセルを指す。
Sheets(“Sheet1”).Select
Cells(1,1)=Cells(1,2)
と書くことで、「Sheet1」のセル番地(1,2)の値をセル番地(1,1)に代入すると明確に書くことができる。
これならSheet2やSheet3をアクティブにしているときでも、Sheet1のセルに処理が行える。
Sheets(“Sheet1”).Selectを書くと、その後ずっとSheet1の操作を行うことになるので、
参照するシートを戻したいときは、Sheets(“Sheet2”).Selectと、再度指定しなければならない。

シートを切り替える処理は重いので、行う場合は、処理の前に
Application.ScreenUpdating = False
と書くと、画面の更新を止めることができ、処理が速くなる。
処理が終わった後で
Application.ScreenUpdating = True
と書くことで、画面の更新を行い処理を反映させる。

デバッグ

デバッグタブ、ステップインをクリックで、1行ずつ処理を実行できる。
F8キーを押すことで、1行ずつ処理を進めていける。
そのとき、変数名にカーソルを合わせると、その変数の値を確認できる。

「マクロなしのブックに保存できません」の対処法

エクセルのブックを保存するとき、「マクロなしのブックに保存できません」と出た場合、保存するときのダイアログにてファイルの拡張子を「.xlsx」から「.xlsm」として保存する。

Worksheet、Worksheets

Worksheetは、エクセルのシートのオブジェクト。
Worksheetsはシートのコレクション。
Worksheets.addで、新しいシートを挿入できたりする。
Worksheets(“シート名”)でシートを参照できたりもする。

Find

特定の文字などが入ったセルを探すことのできる文。
Worksheets(“シート名”).Find(what:=”ああああ” , LookAt:=xlWhole)
と書くと、シート名に指定したシートから、「ああああ」と書かれたセルを探す。
「LookAt:=xlWhole」は、全文一致を表す。
部分一致は「xlPart」
後ろに「.Column」など付けたらそのセルの列を参照することもできる。

FIndは、探した文字が見つからなかったらNothingを返す。
If シート名.Find(waht:=”ああああ”,LoolAt:=xlWhole) Is Nothing then

End If
と書けば、指定した文字が見つからなかったときの処理が書ける。

DisplayAlerts

エクセルの出す警告メッセージを出ないようにする。
シートを削除する際の確認メッセージなど。
DisplayAlerts = False
と書けば、表示されなくなる。

Len,LenB

文字列の長さを取得する関数。LenBは文字列のバイト数を取得する。
VBAでは文字列をUnicodeを扱う。
LenBでは、全角半角に関わらずすべての文字を2バイトとして返すらしい。
全角と半角でバイト数を知りたい場合、文字コードを変換してから数えればいいらしい。

文字列を変換する方法
LenB(StrConv(“文字列” , vbFromUnicode)
と書けば、全角半角を反映したバイト数がわかる。
StrConvで変換して変数に入れた値をさらにStrConvで変換したらおかしくなった。
一度変換して変数に入れたら、それはもう文字コードが変換されたあとなので
そのまま使用していい。

再びエクセルで文字を書き出す場合は、
StrConv(“文字列” , vbUnicode)
とすれば、文字コードをUnicodeに戻せる。

LeftB,RightB,MidB

文字列の中から指定した文字数を抜き出す。

LeftB(“文字列”,5)
上は、指定した文字列の、左から5バイトを抜き出す。

RightB(“文字列”,5)
上は、指定した文字列の、右から5バイトを抜き出す。

MidB(“文字列” , 3 , 10)
上は、指定した文字列の、3バイト目から10バイト目までの文字を抜き出すという記述。

エクセルで全角文字を1バイトで切り分けたら、「・」として表示された。

バイト数でなく、文字数で取得したいならBを抜いた関数を使う。
LEFT(“文字列”,3)
指定した文字列から、左から3文字を抜き出す
RIGHT(“文字列”,2)
指定した文字列から、右から2文字を抜き出す
MID(“文字列”,4,6)
指定した文字列の4文字目から、6文字を抜き出す

選択範囲のコピー、ペースト

Range(“A1:C5”).Copy
これで、A1からC5までの範囲がコピーできる。

Range(“A10”).PasteSpecial
これで、A10にコピーしたものを貼り付ける。

Sheets(“Sheet1”).Range(Cells(5, 2), Cells(8, 5)).Copy
としたら、エラーになる。
Sheets(“”)を指定した上でCellsを指定するときは、Sheets(“Sheet1”)と、どのシートから取得するか記述しなきゃいけないらしい。

また、結合したセルに値をペーストする場合は、
幅を同じにしないとエラーが出る。
逆に結合したセルの値をコピーして1つのセルに貼り付けることはできる。

UBound

配列の長さを調べる関数。
Dim Num(10) As Variant
MsgBox UBound(Num)
としたら、10が返される。

VBAの配列変数は、
Dim Num(10) As Variant
と書いたら、Num(0)~Num(10)までの11個の変数が作られる。

UBound(配列変数,1)
と書いたら、配列の1次元目の長さが取得できる。
Dim var(10,20)
の場合、UBound(var,1)で10、UBound(var,2)で20が返される。

エクセルファイル名の取得

現在開いているエクセルファイルを取得することができる。
MsgBox ActiveWorkbook.Name
としたら、現在のエクセルファイルの名前が返される。
MsgBox ActiveWorkbook.FullName
としたら、現在のエクセルファイルの名前を、パスも含めて出してくれる。

現在のエクセルファイルだけでなく、別の開いているエクセルファイル名を取得するには
Dim aaaa As Workbook
For Each aaaa In Workbooks
MsgBox aaaa.Name
Next aaaa
とすれば、すべてのエクセルファイル名がメッセージボックスで出てくる。

エクセルファイルのコピー

Workbooks(“ワークブック名”).Sheets(“シート名”).Copy After:=Workbooks(“ワークブック名”).Sheets(“シート名”)
と書くと、シートのコピーができる。指定したシートの後ろにシートをコピーする。
AfterをBeforeに変えたら指定したシートの前にシートをコピーする。

ただ、エクセルのシートをコピーすると名前定義も一緒にコピーされる。
それが重なると、名前が重複しているというダイアログが出ることになる。

コピーした後に、Cells.Clearを行い、ペーストをしたら、エラーになった。
Clear処理でコピーが取り消されたらしい。

リストボックスに値を追加

VBAの編集画面で、挿入、ユーザーフォーム、ツールボックスからボタンやリストボックスなどを作成できる。
それぞれのフォームはプロパティから名前やキャプションを変更できる。

VBAからフォームを開くには、
フォーム名.Show
と書けばいい。

VBAからフォームを閉じるには、
Unload フォーム名
と書けばいい。

リストボックスに値を追加する方法
フォーム名.リストボックス名.AddItem “文字列”
で、リストボックスに文字列を入れることができる。

リストボックスで現在選択している値をセルに値を入れる場合、
Cells(1,1) = フォーム名.リストボックス名.Value
と書けばいい。
フォーム名.リストボックス名.Text
でもいい。
Valueは、中身がないとnull、Textは空文字になる。

リストボックスにいくつ値が入っているかは
フォーム名.リストボックス名.Countでわかる。

リストボックスの値を取得するには
フォーム名.リストボックス名.List(番号)
番号は、リストに格納されている値の番号、1番最初の項目だったら0

リストボックスの現在選択されている値の番号は
フォーム名.リストボックス名.ListIndex
現在選択されている値を参照するには
フォーム名.リストボックス名.List(リストボックス名.ListIndex)と書けばいい。

リストボックスに長い文字列を格納すると、ファイルを開くときにリストボックスが破損するようになった。
長い文字列を入れるときは、入力規則で直接文字列を打ち込むよりも、セルに書き出してそこから取得したほうがいい。

ブックを開いたとき、閉じたときの処理

ThisWorkbookに記述する。

ブックを開くときの処理
Private Sub Workbook_Open()

End Sub

ブックを閉じるときの処理
Private Sub Workbook_BeforeClose(Cansel As Boolean)

End Sub

ダイアログを表示してファイルを指定させる

Application.GetOpenFilename (“aaaa,”)
と書いたら、ファイルを指定するダイアログが表示できる。
引数の「aaaa」の部分は、ダイアログの右下に出る任意の文字。
(“aaaa, *.png”)と書いたら、pngファイルだけを選べるように設定できる。
(“aaaa,*.png ; *.jpg”)と書いたら、pngファイルとjpgファイルを選べるように設定できる。

返り値は、選択したファイルのフルパス。
Range(“A1”).value = Application.GetOpenFilename (“aaaa,”)
と書いたら、選択したファイルの名前が、置いてある場所と一緒にセルA1に書かれる。
ダイアログをキャンセルで閉じた場合は、Falseが返ってくる。

セルを結合する

Range(“A1:C5”).Merge
セルA1~C5を結合する。
Range(“A1:C5”).Merge True
と書くと、A1~A5,B1~B5,C1~C5,D1~D5,E1~E5のそれぞれを1行ずつ結合する。

Range(“A1:C5”).UnMerge
と書くと、A1~C5の範囲の結合を解除する。

フォントを変える

フォントサイズを10にする。
Range(“A1”).Font.Size = 10

フォントをMSゴシックにする。
Range(“A1”).Font.Name = “MS ゴシック”

書式の変更

Range(“A1”).HorizontalAlignment = xlCenter
と書くと、A1のセルを、左右に中央ぞろえする。
Range(“A1”).VerticalAlignment = xlCenter
と書くと、A1のセルを、縦方向に中央ぞろえする。

xlLeftと書けば、左寄り。xlRightと書けば、右寄り。
xlTopと書けば、上より。xlBottomと書けば、下より。
xlGeneralと書けば、標準を指定できる。

Replace 特定の文字を変換する

Cells(1,1) = Replace(“月曜日”,”月”,”火”)
と書いたら、1,1のセルに「火曜日」という文字が入る。

特定の文字が入っているか InStr,InStrRev

InStr(“あいうえお”,”う”)
とすれば、「う」は「あいうえお」の3文字目に入っているので3が返ってくる。
もし文字列に指定した文字が複数入っていた場合、文字列の先頭からより近い位置が返ってくる。

InStrRev(“あいうえおあいうえお”,”あ”)
これは、指定した文字を、文字列の後ろから探す。
上記のコードだと、6文字目の「あ」が対象となり6が返ってくる。

シートをクリアする

Sheets(“シート名”).Cells.Clear
で、シート内容を全削除できる。

セルに数式を書き込む formula

Cells(1,1).formula=”=A1+B1″
と書けば、数式を書き込むことができる。

セルの列の幅を変更

Range(“A:D”).Columns.AutoFit
と書けば、AからDまでの列を、入力されている文字列に合わせたサイズに自動調整できる。

文字を太字にする

Cells(1,1).Font.Bold = True
と書けば、指定したセルを太字にできる。

Dir

Dir(“C:\test\aaaa.txt”)
と書くと、C:\testのフォルダに入っている、aaaa.txtというファイル名を取得できる。
Dir(“C:\test\*.txt”)
と書くと、C:\textのフォルダに入っている、「.txt」の文字が入ったファイル名を1つ取得する。
Dir()と書くと、前回にDirで取得を行ったのと同じ条件でファイル名を取得する。さらにこのとき、一度取得したファイル名を除いて取得してくれる。
Dim FileName As String
FileName = Dir(“C:\test\*.txt”)
Do Until FileName <> “”
FileName = Dir()
Loop
と書くと、指定したフォルダのすべてのtxtファイルを参照した処理が行える。

フォルダを開くダイアログ

If Application.FileDialog(msoFileDialogFolderPicker).Show = True then
Cells(1,1) = Application.FileDialog(msoFileDialogFolderPicker).SelectedItems(1)
End If
と書くと、ダイアログで選択させたフォルダのパスを、指定したセルに入力できる。

シートの新規追加

Worksheets.Add After := Worksheets(Worksheets.Count)
と書くと、新しいシートを、一番最後(右)の位置に挿入できる。
Worksheets.Add Before:= Worksheets(1)
と書くと、新しいシートを一番最初(左)の位置に挿入できる。

Dim aaaa As Worksheet
Set aaaa = Worksheets.Add(After:=Worksheets(Worksheets.Count))
aaaa.Name = “つけたい名前”
と書けば、シートを最後尾に追加した上で、シート名を変更できる。

Application.Statusbar 右下に文字を表示する

Application.Statusbar=”aaaa”
と書くと、エクセルの右下に指定した文字列を表示できる。
変数も使える。ただしループごとに表示の処理を行うとかなり重くなる。

テキストファイルを開く

Oepn ファイル名 for Input As #1
Close #1
これは、指定したファイル名を、読み込みで、1という番号を付けて開くという記述。
Open ファイル名 for Output As #1
Close #1
と書くと、指定したファイル名を書き込みで、1という番号を付けて開くという記述。
ファイル名にはパスを指定することもできる。パスを指定しない場合は、カレントフォルダが参照される。

Line Input #1 変数名
と書くと、開いたテキストファイル(1番のテキストファイル)を1行取得し、指定した変数に入れ込む。
この時、1行読み込んだ後、カーソルが次の行に動く。

Do Until EOF(1)
Line Input #1 変数名
Loop
と書くと、すべての行を参照するまで行の取得を行う。
EOFは、読み取り位置が最終行になったときTrueを返す。
EOF(1)とは、#1で指定したファイルが最終行か否かを取得している。
最終行じゃないときはfalseを返す。最終行になったらtrueを返す。
だからEOFがTrueになるまで処理を行うという記述を行っている。

FileSystemObject

フォルダとかファイルを操作できるオブジェクト。
Dim FSO As Object
Set FSO = CreateObject(“Scripting.FileSystemObject”)
FileSystemObjectを使うには、上記のように、FileSystemObjectのインスタンスを作るらしい。
FOS.GetFolder(フォルダのパス).Files.Count
で、フォルダにあるファイルの数を取得できる。
最後にSet FSO = Nothingと書かなきゃいけない?

OpenTextFileメソッド

ファイルを読み込んだり書き込んだりできるメソッド。
FSO.OpenTextFile(ファイルのパス,処理番号)
処理番号 1:読み込み 2:新規書き込み 8:追記書き込み
追記書き込みを選択した場合、ファイルの行数を指定するカーソルが最終行に行くので、
カーソルの位置を取得したら、それがそのファイルの最終行ということになる。
カーソルの位置は.Lineメソッドで取得できる。
FSO.OpenTextFile(ファイルのパス,処理番号).Line

On Error GoTo エラーが起きたときに指定した処理を行う

On Error GoTo aaaa

aaaa:
処理
と書くと、On Error以降でエラーが起きた場合、aaaa以降で記した処理が行われる。
aaaa:の部分はプロシージャの最後に書かないと、そのあとに書いた処理がすべてエラー処理になる。
aaaa:の前にExit Subを書かないと、処理の最後でエラーが起きてないのにaaaaの中の処理を行うことになる。
On Error GoTo 0と書くと、それ以降はエラーが起きてもエラー処理を行わないようにできる。

IsError 数式のセルの値がエラーの場合

数式が入力されたセルにおいて、参照先の値が入力されていないなどの理由で、セルの中身が#N/Aなどのようにエラーになっている場合、それを判定するのに
IsError()が使える。
IsError(Cells(1,1))と書くと、1,1の値がエラーの場合、trueを返す。

ピボットテーブル

Sheets(“シート名”).PivotTables(“ピボットテーブル名”)
で、ピボットテーブルの操作を行える。
.Nameでピボットテーブルの名前を取得

ピボットテーブルは、テーブルの情報を一度ピボットキャッシュというものに保存して、ピボットテーブルを作成している。VBAでピボットテーブルを作る際にもこの手順を踏む。
Dim aaaa As PivotCache
Set aaaa=ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase,
SourceData:=Sheets(“Sheet6”).Range(“A1:B2”))
と書くことで、A1:B2の範囲をピボットキャッシュに保存できる。
さらに、
aaaa.CreatePivotTable TableDestination:=Sheets(“シート名”).Range(“A1″),TableName:=”名前”
と書くと、指定したシートのA1に指定した名前でピボットテーブルを作れる。
さらに、
aaaa.AddFields ColumnFields:=Array(“項目名”),RowFields:=Array(“項目名”)
と書くと、ピボットテーブルの列と行を設定できる。

Sheets(“シート名”).PivotTables(“ピボットテーブル名”).AddField ColumnFields:=Array(“項目名”)
でもいい。

ピボットテーブルに集計フィールドを追加するには
Sheets(“シート名”).PivotTables(“ピボットテーブル名”).CalculatedFields.Add “項目名”,
“=項目1/項目2”
と書くと、項目1と項目2の結果を持った項目を、フィールドとして追加できる。
=項目1/項目2の、項目名に#や()などの文字が入っている場合、正しく計算が行えずフィールドの追加が行われない。そのときは=’項目1’/’項目2’と書けばいい。

ピボットテーブルに値を追加するには
Sheets(“シート名”).PivotTables(“ピボットテーブル名”).AddDataField Field:=Sheets(“シート名”).PivotTables(“ピボットテーブル名”).PivotFields(“項目名”),Caption:=”値につける名前” ,Function:=xlSum
と書く。
Captionは省略してもいい。Captionを省略した場合、項目名がそのまま値の名前になる。
Functionも省略できる。xlSumは合計値を値として出すという意味。xlAverageと書くと平均値が出せる。

ピボットテーブルのデータソースの範囲を変更するには
Sheets(“Sheet2”).PivotTables(“ピボットテーブル名”).ChangePivotCacheActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:=Sheets(“Sheet3”).Range(“A6:B7”))
と書くと、指定した名前のピボットテーブルのデータソースの範囲をSheet3のA6:B7に変更できる。

ピボットテーブルのグラフを作成するには
Dim グラフ範囲 As Range
Set グラフ範囲 As Range
まずこれで、グラフを表示する範囲を設定したあと
Sheets(“シート名”).Shapes.AddChart(xl3DColumn,グラフ範囲.Left,グラフ範囲.Top,グラフ範囲.Width,グラフ範囲.Height).Chart.SetSourceData Source:=Sheets(“シート名”).PivotTables(“ピボットテーブル名”).TableRange1
と書くと、指定したピボットテーブルのデータのグラフが作れる。
TableRange1とは、ピボットテーブルのページフィールドを除いた範囲を取得できる。ページフィールドが何なのかはよくわからない。
xl3DColumnは、3Dのデータを作ることを示す。
xlcolumnclusteredと書けば、2Dの棒グラフ。

Resize

Range(“A1”).Resize(2,2).select
と書けば、A1から1列先、1行先までの範囲を選択できる。
(“A1:B2”)と同じ。
Range(“A1”).Resize(3,3).select
と書けば、(“A1:C3”)と同じ。

二次元配列の中身をセルに貼り付ける

Dim var(3,4)
Range(“A1”).Resize(UBound(var,1)+1 , UBound(var,2)+1).value = var
と書けば、varの中身をすべてセルに書き出す。
UBoud(var,1)+1としているのは、var(3,4)は、var( 0~3 , 0~4)と、UBoundで取得できる値より1つ多い数の値を保存できるから。

1行目に、var( 0 , 0~4 )の値を書き出し
2行目に、var( 1 , 0~4 )の値を書き出してくれる。

テーブルを作る

Sheets(“シート名”).ListObjects.Add(xlSrcRange,Range(“A1:D6″), ,xlYes).Name=”テーブル名前”
と書くと、A1:D6の範囲を指定したテーブル名のテーブルにできる。
xlSrcRangeは、セル範囲をテーブルにするときにそう書けばいいらしい。
xlYesは先頭行を見出しにするかどうか。xlYesと書くと、見出しにする。

テーブルのソート

Sheets(“シート名”).ListObjects(“テーブル名”).Range.Sort _
Key1:=Range(“B2”) , Order1:=xlAscending , Key2:=Range(“C2”),Order2:=xlDescending,Header:=xlYes
と書くと、Range(“B2”)の項目、Range(“C2”)の項目の順にソートできる。
xlAscendingが昇順、xlDescendingが降順。
Headerは、先頭行を見出しにするかどうか決めている。
KeyとOrderはKey3とOrder3まで指定できる。

4つ以上の項目でソートしたい場合、
最初に3つソートした後、
もう1度別の項目でソートを行えば、後にソートするものを優先したソートが行える。
A,B,C,D,E,F,Gの順にソートしたい場合、
最初にE,F,Gの順にソートし、
次にB,C,Dの順にソートし、
最後にAでソートすれば、
A,B,C,D,E,F,Gの順にソートしたものと同じ結果になる。

編集画面

タイトルとURLをコピーしました