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

# 「こんな関数があったらいいな」を形にする
Excelのワークシートでユーザー定義関数、使ってますか？
シート上の情報だけで自動計算させるなら、ワークシート関数って便利ですよね。

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

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

<details><summary>**更新履歴**</summary><div>

- 2019/10/03
  - 「[シート名を取得](#シート名を取得)」、「[ブック名を取得](#ブック名を取得)」を追加
</div></details>

## アドイン自体の作成と読み込みについて
以前書いた下記の記事なんかを参照のこと。
[【Excel】シート名を部分一致で検索するアドインを作る - Qiita](https://qiita.com/11295/items/fdc1c2448d39078658eb)
[【Excel】シート内のシェイプの文字列を正規表現で置換する - Qiita](https://qiita.com/11295/items/de6f3c2b8ac4dcf1d825) 

ユーザー定義関数では、引数ごとの内容のツールチップが出せません。それぞれ順番と内容を覚えるしかないみたいです。
「関数の挿入」で入力すれば引数名と任意の説明をヘルプとして表示できるけど、セルに直接入力するときは無理っぽい。
ヘルプ表示の方法は、以下を参照。
[Excel ユーザー定義関数に機能説明や独自ヘルプをリンクする方法](https://social.msdn.microsoft.com/Forums/ja-JP/427e16de-0f6c-4f32-955a-8ce6c71cad77/excel-?forum=officesupportteamja)

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

```vb
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指定でフルパス)

```vb
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(区別する))

```vb
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(区別する))

```vb
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%」を取得します。

```vb
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はバグの仕様を理解して使う分には便利ですよね。

```vb
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：結合するセル(可変長引数、範囲が連続する必要なし)

```vb
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：埋め込み文字列(可変長引数)

```vb
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
```
　

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