## Basic substitution cipher

A twitter account belonging to NSA posted this weird message one morning in the beginning of May.

It is a basic substitution cipher meaning t=w, p=a and so on. The message is "*Want to know what it takes to work at NSA? Check back each Monday in May as we explore careers essential to protecting our nation*"

A famous substitution cipher is the Caesar cipher, rotating each letter a number of places.

According to wikipedia, the cipher was reasonably secure at the time because Caesar's enemies would have been illiterate. :-)

The following macro rotates each letter in cell B2 by a number found in cell B8.

VBA Code

Sub Encrypt() Dim enc As String Dim res As String enc = Range("B2") For i = 1 To Len(enc) If Asc(UCase(Mid(enc, i, 1))) < 91 And Asc(UCase(Mid(enc, i, 1))) > 64 Then If (Asc(UCase(Mid(enc, i, 1))) + Range("B8")) > 90 Then res = res & Chr(65 + (Asc(UCase(Mid(enc, i, 1))) + Range("B8") - 90)) ElseIf (Asc(UCase(Mid(enc, i, 1))) + Range("B8")) < 65 Then res = res & Chr(90 - (64 - (Asc(UCase(Mid(enc, i, 1))) + Range("B8")))) Else res = res & Chr(Asc(UCase(Mid(enc, i, 1))) + Range("B8")) End If Else res = res & Mid(enc, i, 1) End If Next i Range("B5") = res End Sub Sub Decrypt() Dim dec As String Dim res As String dec = Range("B5") For i = 1 To Len(dec) If Asc(UCase(Mid(dec, i, 1))) < 91 And Asc(UCase(Mid(dec, i, 1))) > 64 Then If (Asc(UCase(Mid(dec, i, 1))) - Range("B8")) > 90 Then res = res & Chr(65 + (Asc(UCase(Mid(dec, i, 1))) - Range("B8") - 91)) ElseIf (Asc(UCase(Mid(dec, i, 1))) - Range("B8")) < 65 Then res = res & Chr(90 - (64 - (Asc(UCase(Mid(dec, i, 1))) - Range("B8")))) Else res = res & Chr(Asc(UCase(Mid(dec, i, 1))) - Range("B8")) End If Else res = res & Mid(dec, i, 1) End If Next i Range("B2") = res End Sub

### Download excel *.xlsm file

### Category: Vba

Copy selected rows (checkboxes) (2/2)

This post describes how to copy selected rows using checkboxes. In a previous post I showed you how to add/remove […]Comments(31) Filed in category: Check-boxes, Excel, VBA

Counting conditionally formatted cells (vba)

UPDATE: It is not possible to count conditionally formatted cells using vba as far as I know, I recommend you […]Comments(26) Filed in category: Conditional formatting, Count values, Excel, User defined functions (udf), VBA

Extract unique distinct values from a filtered table (udf and array formula)

Robert Jr asks: Oscar, I am using the VBA code & FilterUniqueSort array to generate unique lists that drive Selection […]Comments(17) Filed in category: Excel, Sort values, Unique distinct values, User defined functions (udf), VBA

Run a macro from a drop down list (vba)

You can quickly run a macro using a drop down list. The drop down list may contain multiple macro names. […]Comments(17) Filed in category: Drop down lists, Excel, VBA

Comments(17) Filed in category: Excel, VBA

Comments(16) Filed in category: Count values, Excel, Frequency, User defined functions (udf), VBA

Auto resize columns as you type

Let me show you how to create self adjusting columns in excel with a few lines of vba code. Example, […]Comments(16) Filed in category: Excel, VBA

Create a unique distinct list of a long list without sacrificing performance using vba in excel

Question: hi all, thanks for the great formula/array formula. it works great. lately, i noticed that the array formula will […]Comments(14) Filed in category: Advanced filter, Excel, Unique distinct values, VBA

Find cells containing formulas with literal (hard coded) values

Sometimes you need to find formulas containing literals (hard coded values) in a workbook. I found this excellent UDF in […]Comments(12) Filed in category: Excel, User defined functions (udf), VBA

Consolidate sheets in excel (vba)

Question: I have multiple worksheets in a workbook. Each worksheets is project specific. Each worksheet contains almost identical format. The […]Comments(11) Filed in category: Combine/Merge, Excel, VBA

### 4 Responses to “Basic substitution cipher”

### Leave a Reply

**How to add a formula to your comment:**

<code>your formula</code>

**How to add VBA code to your comment:**

[vb 1="vbnet" language=","]

VBA code

[/vb]

**How to add a picture to your comment:**

Upload picture to postimage.org

Add picture link to comment.

**Contact Oscar**

You can contact me through this webpage

Hi Oscar! Interesting cipher example, I have changed your code using

mod function, which simplifies it a bit.

The mod-function can also be used in formulas.

Torstein,

thanks for your comment.

Your macro rotates the letter Z to ], it should be C, if I use the value 3.

Sorry, one ) lost in pasting, I suppose. The res = - line should be:

Hi,

in Line 8 it must be ...Range("B8") - 91), like in Line 27.

BR

Mr.Hood