VBA – Resolving C# competition problem with VBA and Excel (2)

After the previous two articles, in which I have resolved two C# competition problems of the SoftUni, located here and here I have decided to resolve a new one. Pretty much, it turns out that it is really possible to do so, although VBA is considered a simple scripting language far away from the productivity of C#. Anyway, it is not the language but the person behind the keyboard, that makes the difference (you may cite me for this one!).

So, here comes the task from the test:

You are given N strings. Every string consists of letters, symbols, numbers and whitespace. All letters (a-z) and (A-Z) have values corresponding to their position in the English alphabet * 10 (a = 10, A = 10, b = 20, B = 20, …, z = 260, Z = 260). All symbols (like `~!@#$%^&*()_+{}:”|<>?-=[];’\,./) have a fixed value of 200 and all numbers have the value of their digit * 20 (0 = 0, 1 = 20, 2 = 40, 3 = 60, …, 9 = 180). The whitespace is ignored. Your task is to calculate the sum of all letters, all symbols and all numbers from the input and print them, each at a separate line.

How it is done? Following these steps:

1. Generate a for loop to examine each char of the string.
2. With a few conditional operators determine what exactly is the char (whether a letter, a number or a symbol)
3. At the end display the result.

In Excel it would look like this:

Here comes the code :

Option Explicit

Public Function fCalculator(sRest As String) As String

    Dim lLettersSum As Long
    Dim lSymbolsSum As Long
    Dim lNumbersSum As Long
    Dim i           As Long

    sRest = Trim(sRest)
    sRest = Replace(sRest, " ", "")

    sRest = UCase(sRest)

    For i = 1 To Len(sRest)
        If IsNumeric(Mid(sRest, i, 1)) Then
            lNumbersSum = lNumbersSum + (CLng(Mid(sRest, i, 1)) * 20)
            
        ElseIf (Asc(Mid(sRest, i, 1)) >= 65 And Asc(Mid(sRest, i, 1)) <= 90) Then
            lLettersSum = lLettersSum + ((Asc(Mid(sRest, i, 1)) - 64) * 10)
            
        Else
            If Asc(Mid(sRest, i, 1)) = 10 Then
                'Next Line in the Ascii table, just ignore it
            Else
                lSymbolsSum = lSymbolsSum + 200
            End If
        End If
    Next i

    fCalculator = lLettersSum & vbCrLf & lNumbersSum & vbCrLf & lSymbolsSum

End Function

Enjoy it!