【VBA】Excelの色付きセルのみ計算するカスタム関数を作ってみた

' 標準モジュールに記載

Function colorSUM(color As Variant, selectRange As range) As Double
    Dim colorRange As range
    Dim total As Double
    Dim colorIdx As Integer
    Application.Volatile
    colorIdx = colorJudgement(CStr(color))
    
    If colorIdx = 0 Then
        MsgBox "第一引数が誤っています。再度ご確認お願いします。", vbOKOnly + vbCritical
    Else
        For Each colorRange In selectRange
            ' 特定の色のみ条件に入るようにする
            If colorRange.Interior.colorIndex = colorIdx Then
                total = total + colorRange.Value
            End If
        Next colorRange
        colorSUM = total
    End If
End Function

Private Function colorJudgement(color As String) As Integer
    Dim colorDic As Object
    Set colorDic = CreateObject("Scripting.Dictionary")
    ' 黒色 RGB(0,0,0) #000000
    colorDic.Add "black", 1
    ' 白色 RGB(255,255,255) #ffffff
    colorDic.Add "white", 2
    ' 赤色 RGB(255,0,0) #ff0000
    colorDic.Add "red", 3
    ' 黄緑色 RGB(0,255,0) #00ff00
    colorDic.Add "lime", 4
    ' 青色 RGB(0,0,255) #0000ff
    colorDic.Add "blue", 5
    ' 黄色 RGB(255,255,0) #ffff00
    colorDic.Add "yellow", 6
    ' ピンク色 RGB(255,0,255) #ff00ff
    colorDic.Add "pink", 7
    ' 水色 RGB(0,255,255) #00ffff
    colorDic.Add "aqua", 8
    ' 茶色 RGB(128,0,0) #800000
    colorDic.Add "maroon", 9
    ' 緑色 RGB(0,128,0) #008000
    colorDic.Add "green", 10
    ' 紺色 RGB(0,0,128) #000080
    colorDic.Add "navy", 11
    ' オリーブ色 RGB(128,128,0) #808000
    colorDic.Add "olive", 12
    ' 紫色 RGB(128,0,128) #800080
    colorDic.Add "purple", 13
    ' 青緑色 RGB(0,128,128) #008080
    colorDic.Add "teal", 14
    ' 銀色 RGB(192,192,192) #c0c0c0
    colorDic.Add "silver", 15
    ' 灰色 RGB(128,128,128) #800080
    colorDic.Add "gray", 16

    If colorDic.Exists(color) Then
        colorJudgement = colorDic.Item(color)
    Else
        colorJudgement = 0
    End If
End Function