Write a standalone C# app with the ability to call any VBA macro for office automation?

Question

5.00/5 (1 vote)

See more:

, +

Hey everyone,

Noob here who -- not necessarily because it's the best solution but more so just to learn and this is my idea of fun -- wants to write a super simple C# standalone app that (a) displays a list of macros I've written in VBA and (b) allows a user to select one which the app will call resulting in the macro performing whatever function it would have, had it been called directly from one of the Office host apps. Is this possible? I've seen things like this done using surprisingly short Autohotkey scripts compiled into an exe (with a GUI, the ability to import new modules, and even a taskbar icon with dynamic context menu) I figure if it is possible to call VBA macros from Autohotkey in this manner, it must be doable in a C# app, right?

What I have tried:

I've spent the past 3 days spiraling down a google-search rabbit hole trying to find a tutorial that might get me headed in the right direction, with no luck.

To be clear, I'm not asking for anyone to spoon-feed me or write this for me. I wholeheartedly want to learn this doing my own legwork. Regardless, I appreciate any kind of guidance you can provide me. Thanks in advance.

Posted 1-Dec-23 5:34am

Peter Abbasi

Add a Solution

Comments

Richard MacCutchan 1-Dec-23 11:42am

You will need to find a library that can interpret the contents of the macro. Alternatively you would need to write your own parser. Take a look at yacc which may give some ideas.

Peter Abbasi 1-Dec-23 12:14pm

Thanks for the suggestion, Richard.

[no name] 1-Dec-23 12:09pm

I think you need to "classify" all your macros first; unless they all have the same requirements. A "macro" is a rather broad definition: it could be a whole Excel workbook; with a database interface. It just "sounds" like remote Office automation.

https://learn.microsoft.com/en-us/dotnet/csharp/advanced-topics/interop/walkthrough-office-programming

Peter Abbasi 1-Dec-23 12:21pm

Thank you for providing the link. I will definitely take a look! As far as needing to classify them, I am not sure if this is what you mean by classification: but essentially the app would be installed locally on the same machine as the instance of Office, the macros would be the conventional kind of document review/formatting/find and replace kind of macros you'd find in both excel and word add-in templates. Nothing crazy - the most complicated macro would likely ask for user input (a search term and replace term for find/replace). I imagine there would be two lists in the GUI, one for macros that are found in and called from excel, and the other found in and called from word. Is that the kind of classification you are talking about?

[no name] 1-Dec-23 13:19pm

Yes ... You implied some macros require user input ("search"); that's one "class". Maybe others are "fire and forget". Different "classes" of processing. Partitioning the problem. One class perhaps easier to implement than the others.

Peter Abbasi 1-Dec-23 15:12pm

Understood and thanks again!

Richard MacCutchan 1-Dec-23 12:18pm

Thinking about it, maybe ANTLR[^] is a better choice. Such a long time since I used either.

Peter Abbasi 1-Dec-23 12:23pm

Thanks again. I'll check them both out to be safe :)

Dave Kreskowiak 1-Dec-23 18:01pm

Don't make the mistake of thinking that because you want a "super simple" app that it's going to be super simple to code. It's not.

AutoHotKey is hiding a lot of the implementation details from you. That is stuff that C# is not going to hide from you or do for you. You're going to have to implement a lot of the stuff AutoHotKey is doing behind the scenes.

Primarily, you can use C# and Office Interop to run the macros, or you can "stuff the keyboard" with the keystrokes to get a running instance of Office to run the macros itself. The choice is yours.

Peter Abbasi 3-Dec-23 21:31pm

That just means more opportunity to learn, right? :) I get the message though. It is definitely something to consider before committing to anything, and the fair warning is appreciated.

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Maciej Los · Accepted Answer · 2023-12-01T09:28:00

Solution 1

Couldn't it be better to re-write all macros from VBA to C#? Whatever you want to do with Excel data, you can do it in C#. Take a look at EPPlus library[^]. This library is powerful, elastic. You don't need to have an Office installed on your system to be able to use it.

I would NOT recommend to writing an application in C# in the way you mentioned. You don't need extra wrapping (I mean a C# application that calls a macro in Excel which provides extra stuff to be able to display the final report/data). Do it directly! Create a final report/excel file (or whatever you want) without using Excel application. Your C# application can do everything (and much more) that VBA macro can do.

Posted 1-Dec-23 9:28am

Maciej Los

Comments

[no name] 1-Dec-23 17:54pm

I once created a huge budgeting database for Canada's largest grocer: tables; column definitions; originally spec'd out in Excel. A "macro" generated an entire Oracle database definition from the workbook; and "executed" the DDL on demand. A C# interface provided some parameters and "ran the macro" without having to crack open Excel. I guess there are "database architect tools" out there for a small fortune, but Excel produces nice reports and intranets. I strongly disagree that C# and interop do not have their place.

Maciej Los 1-Dec-23 18:07pm

Do i said that C# and interop do not have their place? No.

Peter Abbasi 1-Dec-23 20:07pm

Thanks Maciej Los - and you are not wrong. It is a valid solution, but not everyone is able to write C#, nor is every individual in the office work setting willing to invest the time or effort into learning it. In fact, I personally know dozens of people who have full libraries of VBA macros they use to automate tasks, and they never even learned Visual Basic. Some people are just set in their ways. I could see a use case for an app like this (not that my intention is to commercialize it) but for the sake of argument, there are plenty of individuals who would rather be able to just plug in their own saved macros (of which they have a full collection) and run them that way, as well as organize, save and view them.

Your solution did bring an important point to my attention also. It was never my intention to limit my question to C#. In my first draft, my exact wording was actually "C# (or any language for that matter including C++, VB.NET, or any of the other languages that is not unusual for Windows to support)." Unfortunately, I was multi-tasking, and erased that portion of the question by mistake.

Andre Oosthuizen · Accepted Answer · 2023-12-01T22:00:00

Yes, it is possible in C#, VB.NET and some others as well. The below is for C# to reference your question -

You can pick up on all of the basics as a starting point at - How to access Office interop objects | Tutorial [^]

Some code to point you in the right direction, you need to reference the 'Microsoft.Office.Interop.Excel' assembly in your project and of course use this as a guideline only -

C#

using System;
using Microsoft.Office.Interop.Excel;

class MacroRunner
{
    static void Main()
    {
        //Create your instance of Excel Application...
        Application excelApp = new Application();

        //Make the Excel workbook visible, optional if you want to...
        excelApp.Visible = true;

        //Open the workbook containing your macros...
        Workbook workbook = excelApp.Workbooks.Open(@"C:\Path\ToYour\Workbook.xlsm");

        //Display a list of all your available macros...
        DisplayListOfMacros(workbook);

        //Select a macro...
        Console.Write("Enter the name of the macro you want to run -> ");
        string selectedMacro = Console.ReadLine();

        //Run the selected macro...
        RunMacro(workbook, selectedMacro);

        //Close your Excel app...
        excelApp.Quit();
    }

    static void DisplayListOfMacros(Workbook workbook)
    {
        Console.WriteLine("Available Macros ->");
        foreach (VBComponent component in workbook.VBProject.VBComponents)
        {
            Console.WriteLine(component.Name);
        }
        Console.WriteLine();
    }

    static void RunMacro(Workbook workbook, string macroName)
    {
        try
        {
            workbook.Application.Run(macroName);
        }
        catch (Exception ex)
        {
            Console.WriteLine($"Error running macro '{macroName}': {ex.Message}");
        }
    }
}