Obfuscate a SHEET?

[ronjon65]

I know you can obfuscate VBA code, but can you actually obfuscate a worksheet? For example

- move cell positions around randomly (but still linked to other sheets properly) so that is difficult to understand patterns.
- add random mathematical functions to cells such as +5 and -5 then the whole thing multiplied by 3.0/3.0 or something like that. It would slow things down, but may be confusing to interpret.
- remove any cells that just contain text (which can be used as notes).

Just wondering. Even if the sheet is VeryHidden, I noticed it can easily be seen in programs (Mac based). So the VeryHidden doesn't do a whole lot and a lot of my coding is in the sheets themselves.

[Kenneth Hobs]

The better thing would be to make a workbook open event to ask for a password if the GetEviron("username") is not one you want to have access. It would then unhide the sheets as needed. The workbook's close event would hide all but one sheet and protect each sheet with a password. I guess you could also obfuscate some formulas or data at that point and reverse it in the open event code. I think that is redundant though.

Keep in mind this only keeps honest people, honest.

[ronjon65]

Well, I am looking to protect content from people that may wish to reverse engineer the worksheet. That is why I am wondering about some sort of obfuscate process. There is no way to protect them gaining access to the sheet. I used to have a way, but that has been cracked. So I figure the crackers will always be able to gain access to the sheet. The goal is to make it very difficult to interpret.

[Kenneth Hobs]

If they crack the worksheet or workbook, then they can see what method you used to obfuscate the data.

If you want to go done that road, look into using something like MD5. e.g. http://answers.microsoft.com/en-us/office/forum/office_2013_release-excel/creating-an-md5-or-sha1-in-excel-or-other-office/2ce637a9-70d1-45f3-9275-e67a453033cd?db=5&auth=1

[ronjon65]

Hmm, MD5 doesn't look very reliable. I got a timeout in fact.

The thinking is that while you can't make it perfect, you want to make it more difficult to work with. That is where sheet obfuscation would help. Just because you can crack the sheet, doesn't mean you know how to address the obfuscation part. If I obfuscate the VBA as well, even if cracked, that will hopefully detract enough people from messing with it.

But I have never seen a program for sheet obfuscation. Only for VBA obfuscation.

[Kenneth Hobs]

I have not seen the VBA obfuscation. I am not sure how it would compile.

Even so, search for MD5 as there are other routines out there that can be added to your VBA more directly. The link was just the first search link that I found. Using a formula MD5 or other encryption method, you can change the name to make the encryption method less evident if the UDF is viewed in the formula toolbar.

Here is another method to explore. http://www.excelvbaprotection.com/

[ronjon65]

I will look into the MD5 stuff. I have not heard of that before.

As far as the other one, I tried it before and it really limited functionality. It just didn't work right and I had all kinds of problems with them. It is a balance between security and usability for sure.

[Kenneth Hobs]

The UDF method here looked like it might work for you.
http://www.mrexcel.com/forum/excel-questions/104386-md%25-hash-generator.html
or the file in:
http://microsoft.wmlcloud.com/forums/t/123968.aspx

[SamT]

It is possible. I don't know how effective it would be.
Write some code to convert all formulas in the workbook to using absolute cell references.
Then you can just start moving cells around while letting Excel take care of cell references.

Any formulas with lookup functions would of course fail since they depend on patterns.

Replace some Formulas with obfuscated User Defined Functions.
Replace all Names, Procedure declarations, Variables, and Constants with same length random strings.
Replace some Variables with Swapping Functions.
Declare everything as Type Default (Variant)
Scatter everything across multiple Modules
Use Cell row and column numbers as values
Use Named Ranges with name Strings long enough that the Defined Names Dialog truncates the string. The untruncated parts can be identical.


Formula = "=DKOVKA($A$1, $G$56)"

Public WIGJWU

Public Function VIDJMA()
VIDJMA =WIGJWU
End Function

Public Function DKOVKA(JCISKS, LODTAU)
GUPTAS = GJUDFPS(JCISKS)
DKOVKA= GUPTAS*LODTAU
End Sub

Function GJUDFPS(BJFKSO)
GJUDFPS = WIGJWU/SDJOWPV(BJFKSO)
End Function

Function SDJOWPV(KDIGHN)
Row number used as a constant or variable value
SDJOWPV=KDIGHN.Row
End Function

Use Functions as Formula Contants

Formula = "=XYZ($A$25)"

Public Function XYZ(a)
'Range Reference is ignored
XYZ = 3
End Function

[ronjon65]

Oh man, this stuff looks complicated and messy to implement. I was hoping for something more automated or proved out...basically something simple that would just make things a little more difficult to understand.

[Aflatoon]

I'm curious as to why you would need this. If your workbook is that important, I'd imagine it needs auditing at some point, which wouldn't be fun if you do this.

Anyway, a simple-ish solution would be to create a defined name for every formula in your workbook and use those in cells rather than the original formulas. Combine that with Sam's suggestion about name length and I think the workbook would become a real pain to decipher.

Here's a simplistic example:

Sub fuscate() Dim n As Long
Dim ws As Worksheet
Dim rgFormulas As Excel.Range
Dim Cell As Range
Dim sFormula As String
Dim wb As Workbook


Const csNAME_PREFIX As String = "_00000000000000000000000000000000000000000000000000000000000000000000"


With Application
.ScreenUpdating = False
.Calculation = xlCalculationManual
End With


Set wb = ActiveWorkbook


For Each ws In wb.Worksheets
ws.Activate
On Error Resume Next
Set rgFormulas = ws.UsedRange.SpecialCells(xlCellTypeFormulas)
On Error GoTo 0
If Not rgFormulas Is Nothing Then
For Each Cell In rgFormulas
If Left(Cell.Formula, Len(csNAME_PREFIX)) <> csNAME_PREFIX Then
n = n + 1
If Cell.HasArray Then
sFormula = Cell.CurrentArray.FormulaR1C1
wb.Names.Add Name:=csNAME_PREFIX & n, RefersToR1C1:=sFormula
Cell.CurrentArray.FormulaArray = "=" & csNAME_PREFIX & n
Else
sFormula = Cell.FormulaR1C1
wb.Names.Add Name:=csNAME_PREFIX & n, RefersToR1C1:=sFormula
Cell.Formula = "=" & csNAME_PREFIX & n
End If
End If
Next Cell
Set rgFormulas = Nothing
End If
Next ws

With Application
.Calculation = xlCalculationAutomatic
.ScreenUpdating = True
End With


End Sub


But you should be aware that this will probably render your workbook really, really slow!

[SamT]

There are 26^32 (1.9E45) random combinations of Upper case 32 character Strings.

It wouldn't be that hard to code to fill a Dictionary with a few million random Key strings. Use "True" for each Item

Some code to loop thru all the Names in the Workbook to substitute Keys, setting each used Key's Item to "False"

Then Loop thru the dictionary one more time and if the Item is True, then add a Name to the Workbook referring to some random cell.

Perform this on a copy and distribute the copy. If you ever had to revise the original, rerun the entire obfuscation code on a new copy and distribute that one. With all new (random) Names hehehe.

IF possible it might be better to just turn the Workbook into an executable :dunno