ネタ元はこちら
暗号で会話ができれば、本当のチャレンジ成功か！？
— わえなび the Theory of Word-Excel (@waenavi_jp) October 11, 2019
問題
Excelを用いて、次の暗号を解読して、暗号で答えてください。
BMT NX YMJ HZWWJSY RNSNXYJW TK IJKJSXJ#エクセルクイズ pic.twitter.com/qTYr7ZmVWG
皆さん、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
