[Excel]実用(?)ヒント集

このページに含まれるプログラム、ファイル等については、

1. 一応、著作権は放棄してません。転載等される方は私にご連絡ください。
2. 内容については万全を期していますが、利用については自己責任でお願いします。いかなる損害にも責任を負いません。
3. 動作確認は、(1)Windows 98+Excel 2000; (2)Windows XP+Excel 2002で行っています。

1. 表の行数を増やす
2. 自作関数を他のワークシートで使う
3. グラフのサイズをマクロで変えよう
4. ダイアログを使ってみよう
5. マクロのやり直し
6. あなたはきっと悪くない
7. セルの参照方法
8. プロシージャの引数
9. 進行度をダイアログで表してみよう
10. Application.Volatile
11. イベントの停止
12. ワークシートとの値のやり取り
13. Runメソッド
14. 仮想セル
15. 覚えにくい関数をまとめてみよう
16. 覚えにくい関数をまとめてみよう(その2)
17. 覚えにくい関数をまとめてみよう(その3)
18. 数式でセル参照をしてる？

今回はプログラムではありませんが、最近知ってとても便利だったことを紹介します。みんな知ってるかもしれませんが。
それは名前に関数が使えること。
表を作っていて、日付や金額等の欄に名前を付けている場合、表が伸びるたびにすべての名前の範囲を変更しているあなた。ちょっとした工夫で自動的に範囲も伸びていきますが、それを怠ったときのメンテナンスが大変。
そんなときは表全体に名前(例えば小遣い帳)を付けましょう。(タイトル行を入れるかどうかはそのときの都合によりますが。) そして、日付の入っている列(例えばA)に名前を付けたい場合、[挿入][名前][定義]で、名前を「日付」と入力し、この名前「日付」の参照範囲を以下のように入力します。

=INDEX(小遣い帳,0,1)

=INDEX(小遣い帳,0,2)

さて、下で自作関数の入ったワークブックを作ったわけですが、他のワークブックで使おうと思ったら、 どうしたら良いでしょう？ひとつには、

=test.xls!test()

=test()

Private Function test()
    test = 1
End Function

Public Function test2()
    test2 = 2
End Function

Public Function test3()
    test3 = test + test2
End Function

Private Sub test4()
    MsgBox test
End Sub

Public Sub test5()
    MsgBox test2
End Sub

=testp.test2()

Public i As Integer
Private p As Integer
Private Const s As String = "test"

私のVBAプログラミングの方法をここで書きたいと思います。私は解説本等を持っていませんので、勉強方法ともいえます。ポイントは

• マクロでしたいことを「新しいマクロの記録」を使って、Excelに記述させます。
• 汎用性を持たせるようにマクロを変更します。
• 分からないキーワードは選択してからF1キーを押してヘルプを見ます。
• 分からなかったらとにかく試す。

    Selection.Height = 2 'あくまで例です。

    ActiveSheet.Shapes("グラフ 1").ScaleWidth 0.69, msoFalse, msoScaleFromBottomRight
    ActiveSheet.Shapes("グラフ 1").ScaleHeight 0.73, msoFalse, msoScaleFromBottomRight

    Selection.Parent.Parent.Height = 2

    Dim nowShapeRange As ShapeRange
    If TypeName(Selection) = "ChartArea" Then
        Set nowShapeRange = Selection.Parent.Parent.ShapeRange
    Else
        Set nowShapeRange = Selection.ShapeRange
    End If
    nowShapeRange.Height = 30

8/10にもダイアログを使ったプログラムを紹介しています。Excelの「普通」の操作のために作られているので、使い勝手は悪いですが、上手に使えば下手なプログラムを組むより早く目標が達せられます。Border.xlsを試された方はいますか?色を取得するためにダイアログが出てくると思います。こんなもの私には作れません。 ここで、プログラムのミソを記したいと思います。

Option Explicit

Sub test()
    Dim c As Variant
    c = getColor
    If Not IsNull(c) Then MsgBox c
End Sub

Function getColor() As Variant
    If Application.Dialogs(xlDialogEditColor).Show(1) Then
        getColor = ActiveWorkbook.Colors(1)
        ActiveWorkbook.ResetColors
    Else
        getColor = Null
    End If
End Function

マクロを実行は基本的にやり直すことが出来ません。 あとで、選んだセルが間違っていることに気づいてもどうにもならないのです。 「基本的な命令ばっかり使っているのになぜ??」と思います。
ただし、ユーザーがやり直しを選択した場合に実行するマクロを指定することは可能です。

Dim temp As String

Sub test()
    With ActiveCell
        temp = .Address(External:=True)
        .Value = .Value * 2
    End With
    Application.OnUndo "Undo test", "utest"
End Sub

Sub utest(Optional dummy As Integer = 0)
    With Range(temp)
        .Value = .Value / 2
    End With
End Sub

1. 引数を指定できないので、マクロの実行と結局同じ
2. マクロ(test)を実行した時点でそれ以前にUndoがさかのぼれなくなる（2度以上Undoが実行できない）ので、一番最後のアドレスを覚えているだけで十分

ExcelVBAでプログラミングする場合、どう考えても正しく動くはずなのにそうならない場合が往々にしてあります。これを「仕様」といいます（ホンマか?）。以下はExcel2000での「仕様」ですので、他のバージョンでは異なるかもしれません。
「2重線で普通の太さで5番の色の枠線」と「普通の太さで2重線で5番の色の枠線」とでは何が違うでしょう?常識で言って何も変わりませんね。変わるわけ無いですよね?
ところが、Excelの世界では通用しなかったりします。

Option Explicit

Sub test()
    Dim b As Border
    Dim w As XlBorderWeight '線の太さ
    Dim l As XlLineStyle    '線の種類
    Dim c As XlColorIndex   '線の色
    w = xlMedium
    l = xlDouble
    c = 5
    Range("A1").Clear
    For Each b In Range("A1").Borders   'お勧め
        With b
            .Weight = w
            .LineStyle = l
            .ColorIndex = c
        End With
    Next b
    Range("A3").Clear
    For Each b In Range("A3").Borders
        With b
            .Weight = w
            .ColorIndex = c
            .LineStyle = l
        End With
    Next b
    Range("C1").Clear
    For Each b In Range("C1").Borders
        With b
            .LineStyle = l
            .Weight = w
            .ColorIndex = c
        End With
    Next b
    Range("C3").Clear
    For Each b In Range("C3").Borders
        With b
            .LineStyle = l
            .ColorIndex = c
            .Weight = w
        End With
    Next b
    Range("E1").Clear
    For Each b In Range("E1").Borders
        With b
            .ColorIndex = c
            .LineStyle = l
            .Weight = w
        End With
    Next b
    Range("E3").Clear
    For Each b In Range("E3").Borders
        With b
            .ColorIndex = c
            .Weight = w
            .LineStyle = l
        End With
    Next b
End Sub

VBAでセルを参照するときにどう書きますか?普通はRange("A1")=1とか書きますか?これは書いてはいませんがRange("A1").Value=1と書いていることと同じです。（このValueを「既定のプロパティ」と呼びます。）
セルの参照方法にはValueの他にFormula、Text、Value2があります。これらはどう違うのでしょう?具体例を書いてみます。（Value2はValueとほとんど変わらないので省略します。）

A1: 100
B1: =A1*10	（B1には通貨（￥）の書式を設定してあるとします。）

Msgbox Range("B1").Formula	'"=A1*10"
Msgbox Range("B1").Value	'1000
Msgbox Range("B1").Text	'"\1,000"

1. Formula…式が違うと等しくならない。「=1+3」と「'=1+3」(文字列)が区別できない。
2. Value…見た目が同じでも等しいとならない、逆に見た目が違っても同じになる場合がある。
3. Text…数値比較は出来ない、処理速度が若干遅い。

A1: ロレックス	A2: $1,000
B1: ロルックヌ	B2: \1,000

Msgbox Range("A2")=Range("B2")	'True

VBAでプログラムを作っていると、「マクロ一覧に出したくない」プロシージャというのが出来てきます。 例えば、

• ボタンのためのマクロ
• 内部処理用のSubプロシージャ
• 使用頻度の低い（一覧に載せるほどでもない）マクロ
• 隠しコマンド（笑）

1. Subプロシージャであること
2. Privateでないこと
3. 標準モジュール内にあること
4. 引数を持たないこと

TimerForm(ユーザーフォーム)

Option Explicit
Option Base 1

Private Full
Private Chara(3) As String
Private iCon As Integer

Private Sub Btn_Click()
    Me.Hide
End Sub

Public Sub Start(EndValue, Optional Counter = 0#)
    Dim Percent As Double, BarNum As Integer
    Me.Btn.Caption = "中止"
    Full = EndValue
    Chara(1) = "."
    Chara(2) = ".o"
    Chara(3) = ".oO"
    iCon = 0
    Percent = Counter / Full
    BarNum = Fix(Percent * 10)
    Me.LblTxt.Font.Name = "MS ゴシック"
    Me.LblTime.Font.Name = "system"
    Me.LblTxt = Format(Percent, "000%") & " 終了"
    Me.LblTime = String(BarNum, "O") & String(10 - BarNum, "-")
    Me.Show vbModeless
End Sub

Public Function Update(Counter) As Boolean
    Dim Percent As Double, BarNum As Integer
    If Me.Visible Then
        iCon = (iCon Mod 3) + 1
        Percent = Counter / Full
        BarNum = Fix(Percent * 10)
        Me.LblTxt = Format(Percent, "000%") & " 終了 " & Chara(iCon)
        Me.LblTime = String(BarNum, "O") & String(10 - BarNum, "-")
        DoEvents
        If Counter >= Full Then Me.Btn.Caption = "終了"
        Update = False
    Else
        Update = True
    End If
End Function

Sub test()
    Dim i As Integer, j As Long
    Const Max As Integer = 500
    TimerForm.Start Max
    For i = 0 To Max
        For j = 0 To 50
        If TimerForm.Update(i) Then GoTo Finish
        Next j
    Next i
Finish:
End Sub

自作関数をワークシートで使っている場合で、セルの内容が変わったのに計算結果が変化しないことがありませんか?この原因のひとつに関数で直接セルを参照していない場合があげられます。
具体的に言うと、関数の引数でセルrを指定している場合、

• セルrの内容で参照するセルを指定する場合
  

  Function MyIndirect(r As Range)
      MyIndirect = Range(r).Value
  End Function
  

• セルrの位置によって参照するセルを決める場合
  

  Function MySum(r As Range)
      MySum = r + r.Offset(1)
  End Function
  

Function test(r As Range)   'どのセルが変化を受けても1ずつセルの内容が増えていきます。
    Static i As Integer
    i = i + 1
    test = r + i
    Application.Volatile
End Function

イベント（ユーザーの操作など状況の変化に反応するマクロ）を記述している場合、「今だけ動くな」ということがあります。例えば、次の場合です。

1. 選択範囲の変化に対応するイベント(SelectionChangeイベント)内でセルを移動させる場合
2. セル内容の変化に対応するイベント(Changeイベント)内でセルの内容を変更する場合
3. イベントを実行させずにマクロを実行したい場合
4. イベントが記述されているシートの内容を変更したい場合

プログラム(シートのコードとして記入）

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    Application.EnableEvents = False
    '操作の記述
    Application.EnableEvents = True
End Sub

1. Visual Basic Editorの画面で「中断(‖)」ボタンを押すと、イベントを含むマクロが停止する。
2. シート内を変更する。
3. Visual Basic Editorの画面で「実行(▲)」あるいは「リセット(■)」ボタンを押すと、イベントを含むマクロが再開する。

ユーザー(ワークシート側)とVBAの間でデータをやり取りする際、その値はどこに保存しますか?

• セル

長所

ユーザー、VBAともに容易にアクセスすることができる。終了時に値を記憶することもできる。

短所

セルが一つ必要。不正な値に変更される危険がある。アクセスが遅い。

使用目的

入力データ

• 変数

長所

アクセスが高速。値の変化をVBAで管理できる。

短所

値の読み書きのマクロ・関数が必要。値が保存できない。

使用目的

一時的なパラメータ

プログラム

Const TestName As String = "Test"

Sub NameRead()
    MsgBox Evaluate(Names(TestName).Value)	'読み込み
End Sub

Sub NameWrite()
    Dim i As Integer
    i = 5
    On Error Resume Next
    Err.Clear
    Names(TestName).Value = i	'書き込み
    If Err.Number <> 0 Then		'名前がない場合
        Names.Add Name:=TestName, RefersToR1C1:="=" & i
    End If
    On Error GoTo 0
End Sub

ApplicationオブジェクトにはRunメソッドというものがあります。これはマクロを実行させるメソッドで、文字列で実行するマクロを指定します。通常のマクロ・関数の呼び出しと比べて以下のような利点があります。

1. プログラムを走らせている時点で(動的に)呼び出す関数を決定することができる。
2. 外部プロシージャ(別のワークブック)の関数を呼び出すことができる。

プログラム

Sub RunMyMacro(num As Integer)
    Application.Run Range("MyMacroList").Cells(num).Value
End Sub

プログラム2

Function RunTest(v As Variant)
    On Error Resume Next
    RunTest = Application.Run("RunTest" & TypeName(v), v)
    If Err.Number <> 0 Then '例外処理
        RunTest ="規定外：" & TypeName(v)
    End If
    On Error GoTo 0
End Function

Function RunTestInteger(i As Integer) As String
    RunTestInteger = "Integer: " & i
End Function

Function RunTestDouble(d As Double) As String
    RunTestDouble = "Double: " & d
End Function

Function RunTestRange(r As Range) As String
    RunTestRange = "Range: " & r.Address(False, False)
End Function

プログラム

Sub VirtualCell()
    Dim VCell As String
    Dim Result As Variant
    VCell = InputBox("式を入れてください。")
    Result = Evaluate(VCell)
    If Not IsError(Result) Then
        MsgBox Result
    Else
        MsgBox "エラー: " & CInt(Result)
    End If
End Sub

私は仕事柄、指数近似をよく使います。いつもはグラフに近似式を表示して傾きなどの値を読み取っているのですが、この値を別の式に代入してリアルタイムに変化させたい場合、いちいちグラフから読むわけにいきません。そこで、関数を使うのですが、例えばXの範囲がA1:C1,Yの範囲がA2:C2の以下のような場合、

	A	B	C
1	0	1	2
2	100	50	25

切片を求めようとすると次のようになります。

=Exp(Index(LinEst(Ln(A2:C2), A1:C1, True, True), 1, 2))

1. 縦型でも横型でもOK
2. ｘとｙの範囲がくっついていても離れていてもOK
3. 傾き、切片、r2を求めたい

プログラム

Public Function 指数近似(mode As String, x As Range, Optional y As Range = Nothing) As Double
    If データセット(x, y) Then
        With Application.WorksheetFunction
            Select Case mode
            Case "傾き"
                指数近似 = Evaluate("=Index(LinEst(Ln(" & y.Address & "), " & x.Address & ", True, True), 1, 1)")
            Case "切片"
                指数近似 = Evaluate("=Exp(Index(LinEst(Ln(" & y.Address & "), " & x.Address & ", True, True), 1, 2))")
            Case "r2", "R2", "相関係数"
                指数近似 = Evaluate("=Index(LinEst(Ln(" & y.Address & "), " & x.Address & ", True, True), 3, 1)")
            End Select
        End With
    Else
        指数近似 = CVErr(11)
    End If
End Function

Private Function データセット(x As Range, y As Range) As Boolean
    If y Is Nothing Then
        If x.Rows.Count = 2 Then
            Set y = x.Rows(2)
            Set x = x.Rows(1)
        ElseIf x.Columns.Count = 2 Then
            Set y = x.Columns(2)
            Set x = x.Columns(1)
        Else
            データセット = False
            Exit Function
        End If
    End If
    データセット = True
End Function

前回のおさらいです。物が一定の時間にある割合で減っていく様子は、次のような式で表されます。

    % = Ae-kt

[A]: =Exp(Index(LinEst(Ln(A2:C2), A1:C1, True, True), 1, 2))
[k]: =Index(LinEst(Ln(A2:C2), A1:C1, True, True), 1, 1)

プログラム

Public Function DegFunc(mode As String, x As Range, Optional y As Range = Nothing, Optional v As Variant) As Double
    Dim k As Double, A As Double, R2 As Double
    Dim RA As Variant
    Const DefY As Double = 100
    If DegFunc_Separation(x, y) Then
        If x.Cells.Count = 1 And y.Cells.Count = 1 Then
            RA = Evaluate("LinEst(Ln({" & DefY & "," & y & "}), {0," & x & "}, True, True)")
        Else
            RA = Evaluate("LinEst(Ln(" & y.Address & "), " & x.Address & ", True, True)")
        End If
        
        k = -RA(LBound(RA), LBound(RA))
        A = Exp(RA(LBound(RA), LBound(RA) + 1))
        R2 = RA(LBound(RA) + 2, LBound(RA))
        
        With Application.WorksheetFunction
            Select Case mode
            Case "k":               DegFunc = k                     '分解速度
            Case "A":               DegFunc = A                     'Y切片
            Case "r2", "R2":        DegFunc = R2                    '相関係数(決定係数)
            Case "DT50", "T1/2":    DegFunc = Log(2) / k            '半減期(50%になるまでの期間)
            Case "DT90":            DegFunc = Log(10) / k           '90%になるまでの期間
            Case "t":               DegFunc = Log(A / CDbl(v)) / k  '一定の割合になるまでの期間
            Case "%":               DegFunc = A * Exp(-k * CDbl(v)) '一定期間後の割合
            End Select
        End With
    Else
        DegFunc = CVErr(11)
    End If
End Function

Private Function DegFunc_Separation(x As Range, Optional y As Range) As Boolean
    If y Is Nothing Then
        If x.Rows.Count = 2 Then
            Set y = x.Rows(2)
            Set x = x.Rows(1)
        ElseIf x.Columns.Count = 2 Then
            Set y = x.Columns(2)
            Set x = x.Columns(1)
        Else
            DegFunc_Separation = False
            Exit Function
        End If
    End If
    DegFunc_Separation = True
End Function

さて、前回まで自作関数を作ってきました。この関数をほかのワークブックで使うにはどうしたらいいでしょう?

一つには標準モジュールをそのままコピーすることが考えられます。この方法は最も手軽ですが、使いたいワークブックに全部コピーするのはめんどくさいですよね。他には参照設定をするという方法があります。こちらは、以前紹介しています。ただ、こちらも使いたいワークブック全てで設定する必要があります。

今回は「アドイン」にしてしまうという方法を紹介します。実は前から、SuperSubのファイルに書いてあるのですが。
そのまま引用してみます。

常に使う予定であれば、「名前を付けて保存」の「ファイルの種類」で
「Microsoft Excel アドイン」(一番下です)を選択して保存し、
Excelを再起動した後、「ツール」→「アドイン」でSupersubにチェックを入れてください。
次回から、Excelを立ち上げるたびに自動的に起動されます。
(マクロの警告パネルも出なくなります。)

数式でセル参照をしているかどうかを調べる条件式を考えてみました。

プログラム

If ActiveCell.Formula <> ActiveCell.FormulaR1C1 Then
	MsgBox "数式内にセル参照があります。"
End If