While we work in Excel, sometimes may want to encrypt data for security purposes. We can easily create encrypt function to encrypt the data in Excel. Here, we will show step-by-step procedures to create encryption function in Excel.
Table of Contents
hide
Create Encryption Function in Excel: Step-by-Step Procedure
In this section, we will discuss the stepwise procedures to create an encryption function in Excel. A simple piece of VBA code will do the job. Follow the steps given below.
STEP 1: Prepare Dataset for Encryption Function
- Firstly, create a dataset in your worksheet. For demonstration, we have included a dataset having Salesman, Salary, State for a company. We will encrypt the data in Salary column.
STEP 2: Open VBA Window in Excel
- Next, go to the Developer tab and select Visual Basic to open the VBA window.
- Alternatively, you can press Alt + F11 to open the window.
STEP 3: Type Code in VBA Module for Encryption Function
- Afterward, select Insert > Module from the VBA window.
- Consecutively, a Module window will show up.
- Write the following code there.
Private Function iStrTPsd(ByVal Txt As String) As Long
Dim xVl As Long
Dim xCha As Long
Dim xSf1 As Long
Dim xSf2 As Long
Dim J As Integer
Dim xLn As Integer
xLn = Len(Txt)
For J = 1 To xLn
xCha = Asc(Mid$(Txt, J, 1))
xVl = xVl Xor (xCha * 2 ^ xSf1)
xVl = xVl Xor (xCha * 2 ^ xSf2)
xSf1 = (xSf1 + 7) Mod 19
xSf2 = (xSf2 + 13) Mod 23
Next J
iStrTPsd = xVl
End Function
Private Function iEncryption(ByVal Pd As String, ByVal InTx As String, Optional ByVal Encc As Boolean = True) As String
Dim xOfset As Long
Dim xLn As Integer
Dim J As Integer
Dim xCha As Integer
Dim xOutTx As String
xOfset = iStrTPsd(Pd)
Rnd -1
Randomize xOffset
xLn = Len(InTx)
For J = 1 To xLn
xCha = Asc(Mid$(InTx, J, 1))
If xCha >= 32 And xCha <= 126 Then
xCha = xCha - 32
xOfset = Int((96) * Rnd)
If Encc Then
xCha = ((xCha + xOfset) Mod 95)
Else
xCha = ((xCha - xOfset) Mod 95)
If xCha < 0 Then xCha = xCha + 95
End If
xCha = xCha + 32
xOutTx = xOutTx & Chr$(xCha)
End If
Next J
iEncryption = xOutTx
End Function
Sub xEncryption_Range()
Dim xxRg As Range
Dim xxPsd As String
Dim xxTxt As String
Dim xxEnc As Boolean
Dim xxRet As Variant
Dim xxCell As Range
On Error Resume Next
xxTxt = ActiveWindow.RangeSelection.Address
Set xxRg = Application.InputBox("You need to select range:", "Excel Encryption", xxTxt, , , , , 8)
Set xxRg = Application.Intersect(xxRg, xxRg.Worksheet.UsedRange)
If xxRg Is Nothing Then Exit Sub
xxPsd = InputBox("Type your password:", "Excel Encryption")
If xxPsd = "" Then
MsgBox "Your password can't be empty", , "Excel Encryption"
Exit Sub
End If
xxRet = Application.InputBox("Insert 1 to encrypt cells or Insert 2 to decrypt cells", "Excel Encryption", , , , , , 1)
If TypeName(xxRet) = "xBoolean" Then Exit Sub
If xxRet > 0 Then
xxEnc = (xxRet Mod 2 = 1)
For Each xxCell In xxRg
If xxCell.Value <> "" Then
xxCell.Value = iEncryption(xxPsd, xxCell.Value, xxEnc)
End If
Next
End If
End Sub
STEP 4: Run Code for Data Encryption
- Further, press the Run button from the VBA window.
- Instantly, a Macros dialog box will appear. Click on the Run button.
- After that, an Excel Encryption dialog box will appear.
- Select the desired range of cells whose data you want to encrypt. Press OK.
- Again, a dialog box will appear.
- Write the password there. Press OK.
- Consecutively, another dialog box will appear.
- Write 1 there for encrypting the data. Also, press OK.
Final Output
- Finally, come back to the worksheet and see the data of the selected range has been encrypted.
- Further, for decrypting the data run the code again and follow the steps given previously.
- Just write 2 in the last dialog box instead of 1.
- Also, don’t forget to enter the same password for decryption like you gave for the encryption.
- And you will see the encrypted data got decrypted again.
Read More: How to Decrypt Excel File Without Password
How to Encrypt Excel File with Password
In this section, we will discuss the stepwise procedures to encrypt an Excel file with password to open the file.
- Firstly, go to the File tab from the Excel ribbon.
- Then, select Save a Copy option from the pane.
- Afterward, select More options from the appeared window.
- Further, select Tools > General Options from the next window.
- Next, give a password in the Password to open section of General Options dialog box. Also, press OK.
- After that, rewrite the password in the Confirm Password dialog box. Press OK.
- Finally, save the file.
- From the next time, you have to give the password to open the Excel file.
Read More: How to Encrypt Cell Contents in Excel
Download Practice Workbook
You can download the practice workbook from here.
Conclusion
In this article, we have shown stepwise easy procedures to encrypt data in Excel. If you have any queries or suggestions, please leave a comment.
