Denis Pitcher

I'm in the process of figuring out how to migrate an old VBA based Excel Add-in to VSTO .net

Quite unfortunately, UDF function support in Excel is critical for the add-in I'm updating and yet it is not supported in the present version of VSTO nor will it ever be with Excel 2003.

Thus, I'm stuck figuring out a means around the issue.

After a great deal of digging, I found a means to create VBA code from within .net and add it when the runtime loads.  A great summary of how to do this is available here:  http://blogs.msdn.com/pstubbs/archive/2004/12/31/344964.aspx

The issue, however, is just as Paul outlines being that you then have to deal with 2 different security models, both .net and VBA Macros.

What I hoping to achieve is the ability to take advantage of VSTO's deployment model and .net to dynamically create and update the VBA as needed rather then having to redeploy the addin manually every time there is a change.

In order to dynamically add the code in Paul's sample I'm utilizing VBE interop to load the module when the addin starts.

---

Public Sub New(ByVal application As Excel.Application)

applicationObject = application

vbeProj = applicationObject.ActiveWorkbook.VBProject

vbeComp = vbeProj.VBComponents.Add(vbext_ComponentType.vbext_ct_StdModule)

CreateCallback()

BuildFunctions()

LoadFunctions()

End Sub

---

I then build the VBA code as a string (at least for temp purposes), load it and run the callback function to pass my .net object to the VB macro.

---

Private Sub LoadFunctions()

' Load Modules

vbeComp.CodeModule.AddFromString(sb.ToString())

' Initalize Callback

applicationObject.Run("CallbackReg", New GenericFunctions())

End Sub

---

Upon running the deployed addin I get the following error:

An unhandled exception occured when loading the Script Builder: System.Runtime.InteropServices.COMException (0x800A03EC): Programmatic access to Visual Basic Project is not trusted

The easiest way to get around this is to simply go into Tools-->Macro-->Security and change the security setting to allow programmatic access.  This option, however, is far from ideal and aside from producing unnecessary security risks it also means that this setting would need to be changed on every machine that uses the addin, which is also what I want to avoid.

Is there any means to programmatically add a digital signature to VBE generated code

I appreciate any suggestions, ideas or even recommendations of other forums/people I should ask who might have a better idea

I've also posted this in the office developer com addins newsgroup and am looking into Excel DNA as an alternative as this may topic may or may not directly relate to VSTO.

Thanks,

Denis




Re: Visual Studio Tools for Office UDF support in Excel 2003 with VSTO .net

Denis BDA

So far no responses.

I've hunted all through the web and it appears that while in Word 2003 you can access the signatureset property to review and add signatures, you cannot do so in Excel 2003. Even some of the documentation incorrectly says you can. There does not appear to be any means to progammatically sign a VBA Macro which is disappointing.

I have found one other alternative that would allow me to programmatically change the registry setting for "Trusting Access To Visual Basic Project" at the following link:

http://www.experts-exchange.com/Applications/MS_Office/Word/Q_21730594.html

Set wsh = CreateObject("WScript.Shell")
'key to modify
str1 = "HKEY_LOCAL_MACHINE\Software\Microsoft\Office\" & _
Application.Version & "\Word\Security\AccessVBOM"
'enable access
wsh.RegWrite str1, 1, "REG_DWORD"
'read the vba project name
MsgBox Application.NormalTemplate.VBProject.Name
'disable access
wsh.RegDelete str1

I have yet to test this setting, but I'm thinking it may suffice as a loophole around not signing the generated macro. I'm quite disappointed at having to go to these lengths as this is nearly as bad as turning off office security alltogether.






Re: Visual Studio Tools for Office UDF support in Excel 2003 with VSTO .net

Denis BDA

After some testing, I've confirmed that the HKCU\Software\Microsoft\Office\11.0\Excel\Security\AccessVBOM setting in the registry indeed does turn on and off access to the visual basic project. It isn't the workaround I was hoping for, but it may suffice.

The next question that comes to mind is one which my coworker proposed: Will this work in Vista when we migrate

Unfortunately, I do not know the answer to this question yet.






Re: Visual Studio Tools for Office UDF support in Excel 2003 with VSTO .net

Mohit Gupta - MSFT

Hi Denis.

It looks like you have figured a workaround and there have been no responses to the question, so I am closing the thread.

Thanks.