Excel VBAを使ったカスタム関数の開発と実用事例

カスタム関数は、Excelの標準関数にない機能を実現するために、VBAを使って作成されるユーザー定義関数です。これにより、独自の計算やデータ操作が可能になります。以下に、カスタム関数の開発方法といくつかの実用事例を紹介します。

【開発方法】

Excelを開き、[Alt] + [F11]キーを押してVBAエディターを起動します。

[挿入]メニューから[モジュール]を選択し、新しいモジュールを作成します。

モジュール内に、Functionキーワードを使用してカスタム関数を定義します。例: Function CustomFunctionName(Argument1 As DataType, Argument2 As DataType) As ReturnType

関数内に、処理を記述し、最終的な結果を関数名に代入します。例: CustomFunctionName = CalculationResult

[Ctrl] + [S]キーを押してプロジェクトを保存し、VBAエディターを閉じます。

【実用事例】

1.文字列の逆順表示関数:

このカスタム関数は、与えられた文字列を逆順にして返す機能を提供します。例えば、"Excel"を入力すると、"lecxE"を返します。

Function ReverseString(text As String) As String
Dim i As Integer
For i = Len(text) To 1 Step -1
	ReverseString = ReverseString & Mid(text, i, 1)
Next i
End Function

＜使用イメージ＞

2.営業日計算関数:

この関数は、指定された日数を加算または減算した後の営業日を返す機能を提供します。このコードでは土日のみ営業日から除外していますが、祝日のデータがあればカスタマイズにより祝日も除外することも可能です。

Function AddWorkdays(startDate As Date, numDays As Integer) As Date
Dim i As Integer
Dim currentDate As Date
currentDate = startDate
For i = 1 To Abs(numDays)
    Do
        currentDate = currentDate + Sgn(numDays)
    Loop While Weekday(currentDate) = 1 Or Weekday(currentDate) = 7 ' 週末をスキップ
Next i

AddWorkdays = currentDate
End Function

＜使用イメージ＞

3.セル内の特定文字数カウント関数:

この関数は、セル内の特定の文字がいくつ含まれているかをカウントする機能を提供します。

Function CountSpecificCharacter(text As String, targetChar As String) As Long
Dim i As Integer
Dim count As Long
count = 0
For i = 1 To Len(text)
    If Mid(text, i, 1) = targetChar Then
   		count = count + 1
	End If
Next i
CountSpecificCharacter = count
End Function

＜使用イメージ＞

4.複数セルのデータを連結して重複を除去する関数:

この関数は、複数のセルのデータを連結し、重複するデータを除去して返します。

Function UniqueConcatenate(rng As Range, Optional delimiter As String = ", ") As String
Dim cell As Range
Dim result As String
Dim elements() As String
Dim element As Variant
Dim isUnique As Boolean
For Each cell In rng
    elements = Split(cell.Value, delimiter)
    For Each element In elements
        isUnique = True
        If InStr(result, element) > 0 Then
            isUnique = False
        End If
        If isUnique Then
            result = result & delimiter & element
        End If
    Next element
Next cell

UniqueConcatenate = Mid(result, Len(delimiter) + 1)
End Function

＜使用イメージ＞

5.二つの整数の最大公約数 (GCD) を求める関数:

この関数は、2つの整数の最大公約数を求める機能を提供します。

Function GCD(num1 As Long, num2 As Long) As Long
If num2 = 0 Then
	GCD = num1
Else
	GCD = GCD(num2, num1 Mod num2)
End If
End Function

＜使用イメージ＞

6.二つの数の最小公倍数 (LCM) を求める関数:

この関数は、2つの整数の最小公倍数を求める機能を提供します。

Function LCM(num1 As Long, num2 As Long) As Long
LCM = Abs(num1 * num2) / GCD(num1, num2)
End Function

＜使用イメージ＞

7.2地点の位置情報（緯度経度情報）から地点間の直線距離を計算する関数:

この関数は、2点間の緯度経度に基づいて距離を計算する機能を提供します。

※テストした限り数メートル単位の誤差は出そうなので注意（より近似する値を出す方法ご存知の方いたらご連絡ください）。

Function GetDistance(lat1 As Double, lon1 As Double, lat2 As Double, lon2 As Double) As Double
' 引数をラジアンに変換する
lat1 = Application.WorksheetFunction.Radians(lat1)
lon1 = Application.WorksheetFunction.Radians(lon1)
lat2 = Application.WorksheetFunction.Radians(lat2)
lon2 = Application.WorksheetFunction.Radians(lon2)
' 緯度差と経度差を計算する
Dim dLat As Double
Dim dLon As Double
dLat = lat2 - lat1
dLon = lon2 - lon1

' 球面三角法で距離を計算する
Dim a As Double
Dim c As Double
Dim R As Double
R = 6371 ' 地球の半径（km）
a = Sin(dLat / 2) ^ 2 + Cos(lat1) * Cos(lat2) * Sin(dLon / 2) ^ 2
c = 2 * Application.WorksheetFunction.Asin(Sqr(a)) '修正：Atan2関数→Asin関数に変更
GetDistance = R * c * 1000 ' 1000を掛けてメートルに変換

End Function

＜使用イメージ＞

まとめ

VBAでワークシート上で利用できる関数を定義する事で、様々な可能性が広がります。マクロの実行許可が前提になってしまうのはデメリットですが、ワークシート関数にしておけばユーザにマクロを詳しく説明しなくても自由に使わせる事が出来るのはメリットになります。シチュエーションにより使い分けてください。