VBA – Sorting VBA array – Functions in VBA

Posted on February 14, 2015 by

In the current article, I will show how to resolve with VBA an entry exam for programming in HackBulgaria. The problems could be resolved in any language and I will use VBA for this last task. The program is actually the development of a cipher, thus it takes string as input and codes it as per the cipher. This is how the task looks like:

Caesar cipher

Caesar cipher is one of the simplest and most widely known encryption techniques. The method is named after Julius Caesar, who used it in his private correspondence.

You have the implement a function with the following signature: caesar_encrypt(str, n).

The argument str is of type string
The argument n is of type integer.
The function should return an encrypted string

The encryption can be represented using modular arithmetic by first transforming the letters into numbers, according to the scheme, A = 0, B = 1,…, Z = 25. Encryption of a letter x by a shift n can be described mathematically as,

Do not encrypt any non-alphabetical characters!

Example:

&gt;&gt;&gt; caesar_encrypt("abc", 1)
"bcd"
&gt;&gt;&gt; caesar_encrypt("ABC", 1)
"BCD"
&gt;&gt;&gt; caesar_encrypt("abc", 2)
"cde"
&gt;&gt;&gt; caesar_encrypt("aaa", 7)
"hhh"

>>> caesar_encrypt("abc", 1)

"bcd"

>>> caesar_encrypt("ABC", 1)

"BCD"

>>> caesar_encrypt("abc", 2)

"cde"

>>> caesar_encrypt("aaa", 7)

"hhh"

This is how the magic is done:

So, how does it looks like in vba?

Option Explicit
Public Const LETTERS_IN_ALPHABET = 26

Public Function caesar_encrypt(sInput As String, n As Integer) As String

    Dim sResult                 As String
    Dim i                       As Integer
    Dim iAsciiInput             As Integer
    Dim iAsciiOutput             As Integer
    
    sResult = sInput
    
    For i = 0 To Len(sResult) - 1
            
        iAsciiInput = Asc(Mid(sInput, i + 1, 1))
        iAsciiOutput = Asc(Mid(sInput, i + 1, 1))
        
        'Capital letters
        If iAsciiInput > 64 And iAsciiInput < 91 Then
        Mid(sResult, i + 1, 1) = Chr(((iAsciiOutput + n - 65) Mod LETTERS_IN_ALPHABET) + 65)
        
        'Small letters
        ElseIf iAsciiInput > 96 And iAsciiInput < 123 Then
        Mid(sResult, i + 1, 1) = Chr(((iAsciiOutput + n - 97) Mod LETTERS_IN_ALPHABET) + 97)
        
        Else
        'Others
        Mid(sResult, i + 1, 1) = Mid(sInput, i + 1, 1)
        
        End If
    Next i

    caesar_encrypt = sResult
    
End Function

Option Explicit

Public Const LETTERS_IN_ALPHABET = 26

Public Function caesar_encrypt(sInput As String, n As Integer) As String

Dim sResult As String

Dim i As Integer

Dim iAsciiInput As Integer

Dim iAsciiOutput As Integer

sResult = sInput

For i = 0 To Len(sResult) - 1

iAsciiInput = Asc(Mid(sInput, i + 1, 1))

iAsciiOutput = Asc(Mid(sInput, i + 1, 1))

'Capital letters

If iAsciiInput > 64 And iAsciiInput < 91 Then

Mid(sResult, i + 1, 1) = Chr(((iAsciiOutput + n - 65) Mod LETTERS_IN_ALPHABET) + 65)

'Small letters

ElseIf iAsciiInput > 96 And iAsciiInput < 123 Then

Mid(sResult, i + 1, 1) = Chr(((iAsciiOutput + n - 97) Mod LETTERS_IN_ALPHABET) + 97)

Else

'Others

Mid(sResult, i + 1, 1) = Mid(sInput, i + 1, 1)

End If

Next i

caesar_encrypt = sResult

End Function

Enjoy it! 🙂

Tagged with: Cipher, custom VBA formula in Excel, VBA

VBA – Sorting VBA array – Functions in VBA

Caesar cipher

Related posts: