Hi folks

For some crazy reason I have been trying to make a function that adds the numerical value of letters (A = 1, B = 2, ...) in corresponding positions within pairs of equal length words and outputs the “word” that is the translation of their sum modulo 26. Thus summer(ace, bad) = CDI since a + b = C, c + a = D and e + d = I.

My futile attempt is here:

Public Function summer(P As String, Q As String) As String
Dim N As Integer
Dim X As Double
Dim Y As Double
Dim Z As Double

For N = 1 To Len(P)

X = Asc(UCase(Mid(P, N, 1))) - Asc("A") + 1
Y = Asc(UCase(Mid(Q, N, 1))) - Asc("A") + 1
Z = (X + Y - 1) Mod 26 + Asc("A")

summer = summer & Chr(Z)

Next N

End Function

Please can someone tell me why it doesn’t work (I just get #VALUE!) and what I should be doing? I am (obviously) a beginner when it comes to VBA.


Added later:
DON'T WORRY - I now realize that I should enter =summer("ace", "bad") rather than =summer(ace, bad) because strings are expected. It seems I have answered my own question!