Error when calling VBA function from VSTO code

Question

I have the following VBA code in a module of one of my .xlt files:

Public Sub SetShapeTop(ByVal shapeName As String, ByVal topValue As Single)
  ThisWorkbook.ActiveSheet.Shapes(shapeName).Top = topValue
End Sub

In my VSTO add-in, I have the following C# code which tries to call that VBA function:

m_worksheet.Application.Run("SetShapeTop", kv.Key, kv.Value, Type.Missing, Type.Missing, Type.Missing, Type.Missing,
  Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing,
  Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing,
  Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);

That line throws an exception, as follows:

(Excerpt from my log4net logs)

[Exception: COMException]
{Target: Cluster Report}
{Invoked Method: Macro execution}
{Target Method: Cluster Report}
{Parameters: }
{Message: Cannot run the macro 'SetShapeTop'. The macro may not be available in this workbook or all macros may be disabled.}
{Stack trace: 
Server stack trace: 


Exception rethrown at [0]: 
  at System.Runtime.Remoting.Proxies.RealProxy.HandleReturnMessage(IMessage reqMsg, IMessage retMsg)
  at System.Runtime.Remoting.Proxies.RealProxy.PrivateInvoke(MessageData& msgData, Int32 type)
  at Microsoft.Office.Interop.Excel._Application.Run(Object Macro, Object Arg1, Object Arg2, Object Arg3, Object Arg4, Object Arg5, Object Arg6, Object Arg7, Object Arg8, Object Arg9, Object Arg10, Object Arg11, Object Arg12, Object Arg13, Object Arg14, Object Arg15, Object Arg16, Object Arg17, Object Arg18, Object Arg19, Object Arg20, Object Arg21, Object Arg22, Object Arg23, Object Arg24, Object Arg25, Object Arg26, Object Arg27, Object Arg28, Object Arg29, Object Arg30)
  at MSA.Excel.ClusterPresenter.RenderReport(IReportData reportData, IResponseReportParameters reportParams, Worksheet worksheet) in C:\workspace\MSABasketlink\2.0\MSA.Library.UI\Presentation\ClusterPresenter.cs:line 647}

Am I missing something here? If anyone can point me in the right direction, I'd really appreciate it...!

SLaks · Accepted Answer · 2010-08-05 16:59:36Z

1

This is a security restriction.

Go to Trust Center in Excel and check Trust access to VBA project object model.

answered Aug 5, 2010 at 16:59

SLaks

891k182 gold badges1.9k silver badges2k bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

code4life Over a year ago

Thanks for the tip SLaks. However the options are all grayed out! (Fortunately the Trust Access to VBA project model checkbox is checked). Does this indicated anything? BTW, it's a .xlt file (Excel 2003) which I'm running in 2007.

SLaks Over a year ago

The settings are probably controlled by your domain admin.

Todd Main · Accepted Answer · 2010-08-05 18:45:06Z

0

Is your calling code running in your VSTO Addin's startup? If so, probably not everything has loaded just yet. One (stupid) way to get around this is to just run it until it works. Sorry the VB.NET, but C# should be similiar

Dim hasRun As Integer
Do 
    hasRun = Me.Application.Run("SetShapeTop", kv.Key, kv.Value)
Until hasRun > 0

Also, take a look at this article: Extend Your VBA Code With VSTO

One thing to note is that this code will fail if the appropriate access is not granted to the VBA macros.

edited Aug 5, 2010 at 18:45

answered Aug 5, 2010 at 18:37

Todd Main

29.2k11 gold badges85 silver badges150 bronze badges

1 Comment

code4life Over a year ago

no, it's not in the Addin's startup, it's somewhere else. I thought I set all the appropriate access, but getting this error.

Collectives™ on Stack Overflow

Error when calling VBA function from VSTO code

2 Answers 2

2 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related