How to use .NET 3.5 class from Excel 2010 VBA

[PhilSwEngr]

Is there a way I can use System.Security.Cryptography from an Excel 2010 VBA module subroutine to calculate an MD5 checksum of a file? In C# I use the statement:

using System.Security.Cryptography;
…
MD5CryptoServiceProvider md5Generator = new MD5CryptoServiceProvider();

Since System.Security.Cryptography is available as part of .NET 3.5, I s/b able to use it from Excel VBA w/o having to register any DLLs, correct?

Any help is appreciated.

 

[Zack Barresse]

Hi there, welcome to the board!

Not necessarily. Excel [2010] runs VBA 7.0, and does not tie to the .NET framework, so you really don't have access to that class library. You would if the solution was built in Visual Studio, but not in VBA. There are some tricky ways to reference COM add-ins with VBA, but it must be set as ComVisible, and the ClassInterfaceType must match as well. While I haven't done much of this, I think the logic would be more to create your C/VB add-in, ensuring it's ComVisible, create your class(es) appropriately, then set a reference from VBA to your project as to access your class.

Here is an example. And here is a walkthrough example.

 

[PhilSwEngr]

Thanks for the reply Zack. The "walkthrough example" link you provided is excellent, but what I was hoping for was to do either of the following if possible:

- "directly" call/use System.Security.Cryptography from VBA code w/o having to create a DLL
.OR.
- create a DLL to call System.Security.Cryptography w/o having to register it as a COM DLL. A DLL w/b fine if it's the type I don't register but instead just drop it into the dir containing the VBA code module.

BTW, I'm using VS2008 and Excel 2010, running on a Windows 7 PC.

Phil

 

[RoryA]

Try simply:

    Dim oCryptMD5
    Set oCryptMD5 = CreateObject("System.Security.Cryptography.MD5CryptoServiceProvider")

 

[PhilSwEngr]

Thanks Rory. What reference name do I need to add to use oCrypMD5? In general, how does one determine what reference to select given a class such as "System.Security.Cryptography.MD5CryptoServiceProvider"?

The code below gets a "438" run-time error ("Object doesn't support this property or method") on the line, "oCryptMD5.ComputeHash (oTs)". Any help is appreciated.

Sub TryThis()

    Dim oCryptMD5
    Dim oFso As FileSystemObject   ' ref Microsoft Scripting Runtime library
    Dim oTs As TextStream
    Dim chksm As String
    
    Set oFso = New FileSystemObject
    Set oTs = oFso.OpenTextFile("C:\d\readme_pg.txt", ForReading)
    Set oCryptMD5 = CreateObject("System.Security.Cryptography.MD5CryptoServiceProvider")

    oCryptMD5.ComputeHash (oTs)
    chksm = oCryptMD5.Hash      ' assign (convert) byte array to string
    
    MsgBox chksm

End Sub

 

[Zack Barresse]

To use CreateObject (i.e. Late Binding), the COM project must be visible. In other words, if, from the VBE, you went to Tools | References, the project would need to be visible in that list, which means it would need to be registered. If it's not COM visible and registered, you can't set it to an object by using the CreateObject method.

Setting the reference (checking the box in the References dialog box) would give you Early Binding, which gives you intellisense and negates the need to reference it as an object, but by its actual type. If you don't see it in the list, you can click the Browse button and find the dll.

Here is a better example, more specific to yours, for creating your own project for reference in VBA:
HOW TO: Call a Visual Basic .NET Class Library from Visual Basic for Applications in Microsoft Office

 

[PhilSwEngr]

Thanks Zack. I definitely have some learning to do when it comes to using VBA. Will post back in a bit. For now, I'm guessing I will need to utilize cryptsp.dll or cryptsvc.dll.

 

[Zack Barresse]

I believe it's cryptsp.dll, not the service dll. I'm not sure the dll has a ComInterface though, so you may not be able to reference it. Using late binding, as Rory pointed out, should still work. A small example of using only VBA to reference it:

Excel Macro that utilizes Toodledo.com's API

 

[RoryA]

ComputeHash expects a Stream object so you need to remove the parentheses from the call.

 

[PhilSwEngr]

With all your help, I got a VBA-only implementation working, producing the same MD5 checksums for "text" files as I get with my C# (DLL) implementation. Unfortunately, I cannot say the same when calculating checksums for "binary" files. I am not sure what the problem is but feel it may have something to do with using UTF8 encoding in VBA but not in the C# implementation.

I have spent way to much time on this and need to move on. ...thanks again