Common Error when compiling the VBA using the VSTO is found here. In order to fix it, I changed the long parameter in the VSTO to Int32. Then the VBA compiled fine.
Have to close excel in order to build the project.
Don’t need a public interface. Only these lines of code before the public worksheet object:
[ComVisible(true)]
[ClassInterface(ClassInterfaceType.AutoDual)]
In order to add a reference to the VSTO you must click on “Register for COM interop”. This creates a .tbl file that is the reference that you need to add in order to use the VSTO. The “Register for COM interop” is found i the Project Properties -> Build -> Output.
Once the reference is added (you may have to check it as well) to Excel, hit “F2” from the VBA code which opens the Object Browser. Navigate to the VSTO class and check that none of the Members has “Unknown” as a property. If so, then this is due to an improper variable type. See the Compatible Variable Types between VBA and VSTO link. Fix any unknown types in the VSTO code and recompile.
In VBA I received the following error:
In order to fix this, instead of using a cast to (Int32), I used Convert.ToInt32.
- Compatible Variable Types between VBA and VSTO
- A basic diff beteen VBA and VSTO – a little to basic – but it is well written. So here it is.
- Function marked as restricted error … (MSDN)
- Registering a file with COM (regasm.exe)
- Expose VSTO addin to VBA (scroll down to July 13, 2012 – that started some success)
- There might be some help in making UDF’s with VSTO which is not straightforward.
- Excel-DNA is an interesting free idea. It might have some solutions as they have gotten over some hurdles with UDFs etc.
- Could it be 32bit – WHOA – working – change the Regasm and the CPU to 32bit. Then we will try backing off as many 32 bit dependancies as possible. Ideally working in 64 bit would be better for pure VSTO.
- it is 64 bit that make “unknown” in VBA’s object browser