Data Encryption In excel

Hello Guys,

Today, I have came up with topic of Data encryption in Excel. 

Many times we have seen that, we are required to share our excel files which have confidential data which we do not want to share with other person. 

In such a case we can encrypt our confidential data with the help of VBA in excel.

This is very very easy. We just need to run the below macro code and our data will get encrypt. The encrypted data can be decrypted again only using the same macro.  

So here is the code:

Sub Encrypt()
    Dim r, Rngselected As Range
    Dim i As Long
    Dim j, k, l As String
    Dim ans, temp
    Dim enc As Integer
    
    ans = InputBox("Do you want to encrypt or Decrypt the data. Enter your choice." & vbCr _
    & "Enter 1 to Encrypt" & vbCr & "Enter 2 to Decrypt")
    
    If ans = "1" Then
            'set encrypt value
            enc = 40
        ElseIf ans = "2" Then
            ' set decrypt value
            enc = -40
        Else
            MsgBox ("You have not enter the correct value.")
            Exit Sub
    End If
    
    Set Rngselected = Application.Selection
    For Each r In Rngselected
        l = ""
        temp = VBA.StrReverse(r)
        For i = 1 To Len(temp)
            j = Mid(temp, i, 1)
            k = Chr(Asc(j) + enc)
            l = l & k
        Next i
        r.Value = l
    Next r
End Sub

Steps to Run the code:

(Please find below link to download the excel file) 

Step 1 : Open a excel workbook and Press Alt+F11 key

Step 2. Insert a module and paste the above code.

            

Step 3. Go to any sheet and select any data to encrypt.

        

Step 4.  Press Alt+F8 key and select Macro "Encrypt"

    

Step 5: Press 1 to Encrypt the data or Press 2 if you want to decrypt the encrypted data.

You will see the selected data has been encypted. Follow the same process to decrypt the data again.

Output:

Link to download the Excel file contaning macro:

Download

If you want to develop any macro for any office product or automate your task in excel or any other MS office product you can reach out to my Fiverr account:

https://www.fiverr.com/users/sg_2021/seller_dashboard

sg_2021

Fiverr

Seller

Hi I am a software developer and have expertise into VBA, Macros and Microsoft office automation. I have 12 years of experience and have worked on various Microsoft office automation projects in Excel and Outlook.