Excel VBA

フォルダの作成：FileSystemObject.CreateFolder

2014-11-09T18:00:00.000+09:00

FileSystemObject.CreateFolderメソッドによってフォルダを作成することができます。
下ではfsにFileSystemObjectを指定した後、CreateFolderメソッドで C:\tmp\workフォルダを作成します。エラー処理がなされていないため、既にこのフォルダが存在する場合にはエラーで終了します。

Sub test()
Dim fs As Object
Set fs = CreateObject("Scripting.FileSystemObject") ' オブジェクトを指定

fs.CreateFolder "C:\tmp\work" ' フォルダを作成

Set fs = Nothing

End Sub

8進数： Oct

2014-11-09T17:51:00.004+09:00

8進数への変換は、Oct関数を用います。引数に10進数の数を入力するだけです。
ここでは16を与えているため、8進数で表した"20"がB2セルに記録されます。

Sub test()
a = Oct(16) '8進数に変換
Cells(2, 2) = a
'MsgBox a 'メッセージとして表示する場合
End Sub

16進数： Hex

2014-11-09T17:48:00.002+09:00

16進数への変換は、Hex関数を用います。引数に10進数の数を入力するだけです。
ここでは16を与えているため、16進数で表した"10"がB2セルに記録されます。

Sub test()
a = Hex(16) '16進数に変換
Cells(2, 2) = a
'MsgBox a 'メッセージとして表示する場合
End Sub

インターネットエクスプローラを操作する：InternetExplorer.Application

2014-11-09T14:43:00.001+09:00

Excelからインターネットエクスプローラ（IE）を扱うこともできます。
CreateObject関数を用いて、変数browserにIEをセットします。このオブジェクト変数browserのプロパティに値をセットすることで、様々な操作をすることができます。下の例では.Visible、.Navigateをセットしていて、Infoseekのページを開いています。

Sub test()
Dim browser As Object    'IEを扱う変数を定義

Set browser = CreateObject("InternetExplorer.Application")
'CreateObject関数でIEを起動
  browser.Visible = True ' プロパティVisibleに値をセット
  browser.Navigate "http://www.infoseek.co.jp/" 'Infoseekのページを開く
End Sub

IEの操作については、次の本が非常に参考になります。
Excel VBAでIEを思いのままに操作できるプログラミング術

補助線を引く： SetElement

2014-10-26T20:11:00.000+09:00

グラフの補助線を引くには、SetElementプロパティを指定します。下の例では、ワークシート"sheet1"の"fig01"チャートで、縦と横の補助線を指定しています。このSetElementプロパティはとても便利で、グラフツール[レイアウト]タブの[ラベル]や[軸]などのグループで設定できるものは全て設定が可能です。

Sub test()
Dim ws As Worksheet 'ワークシート型変数の定義
Dim fig As ChartObject 'ChartObject型変数の定義

Set ws = Worksheets("sheet1") 'ワークシート名を指定
Set fig = ws.ChartObjects("fig01") 'グラフを指定

fig.Chart.SetElement (msoElementPrimaryCategoryGridLinesMajor) '縦軸
fig.Chart.SetElement (msoElementPrimaryValueGridLinesMajor) '横軸
End Sub

凡例を表示する：HasLegend

2014-10-26T19:48:00.000+09:00

グラフに凡例を表示するには、Chart.HasLegendプロパティを指定します。
ここではワークシート"sheet1"に、"fig01"というグラフがあるとします。
このChartObjectについて、Chart.HasLegendプロパティの値をTrueにすると、凡例が表示されます。逆にFalseにすると、凡例が消えます。

Sub test()
Dim ws As Worksheet 'ワークシート型変数の定義
Dim fig As ChartObject 'ChartObject型変数の定義

Set ws = Worksheets("sheet1") 'ワークシート名を指定
Set fig = ws.ChartObjects("fig01") 'グラフを指定

fig.Chart.HasLegend = True '凡例を表示
' fig.Chart.HasLegend = False '凡例を非表示
End Sub

文字の抽出: Mid, Left, Right

2014-10-05T22:08:00.002+09:00

文字列の中から特定の文字を抽出したくなることがあります。例えばファイルの名前に特定の文字列を含むものだけを抽出したい時や、データから”高橋”さんを含む行を選択したり、などです。文字列の操作に長けてくると、このような検索、抽出といった操作が楽にできるようになってきます。今回はMid, Left, Right関数を使って、B2セルに書かれている文から特定の文字を抽出します。

Mid, Left, Rightとも、最初の引数には文字列を与えます。Mid関数の次の引数は、何文字目から選択するか、を指定し、3つめの引数で何文字分抽出するかを決めます。Mid(sss, 5, 2)とした場合、5文字目から2文字分を選択することになります。Leftは文字列の左から、Rightは右から指定文字数分を選択します。さて、今回は日本語の文字列から文字を抽出していますが、abcdeのようにアルファベットから抽出するのも同じ形です。半角・全角の違いがあるかと思っていましたが、Left("abcde",2)とすると、日本語と同じく2文字分の"ab"が選択されます。

Sub test()
Dim sss As String '文字列変数を宣言
sss = Cells(2, 2) 'B2セルの文字列をsssに格納

Cells(5, 2) = Mid(sss, 5, 2) '5文字目から2文字文
Cells(6, 2) = Left(sss, 2)
Cells(7, 2) = Right(sss, 2)
End Sub

ダブルクリックでセルに色をつける: BeforeDoubleClick

2014-10-05T16:31:00.000+09:00

ダブルクリックなどユーザーが何らかの操作をしたときに、その操作に応じてイベントが発生するようにすると、更に操作性を高めることができます。これを指定するには、Worksheetモジュールと呼ばれるモジュールにコードを記述します。VBエディタ上のプロジェクトエクスプローラで、 Microsoft Excel Objects - Sheet1(sheet1)を選びます。デフォルトでは(General)というオブジェクトになっていますが、ドロップボックスからWorksheetを選びます。ここではダブルクリックに対するイベントを操作しますので、右上のプロシージャボックスの中から、BeforeDoubleClickを選びます。このプロシージャの中に記述したコードがダブルクリック時に実行されます。下の例では、セルが空でない場合ColorIndexを6（黄）に指定しています。

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
'ダブルクリックしたセルが空で無い場合
If Target.Value <> "" Then
'セルの色を黄色に指定
Target.Interior.ColorIndex = 6
End If
End Sub

上の例では、色が黄色になったまま元に戻せなくてちょっと不便です。そこで、ColorIndexが既に6になっているセルをダブルクリックした場合のイベントを追加すると、もう少し使い勝手が良くなります。下の例ではColorIndexをxlNoneにすることで、色をはずしています。

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
'ダブルクリックしたセルが空で無い場合
If Target.Value <> "" Then
'セルの色を黄色に指定
If Target.Interior.ColorIndex <> 6 Then
Target.Interior.ColorIndex = 6
'既に黄色になっている場合は色をはずす
Else
Target.Interior.ColorIndex = xlNone
End If
End If
End Sub

確認のダイアログを表示しない: DisplayAlerts

2014-10-05T16:02:00.001+09:00

複数のワークブックを閉じたい、など繰り返し複数の処理を行いたい場合、保存するかどうかを聞かれるダイアログボックスが一回一回表示されると自動化になりません。そこでダイアログを表示せず、強制的に処理を実行していく方法が必要です。ワークブックを閉じるのを例にとります。閉じるにはWorkbooks.Closeを指定しますが、この前にApplication.DisplayAlertsプロパティをFalseに指定します。但しこの方法を取ると連続して全てのワークブックを未保存でも閉じてしまうので、使用には注意が必要です。

Sub test()
Application.DisplayAlerts = False
Workbooks.Close '全てのワークブックを閉じる
Application.DisplayAlerts = True
End Sub

ワークブックを閉じる: Workbooks.Close

2014-09-21T22:12:00.001+09:00

ワークブックを閉じるには、Workbooks.Closeを指定します。複数のワークブックを開いている場合、この方法では全てのファイルを閉じに行きます。未保存の場合、上書きするかどうかを確認するダイアログボックスが開きます。

Sub test()
Workbooks.Close '全てのワークブックを閉じる
End Sub

ワークブックを開く: Workbooks.Open

2014-09-21T22:12:00.000+09:00

ワークブックを開くには、次のように指定します。これも1行で完結です。ここでは開くファイルを直接指定していますが、String型の変数などをうまくつかうことでExcelのシートに書き込んだファイルを読み込んだり、ダイアログボックスでファイルを開いたり、といった応用ができます。

Sub test()
Workbooks.Open "C:\test\test.xlsm" 'test.xlsmを開く
End Sub

セルの背景をグラデーションにする

2014-09-21T22:08:00.000+09:00

シートを美しく見せるために、セルの背景にグラデーションをかけたくなる場合があります。セルの背景に色をつけるで説明した通り、セルの色はInterior.Colorプロパティに色を与えることで着色できます。そこでこれを少し工夫し、与える色を少しずつ変える工夫を加えることでグラデーションを作ることができます。ここでは色の指定をRGBで行います。RGBはその名の通り、順番にRed、Green、Blueを255段階で指定します。今回は赤いグラデーションを作ることにしますので、赤は常に255にします。緑、青の数字が0に近いほど純粋な赤、255に近いほど白に近づきますから、forを使って他の色の大きさを変化させれば良いわけです。このマクロでB列を10行目まで着色すると、こんな感じになります。

Sub test()
For i = 1 To 10
'G,Bの色を指定する。255をi/10ずつ増加させ、変数idxに格納
idx = 255 * i / 10
'セルの色を指定
Cells(i, 2).Interior.Color = RGB(255, idx, idx)
Next i
End Sub

ビジネスプロフェッショナルのExcel術 (日経BPムック)

図の大きさを指定：ChartObjects.Width/Height

2014-09-21T13:50:00.000+09:00

図の大きさを指定するには、ChartObjects.Width/Heightプロパティを指定します。ここではワークシート"sheet1"にある、"fig01"という図の大きさを、幅100ピクセル、高さ100ピクセルにする例を示します。ちなみに、（100、100）は小さすぎて実用的ではありませんので、使っているディスプレイのサイズに合わせてこの数値を好みの値に変更してください。下の例ではChartObject型の変数"fig"を作り短い名前にすることで、視認性を高めています。図の位置はChartObjects.Top/Leftで指定しますから、この4つのプロパティを使うと図を好き配置にすることができます。

図の移動、位置の指定：ChartObjects.Top/Left

2014-09-21T13:44:00.000+09:00

図を移動させたり位置を指定するには、ChartObjectsのTop、Leftプロパティを指定します。それぞれ数字を指定しますが、これは画面の左上からのpixel数になり、(0,0)を指定すれば画面左上の位置にぴったりとハマります。下の例ではワークシート"sheet1"にある"fig01"という名前の図の位置を指定しています。ChartObjects("fig01")をfigという変数に割り当ててあり、fig.Top、fig.Leftをそれぞれ10に指定していますので、左上からちょっと内側に入った位置に移動します。

Sub test()
Dim ws As Worksheet    'ワークシート型変数の定義
Dim fig As ChartObject    'ChartObject型変数の定義

Set ws = Worksheets("sheet1") 'ワークシート名を指定
Set fig = ws.ChartObjects("fig01")     'グラフを指定

fig.Top = 10
fig.Left = 10
End Sub

グラフを作成: Shapes.AddChart, ChartObjects.Add

2014-09-14T17:51:00.001+09:00

グラフを作成します。実はものすごく簡単で、以下の1行で終了です。

Sub test()
ActiveSheet.Shapes.AddChart
End Sub

他の方法でも図を追加でき、下の例では最初に表示する大きさ、場所を指定できます。

Sub test()
Set fig = ActiveSheet.ChartObjects.Add(120, 15, 200, 150)
End Sub

数値は左端からの座標、上端からの座標、初期サイズの幅、初期サイズの高さを指定する。

グラフに表示するデータを指定する: XValues, Values

2014-09-14T17:49:00.001+09:00

2次元のグラフでは、x軸、y軸それぞれにデータが必要です。この値の指定の仕方を紹介します。このマクロをうまく使うと、新しい図を追加することなく、同じ図の中でいくつものグラフを表示するような機能を持たせることもできます。大量のデータを処理する場合には図が沢山あるとエクセル君はどんどん重くなって、PCも自分もどんどんイライラしてきますから、一工夫することで軽い処理を実現できます。
さて、ここではワークシート"sheet1"のB5からB14にxのデータ、C5からC14にyのデータが入力されているとし、"fig01"という名前の図が既に存在するとします。x軸のデータは"XValues"、y軸のデータは"Values"を指定します。

Sub test()
Dim ws As Worksheet 'ワークシート型変数の定義
Dim fig As ChartObject 'ChartObject型変数の定義

Set ws = Worksheets("sheet1") 'ワークシート名を指定
Set fig = ws.ChartObjects("fig01") 'グラフを指定

fig.Chart.SeriesCollection(1).XValues = "=Sheet1!$B$5:$B$14"
fig.Chart.SeriesCollection(1).Values = "=Sheet1!$C$5:$C$14"
End Sub

グラフがまだ無い場合に実行するとエラーがでますから、グラフを作成してから上記プログラムを試してみてください。

図を選択する: ChartObjects.Select

2014-09-14T17:41:00.000+09:00

アクティブなシートの図を選択するには、ChartObjects.Selectプロパティを指定します。ここで、図は"fig01"という名前ということにします。

Sub test()
ActiveSheet.ChartObjects("fig01").Select
End Sub

ワークシート名、グラフ名をそれぞれ別の変数に格納しておくと、大きいプログラムを作るときの自由度、視認性が格段にあがります。ただ上のような1行のプログラムを書くのと比べるとちょっと長くなってしまいますが。。。

Sub test()
Dim ws As Worksheet    'ワークシート型変数の定義
Dim fig As ChartObject 'ChartObject型変数の定義

Set ws = Worksheets("sheet1")    'ワークシート名を指定
Set fig = ws.ChartObjects("fig01")   'グラフを指定

fig.Select 'グラフを選択
End Sub

他のプログラムを動かす： SendKeys

2014-09-14T12:53:00.004+09:00

文章を書くのはWordが得意だったり、計算をするのはExcelだったりと、ソフトによって得意な分野が異なることから、用途に応じてソフトを使い分けていると思います。そこで、VBAで他のソフトを動かせるようになるとますます世界が広がります。 "SendKeys"はこの中でも一番ベーシックな関数で、キーストロークを他のプログラムに渡すものです。応用すると、プリントする際に、両面指定や白黒⇔カラー印刷の変更なども、このSendKeysを使って行うことができます。

まず最初の例として、メモ帳を立ち上げて、そこに文字を記録するプログラムです。

'新しくwindowを立ち上げる場合
Sub test()
Shell "Notepad.exe", 1
SendKeys "この文字列が記録されます"
End Sub

次は、既に立ち上がっているメモ帳に、文字を記入する場合です。この場合は、"AppActivate"関数を使って、そのwindowをアクティブにします。AppActivateには、該当のプログラムのタイトルバーに表示されている文字列を渡します。

'既に起動しているメモ帳に記録する場合
Sub test2()
AppActivate "notepad" 'タイトルバーの文字列を指定
SendKeys "この文字列が記録されます"
SendKeys "{Enter}" 'Enterキーを押すのに相当
End Sub

Ctrl、Shift、Altなどを押す場合はそれぞれ "^"、"+"、"%"で指定します。

'既に起動しているメモ帳に記録する場合
Sub test3()
AppActivate "notepad"          'タイトルバーの文字列を指定
SendKeys "この文字列が記録されます"
SendKeys "{Enter}"

SendKeys "^s"                  ' Ctrl + s（保存）
' SendKeys "+(ab)"                 ' Shiftを押しながらa, bと押す
SendKeys "%{F4}"                ' Alt + F4(閉じるコマンド)
End Sub

微分方程式 - ルンゲ・クッタ法

2014-09-14T12:39:00.000+09:00

ダウンロード

微分方程式に通常用いるのは、このルンゲ･クッタ法になります。なんといっても精度が高いです。ですが、「なぜこんな式になっているの？」に答えるには結構一生懸命面倒な式を解かないといけません。
私はアルゴリズムの専門家ではないので、この「なぜ？」の部分はあまり深く立ち入らず、ユーザーとして使うようにしていますが、いずれ解いてみたいと思っています。

Option Explicit

Public Const m = 100

Sub Runge_Kutta()

'変数の定義 -------------------------------------
Dim i%, j%, n%
Dim h#
Dim t(m) As Double, f(m) As Double
Dim k1#, k2#, k3#, k4#

'初期値の設定 ----------------------------------
n = Cells(2, 3) 'データの数
h = Cells(3, 3) '刻み
t(0) = Cells(5, 3) 'xの初期値
f(0) = Cells(6, 3) 'fの初期値

'xとfの計算 ------------------------------------
For i = 0 To n - 1 'VBAの配列は0から始まる！
t(i + 1) = t(i) + h
k1 = h * dfdt(t(i), f(i))
k2 = h * dfdt(t(i) + h / 2, f(i) + k1 / 2)
k3 = h * dfdt(t(i) + h / 2, f(i) + k2 / 2)
k4 = h * dfdt(t(i) + h, f(i) + k3)

f(i + 1) = f(i) + 1 / 6 * (k1 + 2 * k2 + 2 * k3 + k4)
Next i

'シート出力 ------------------------------------
For i = 0 To n - 1
Cells(i + 12, 2) = t(i)
Cells(i + 12, 3) = f(i)
Next i

End Sub

Function dfdt(t#, f#) As Double
dfdt = f - 2 * Exp(-t)
End Function

Sub Initialize()
Range("B12:C100") = ""
End Sub

関数の呼び出し: Call

2014-09-14T12:33:00.003+09:00

プログラムが大きくなると、一つのプロシージャの中に全てを書いていくと、後で振り返って直したり、プログラムを追加したり、ということが不可能なほど奇奇怪怪、冗長なプログラムになってしまう危険性があります。そこで機能毎にプログラムを小分けすることで、振り返りやすくなります。ここでは、関数を呼ぶ方法を紹介します。"Call xxx"と記述することで、関数xxxを呼び出すことができます。"Call"をつけるとわかりやすいですが、つけなくても動きます。下の例ではcells(2,2)、B2セルに2を代入する関数を作り、それをmainから呼び出しています。

' 関数を呼び出す
Sub main()
Call subfunction '関数を呼ぶ
End Sub

Function subfunction() '関数の定義
Cells(2, 2) = 2
End Function

引数をつけて関数を呼び出すこともできます。関数を定義する最初の functionの名前の後の括弧内に、呼び出す引数を定義します。ここではdouble型の引数を一つ定義していて、cells(2,2)、B2セルにその数値を書き込むようになっています。

' 引数を与えて関数を呼び出す
Sub main2()
Dim a# '変数(double型)を定義
a = 1.5 '変数の値を入力

Call subfunction2(a) '関数を呼び出し
End Sub

Function subfunction2(x#)
Cells(2, 2) = x
End Function

ループを抜ける: Exit

2014-09-14T12:26:00.002+09:00

Exitは、自分のタイミングでループ（繰り返し）を抜けるときに記述します。
複数のforなどで囲まれている場合は、一番内側のループを抜けることになります。

Sub test()
Dim i%, j%

For i = 1 To 10
Cells(i + 10, 2) = i

If i = 5 Then 'iが5だったらループを抜ける
Exit For
End If

Next i
End Sub

子プロシージャの呼び出し

2014-09-14T12:23:00.001+09:00

プログラムが大きくなると、一つのプロシージャの中に全てを書いていくと、後で振り返って直したり、プログラムを追加したり、ということが不可能なほど奇奇怪怪、煩雑なプログラムになってしまう危険性があります。そこで機能毎にプログラムを小分けすることで、振り返りやすくなります。

ここでは、プロシージャを呼ぶ方法を紹介します。"Call xxx"と記述することで、プロシージャxxxを呼び出すことができます。子プロシージャは同じモジュールに記述する必要は無いので、モジュール自身も上手にカテゴライズすると良いです。

' 親プロシージャ
Sub test()
Call subtest '別プロシージャの呼び出し
End Sub

'子プロシージャ（親から呼び出される側）
Sub subtest()
Cells(2, 2) = 1
End Sub

変数の型

2014-09-13T03:38:00.000+09:00

データの型の宣言。
私が良く使う変数は以下4つの変数です。他にも沢山変数があり、どれを使うかはしっかり考えるべきかもしれませんが、この4つは省略記号がついており、1行を短くするにはもってこいです。いろいろなホームページを見ると省略形を使わないように推奨しているところもありますが、実用上困っていないので、短い記述が好きな私は好んで%や#を使います。

No.	型名	型	省略記号
1	整数型	integer	%
2	長整数型	long	&
3	倍精度浮動小数点型	double	#
4	文字列型	string	$

この他に良く使うモノとして、Variant型があります。これは、「何でもいいよ〜」という型です。

Sub test()
Dim i as integer, j as integer 'ちゃんとした宣言
Dim k%, l% '省略した宣言。こちらの方が短い

i = 3
k = 4

Cells(2,2) = i
Cells(3,2) = k
End sub

微分方程式 - オイラー法

2014-09-11T00:31:00.001+09:00

ダウンロード

微分方程式を解くもっとも簡単な解法は、オイラー方程式です。
計算の精度は使い物になりませんが一番わかりやすい為、最初に見るのは良いと思います。

$ \frac{df}{dx}=3x^2 - 10 +5 $

計算で求めるため実際のfはわからなくてもよいのですが、ここでは解析的に解ける式（というか、検証の為そんな関数にした）を選びました。

$ f=x^3 - 5x^2 + 5x +f_0 $

離散的に解を求めると、
$ \frac{df}{dx}=\frac{f_{i+1}-f_{i}|}{h} $
ここで、全部分かっているi番目の値を基準にしてi+1番目が求められるように、式の順番を入れ替えます。
$ f_{i+1}=f_i + h*\frac{df}{dx} $

結果：
初期値付近ではほどほどの精度であるが、iが増えていくと誤差が大きくなる。

hは刻み幅。

Option Explicit

Public Const m = 100

Sub Euler()
'変数の定義 -------------------------------------
Dim i%, j%, k%, n%
Dim h#, x_ave#
Dim f(m) As Double, t(m) As Double

'初期値の設定 ----------------------------------
n = Cells(2, 3) 'データの数
h = Cells(3, 3) '刻み
t(0) = Cells(5, 3) 'xの初期値
f(0) = Cells(6, 3) 'fの初期値

'xとfの計算 ------------------------------------
For i = 0 To n - 1 'VBAの配列は0から始まる！
t(i + 1) = t(i) + h
f(i + 1) = f(i) + h * dfdt(t(i), f(i))
Next i

'シート出力 ------------------------------------
For i = 0 To n - 1
Cells(i + 12, 2) = t(i)
Cells(i + 12, 3) = f(i)
Next i
End Sub

Function dfdt(t#, f#) As Double
dfdt = f - 2 * Exp(-t)
End Function

Sub Initialize()
Range("B12:C100") = ""
End Sub

プログラム実行の高速化: Application.ScreenUpdating

2014-09-07T23:39:00.003+09:00

計算回数の多いプログラムを作ると、だんだんと実行速度が遅くなっていきます。
VBAはインタプリタで1行1行順次実行するという性質上これは避けられません。
その為少しでも高速化するために、記述を工夫する必要があります。
少し慣れたVBAのプログラマが必ずやるのは、画面の更新を一旦停止することです。
プログラムの最初に、
Application.ScreenUpdating = False
という記述を行い、実行中画面の更新を停止します。このたった一行の追加で
プログラムは何倍も高速化しますから、書くのを癖にしてしまいましょう。

Sub test()
   Application.ScreenUpdating = False    '画面更新停止

   For i = 1 To 10 ' 1から10まで繰り返す
Cells(i, 2) = i ' i行2列に、iを入力する
Next i

   Application.ScreenUpdating = True     '画面更新再開
End Sub