Inserting a VB macro throws Argument not optional error

Question

I am trying to open a Word document from a C# app, insert a macro, run it, then close. The problem I am running into is

Compile Error: Argument not optional

I've been looking at this article but I am not returning anything. Not seeing what I am missing here.

Here is the debugger:

Here is the C# code:

Microsoft.Office.Interop.Word.Application newApp = new Microsoft.Office.Interop.Word.Application();
newApp.Visible = true;
object Unknown = Type.Missing;
var Source = fileName;
var doc = newApp.Documents.Open(Source);

var project = doc.VBProject;
var module = project.VBComponents.Add(vbext_ComponentType.vbext_ct_StdModule);

var macro = "Public Sub DoKbTest()\r\n" +
                "MsgBox 'Hello'\r\n" +
            "End Sub\r\n" +
            "Public sub DoKbTestWithParameter(sMsg As String)\r\n" +
                "MsgBox sMsg\r\n" +
            "End Sub";

module.CodeModule.AddFromString(macro);
newApp.Run("DoKbTest");
newApp.Quit(ref Unknown, ref Unknown, ref Unknown);

VBA expects strings to be in quotes. Can you try `"MsgBox \"Hello\"\r\n" +` ? — schudel, Oct 23 '17 at 16:18
FYI you don't need to pass `Type.Missing` to every single missing optional parameters in a COM interop call, since .net 4.5. — Mathieu Guindon, Oct 23 '17 at 18:37

score 5 · Accepted Answer · answered Oct 23 '17 at 16:38

A single quote in VBA denotes a comment. This is what you're generating:

Public Sub DoKbTest()
    MsgBox 'Hello'
End Sub

Compare to:

Public Sub DoKbTest()
    MsgBox "Hello"
End Sub

Notice how syntax highlighting looks different with double quotes around the string literal.

You're getting that error because the MsgBox function's Message parameter isn't optional, and thus the MsgBox [some comment] instruction isn't a valid function call.

I'd suggest using a verbatim string, it's much cleaner IMO - simply double-up your double quotes to escape them:

    var macro = @"
Public Sub DoKbTest()
    MsgBox ""Hello""
End Sub

Public Sub DoKbTestWithParameter(ByVal msg As String)
    MsgBox msg
End Sub
";

score 0 · Answer 2 · answered Oct 23 '17 at 16:18

0

Use DoubleQuotes to enquote the Text to MsgBox.

var macro = "Public Sub DoKbTest()\r\n" +
                    "MsgBox ""Hello""\r\n" +
                 "End Sub\r\n" +
                 "Public sub DoKbTestWithParameter(sMsg As String)\r\n" +
                     "MsgBox sMsg\r\n" +
                 "End Sub";

answered Oct 23 '17 at 16:18

FunThomas

23,043
3
18
34

This is not how you escape quotes in C# in a [non-verbatim literal](https://stackoverflow.com/q/3311988/11683). – GSerg Oct 23 '17 at 16:21
@GSerg: True - but when I issued the answer the question was flagged not tagged as C# but as VB (changed several times...) – FunThomas Oct 24 '17 at 06:56
The code OP showed could not possibly be in VB. Even the single line you quoted cannot be misunderstood as VB because there is no `var` in VB. There wasn't a C# tag initially, but from its [very first edition](https://stackoverflow.com/revisions/46893953/1) the question explained it's a C# app that generates VBA code. – GSerg Oct 24 '17 at 07:45

Inserting a VB macro throws Argument not optional error

2 Answers2