CODEWITHSUNDEEP
c#vbams-wordcom-interop
Troy Bryant
Troy Bryant

Reputation: 1024

Inserting a VB macro throws Argument not optional error

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:

enter image description here

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);

Upvotes: 0

Views: 570

Answers (2)

Mathieu Guindon
Mathieu Guindon

Reputation: 71187

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
";

Upvotes: 5

FunThomas
FunThomas

Reputation: 29296

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";

Upvotes: 0

Related Questions