Accessing a specific VBA code module using C#

Question

I am creating workbooks from a template. The template already has the required code modules in it. I am trying to get a reference to one of the code modules, the one that the code needs to be imported to (the code is always different so having a .bas file wont work here as there would be hundreds of them).

I can easily access a new code module using

var codeModule = excelWorkbook.VBProject.VBComponents.Add(VBIDE.vbext_ComponentType.vbext_ct_StdModule);

But I need to access the existing "Methods" code module in the instantiated workbook variable

 //initialize the Excel application and make it invisible to the user.
        var excelApp = new Excel.Application
        {
            UserControl = false,
            Visible = false
        };

        //Create the Excel workbook and worksheet - and give the worksheet a name.
        var excelWorkbook = excelApp.Workbooks.Open(TemplateFilePath);
        excelWorkbook.Author = Acknowledgements.Author;
        var bookPath = excelWorkbook.Path;

        //add the macro code to the excel workbook here in the Methods module.
       //this adds a new module, how do I access an existing one?
        var codeModule = excelWorkbook.VBProject.VBComponents.Add(VBIDE.vbext_ComponentType.vbext_ct_StdModule);

I could just loop through the modules and get it by testing name equality but there must be a way to retrieve it in one line of code.

Daniel Möller · Accepted Answer · 2022-04-04 17:52:20Z

4

Use the Item method of the VBComponents collection to retrieve an existing VBComponent by name or numeric index:

using VBIDE = Microsoft.Vbe.Interop;

VBIDE.VBComponent component = excelWorkbook.VBProject.VBComponents.Item("Methods");
VBIDE.CodeModule module = component.CodeModule;

edited Apr 4, 2022 at 17:52

Daniel Möller

86.8k24 gold badges202 silver badges222 bronze badges

answered Sep 17, 2016 at 17:36

Michael Liu

55.8k14 gold badges126 silver badges158 bronze badges

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

3 Comments

dinotom Over a year ago

Just figured that out and tested. Thank you for confirming

ScottinTexas Over a year ago

Where does the VBIDE come from? Apparently it is not part of Microsoft.VBE.Interop or Microsoft.Office.Interop.Excel?

Michael Liu Over a year ago

@ScottinTexas: I don't remember exactly where. Microsoft.Vbe.Interop shows up as VBIDE in Solution Explorer, but there's no VBIDE namespace. Maybe I had added using VBIDE = Microsoft.Vbe.Interop;

Collectives™ on Stack Overflow

Accessing a specific VBA code module using C#

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related