Encrypt | E for Excel | Awakening Microsoft Excel Student Inside You

Sat 17 Jun 2017

VBA - A Function to Check whether a Given Number is Prime

By eforexcel|2017-06-20T19:42:14+00:00Saturday, June 17th, 2017|Categories: VBA|Tags: Cauchy, Decrypt, Decryption, Encrypt, Encryption, Euler, Gauss, Primality, Prime Number, vba|0 Comments

Below is an optimized function to check on the primality of a number. This function takes input number as Double, hence can be used to check upto a number having 15 significant digits. Whereas Long can take up to a maximum of 10 significant digits and maximum number it can support is 2,147,483,648.

First function is when pure number is passed, hence argument can be declared as Double. Hence, you will have to pass the value not the range.

Second function is when variant is passed as argument so that even range can be passed in this function.

Function IsPrime(Num As Double) As Boolean
    Dim i As Double
    If Int(Num / 2) = (Num / 2) Then
        Exit Function
        Else
        For i = 3 To Sqr(Num) Step 2
            If Int(Num / i) = (Num / i) Then
                Exit Function
            End If
        Next i
    End If
    IsPrime = True
End Function

1

2

3

4

5

6

7

8

9

10

11

12

13

Function IsPrime(Num As Double) As Boolean

Dim i As Double

If Int(Num / 2) = (Num / 2) Then

Exit Function

Else

For i = 3 To Sqr(Num) Step 2

If Int(Num / i) = (Num / i) Then

Exit Function

End If

Next i

End If

IsPrime = True

End Function

Function IsPrimeV(Num) As Boolean
    Dim i As Double
    If Int(Num / 2) = (Num / 2) Then
        Exit Function
        Else
        For i = 3 To Sqr(Num) Step 2
            If Int(Num / i) = (Num / i) Then
                Exit Function
            End If
        Next i
    End If
    IsPrimeV = True
End Function

1

2

3

4

5

6

7

8

9

10

11

12

13

Function IsPrimeV(Num) As Boolean

Dim i As Double

If Int(Num / 2) = (Num / 2) Then

Exit Function

Else

For i = 3 To Sqr(Num) Step 2

If Int(Num / i) = (Num / i) Then

Exit Function

End If

Next i

End If

IsPrimeV = True

End Function

Below is time performance for both functions -

The file used for checking time performance - Prime Number Checker

Mon 19 Dec 2016

Solution - Challenge 56 – Cryptography Challenge 5 – Fully Functional Caesar’s Shift Cipher Decrypter

By eforexcel|2017-06-08T17:43:35+00:00Monday, December 19th, 2016|Categories: Solutions, VBA|Tags: Caesar, Cipher, Code, Crypt, Cryptography, Decrypt, Decryption, Encrypt, Encryption, Julius, SHIFT, Substitution, vba|0 Comments

Below is a possible solution to Challenge 56 – Cryptography Challenge 5 – Fully Functional Caesar’s Shift Cipher Decrypter

(more…)

Sat 19 Nov 2016

Challenge 56 – Cryptography Challenge 5 – Fully Functional Caesar’s Shift Cipher Decrypter

By eforexcel|2017-06-08T17:41:58+00:00Saturday, November 19th, 2016|Categories: Challenges, VBA|Tags: Caesar, Cipher, Code, Crypt, Cryptography, Decrypt, Decryption, Encrypt, Encryption, Julius, SHIFT, Substitution, vba|1 Comment

We made ourselves familiarized with Caesar's Shift in Challenge 39 - Cryptography Challenge 1 - Caesar's Shift Cipher and made its decrypter in Challenge 53 - Cryptography Challenge 4 - Caesar's Shift Cipher Decrypter. We also made a fully functional encrypter in Challenge 40 – Cryptography Challenge 2 – Fully Functional Caesar’s Shift Cipher based on VBA solution as formula based solution has its own limitation.

The challenge before you is to write a VBA function to create a fully functional Caesar's Shift Cipher Decrypter. This should take cell or text as first argument and amount of shift as second argument to generate the Decrypted Text.

Hence, to generate answer in A3, we have called the function as =DecryptCS(A2,C2)

Note - The shift is only for the English Alphabets and case sensitive. Any other character like comma, space etc. should appear as they are.

The Excel file related to this challenge can be downloaded from Challenge 56 – Cryptography Challenge 5 – Decryption - Fully Functional Caesar’s Shift Cipher

The answer to this challenge would be published after a month i.e. on 19-Dec-16.

Tue 08 Nov 2016

Solution - Challenge 53 - Cryptography Challenge 4 - Caesar's Shift Cipher Decrypter

By eforexcel|2016-08-24T16:48:59+00:00Tuesday, November 08th, 2016|Categories: Solutions|Tags: Casesar, Cipher, Code, Crypt, Cryptography, Decrypt, Decryption, Encrypt, Encryption, Julian, SHIFT, Substitution|0 Comments

Below is a possible solution to the Challenge 53 - Cryptography Challenge 4 - Caesar's Shift Cipher Decrypter.

Put following formula and drag down

=IFERROR(CHAR(IF(CODE(LOWER(G2))-$B$3<97,CODE(G2)-$B$3+26,CODE(G2)-$B$3)),"")

The Excel file containing the solution can be downloaded from Solution - Challenge 53 - Decryption - Caesar's Shift Cipher

Sat 08 Oct 2016

Challenge 53 - Cryptography Challenge 4 - Caesar's Shift Cipher Decrypter

By eforexcel|2016-08-24T16:44:53+00:00Saturday, October 08th, 2016|Categories: Challenges|Tags: Casesar, Cipher, Code, Crypt, Cryptography, Decrypt, Decryption, Encrypt, Encryption, Julian, SHIFT, Substitution|2 Comments

We published a cryptography challenge Challenge 39 - Cryptography Challenge 1 - Caesar's Shift Cipher. The purpose of the challenge was to create an Excel based Encrypter.

This time, we need to create the de-crypter for the same. The Excel file related to this challenge can be downloaded from Challenge 53 - Decryption - Caesar's Shift Cipher

(more…)

Tue 27 Sep 2016

Solution - Challenge 50 - Cryptography Challenge 3 - Generate Tabula Recta

By eforexcel|2016-08-23T09:17:06+00:00Tuesday, September 27th, 2016|Categories: Solutions|Tags: Caesar, Cipher, Code, Crypt, Cryptography, Decrypt, Decryption, Encrypt, Encryption, Julius, Recta, SHIFT, Substitution, Tabula, Vignere|0 Comments

Below is a possible solution to the Challenge 50 - Cryptography Challenge 3 - Generate Tabula Recta

Put following formula and drag down and right

=CHAR(MOD(ROWS($1:1)+COLUMNS($A:A)-2,26)+65)

A workbook containing the above solution can be downloaded from Solution - Challenge 50 - Cryptography Challenge 3 - Generate Tabula Recta

Sat 27 Aug 2016

Challenge 50 - Cryptography Challenge 3 - Generate Tabula Recta

By eforexcel|2016-08-22T18:20:30+00:00Saturday, August 27th, 2016|Categories: Challenges|Tags: Caesar, Cipher, Code, Crypt, Cryptography, Decrypt, Decryption, Encrypt, Encryption, Julius, Recta, SHIFT, Substitution, Tabula, Vignere|1 Comment

Tabula Recta is supposed to be one of the foundation stones of cryptography. This was one of the earliest forms of encryption. The more about this can be read at https://en.wikipedia.org/wiki/Tabula_recta

One look at below and you will understand what it is. In upcoming challenges in future, Tabula Recta will be used. The first row is from A to Z and first column is from A to Z (Yellow row and column). You need to populate all other values by a single formula. (Yellow row and column is something which you can put as constant. But white area is something which you need to populate)

The solution to the above challenge will be published after a month i.e. on 27-Sep-16.

Mon 09 May 2016

Solution - Challenge 40 – Cryptography Challenge 2 – Fully Functional Caesar’s Shift Cipher

By eforexcel|2017-06-08T17:44:42+00:00Monday, May 09th, 2016|Categories: Solutions, VBA|Tags: Caesar, Cipher, Code, Crypt, Cryptography, Decrypt, Decryption, Encrypt, Encryption, Julius, SHIFT, Substitution|1 Comment

Below is a possible solution to the challenge - Challenge 40 – Cryptography Challenge 2 – Fully Functional Caesar’s Shift Cipher

1. Make a backup of your workbook
2. Open your workbook and ALT+F11
3. Locate your Workbook name in Project Explorer Window
4. Right click on your workbook name > Insert > Module
5. Copy paste the Macro code given below
6. Save your file as .xlsm
7. Call your function as =EncryptCS(A1,$B$2)

Function EncryptCS(PText, Shift) As String
    Dim Ws As Worksheet
    Dim i As Long
    Dim CText As String
    Dim PArr, CArr
    Application.Volatile
    PArr = Split(StrConv(PText, vbUnicode), vbNullChar)
    ReDim CArr(UBound(PArr) - 1)
    For i = LBound(PArr) To UBound(PArr) - 1
        If Abs(77.5 - Asc(UCase(PArr(i)))) < 13 Then
            If Asc(LCase(PArr(i))) + Shift > 122 Then
                CArr(i) = Chr(Asc(PArr(i)) + Shift - 26)
                Else
                CArr(i) = Chr(Asc(PArr(i)) + Shift)
            End If
        Else
            CArr(i) = PArr(i)
        End If
    Next i
    EncryptCS = Join(CArr, "")
End Function

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

Function EncryptCS(PText, Shift) As String

Dim Ws As Worksheet

Dim i As Long

Dim CText As String

Dim PArr, CArr

Application.Volatile

PArr = Split(StrConv(PText, vbUnicode), vbNullChar)

ReDim CArr(UBound(PArr) - 1)

For i = LBound(PArr) To UBound(PArr) - 1

If Abs(77.5 - Asc(UCase(PArr(i)))) < 13 Then

If Asc(LCase(PArr(i))) + Shift > 122 Then

CArr(i) = Chr(Asc(PArr(i)) + Shift - 26)

Else

CArr(i) = Chr(Asc(PArr(i)) + Shift)

End If

Else

CArr(i) = PArr(i)

End If

Next i

EncryptCS = Join(CArr, "")

End Function

A workbook containing the above solution can be downloaded from Solution - Challenge 40 – Cryptography Challenge 2 – Fully Functional Caesar’s Shift Cipher.

Tue 26 Apr 2016

Solution - Challenge 39 – Cryptography Challenge 1 – Caesar’s Shift Cipher

By eforexcel|2016-01-23T22:59:08+00:00Tuesday, April 26th, 2016|Categories: Solutions|Tags: Casesar, Cipher, Code, Crypt, Cryptography, Decrypt, Decryption, Encrypt, Encryption, Julian, SHIFT, Substitution|1 Comment

Below is a possible solution to the challenge Challenge 39 – Cryptography Challenge 1 – Caesar’s Shift Cipher.

Put below formula in G2 and drag down -

=IFERROR(CHAR(IF(CODE(LOWER(D2))+$B$3>122,CODE(D2)+$B$3-26,
CODE(D2)+$B$3)),"")

The workbook containing the solution can be downloaded from Solution - Challenge 39 - Caesar's Shift Cipher.

Sat 09 Apr 2016

Challenge 40 – Cryptography Challenge 2 – Fully Functional Caesar’s Shift Cipher

By eforexcel|2017-06-08T17:42:20+00:00Saturday, April 09th, 2016|Categories: Challenges, VBA|Tags: Caesar, Cipher, Code, Crypt, Cryptography, Decrypt, Decryption, Encrypt, Encryption, Julius, SHIFT, Substitution|1 Comment

We made ourselves familiarized with Caesar's Shift in last challenge - Challenge 39 - Cryptography Challenge 1 - Caesar's Shift Cipher

That challenge was for writing a formula to perform Caesar's Shift on a single alphabet. But now, we need to have a fully functional Caesar's Shift Cipher.

The challenge before you is to write a VBA function which takes cell or text as first argument and amount of shift as second argument to generate the Cipher Text.

Hence, to generate answer in A2, we have called the function as =EncryptCS(A1,B2)

Note - The shift is only for the English Alphabets and case sensitive. Any other character like comma, space etc. should appear as they are.

The answer to this challenge would be published after a month i.e. on 09-May-16.

Categories

Microsoft Links

Archives