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

HTH,
Vikas

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

                Else

                    'check blank

                    

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

                    

                    

                    If (value = "") Then

                        blankCounter = blankCounter + 1

                    Else

                        blankCounter = 1

                        added = AddSuccess(value)

                        lastRow = GetLastRow(duplicate, 1) + 1

                        If (added) Then

                             'dont do anything. not a duplicate

                        Else

                            '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

                

            Wend

        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)

    Err.Clear

    

End Function



Private Sub MoveRow(rngToMove As Range, moveAt As Range)

    rngToMove.EntireRow.Copy moveAt

    rngToMove.EntireRow.Delete

End Sub



Private Function AddSuccess(name As String) As Boolean

    Err.Clear

    On Error Resume Next

        duplicates.Add name, name

            

        If (Err.Description <> "") Then

            AddSuccess = False

        Else

            AddSuccess = True

        End If

            

    Err.Clear

End Function

HTH,
Vikas

Add-in Express discounts and special offers

Add-in Express discounts and special offers

Excel VBA : Find data using Control+F and not looping

Hi All,

I was sitting and reading few posts and saw that lots of people tries to loop and find the data in different ranges. But it really becomes very very slow if the data you are looping at is huge. Also, what if probability of the occurrences of the desired string/value is very low.

Lets take an example, if I am using approx 1 Million cells in a worksheet, and I am trying to find if a particular text "xysqhqhsyqhsn" is available in how many cells. Obviously, the probability is quite low, and even equal to nil that I will find this string at all in the whole worksheet. But to find, I will need to search first to ensure that the value doesn't exist in the worksheet or not. Now, which option which one would like to have, either to loop to all the 1 million cells or just to jump on the cells which contains the value?

Obviously, in out case, we would like for the second option. I always do that. I always go for the second option to search the string in a worksheet, rather than looping through all the cells, a slow movement.

I have shown an example here. In this example, first I take an input from the user, and tries to give a counter which signifies that the input string exists in how many cells. I have used a function "GetValidRanges" which would return a collection of cells, as shown below

Sub FindInstances()
    Dim inputStr As String ' the variable to accept the input from user
    Dim rngToFind As Range ' the range where we have to find
    Dim activeSht As Worksheet
    Dim cell As Range
    Dim firstAddress As String
    Dim txtCounter As String
    Dim ranges As Collection
    
    'take the input of the string which we want to search.
    
    inputStr = InputBox("Please enter the string which you want to search.")
    Set activeSht = ActiveSheet
    Set rngToFind = activeSht.Range("A1:A300")
    If (inputStr = "") Then
        MsgBox "Invalid Input"
    Else
        'there is a string to search
        Set ranges = GetValidRanges(inputStr, rngToFind)
        
        MsgBox "Total number of occurrences are " & ranges.Count
        
    End If
    
    
    
    
End Sub


'''this function loops through the cells in the range passed, and returns a collection of those cells (eventually range objects) which contains the passed values.

Public Function GetValidRanges(inputStr As String, rngToSearch As Range) As Collection
    Dim cell As Range
    Dim firstAddress As String
    Dim ranges As Collection
    
    Set ranges = New Collection
    
    With rngToSearch
        Set cell = .Find(inputStr, LookIn:=xlValues)
        
        If Not cell Is Nothing Then
            firstAddress = cell.Address
            
            Do
                ranges.Add cell
                Set cell = .FindNext(cell)
            Loop While Not cell Is Nothing And cell.Address <> firstAddress
        End If
    End With
    
    Set GetValidRanges = ranges
    
End Function

User can easily use this collection in many ways. We can get the count of the total cells returned, or loop through all the items in the collection to get the actual address.

Hope this helps :)

Thanks,
Vikas