エクセルクイズ 回答コード

ネタ元はこちら

暗号で会話ができれば、本当のチャレンジ成功か！？

問題
Excelを用いて、次の暗号を解読して、暗号で答えてください。
BMT NX YMJ HZWWJSY RNSNXYJW TK IJKJSXJ#エクセルクイズ pic.twitter.com/qTYr7ZmVWG

— わえなび the Theory of Word-Excel (@waenavi_jp) October 11, 2019

皆さん、CONCAT 関数で回答されていたのですが、
うちのExcelは、2013 なので、
CONCAT 関数が使えない

くやしいので、VBAでやったやつがこれ。

Public Enum ConvertMode
    cmDecode
    cmEncode
End Enum

Public Function convertText(ByVal sText As String, Optional ByVal mode As ConvertMode = cmDecode) As String

    Dim sResult As String
    Dim lLength As Long
    Dim i       As Long

    lLength = Len(sText)

    sResult = Space(lLength)

    For i = 1 To lLength
        If Mid$(sText, i, 1) <> " " Then
            If mode = cmDecode Then
                Mid$(sResult, i, 1) = decode(Mid$(sText, i, 1))
            Else
                Mid$(sResult, i, 1) = encode(Mid$(sText, i, 1))
            End If
        End If
    Next i

    convertText = sResult

End Function

Private Function decode(ByVal s As String) As String

    Const CONV_TEXT As String = "VWXYZABCDEFGHIJKLMNOPQRSTU "

    Dim lBaseCode   As Long
    Dim lIndex      As Long

    lBaseCode = Asc(UCase(Left$(s, 1)))
    lIndex = (lBaseCode - Asc("A") + Len(CONV_TEXT)) Mod Len(CONV_TEXT) + 1

    decode = Mid$(CONV_TEXT, lIndex, 1)

End Function

Private Function encode(ByVal s As String) As String

    Const CONV_TEXT As String = "FGHIJKLMNOPQRSTUVWXYZABCDE "

    Dim lBaseCode   As Long
    Dim lIndex      As Long

    lBaseCode = Asc(UCase(Left$(s, 1)))
    lIndex = (lBaseCode - Asc("A") + Len(CONV_TEXT)) Mod Len(CONV_TEXT) + 1

    encode = Mid$(CONV_TEXT, lIndex, 1)

End Function

FYFWFXMNN UH YT TKKNHJ MTXMNN