記事キャッチ画像  (29) VBAからExcelワークシート関数を利用するには… 

 1. Excel 数式リボン の 数式パレット や 関数のヘルプ を参照すれば、関数の機能だけでなく引数の型も推測可能 

 2. ワークシート関数の引数の型は、「 Office デベロッパー センター 」文書も参考に! 
 
  「広告」  
 

 (29) VBAからExcelワークシート関数を利用するには… 

Excel メニューバーの [ヘルプ] - [更新プログラムのチェック] にて、適用されていないアップデートがあれば行ってください。 

Excel 2016 for Mac 環境での説明となりますので、 Mac版 Excel 2011 では( VBA が利用できますが )同じ結果にならない場合があるかもしれません。

現状、 VBE 内で日本語を記述すると(貼り付けのみ可能な場合あり)乱れたり、保存時にクラッシュすることが多いため、 VBAコード は「未変換の英数字のみ」で記述してください。(テキストエディタからの貼り付けは可能)

( ユーザー定義関数 のVBAコードを含んだ)前回使用した XLSM ファイルを、「マクロを有効」にして開きます。

今回は、今までに作成したプロシージャの一部を Excelワークシート関数 に置き換えます!
Application オブジェクトの WorksheetFunction プロパティにて、 WorksheetFunction オブジェクトを取得する。
( Excel ワークシート関数は、VBA から 同オブジェクトのメソッドとして利用可能 )

38


VBE にて 2つめのExcelシート 対応モジュールを、以下のコードの通りに変更するかそのままコピーして貼り付ける。(緑色の部分はコメントのため、省略可)

15


'----------------------------------------
'    Event: Worksheet_Change()
'----------------------------------------
Private Sub Worksheet_Change(ByVal Target As Range)

Rem If Application.Intersect(Target, Range("B2:B11")) Is Nothing Then
Rem Else
    If Not Application.Intersect(Target, Range("B2:B11")) Is Nothing Then
        If IsNumeric(Target.Value) Then

            Application.EnableEvents = False

                Target.Offset(0, -1).Value = Target.Value
Rem             ' 通貨型丸め処理(丸めた結果が偶数になるように、丸められる)
Rem             Target.Value = Round(Target.Value, 0)    ' 5捨6入 の場合あり
Rem             Target.Value = Int(Target.Value + 0.5)   ' VBA 4捨5入
                Target.Value = WorksheetFunction.Round(Target.Value, 0)    ' 4捨5入

           Application.EnableEvents = True

        End If
    Else
        MsgBox ActiveSheet.Name & " Event : " & Target.Address(False, False)
    End If

End Sub


続いて、 3つめのExcelシート 対応モジュールを、以下のコードの通りに変更するかそのままコピーして貼り付ける。(緑色の部分はコメントのため、省略可)

54


'----------------------------------------
'    Event: Worksheet_SelectionChange()
'----------------------------------------
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    Dim aRange As Range
    Dim sum As Long
    Dim n As Integer
    sum = 0
    Range("A1:I9").Interior.ColorIndex = 0
    If Target.Address(False, False) = "A1" Then
        For n = 1 To 9
            Cells(n, 1).Value = n
        Next n
        For n = 2 To 9
            Cells(1, n).Formula = "=A1*" & CStr(n)
        Next n

        Application.EnableEvents = False
            Range("B1:I1").Select
            Selection.Copy
            Range("B2:I9").Select
            ActiveSheet.Paste
            Range("A1").Select
        Application.EnableEvents = True

        MsgBox "Sum = " & Range("A1").Value
    Else
Rem     For Each aRange In Selection
Rem         sum = sum + aRange.Value
Rem     Next aRange

        sum = WorksheetFunction.Sum(Selection)

        MsgBox "Sum = " & sum
    End If
End Sub


 1. Excel 数式リボン の 数式パレット や 関数のヘルプ を参照すれば、関数の機能だけでなく引数の型も推測可能 

Excel 数式リボン の「数式パレット」や「関数のExcelヘルプ」を参照すれば、関数の機能や使用方法だけなく、引数の型も推測可能。

なお、数値のデフォルトは Double (倍精度浮動小数点型)となる。

16

第2引数の指定方法も解り、切り上げの ROUNDUP や 切り捨ての ROUNDDOWN 関数も用意されていることが判る。
小数点以下だけでなく、桁数に −3 を 指定すれば 「千円単位で四捨五入」可能とも…


50

引数として 最大255個までの 数値を列挙する以外に、マウスでセル選択範囲を指定するのと同様、セル(選択範囲)を示す Rangeオブジェクト の指定も可能なことが判る。

VBA で変数を使って自分で計算するより、(間違いもなく)手っ取り早いだろう。
目的に叶えば、最大限 Excel ワークシート関数 を利用すべきだ。


 2. ワークシート関数の引数の型は、「 Office デベロッパー センター 」文書も参考に! 

【Office デベロッパー センター】WorksheetFunction メンバー (Excel)

上記文書の該当の関数(メソッド)を参照すれば、正式な引数の型が参照可能。


次回は、 Excelブックの まだ説明していないイベント です!


お疲れ様でした!
[command] + [Q] にてExcel を終了時、「作業中のブック」を保存するか確認のダイアログ画面が表示されるため 「保存」しましょう。
現状、 VBE 内で日本語を記述すると(貼り付けのみ可能な場合あり)乱れたり、保存時にクラッシュすることが多いため、 VBAコード は「未変換の英数字のみ」で記述してください。(テキストエディタからの貼り付けは可能)


最後まで読んでいただき、ありがとうございます。
ぜひ、右側の「人気ブログランキング」投票に ご協力ください。
また、お越しくださいませ。
// アタル

  「広告」