Let's Play with Excel (VB Design & Code of Snake & Ladder Game and 51 Excel Macros) - Softcover

PANDEY, ANURAG

Softcover

ISBN 13: 9798502654241

Publisher: Independently published, 2021

Synopsis

Two of 51 Macros from this book are given below:

Macros to Encode and Decode the Sheet

[Please check both the Macros by copying into VB Editor of Any Excel file. Then RUN the Macro.]

Sub Encode_the_Sheet_Selected_Rows_N_Columns()
On Error GoTo ERR
Dim S1, S2 As Variant
Dim change(20), CHANGE2(20), l, M As Integer
Dim ic, lc, ir, lr, hello, J, k As Long

For I = 1 To 20
If I < 11 Then
change(I - 1) = I * I
CHANGE2(I - 1) = I * 4
Else
change(I - 1) = I * 3
CHANGE2(I - 1) = I * 2
End If
Next
ic = Int(InputBox("Enter begining Column No from where you want to Encode the Data."))
If ic < 1 Or ic = Empty Then ic = 1
lc = Int(InputBox("Enter Last Column No upto where you want to Encode the Data."))
If lc < ic Or lc = Empty Then
hello = lc
lc = ic
ic = hello
End If

ir = Int(InputBox("Enter begining Row No from where you want to Encode the Data."))
If ir < 1 Or ir = Empty Then ir = 1
lr = Int(InputBox("Enter Last Row No upto where you want to Encode the Data."))
If lr < ir Or lr = Empty Then
hello = lr
lr = ir
ir = hello
End If
M = 0
For J = ir To lr

For k = ic To lc
S1 = ""
S1 = """" & Sheets("Sheet1").Cells(J, k).NumberFormat & """"
S1 = S1 & Sheets("Sheet1").Cells(J, k).Value
Sheets("Sheet1").Cells(J, k).NumberFormat = "General"
S2 = """"
l = 0
For I = 1 To Len(S1)
S2 = S2 & Chr(Asc(Mid(S1, I, 1)) + change(l) + CHANGE2(M))
l = l + 1
If l > 19 Then l = 0
Next
M = M + 1
If M > 19 Then M = 0
Sheets("Sheet1").Cells(J, k).Value = S2
Next
Next
Exit Sub
ERR:
MsgBox ERR.Number & " " & ERR.Description
End Sub

Sub Decode_the_Sheet_Selected_Rows_N_Columns()
On Error GoTo ERR
Dim S1, S2, s3 As Variant
Dim change(20), CHANGE2(20), l As Integer
Dim ic, lc, ir, lr, hello, J, k As Long

For I = 1 To 20
If I < 11 Then
change(I - 1) = I * I
CHANGE2(I - 1) = I * 4
Else
change(I - 1) = I * 3
CHANGE2(I - 1) = I * 2
End If
Next
MsgBox ("You are required to tell exact Begining and End Columns and Rows Nos. of Encoded Data.")
ic = Int(InputBox("Enter begining Column No of the Encoded Data."))
If ic < 1 Or ic = Empty Then ic = 1
lc = Int(InputBox("Enter Last Column No of the Encoded Data."))
If lc < ic Or lc = Empty Then
hello = lc
lc = ic
ic = hello
End If

ir = Int(InputBox("Enter begining Row No of the Encoded Data."))
If ir < 1 Or ir = Empty Then ir = 1
lr = Int(InputBox("Enter Last Row No of the Encoded Data."))
If lr < ir Or lr = Empty Then
hello = lr
lr = ir
ir = hello
End If
M = 0
For J = ir To lr

For k = ic To lc
S1 = ""
S1 = Sheets("Sheet1").Cells(J, k).Value
S2 = ""
l = 0
For I = 2 To Len(S1)
S2 = S2 & Chr(Asc(Mid(S1, I, 1)) - (change(l) + CHANGE2(M)))
l = l + 1
If l > 19 Then l = 0
Next
For I = 2 To Len(S2)
If Mid(S2, I, 1) = """" Then
s3 = Mid(S2, 2, I - 2)
S1 = Right(S2, Len(S2) - I)
Exit For
End If
Next
M = M + 1
If M > 19 Then M = 0
If Left(S2, 1) = """" And I <= Len(S2) Then
Sheets("Sheet1").Cells(J, k).Value = S1
Sheets("Sheet1").Cells(J, k).Select
Selection.NumberFormat = s3
End If
Next
Next
Exit Sub
ERR:
MsgBox ERR.Number & " " & ERR.Description
End Sub

Edition details

• Publisher: Independently published
• Publication date: 2021
• Language: English
• ISBN 13: 9798502654241
• Binding: Paperback
• Number of pages: 218