7

I have successfully been able to run my own .Net code by following the steps posted here Execute .NET 3.0 code from Office 2003

Is there a way to use the standard .Net libraries without having to write a wrapper? This way we can avoid having to register and install a custom DLL into the GAC on the client's machine.

I've found tlb files already in the C:\Windows\Microsof.NET\Framework folders, and have been able to add a reference to mscorlib.dll. Looking at the documentation for RijndaelManaged, this class appears to be COM visible.

I am able to create an instance, but as soon as I try and work with it, I get errors (e.g. "Type mismatch").

Sub Macro1()
   Dim aesImplementation As New RijndaelManaged

   Set key = aesImplementation.GenerateKey()
   Set iv = aesImplementation.GenerateIV()
End Sub

I am willing to accept any hacks you have to offer!

Community
  • 1
  • 1
Evil Pigeon
  • 1,887
  • 3
  • 23
  • 31
  • 2
    I don't have an answer re: using .NET without a wrapper, but if you're new to VBA, you need to know that declaring something 'As New' doesn't actually instantiate anything at that point like it does in .NET. It's not clear to me whether your error is being raised on instantiation or on the 'Set'. (I'm assuming you've declared your 'key' variable as the correct type, etc.) See http://stackoverflow.com/questions/2478097/vba-difference-in-two-ways-of-declaring-a-new-object-trying-to-understand-why/2480559#2480559 – jtolle Nov 17 '10 at 15:11
  • That's crazy Why have As New as valid syntax if it doesn't instantiate a new object?! It does seem to create an instance in this case though, I'll remember at least that "As New" isn't best practice. The reason I haven't declared the key object explicitly is because I get "Can't assign to array" error if i do. Apparently this error occurs when you try and assign to an array of a different type. – Evil Pigeon Nov 19 '10 at 02:49
  • 1
    Isn't this a lot of work to save just some work? I mean, if your user will allow you to run a macro, why not run an actual .NET application? The Office interop in .NET seems quite good, so you could manipulate documents equally or better! – Camilo Martin Nov 20 '10 at 02:12
  • 6
    `Dim ... As New` does create a new object, it just does so on demand where it is referenced rather than where it is declared. A subtle difference, and a totally irrelevant one for this particular code example. – Christian Hayter Nov 22 '10 at 08:41
  • 1
    As far as assigning to an array, in VBA you wouldn't use 'Set' for that, just ordinary assignment. But I have no idea if assigning a VBA variant to a .NET byte array will work. – jtolle Nov 22 '10 at 15:45

2 Answers2

1

You should be able to use ComVisible .NET classes in this way. However the GenerateKey and GenerateIV methods don't have a return value. Try:

Sub Macro1()
   Dim aesImplementation As New RijndaelManaged

   aesImplementation.GenerateKey
   Key = aesImplementation.Key
   aesImplementation.GenerateIV
   IV = aesImplementation.IV
End Sub

or better, not least because when debugging you can see whether an error occurs during construction or when you call the method:

Sub Macro1()
   Dim aesImplementation As RijndaelManaged
   Set aesImplementation = New RijndaelManaged

   aesImplementation.GenerateKey
   Key = aesImplementation.Key
   aesImplementation.GenerateIV
   IV = aesImplementation.IV
End Sub
Joe
  • 122,218
  • 32
  • 205
  • 338
1

Not yet possible. VBA (VB for Applications) is not VB, but rather a separate deal mimicking all the basic syntax as older versions of VB. It's almost like using a very stripped down version of regular, older VB. In fact, VBScript is the closest match to VBA. Perhaps someday, Microsoft will build in methods to work directly with the GAC, but until then (and that day will likely mean the death of COM I'm sure), you're stuck using COM CreateObject() method, adding a reference to a COM registered library to your Office project, or directly referencing a VBA/COM compatible DLL or TLB file in your Office project.

There are quite a few COM-enabled libraries in the default GAC, but for the majority, you are stuck creating a Com Callable Wrapper first in VB.Net or C#.

Conversely, pretty much all MS Office apps are COM callable, so you can work with installed Office apps through VB.Net projects all you want.

bob-the-destroyer
  • 3,164
  • 2
  • 23
  • 30
  • 3
    By adding a reference to `mscorlib.dll` you can create an object of any class that has a parameterless public constructor in the standard .net libraries. You will find this very limiting. – AndASM Oct 12 '14 at 16:46
  • 5
    "VBScript is the closest match to VBA" - VBA is *much* closer to classic VB6 than it is to VBScript: https://msdn.microsoft.com/en-us/library/ms970436.aspx – Joe Jan 27 '17 at 22:11