関数を作る（VBA研究会議事録_12）

VBA研究会で論じられた内容を記録する議事録の12本目（2023/04/08開催分）です。前回の議事録はコチラになります。

hirocom777.hatenadiary.org

前回までシート間のコピーについて取り組んできました。

今回から新しい課題です。Excelではシートにワークシート関数を入力して色々な機能を実現できます。徐々に普及しているOffice365ですが、新しく出てきている関数が以前のExcelで使えないという問題があります。そこを何とかしてみようという訳です。

UNIQUE関数

UNIQUE関数は、指定された範囲からユニークな値を返す関数です。

support.microsoft.com

便利な関数なのですが、使用できるのはOffice365やExcel2021だけです。代わりになる関数があるといいですね。

今回のお題

それでは今回のお題です。まずは基本となるファンクションプロシージャを作りましょう。条件は以下になります。

セルの範囲を引数として受け取る
ユニークな値の1次配列を返す
配列化する値の最大、最小値を指定できる

実際のコード

それでは実際のコードです。以下の様になりました。このプロシージャは辞書を使用していますで、「ツール」→「参照設定」で、Microsoft Scripting Runtimeを参照しています。

'指定範囲の数値を配列で返します
'minValue　取得値の最小値、省略すると判定しない
'maxValue　取得値の最大値、省略すると判定しない
'checkType 判定方法   0-最小値以上で、最大値以下（デフォルト）
'                    1-最小値より大きく、最大値以下
'                    2-最小値以上で、最大値より小さい
'                    3-最小値より大きく、最大値より小さい
Private Function exp1(ByVal tarRng As Range, _
                        Optional ByVal minValue As Variant, _
                        Optional ByVal maxValue As Variant, _
                        Optional ByVal checkType As Long = 0) As Variant

    Dim dic As New Dictionary
    
    Dim rng As Range
    For Each rng In tarRng
        Do
            '数値以外は処理しない
            If Not IsNumeric(rng.Value) Then Exit Do
            '最小値以上（より大きい）か確認
            If Not IsMissing(minValue) Then
                If rng.Value < minValue Then Exit Do
                If (checkType And 1) And rng.Value = minValue Then Exit Do
            End If
            '最大値以下（より小さい）か確認
            If Not IsMissing(maxValue) Then
                If rng.Value > maxValue Then Exit Do
                If (checkType And 2) And rng.Value = maxValue Then Exit Do
            End If
            '重複していない値を登録
            If Not dic.Exists(rng.Value) Then
                dic.Add rng.Value, Null
            End If
         Loop While False
    Next rng
        
    exp1 = dic.Keys

End Function

複数の判定が成立したときのみ処理する

このプロシージャでは、取得した範囲のうち指定した条件に当てはまったデータのみを辞書に登録しています。条件とは、

数値型であること
指定した範囲の値であること
重複していない事

です。この様に複数の条件が成立したときのみ処理を失効したい場合には、Do-Loopで括った中で判定して成立していない場合はExit Doでスキップするという書き方があります。

Do
    判定_1が成立しない場合 Exit Do
    判定_2が成立しない場合 Exit Do
    判定_3が成立しない場合 Exit Do
                ・
                ・
                ・
    処理
Loop While False

数値範囲の判定

配列化する値の最大、最小値を指定できなければいけません。この値は引数のminValue、maxValueで指定するのですが、オプションとなっています。指定しない場合は判定を行いません。指定されていない状態を判定するにはIsMissing関数を使用します。IsMissing関数はオプションの引数が省略されているとTrueを返します。判定する引数はバリアント型でなければなりません。

また、判定方法は「以上、以下」と「より大きい、より小さい」の組み合わせができると便利でする引数のcheckTypeで指定しています。0~3の値で指定しているのですが、以下の組み合わせで判定しています。