Microsoft Access Add-In using VSTO

I don’t think the VSTO tools are really worth the trouble, and besides if you create a “add-in”, then such a add=in will be loaded upon Access start-up for ALL Access applications, and not limited to your one application. And having to register the add-in with office OFTEN will cause issues for start up in Access. So your add-in will have to load, and load for ANY and ALL cases in which Access is simply launched for any reason.

All the VSTO tools really do is setup a com interface to office, and it is really much less hassle to just build a “simple” class in .net, and then consume that from your VBA button in access.

So a easy approach is to “just” consume a .net class from VBA/office code. Messing with VSTO really does not help much anyway, and by time you get all that VSTO stuff working, you be long done and finished by just writing a simple class in .net.

A few tips: Ensure you check the box in .net “register” for COM interop. Keep in mind this option ONLY executes a regasm for your convenience during the development process on your dev computer.

The other option (which is set by default anyway!!!) is in the assembly area, and you just have to ensure “Make assembly COM-visible).

For distribution you will have to include a small batch file or use an installer. That simple batch file or installer has to execute a “regasm” on target machines (you non dev computer).

And ensure you force the project to x86 (assuming your using office x32, which is most certainly the case). So do NOT use “any” CPU but force project to x86 CPU.

Other than above, that’s about it.

Here is a simple .net class. Assuming .net 4.5 or later, this lets you create zip files from Access VBA.

(you need both system.IO.Compression, and system.io.compression.Filesystem as refs in your .net project. The result is zipping files from Access without any 3rd party tools.

The class in .net is thus this:

Imports System.Runtime.InteropServices
Imports System.IO.Compression
Imports System.IO

<ClassInterface(ClassInterfaceType.AutoDual)>
Public Class AlbertCom1

Private m_Times2 As Integer

Public Sub MsgHello()

    MsgBox("Hello world", MsgBoxStyle.Information, "VB.net example")

End Sub

Public Sub MyZipper(strFileName As String, strZipFile As String)

    Using archive As ZipArchive = ZipFile.Open(strZipFile, ZipArchiveMode.Update)
        archive.CreateEntryFromFile(strFileName, Path.GetFileName(strFileName), CompressionLevel.Fastest)
    End Using

End Sub

Public Function GetConValue(strSetting As String) As String

    ' read a simple value from config file
    Return My.Settings(strSetting).ToString

End Function

Public Property Times2 As Integer
    Get
        Return m_Times2
    End Get
    Set(value As Integer)
        m_Times2 = value * 2
    End Set
End Property

End Class

So the above is rather simple, and short.

In Access VBA, behind our button, we can thus use these code:

Call/use the Hello message box method from above:

Sub TestCOMHello()

  Dim mycom      As Object
  Set mycom = CreateObject("AlbertCom1.AlbertCom1")

  mycom.MsgHello

End Sub

Note how above is LATE binding – this will work without a reference in VBA to the object. (so createObject() is required).

However, the following examples use early binding, but all would work as per above to create the object.

Zip a file from VBA:

Sub TestCOMZip()

  Dim strFromFile      As String
  Dim strToFile        As String

  Dim mycom      As New AlbertCom1.AlbertCom1

  strFromFile = "c:\test\data.txt"
  strToFile = "c:\test\data.zip"

  mycom.MyZipper strFromFile, strToFile


End Sub

A few more tips:

You don’t really have to build a custom interface in your class, and while many “frown” upon using AutoDual, I think such an approach is just fine. The “examples” on the internet that spend all this time creating the custom interface really are just an excuse for world poverty.

One last big tip: Do NOT expose any non-compatible data types in your class. If you expose (by accident or by intention) any .net object TYPE that is not compatible with standard “com” objects, then your .net class will compile just fine, but Access will not see or consume the object correctly. So keep (declare) those variables and routines as private in the actual above class. (The other code and parts don’t matter – just the one class is what I am talking about).

So don’t for example expose a .net long data type. If you stick to strings, standard collections, arrays etc., then you should be just fine. And you actually “can” expose non compatible object types as “object” from .net. (Intel-sense in Access will not show the objects methods, but you can still use them).

And while public functions and even public vars are most simple for your methods of the object, here is use of a get/set as per “standard” approach as to how class methods are created.

So in .net we have:

Public Property Times2 As Integer
    Get
        Return m_Times2
    End Get
    Set(value As Integer)
        m_Times2 = value * 2
    End Set
End Property

And in your VBA code, then we have:

Sub TestTimes()

   Dim mycom     As New AlbertCom1.AlbertCom1

   mycom.Times2 = 40

   Debug.Print mycom.Times2

End Sub

output: 80

Note for early binding, then the Access VBA editor will “spit out” the properties and methods of the class:

Eg this:

All in all use of VSTO is in general a HUGE overkill. Just build a simple .net class to expose what you need to use/call from VBA and you off to the races. And the other BIG bonues is that this COM object can be used from windows scripting, VB6, FoxPro, Excel, Word, power-point etc. In other words you not limited to any one platform or JUST Access to consume + use your simple class you expose as a COM object. I am hard pressed to make a case for VSTO in most cases. So this object can now be used from ANY office application (VBA code) with great ease.