【ExcelVBA】汎用的で便利なユーザー定義関数

「こんな関数があったらいいな」を形にする

Excelのワークシートでユーザー定義関数、使ってますか？
シート上の情報だけで自動計算させるなら、ワークシート関数って便利ですよね。

ただ、「何でこういう関数ないの？」とか「こんな関数があればいいのに」と思うことは日々あります。
そんな時は手作業でどうにかしたり、結局VBA書いちゃったり。
どうせVBA書くなら、いろんなところで使い回せるように、アドインにしておくのが便利です。

と、いうわけで、私が普段使っているユーザー定義関数を置いておきます。
標準モジュールにPublicで記述したFunctionは、ワークシートや別ブックから呼び出せます。
アドインや使いたいファイルの標準モジュールにコピペしてください。

更新履歴

• 2019/10/03
  • 「シート名を取得」、「ブック名を取得」を追加

アドイン自体の作成と読み込みについて

以前書いた下記の記事なんかを参照のこと。
【Excel】シート名を部分一致で検索するアドインを作る - Qiita
【Excel】シート内のシェイプの文字列を正規表現で置換する - Qiita

ユーザー定義関数では、引数ごとの内容のツールチップが出せません。それぞれ順番と内容を覚えるしかないみたいです。
「関数の挿入」で入力すれば引数名と任意の説明をヘルプとして表示できるけど、セルに直接入力するときは無理っぽい。
ヘルプ表示の方法は、以下を参照。
Excel ユーザー定義関数に機能説明や独自ヘルプをリンクする方法

シート名を取得

標準のワークシート関数の組み合わせでも取得できるけど、覚えてないので毎回ググっている…なんて場合にどうぞ。
表示がおかしい時は再計算(デフォルトのショートカットキーだとF9)すれば直ると思います。
Application.Volatile入ってるのでシート内でなんかしら変更があれば再計算されるはずですが。

Public Function SHEETNAME() As String
    Application.Volatile
    SHEETNAME = ActiveSheet.name
End Function

　

ブック名を取得

標準のワークシート関数の組み合わせでも取得できるけど以下略。
これもApplication.Volatile入ってるので変更があれば再計算されて正しく表示されるはず。
未保存でファイルとして存在してないワークブック上で使うと、エラー値が表示されます。

Enum宣言したらインテリセンス利くかと思ってたんですけど、ユーザー定義のワークシート関数はEnumだろうが入力補完不可だそうで、Enum自体止めちゃいました。使用者側で引数を覚えざるを得ないの不便ですねえ…
でもよく考えたら標準ワークシート関数のMATCH関数の第3引数も-1～1までを数字で指定するし、ワークシート上からはEnum自体参照不可なんでしょうね。まあ標準関数は入力補完利きますけど。

• target：取得対象(省略可、省略時は0(ファイル名だけ)、1指定でパスだけ、2指定でフルパス)

Public Function BOOKNAME(Optional ByVal target As Integer = 0) As Variant
    Application.Volatile
    If Len(ActiveWorkbook.path) = 0 Then
        BOOKNAME = CVErr(xlErrNA)
        Exit Function
    End If

    Select Case target
        Case 0
            BOOKNAME = ActiveWorkbook.name
        Case 1
            BOOKNAME = ActiveWorkbook.path
        Case 2
            BOOKNAME = ActiveWorkbook.FullName
        Case Else
            BOOKNAME = ""
    End Select
End Function

　

正規表現で置換

参照設定がしてある場合、CreateObject("VBScript.RegExp")はアーリーバインドのNew RegExpで使えます。
複数行に対応しているので、\rや\nのあとの^や$にも一致します。

• value：元の文字列
• ptn：正規表現パターン
• rep：置換パターン
• ignoreCase：大文字小文字区別(省略可、省略時はFalse(区別する))

Public Function REGEXPREPLACE(ByVal value As String, ByVal ptn As String, ByVal rep As String, Optional ByVal  ignoreCase As Boolean = False) As String
    Application.ScreenUpdating = False
    With CreateObject("VBScript.RegExp")
        .Global = True
        .Pattern = ptn
        .ignoreCase = ignoreCase
        .MultiLine = True
        REGEXPREPLACE = .Replace(value, rep)
    End With
    Application.ScreenUpdating = True
End Function

正規表現パターンチェック

参照設定がしてある場合、CreateObject("VBScript.RegExp")はアーリーバインドのNew RegExpで使えます。
複数行に対応しているので、\rや\nのあとの^や$にも一致します。

• value：元の文字列
• ptn：正規表現パターン
• ignoreCase：大文字小文字区別(省略可、省略時はFalse(区別する))

Public Function REGEXPCHECK(ByVal value As String, ByVal ptn As String, Optional ByVal ignoreCase As Boolean = False) As Boolean
    Application.ScreenUpdating = False
    With CreateObject("VBScript.RegExp")
        .Global = True
        .Pattern = ptn
        .ignoreCase = ignoreCase
        .MultiLine = True
        REGEXPCHECK = .test(value)
    End With
    Application.ScreenUpdating = True
End Function

指定範囲の文字列結合

～Excel2013用として作りました。CONCATENATE関数だと単一セルでしか指定できないので。
Excel2016以降では、標準で複数セルを含む範囲を複数指定可能なCONCAT関数が追加されています。しかし区切り文字や囲み文字の指定ができないので、CSV用の1行データを作るのはこちらのほうが向いているかもしれません。
ただし、関数名が重複するので、Excel2016以降でこちらの関数を利用する場合は、関数名を変更したほうがいいかもしれません。(2016以降を触ったことがないので標準関数と重複する関数名のユーザー定義関数が有効かどうかわかりませんすいません)

単純な表示内容の文字結合の場合の使用例：=CONCAT(A1:Z1,,,False)
ダブルクオート囲みのカンマ区切りにしたい場合の使用例：=CONCAT(A1:Z1,",","""")

• rng：結合するセル
• separator：区切り文字(省略可、省略時は空文字)
• wrapChar：囲み文字(省略可、省略時は空文字)
• isValue：セル取得対象(省略可、省略時はTrue(Valueプロパティ)、False指定でTextプロパティ)

※isValueについて
省略時またはTrueの場合、RangeオブジェクトのValueプロパティを、Falseの場合、Textプロパティを取得します。
Valueプロパティはセルの計算結果となる値、Textプロパティは表示結果です。
例えばセルの書式設定が「パーセンテージ」のセルに「30%」と表示されている場合、isValueがTrueなら「0.3」、Falseなら「30%」を取得します。

Public Function CONCAT(ByVal rng As Range, Optional ByVal separator As String = "", Optional ByVal wrapChar As String = "", Optional ByVal isValue As Boolean = True) As String
    Application.ScreenUpdating = False
    CONCAT = ""
    Dim cell As Range: For Each cell In rng
        If Len(CONCAT) > 0 Then
            CONCAT = CONCAT & separator
        End If
        If isValue Then
            CONCAT = CONCAT & wrapChar & cell.value & wrapChar
        Else
            CONCAT = CONCAT & wrapChar & cell.text & wrapChar
        End If
    Next
    Application.ScreenUpdating = True
End Function

蛇足。For文中の処理はIifで1行でも書けます。三項演算子好きな人向け。Iifはバグの仕様を理解して使う分には便利ですよね。

CONCAT = CONCAT & IIf(Len(CONCAT) > 0, separator, "") & wrapChar & IIf(isValue, cell.value, cell.text) & wrapChar

複数の指定範囲の文字列結合

指定範囲の文字列結合の派生であり、左記が有効である前提の関数です。利用時は両方の関数を使用可能な状態にする必要があります。
連続するセル範囲の中に含めたくないセルがある場合等、結合するセル範囲を複数指定したい場合はこちらをどうぞ。
ParamArrayでセル範囲の数を任意にしたため、その他の引数が省略不可になっています。

単純な文字結合の場合の使用例：=CONCAT2("","",True,A1:Z1,B2,C3:F5)
ダブルクオート囲みのカンマ区切りにしたい場合の使用例：=CONCAT2(",","""",False,A1:Z1,B2,C3:F5)

• separator：区切り文字
• wrapChar：囲み文字
• isValue：セル取得対象(True：Valueプロパティ、False：Textプロパティ)　※指定範囲の文字列結合参照
• rng：結合するセル(可変長引数、範囲が連続する必要なし)

Public Function CONCAT2(ByVal separator As String, ByVal wrapChar As String, ByVal isValue As Boolean, ParamArray rngArr() As Variant) As String
    Application.ScreenUpdating = False
    CONCAT2 = ""
    Dim rng As Variant: For Each rng In rngArr
        If Len(CONCAT2) > 0 Then
            CONCAT2 = CONCAT2 & separator
        End If
        CONCAT2 = CONCAT2 & CONCAT(Range(rng.address), separator, wrapChar, isValue)
    Next
    Application.ScreenUpdating = True
End Function

文字列にパラメータの文字列埋め込み

JavaのMessageFormat.format(String pattern, Object... arguments)みたいなものです。
Textのパラメータ順にvaluesの値で置換します。同じインデックスのパラメータがある場合、すべて置換します。
下記の使用例だと「{0}」にはどちらもA1セルの値が入ります。

使用例：=FORMATTXT("【{0}】{1}：{0}", A1, B2)

• text：フォーマット
• values：埋め込み文字列(可変長引数)

Public Function FORMATTXT(ByVal text As String, ParamArray values() As Variant) As String
    Application.ScreenUpdating = False
    FORMATTXT = text
    Dim i As Integer: For i = 0 To UBound(values)
        FORMATTXT = Replace(FORMATTXT, "{" & i & "}", values(i))
    Next
    Application.ScreenUpdating = True
End Function

　

気が向いたら追加とかします。