Link an Excel VBA macro with an Excel ribbon button made with VSTO Visual Studio

Question

I want to create a custom ribbon Excel application for multiple users. However, I am restricted to using the VSTO package of Visual Studio. I cannot use the RibbonX Editor application nor the Custom UI Editor Application (Company Policy). I have an .xlsm Excel document with multiple macros. I want to be able to run the macros easily.

Within Visual Studio I am having a hard time with callbacks. I don't know how to connect both my Excel VBA macros with my designed Ribbon buttons. I don't Know how to code in C nor vb.Net.

Here is an xml example code:

<?xml version="1.0" encoding="utf-8"?>
<customUI xmlns="http://schemas.microsoft.com/office/2009/07/customui" onLoad="Ribbon_Load">
    <ribbon startFromScratch="false">
        <tabs>

            <tab id="customTab" label ="Custom Ribbon">
                <group id="CustomGroup" label ="Change Reference">
                    <button id="Button1" size="large" label ="Will It Work?" onAction ="Test1"/>
                </group>
            </tab>

        </tabs>
    </ribbon>
</customUI>

Here is a callback example vb.net code:

    Public Sub Ribbon_Load(ByVal ribbonUI As Office.IRibbonUI)
        Me.ribbon = ribbonUI
    End Sub

    Public Sub Test1(ByVal Control As Office.IRibbonControl)
    End Sub

Here is an Example VBA macro code:

Sub Test1()
MsgBox "I hope this works"
End Sub

I have tried to build the excel Ribbon with C, vb.net and xml. However, I always get stuck at the same step. I can't link the vba macros with designed ribbon buttons.

@DmitryStreblechenko The code above is a test that I tried to conduct. For my macros I have around 3000 lines of code. Is there an easy way to convert VBA into VB.Net? — Olivier, Feb 16 '23 at 05:52
VB.Net and VBA are close enough to just try to compile and fix compiler errors. 3000 lines of VBA code certainly does not sound like a lot. — Dmitry Streblechenko, Feb 16 '23 at 15:21
check https://stackoverflow.com/questions/60067247/how-or-is-it-possible-to-call-vba-sub-or-function-from-ribbon-added-to-excel — Volkan Yurtseven, Feb 18 '23 at 12:48

score 0 · Answer 1 · answered Feb 20 '23 at 22:51

The simplest way is to create a VSTO add-in using VB.NET which is close to the VBA syntax. Where you can re-use your ribbon XML markup and just add the callbacks to the add-in. The Walkthrough: Create a custom tab by using Ribbon XML explains how to create a custom UI using the ribbon XML markup. Note, you may use the ribbon designer available in Visual Studio with VSTO and then export the created UI to the ribbon XML. See How to: Export a ribbon from the Ribbon Designer to Ribbon XML for more information.

See Walkthrough: Create your first VSTO Add-in for Excel to get started with add-in in Visual Studio.

Link an Excel VBA macro with an Excel ribbon button made with VSTO Visual Studio

1 Answers1