カンマ区切り文言をユーザー関数で分割(ExcelVBA)

ゆーしゃん
2022.03.13
144 views

概要

カンマ区切り文言をユーザー関数を用いて自動分割する

◆ユーザー関数説明
=Get_Split(“,”,B$1,$A2)
第一引数：区切り文字
第二引数：取得区切り位置(最小1)
第三引数：区切り文言

↓実装イメージ動画

プログラムを作成した理由

先日、Twitterでの質問を拝見して思い出したのですが、
Excel標準機能を用いて、カンマ区切り文言を分割するのは意外と面倒くさいものです。

VBAを使わないならば、下記2点が定番かと思います。

しかし、ワークシート関数でsplit関数を作る定番はどうすればよいのだろうか？？
— ほえほえ＠スプシマン (@hoehoe1234) March 6, 2022

分割パターン１：データ「区切り位置」で分割

まず最初に思いつくのが、Excelタブにある「区切り位置」を指定して分割する方法です。

ただ、この方法は手動で設定する為、面倒です。
~~(10秒で出来るだろって？手動対応は1秒もやりたくないのです←)~~

分割パターン２：Excel関数

次に思いつくのが、Excel関数を駆使して分割する方法です。

これで自動的に分割出来るようにはなりましたが、

~~(私のExcel関数の組み方の問題かもしれませんが)~~
1個のカンマを区切るのに3列使っている為、
10個を区切るには30列も必要となってしまいます。
(メンテナンスも面倒…)

「分割パターン２：Excel関数」の考えで関数組んでいる時、
「Excel関数でもSplit関数があれば簡単なのに！」とないものねだりをしていました。

その際「標準関数で組んでも私以外に修正出来ない~~(しない)~~ならば、
ユーザー関数で組めばいいか。その方が(私個人としては)メンテナンスし易いし！」

と思い立ったのがキッカケです。

ソースコード

Option Explicit

'概要：文字分割し、指定位置の文言を返却
'関数使用例
'A2セル「3,5,7,38」に対し「=Get_Split(",",1,A2)」と指定し、一番目の「3」を取得

'第一引数：区切り文字
'第二引数：分割文言取得位置
'第三引数：対象セル


'第四引数：結合セル範囲
Function Get_Split(ByVal Delim As String, ByVal Position As Integer, ByVal Contents As String) As String
                                        
    '変数定義
    Dim Spilt_list As Variant

    'イベントの連鎖反応中止
    Application.EnableEvents = False
    
    '指定区切り文字で分割
    Spilt_list = Split(Contents, Delim)
    
    
    'この中でエラーが発生した場合、Errerに移行する
    On Error GoTo Errer
    
        '指定位置の文言をセルに返却
        Get_Split = Spilt_list(Position - 1)
    
    On Error GoTo 0
    
    '処理中止
    Exit Function

Errer:
    '配列範囲外を指定した場合等、エラー発生時には空白を返却
    Get_Split = ""
        
End Function