Results 1 to 1 of 1
  1. #1
    Join Date
    Sep 2003
    London, England

    Unanswered: Adding numerical values of letters

    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!
    Last edited by JLR; 08-17-09 at 08:35.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts