Tuesday, August 30, 2011

Ever Wondered for Obfuscating VSTO .net Solutions? Here is the solution by Michael Zlatkovsky

Hi All,

I tried to obfuscate my VSTO COM code lots of time, but it never used to register the DLLs and Excel was never able to call my DLL successfully after Obfuscating. Although I have not tried it yet, but it seems we have got the solution :)

This is posted from a MSDN Forum thread. It was posted by Michael Zlatkovsky and is very very useful. Please see what he had posted :

Just to report back: after a few weeks of trying out various obfuscators, I finally did manage to obfuscate my VSTO project, both cheaply (only $179) and (now that I've got everything configured properly) painlessly.

The obfuscator that I settled on is called .NET Reactor (http://www.eziriz.com/dotnet_reactor.htm). It is VERY well-engineered piece of software, and costs remarkably little, $179 (compared to, say, Preemptive's Dotfuscator, which even after I emailed them and explained that I'm a freelance developer with little money, the best they could do is drop the price from ~$2000 to a "mere" $995). Unlike some of the other obfuscators, .NET Reactor works with VSTO solutions, and, also very importantly, it integrates with Visual Studio to make the obfuscation process just part of the build/publish one-click process. The importance of this integration is something you'll only discover when you need to obfuscate and sign assemblies manually -- which is a very tedious, 10-minute process of copying a file, obfuscating it, copying it back, re-signing the .dll.deploy file with MAGE, re-signing the .vsto file with MAGE, etc.

.NET Reactor's chief developer, Denis Mierzwiak, was VERY helpful and available when I needed some help in configuring .NET Reactor for VSTO. Initially my project was not lending itself to obfuscation, but Denis spent several hours with me on Skype to get things working, and kept sending me new releases by email as we continued to work out the kinks in the system. The Obfuscation now works 100%, integrates beautifully into Visual Studio, and is completely painless -- I actually can't recall a time when I was more pleased with a software product or with the level of technical support!

For those who'll be using .NET Reactor for VSTO, just a few hints:
1) It might be that the latest changes to the .NET Reactor (introduced for better VSTO support and integration) are still not in the "official" release -- you might want to email support@eziriz.com to ask about obfuscating an Office solution and to get the latest version.

2) It IS possible to fully obfuscate a VSTO solution (including renaming public types); since you are obfuscating a .dll file, however, that option is disabled by default. To enable it, go to the Settings tab, option #2 (Protection Settings), Obfuscation, and select "obfuscate public types".

3) Once you obfuscate public types, you will need to add a regular expression to EXCLUDE the several classes that VSTO expects (under .NET Reactor's settings -> Protections Settings -> Obfuscation -> Exclusions -> Regular Expressions). In the case of an Excel spreadsheet, those would be ExcelSampleWorkbook\.Sheet1;ExcelSampleWorkbook\.Sheet2;ExcelSampleWorkbook\.Sheet3;ExcelSampleWorkbook\.ThisWorkbook

4) Finally, if you obfuscate public types but exclude the required VSTO objects from obfuscation, be aware that code in those files (Sheet1, ThisWorkbook, etc) will obviously NOT be obfuscated. In my case, I created a "HelperDelegate" for each of those classes (i.e., ThisWorkbookDelegate) to hide the code behind the delegate. All that my Excel objects carry are event handlers (things like BeforeDoubleClick) and a reference to the delegate (so I can call Globals.ThisWorkbook.helper.myFunctionName())

Hope my experience is useful!

- Michael Zlatkovsky


Friday, August 26, 2011

Removing Duplicates with in the worksheet

Hello All,

I had asked by someone to create a code for removing a duplicate from worksheets.

The requirement was such that one keyword should only appears once in Column B in all the worksheets. So if a value (for example "Data-1234") appears in sheet 1 in column B, then it cannot appear again in Column B, in any of the worksheets. If it appears, then move it to Duplicate sheet.

So I used the following code. Thought it might be a help so posting it here. To make it work, you will need to add a reference to Microsoft Scripting Runtime.

Const ColumnToSearch As String = "B"
Const ColumnToSearch_i As Integer = 2
Private duplicates As Scripting.Dictionary

Sub MoveDuplicates()

Dim blankCounter As Integer
Dim rowCounter As Long
Dim duplicate As Worksheet
Dim sht As Worksheet
Dim runLoop As Boolean
Dim value As String
Dim added As Boolean
Dim lastRow As Long

runLoop = True

Set duplicates = New Scripting.Dictionary

Set duplicate = GetWorksheet(ActiveWorkbook, "DuplicateEntries")

If (duplicate Is Nothing) Then
Set duplicate = ActiveWorkbook.Worksheets.Add
duplicate.name = "DuplicateEntries"
End If

'get duplicate sheet

For Each sht In ActiveWorkbook.Worksheets

blankCounter = 1
rowCounter = 2
runLoop = True

If (sht.name <> "DuplicateEntries") Then

lastRow = GetLastRow(duplicate, 1)

If (lastRow > 1) Then
lastRow = lastRow + 2
End If

'enter the log

duplicate.Range("A" & lastRow).value = "Duplicate Entries Entered on : " & Now & " from Worksheet " & sht.name
duplicate.Range("A" & lastRow + 1).value = "'-"
duplicate.Range("A" & lastRow + 2).value = "'-"

While runLoop
If (blankCounter > 10) Then
runLoop = False
'check blank

value = Trim(sht.Cells(rowCounter, ColumnToSearch_i).value)

If (value = "") Then
blankCounter = blankCounter + 1
blankCounter = 1
added = AddSuccess(value)
lastRow = GetLastRow(duplicate, 1) + 1
If (added) Then
'dont do anything. not a duplicate
'Duplicate item, Move
MoveRow sht.Cells(rowCounter, ColumnToSearch_i), duplicate.Cells(lastRow, 1)
rowCounter = rowCounter - 1
End If

End If

End If

rowCounter = rowCounter + 1

End If
Next sht

End Sub

Private Function GetLastRow(sht As Worksheet, Optional Col As Integer = 1) As Long
Dim rng As Range

GetLastRow = sht.Cells(sht.Rows.Count - 1, Col).End(xlUp).Row

End Function

Private Function GetWorksheet(wkb As Workbook, shtName As String) As Worksheet
Dim sht As Worksheet

On Error Resume Next
Set GetWorksheet = wkb.Worksheets(shtName)

End Function

Private Sub MoveRow(rngToMove As Range, moveAt As Range)
rngToMove.EntireRow.Copy moveAt
End Sub

Private Function AddSuccess(name As String) As Boolean
On Error Resume Next
duplicates.Add name, name

If (Err.Description <> "") Then
AddSuccess = False
AddSuccess = True
End If

End Function