マクロの自動記録で雛型作って適当に改造というお手軽さ。
一般企業の業務システムなんて殆ど何でも作れちゃうし!
でもトラブルの元になることもあったりする。
VBAはMicrosoft Officeに搭載されている統合開発環境Visual Basic Editor(VBE)上で開発する。
コードはMicrosoft Excel 2003と2007で動かしているが2003以前でも2007以降でもたぶん動く。
Sub HelloWorld() 'これはコメント、アポストロフィーのあとに入力する 'メモみたいなもん '実行結果はA1セルにHelloWorldと表示される Range("A1").Value = "HelloWorld" End Sub
オブジェクト名.メソッド名 オブジェクト名.プロパティ名 = 値Range("A1").Value はRange(セル)にValue(値の代入の設定を行うプロパティ)をするという意味である。
Sub 変数() 'あらかじめA1セルとB1セルに入力されている整数を掛けて計算結果をC1セルに表示する Dim i As Integer Dim j As Integer i = Range("A1").Value j = Range("B1").Value Range("C1").Value = i * j End Sub
Dim i As Integer Dim j As Integer次に用意した変数に値を代入する。今回はセルを参照してそこに入力されている値を代入する。
i = Range("A1").Value j = Range("B1").ValueC1セルに計算結果を代入する。
Range("C1").Value = i * j
int i,j,k;VBAではこのようにするとk以外はVariantになってしまう。
Dim i, j, k As Integer正しくは
Dim i As Integer, j As Integer, k As Integer他にもVBA(VB)は独特の仕様があるから気を付けよう。
型 | 扱えるデータ | メモ |
Integer | -32768から32767の整数のみ | |
long | -2147483648から2147483647の整数のみ | |
Single | ±1.401298E-45から±3.402823E38 | |
Double | ±4.94065645841247E-324から±1.79769313486232E308 | |
String | 文字列 | |
Byte | 0から255の整数 | バイナリファイルの読み書きなど。 |
Boolean | 真偽 | ゲームの当たり判定などで使う。知らない間に内部的な処理で使っていることもある。 |
Currency | -92233703685477.5808から922337203685477.5807 | お金を扱うときに使う。 |
Date | 日付と時刻 | |
Object | Excel上のオブジェクトを扱う。 | オートシェイプ、ワークシート、セルなど。 |
Variant | なんでも取り扱える | 変数を宣言せずに使うとこの型になる。 |
Sub 計算() Dim i As Double Dim j As Double i = 3 j = 2 '加 Range("D1").Value = i + j '減 Range("E1").Value = i - j '乗 Range("F1").Value = i * j '除 Range("G1").Value = i / j '除(整数) Range("H1").Value = i \ j 'あまり Range("I1").Value = i Mod j 'べき乗 Range("J1").Value = i ^ j '文字列結合演算子 Range("k1").Value = i & j End Sub
Sub 相対参照() 'セルG7を選択する Range("G7").Select 'ActiveCellは現在選択されているセルを参照する。 'Offsetはセルを移動させる。左の数値は上下の指定、右の数値は左右の指定。 '下に1つ、右に3つセルを移動させる。移動後のセルはJ8。 ActiveCell.Offset(1, 3).Select End Sub
-2,-2 | -2,-1 | -2,0 | -2,1 | -2,2 |
-1,-2 | -1,-1 | -1,0 | -1,1 | -1,2 |
0,-2 | 0,-1 | 基準 | 0,1 | 0,2 |
1,-2 | 1,-1 | 1,0 | 1,1 | 1,2 |
2,-2 | 2,-1 | 2,0 | 2,1 | 2,2 |
=$C$1+$D$1
Sub 絶対参照01() Range("A1").Select End Sub
Sub 絶対参照02() Range("B1,B3,B5,B7,B9").Select End Sub
Sub 絶対参照03() Range("C1:D5").Select End Sub
Sub 絶対参照04() Range("A1,C1:D5").Select End Sub
Cells(行番号,列番号).Select
Sub コピペ() '文字列の代入 Range("A1").Value = "( ^ω^)" Range("A2").Value = "\(^o^)/" Range("A3").Value = "(´∀`)" Range("A4").Value = "キタ━(゚∀゚)━!" Range("A5").Value = "vipper" 'コピペ Range("A1:A5").Copy 'Copy→Cutにするとカット Range("B1").PasteSpecial'コピー先の先頭を指定してペースト End Sub
Range("B1,B3,B5,B7,B9").Copyや
Range("A1,C1:D5").Copyのようなセルの指定はできない。
Sub 設定() 'フォント Range("A1:A10").Font.Name = "MS明朝" '太字 Range("B1:B10").Font.Bold = True '斜体 Range("C1:C10").Font.Italic = True '下線 Range("D1:D10").Font.Underline = True End Sub
Range("A1:A10").Font.Name = "MS明朝"Boldプロパティによる太字の設定。Falseは太字の解除を行う。
Range("B1:B10").Font.Bold = TrueItalicプロパティによる斜体の設定。Falseは斜体の解除を行う。
Range("C1:C10").Font.Italic = TrueUnderlineプロパティによる下線の設定。Falseは下線の解除を行う。
Range("D1:D10").Font.Underline = True
'セルの塗りつぶし Range("A1").Interior.ColorIndex = 10 '文字色の変更 Range("A1").Font.ColorIndex = 10
'セルを塗りつぶす Range("A1").Interior.Color = RGB(255, 255, 255) '文字色の変更 Range("A1").Font.Color = RGB(255, 255, 0)
Range("A1").Font.Size = 12
条件(セルの中の値や計算の結果など)によって処理を分けたいときにはIf文やCase文を使う。~
Sub 条件分岐If() Dim hundred As Integer hundred = Range("A1").Value If hundred < 100 Then 'A1セルに100未満が入力されたとき Range("A2").Value = "100未満" Else 'A1セルに100以上が入力されたとき Range("A2").Value = "100以上" End If End Sub
If 条件 Then '条件が満たされているときの処理 処理1 Else '条件が満たされていないときの処理 処理2 End If< は比較演算子で~未満を表す。意味は変数hundredが100未満のときである。
If hundred < 100 Then
演算子 | 記号 | メモ |
未満 | 1 < 2 | 2を含まないので2 < 2は成立しない。 |
以下 | 1 <= 2 | 2を含むので2 <= 2も成立する。 |
超過 | 2 > 1 | 2を含まないので2 > 2は成立しない。 |
以上 | 2 >= 1 | 2を含むので2 >= 2も成立する。 |
等号 | 1 = 1,1 <> 1 | |
文字列の比較 | Like "a" | 詳細は後述 |
Sub 文字列の比較1完全一致() Dim str As String str = Range("A1").Value If str Like "a" Then 'A1セルに半角のaが入力されたとき Range("A2").Value = "一致" Else 'それ以外 Range("A2").Value = "一致しない" End If End Sub
Sub 文字列の比較2部分一致() Dim str As String str = Range("A1").Value If str Like "*a*" Then '複数の指定はa*b*cのようになる 'A1セルに半角のaを含む文字列が入力されたとき Range("A2").Value = "一致" Else 'それ以外 Range("A2").Value = "一致しない" End If End Sub
Sub 文字列の比較3ある範囲内での一致() Dim str As String str = Range("A1").Value If str Like "[a-g]" Then '数字[1-9]・ひらがな[あ-こ]・かたかな[ア-コ]も可 'A1セルに半角のa,b,c,d,e,f,gのいずれか一文字が入力されたとき Range("A2").Value = "一致" Else 'それ以外 Range("A2").Value = "一致しない" End If End Sub
Sub 文字列の比較4ある範囲外での一致() Dim str As String str = Range("A1").Value If str Like "[!a-d]" Then 'A1セルにa,b,c,d以外のアルファベットが一文字だけ入力されたとき Range("A2").Value = "一致" Else 'それ以外 Range("A2").Value = "一致しない" End If End Sub
Sub 文字列の比較5任意の文字数() Dim str As String str = Range("A1").Value If str Like "???" Then 'A1セルに3文字の半角全角文字が入力されたとき Range("A2").Value = "一致" Else 'それ以外 Range("A2").Value = "一致しない" End If End Sub
Sub 文字列の比較6任意の桁数の数字() Dim str As String str = Range("A1").Value If str Like "###" Then 'こういう書き方も出来る[0-9][0-9][0-9] 'A1セルに3桁の半角全角数字が入力されたとき Range("A2").Value = "一致" Else 'それ以外 Range("A2").Value = "一致しない" End If End Sub
演算子 | 記号 | メモ |
論理積 | 条件1 And 条件2 | 条件1と条件2が両方満たされたとき。 |
論理和 | 条件1 Or 条件2 | 条件1と条件2のいずれかが満たされたとき。 |
排他的論理和 | 条件1 Xor 条件2 | 条件1と条件2のいずれかが満たされたとき。 |
論理否定 | Not 条件 | 条件が満たされなかったとき。 |
論理等価 | 条件1 Eqv 条件2 | 条件1と条件2が両方満たされたとき。もしくは両方満たされなかったとき。 |
論理包含 | 条件1 Imp 条件2 | 条件1と条件2が両方満たされなかったとき。 |
Sub 条件分岐Else_If() Dim hundred As Integer If hundred >= 100 Then 'A1セルに100以上が入力されたとき Range("A2").Value = "100以上" ElseIf hundred >= 90 Then 'A1セルに90以上が入力されたとき Range("A2").Value = "90以上" ElseIf hundred >= 80 Then 'A1セルに80以上が入力されたとき Range("A2").Value = "80以上" ElseIf hundred >= 70 Then 'A1セルに70以上が入力されたとき Range("A2").Value = "70以上" ElseIf hundred >= 60 Then 'A1セルに60以上が入力されたとき Range("A2").Value = "60以上" Else 'A1セルに60未満が入力されたとき Range("A2").Value = "60未満" End If End Sub
If 条件1 Then 条件1が満たされているときの処理 ElseIf 条件2 Then 条件2が満たされているときの処理 ElseIf 条件3 Then 条件3が満たされているときの処理 … End If
Sub 条件分岐Select_Case() Dim hundred As Integer hundred = Range("A1").Value Select Case hundred Case Is >= 100 'A1セルに100以上が入力されたとき Range("A2").Value = "100以上" Case Is >= 90 'A1セルに90以上が入力されたとき Range("A2").Value = "90以上" Case Is >= 80 'A1セルに80以上が入力されたとき Range("A2").Value = "80以上" Case Is >= 70 'A1セルに70以上が入力されたとき Range("A2").Value = "70以上" Case Is >= 60 'A1セルに60以上が入力されたとき Range("A2").Value = "60以上" Case Else 'A1セルに60未満が入力されたとき Range("A2").Value = "60未満" End Select End Sub
Select Case 変数や式など Case Is 条件1 条件1が満たされているときの処理 Case Is 条件2 条件2が満たされているときの処理 Case Is 条件3 条件3が満たされているときの処理 … Case Else どの条件も満たされなかったときの処理 End Select条件式を使わない場合(例えばString型の変数に特定の文字列が代入されたことを条件にするというようなとき)、ISは不要。
Case "雨"Toを使えば値の範囲を指定できる。
Case 1 To 10'1 <= n => 10 のとき
無限ループを作ってしまったらEscキーで強制終了しよう。
Sub 繰り返しFor_Next() Dim num As Integer Dim i As Integer num = Range("A1").Value 'A1セルに入力された数値だけループを繰り返す For i = 1 To num Step 1 'Cellsについてはセルの操作のCellsによる指定を参照 Cells(i, 2).Value = "このループは" & i & "回目です" Next i End Sub
For カウンタ変数名 = 初期値 To 終了値 Step 加算値(1なら省略可) 処理 Next カウンタ変数名(カウンタ変数名は省略可)初期値はループ開始時のカウンタ変数の値である。このカウンタ変数の値が終了値になるとループが終了する。
Sub 繰り返しDo_While() Dim num As Integer Dim i As Integer num = Range("A1").Value i = 1 'A1セルに入力された数値だけループを繰り返す Do While i <= num Cells(i, 2).Value = "このループは" & i & "回目です" 'ループが一周するごとにiに1が加算される(numの数値に近づく=終了条件に近づく) 'i++のような書き方は出来ない i = i + 1 Loop End Sub
Do While 終了条件 処理 Loopこのように後判定にすることもできる。後判定にすると必ず1回は処理を実行する。
Do 処理 Loop While 終了条件
Sub 二重ループ() '九九の表 Dim i As Integer Dim j As Integer '外側のループが1回終わる間に内側のループが9回行われる For i = 1 To 9 For j = 1 To 9 Cells(i, j).Value = i & "×" & j & "=" & i * j Next j Next i End Sub
Dim i As Integer Dim j As Integer → Dim k As Integer | i | | j | | k | | l | Dim l As Integer |____| |____| |____| |____|しかし配列は大きなひとつの箱に複数の中身が入っている。 そしてそれらは0から始まる添え字によって管理される。配列の中身のことを要素という
| 0 i | |.........| | 1 k | |.........| | 2 j | |.........| | 3 l | |_________|
Sub 一次元配列() '配列0から9の平均を求める '配列numの宣言 Dim num(9) As Integer 'この変数に配列に格納された値の合計を代入する Dim sum As Double '平均 Dim avg As Double Dim i As Integer '配列numの要素 num(0) = 30 num(1) = 67 num(2) = 97 num(3) = 51 num(4) = 39 num(5) = 44 num(6) = 75 num(7) = 23 num(8) = 11 num(9) = 41 '変数sum sum = 0 'カウンタ変数を添え字に紐付けて '配列に格納された値を呼び出してsumに足していき合計を求める For i = 0 To 9 sum = sum + num(i) Next Range("A1").Value = "合計" & sum avg = sum / 10 Range("A2").Value = "平均" & avg End Sub
Dim 配列名(扱いたいデータ数) as 変数の型通常、添え字は0からであるが次のようにすれば指定できる。
'1から10まで10個データを扱う配列 Dim num(1 to 10) As Integer次に扱うデータを配列に代入する。
配列名(添え字) = 数値や文字列等のデータ
|0-0 i |1-0 a |2-0 z | |.........|.........|.........| |0-1 k |1-1 s |2-1 x | |.........|.........|.........| |0-2 j |1-2 d |2-2 c | |.........|.........|.........| |0-3 l |1-3 f |2-3 v | |_________|_________|_________|
Sub 二次元配列() 'あらかじめA1セルとA2セルで指定している配列の要素の表示 '3行4列の二次元配列 Dim num(2, 3) As String Dim i As Integer Dim j As Integer '配列numの生成 num(0, 0) = "i" num(0, 1) = "k" num(0, 2) = "j" num(0, 3) = "i" num(1, 0) = "a" num(1, 1) = "S" num(1, 2) = "d" num(1, 3) = "f" num(2, 0) = "z" num(2, 1) = "x" num(2, 2) = "c" num(2, 3) = "v" '添え字の指定 i = Range("A1").Value j = Range("A2").Value '配列の要素の表示 Range("A3").Value = "配列(" & i & "、" & j & ")の表示" Range("A4").Value = num(i, j) End Sub
Dim num(1 To 3, 1 To 4) As String
Sub Array関数() 'Sub 一次元配列()を書き直した Dim num As Variant Dim sum As Double Dim avg As Double Dim i As Integer num = Array(30, 67, 97, 51, 39, 44, 75, 23, 11, 41) sum = 0 For i = 0 To 9 sum = sum + num(i) Next Range("A1") = "合計" & sum avg = sum / 10 Range("A2").Value = "平均" & avg End Sub
Sub セルの値を配列で利用() 'セル範囲にあらかじめ入力されている数値の平均と合計を求める Dim num As Variant Dim sum As Double Dim avg As Double Dim i As Integer Dim j As Integer num = Range("A1:B10") sum = 0 '行の指定、1行目から10行目を指定しているのでこのようになる For i = 1 To 10 '列の指定、A列からB列の2列なのでこのようになる For j = 1 To 2 sum = sum + num(i, j) Next Next Range("A11").Value = "合計" & sum avg = sum / 20 Range("A12").Value = "平均" & avg End Sub
num = Range("A1:B10")このように1列だけの場合も一次元配列ではなく二次元配列となる。
num = Range("A1:A10")
Sub 動的配列() '配列の要素をA列に貼り付ける。 '配列numの宣言 Dim num() As Integer Dim i As Integer '要素数を決める ReDim num(2) '配列numの生成 num(0) = 10 num(1) = 11 num(2) = 12 '配列の要素の追加 ReDim Preserve num(3) num(3) = 13 '配列の要素の貼り付け For i = 0 To 3 Cells(i + 1, 1) = num(i) Next i End Sub
Dim 配列名() As 変数の型動的配列は以下のようにして扱う要素数を決定する。動的配列のみで使える方法である。
ReDim 配列名(要素数)さらに配列を追加したいときはPreserveを使う。Preserveを使わない場合、すでに生成した要素は消えてしまうので注意。
ReDim Preserve 配列名(要素数)
Sub ワークシート() 'ワークシートの追加とセル範囲のコピペ Dim n As Integer n = Range("A1").Value 'C1からG10セルの範囲をコピー Range("C1:G10").Copy For n = 1 To n 'A1セルに入力した数字だけワークシートを追加する '後ろへ後ろへと追加していく Worksheets.Add After:=Worksheets(n) '現在選択中のワークシート(追加したワークシート)の名前をB列から拾う '1枚目はB1セル、2枚目はB2セル、3枚目はB3セル…を参照する ActiveSheet.name = Cells(n, 2) 'コピーしたセル範囲をどのセルを先頭にしてペーストするか '特に指定しなければA1セル ActiveSheet.Range("C1").Select 'ペースト ActiveSheet.Paste Next End Sub
Worksheets.Add After:= 1ワークシートの名前を設定・取得する。ActiveSheetの部分を
ActiveSheet.Name = Cells(n, 2)
Worksheets("ワークシート名").Protect"パスワード"保護の解除
Worksheets("ワークシート名").Unprotect"パスワード"
Worksheets("ワークシート名").Delete
Worksheets("ワークシート名").Visible = xlSheetVeryHidden
Excel関数やVBA関数の活用
Sub Excel関数() 'セル範囲の数値の合計・平均・順位を求めた後、昇順に並べ替え Dim s As Integer Dim avg As Double Dim r As Object 'SUM関数 s = Application.WorksheetFunction.sum(Range("B2:B11")) 'Average関数 avg = Application.WorksheetFunction.Average(Range("B2:B11")) Range("D1") = "合計" & s Range("E1") = "平均" & avg 'RANK関数 For Each r In Range("B2:B11") r.Offset(0, 1).Value = Application.WorksheetFunction.rank(r.Value, Range("B2:B11")) Next r '値を基準にして降順に並べ替え '順位を基準にして昇順に並べ替えても同じ結果が得られる Range("A1:C11").Sort , Key1:=Range("B1"), Order1:=xlDescending, Header:=xlYes, Orientation:=xlTopToBottom End Sub実行前
'書き方1 Application.WorksheetFunction.関数名(セル範囲) '書き方2 WorksheetFunction.関数名(セル範囲) '書き方3、Excel95時代の古い書き方、これでも動く Application.関数名(セル範囲)For_Each文について解説する。
For Each Object型やVariant型の変数名 In オブジェクトや配列 処理内容 Next Object型やVariant型の変数名(省略可)今回の処理は次の通りである。
'Object型の変数r Dim r As Object … 'rにセル範囲Range("B2:B11")(値のセル範囲)を格納 For Each r In Range("B2:B11") 'Range("B2:B11")の列をひとつずらしたRange("C2:C11")にrank関数の戻り値を代入 r.Offset(0, 1).Value = Application.WorksheetFunction.rank(r.Value, Range("B2:B11")) Next r …For_Each文プログラム例その1
Sub For_Each文01() '選択範囲に60以下の数字があったら赤く塗りつぶす Dim a As Object For Each a In Selection If a.Value < 60 Then a.Interior.ColorIndex = 3 Else End If Next a End SubFor_Each文プログラム例その2
Sub For_Each文02() '選択範囲にの値に10を足す,空セルの場合は代入 Dim a As Object For Each a In Selection a = a + 10 Next a End Sub並べ替えはSortメソッド使う。省略できる設定が多い。
セル範囲.Sort,Key1, Order1, Key2, Type, Order2, Key3, Order3, _ Header, OrderCustom, MatchCase, Orientation, SortMethod, _ DataOption1, DataOption2, DataOption3
'B列の並べ替え Key1:=Range("B1")
'降順 Order1:=xlDescending '昇順、規定値 Order1:=xlAscending
'1行目を見出しと判断、それ以外の範囲を並び替える Header:=xlYes '1行目を見出しではないと判断、セル範囲全体を並び替える、規定値。 Header:=xlNo 'Excelが自動判断 Header:=xlGuess
OrderCustom:=1
'区別しない MatchCase:=False '区別する MatchCase:=true
'行方向への並べ替え、規定値 Orientation:=xlTopToBottom '列方向への並べ替え Orientation:=xlLeftToRight
'ふりがなの50音順に並べ替え、ふりがな情報がない場合は音読み順、規定値 '例:男、女、女、男を昇順に並べ替えると男、男、女、女になる 'Key1に設定されている場合は男のkey2に設定した値の昇順と '女のkey2に設定した値の昇順といったように値がきれいに男女別に分かれる。 SortMethod:=xlPinYin '画数順に並べ替え SortMethod:=xlStroke
'数値と文字列を別々に並べ替え、規定値 '例:すいか2、1、すいか1、2を昇順で並べ替えると1、2、すいか1、すいか2になる DataOption1:=xlSortNormal '数値と文字列を同じとみなして並べ替え '例:すいか2、1、すいか1、2を昇順で並べ替えると1、すいか1、2、すいか2になる DataOption1:=xlSortTextAsNumbers
Sub vba関数() 'BMI値から痩せすぎ・標準・肥満を判断する。 'これまでのプログラムはセルにあらかじめ値を入力してから 'マクロを実行していたが今回は対話型のプログラムである Dim height As Double Dim weight As Double Dim bmi As Double Dim result As String height = InputBox(Prompt:="身長(m)を入力してください", Title:="入力") weight = InputBox(Prompt:="体重(kg)を入力してください", Title:="入力") bmi = weight / (height ^ 2) Select Case bmi Case Is <= 18.5 result = "痩せ過ぎです" Case Is <= 25 result = "標準です" Case Is <= 35 result = "肥満です" End Select MsgBox result, vbOKOnly,"判定結果" End Sub
InputBox(prompt,title,default,xpos,ypos,helpfile context)
項目名 | 内容 |
prompt | メッセージとして表示する文字列。改行コード(chr(10)とchr(13))を入れると改行もできる。 |
title | タイトル バーに表示する文字列。省略するとMicrosoft Excelと表示される。 |
default | テキストボックスにあらかじめ表示する文字列。省略すると空。 |
xpos | 画面左端からダイアログボックス左端までの距離。省略すると中央になる。 |
ypos | 画面上端からダイアログボックス上端までの距離。省略すると上から1/3になる。 |
helpfile | ヘルプボタンを追加する。 |
context | ヘルプファイルのパスを設定する。 |
ボタン名 | 内容 |
vbOKOnly | OKボタンのみ |
vbOKCancel | OKボタンとキャンセルボタン |
vbAbortRetryIgnore | 中止ボタンと再試行ボタンと無視ボタン |
vbYesNo | はいボタンといいえボタン |
vbYesNoCancelvbRetryCancel | はいボタンといいえボタンとキャンセルボタン |
vbRetryCancel | 再試行ボタンとキャンセルボタン |
'はいボタンが押されたとき If vbYes Then MsgBox "はいが押されました", vbOKOnlyCase文で使うときはInteger型の変数に格納して使う。変数に格納するときは()で囲む。
Dim msg As Integer 'はいボタンが押されたとき msg = MsgBox("ボタンを押して下さい。", vbYesNo, "確認") Select Case msg Case vbYes MsgBox "はいが押されました。", vbOKOnly
処理名 | 内容 |
vbOK | OKボタンが押された |
vbCancel | キャンセルボタンが押された |
vbAbort | 中止ボタンが押された |
vbRetry | 再試行ボタンが押された |
vbIgnore | 無視ボタンが押された |
vbYes | はいボタンが押された |
vbNo | いいえボタンが押された |
Excelではあらかじめ用意されているExcel関数だけではなくユーザー定義関数として自作した関数をワークシート上で使うことができる。
また戻り値を他のプログラムで使うことができる。これについては次項で説明する。
'偏差値を求めるユーザー定義関数、偏差値を求める関数はExcelには用意されていない '()内は引数、scoreは偏差値を求めたい点数 'areaは全体の点数、セル範囲を指定するのでVariant型 '一番右端のas Doubleは戻り値の型 Function 偏差値(score As Double, area As Variant) As Double Dim avg As Double Dim std As Double Dim dev As Double 'Excel関数であるAverage関数で平均点を求める avg = Application.WorksheetFunction.Average(area) 'Excel関数であるStDev関数で標準偏差を求める、 '平均値からどれだけ広い範囲に分散しているかという数値 'この数字が大きいほど上と下の差が大きい '逆に小さいほどみんな平均点近ということがわかる std = Application.WorksheetFunction.StDev(area) '偏差値=(点数-平均点)×10÷標準偏差+50 dev = (score - avg) * 10 / std + 50 '戻り値、プロシージャ名 = 戻り値のように書く 'VBA関数であるFix関数で小数点以下切捨て 偏差値 = Fix(dev) End Function
Function プロシージャ名(引数名 As 引数の型,引数名 As 引数の型…) As 戻り値の型 ~ End FunctionStDev関数は標準偏差を求めるExcel関数である。()内はセル範囲を指定する。
std = Application.WorksheetFunction.StDev(area)戻り値はプロシージャ名 = 戻り値のように書く。このプログラムではFix関数で戻り値の小数点以下を切り捨てている。
偏差値 = Fix(dev)ユーザー定義関数をExcel関数としてワークシート上で使うためには関数の分類の一番下のユーザー定義から選択する。
処理を分割したり規模の大きなプログラムを作る。
Sub 複数組み合わせ() '入力された整数を偶数か奇数か判定する Dim i As Integer i = InputBox(Prompt:="数値を入力してください", Title:="入力") 'テキストボックスに入力された整数をA1セルに代入 Range("A1").Value = i If i Mod 2 = 0 Then 'テキストボックスに入力された値が2で割り切れたら判定偶数を呼び出す Call 判定偶数 Else '2で割り切れなければ判定奇数を呼び出す Call 判定奇数 End If End Sub Sub 判定偶数() Range("A2").Value = "偶数" End Sub Sub 判定奇数() Range("A2").Value = "奇数" End Sub
Call プロシージャ名
Sub 複数プロシージャ() '入力された整数を偶数か奇数か判定する Dim i As Integer i = InputBox(Prompt:="数値を入力してください", Title:="入力") 'テキストボックスに入力された値をA1セルに代入 Range("A1").Value = i '判定プロシージャを呼び出し変数iのコピーを渡す Call 判定(i) End Sub Sub 判定(ByVal i As Integer) If i Mod 2 = 0 Then '変数iが2で割り切れたら偶数 Range("A2").Value = "偶数" Else '2で割り切れなければ奇数 Range("A2").Value = "奇数" End If End Sub
Call 変数を渡してやるプロシージャ名(引数として渡す変数名)次のようにCallを省略してプロシージャ名 変数名でもよいがやはり省略しないほうがよい。 Callを省略するときは次のように変数名を()で囲う必要はない。
判定 i引数を受け取るプロシージャはプロシージャ名の後ろの()内のByVal以降に引数名と引数の型を書く。
Sub プロシージャ名(ByVal 引数として受け取る変数名 As データ型,ByVal 引数として受け取る変数名 As データ型…)たとえば次のようなプログラムは間違い。
Sub 入力() Dim i As Integer Dim j As Integer Dim k As Integer i = Range("A1").Value j = Range("A2").Value k = Range("A3").Value Call 計算(i,j,k) End Sub Sub 計算(ByVal i As Integer,ByVal j as integr) Range("A4").Value = i*j*k End Subまた次のようなプログラムも間違い。変数i,j,kはDouble型として宣言されているが
Sub 入力() Dim i As Double Dim j As Double Dim k As Double i = Range("A1").Value j = Range("A2").Value k = Range("A3").Value Call 計算(i,j,k) End Sub Sub 計算(ByVal i As Integer,ByVal j As Integer,ByVal k As Integer) Range("A4").Value = i*j*k End Sub
高度なトピックやTips、説明漏れ、今後の指標など。
VBAはオブジェクト指向言語ではないが一応おまけ程度のクラスの仕組みはあるので
意識すればオブジェクト指向のような実装は可能。
クラスのメソッドを経由して親クラスの変数やメソッドにアクセスできない、
子クラス内から親クラスの変数やメソッドにアクセスできないなどの制限がある。
詳細は参考のオブジェクト指向を参照。
そのようなときはVB(ただし.net以前のVBである6.0等)のものを参考にすればよい。
VBAはOfficeの活用に特化したVBであり実行ランタイムも同じである。
つまりVBAもVBの一種でありVBでできることの多くはVBAでもできるのである。
VBの資産を有効活用してより効率的なプログラムの開発をしよう。
VBAの言語仕様はほぼ変わっていないがリボンの導入や最大列数・行数の増加、Officeアシスタントの廃止など
操作する対象であるExcel自体がずいぶんと様変わりしてしまった。
そのためプログラムの内容(メニューやツールバー周りや検索するといったようなもの)によっては
予期せぬ結果になってしまったりエラーになってしまう場合もある。
またFileSearchのように一部廃止・変更になっている関数やメソッドもある。
配布や移行などする場合はこれやこのようなMSの資料やヘルプ、オブジェクトブラウザに目を通したり
検索や書籍等でして調べるなどしてバージョン間の違いについてしっかり把握しておきたい。
ごく基本的なことを除いてあまり互換性はない。まず動かない。
With文を使うことによってプログラムの記述量を減らすことができる。
また速度的にも有利である。ただしコードが読みづらくなる。
次のプログラムをWith文を使い書き換える。
Sub 設定() 'セルの指定 Range("A1:A10").Select 'フォント 'Selectionは相対参照の項で使ったActiveCellと同じで現在選択されているセルを参照する。 'ただし複数セルを選択しているときActiveCellは先頭のセルしか参照しないので注意すること。 '今回の場合、ActiveCellを使うとA1セルしか以下の設定が反映されない。 Selection.Font.Name = "MS明朝" '太字 Selection.Font.Bold = True '斜体 Selection.Font.Italic = True '下線 Selection.Font.Underline = True End Sub
With文を使うと以下のようになる。With~End With間のすべてのSelectionを省略することができる。
オブジェクトの記述を1回にまとめてしまうのだ。
Sub 設定() 'セルの指定 Range("A1:A10").Select With Selection 'フォント .Font.Name = "MS明朝" '太字 .Font.Bold = True '斜体 .Font.Italic = True '下線 .Font.Underline = True End With End Sub
With文の基本形
With 重複しているメソッドやオブジェクト … End With
次のように複数のメソッドやオブジェクトを省略することもできる。
'セルの指定 Range("A1:A10").Select With Selection.Font 'フォント .Name = "MS明朝" '太字 .Bold = True '斜体 .Italic = True '下線 .Underline = True End With End Sub
このようにIf文(後述)などの他の構文と組み合わせることもできる。
Sub 文字列の比較1完全一致() Dim str As String str = Range("A1").Value With Range("A2") If str Like "a" Then 'A1セルに半角のaが入力されたとき .Value = "一致" Else 'それ以外 .Value = "一致しない" End If End With End Sub