Interacting with COM Add-Ins through VBA macros

Question

I'm currently using Microsoft Project 2010 with the Project 2010 Scrum Solution Starter add-in. The add-in is pretty nice, as it adds a lot of custom views/task types/etc. for planning & managing a scrum project. However, it also has some problems (like having to manually edit the custom fields to move tasks to the product or sprint backlog, problems deleting sprints, as well as occasionally creating duplicate sprints with the same sprint number), so I've been trying to use VBA to fix these shortcomings.

However, I'm having trouble getting the active sprint number from the add-in. Looking at the C# source code for the add-in, this is stored in a class variable named sprintNumber:

namespace Scrum
{
    public delegate void SprintAdded(object sender, EventArgs e);

    public partial class AddNewSprint : Form
    {
        public event SprintAdded onSprintAdded;

        private Microsoft.Office.Interop.MSProject.Application _application
            = null;
        private int sprintNumber = -1;

There is also a drop-down list in the ribbon menu that displays this number. But I have no idea how to access either the class variable or the Add-In created drop-down list.

Is this even possible, or am I going to have to purchase Visual Studio and learn C#, or is there an easier way to go about this that I'm overlooking?

@David: The thing is, as little experience as I have in VBA development, I have even less in C# and COM add-in development. I've considered purchasing Visual Studio and learning C#, but I just want to make sure there isn't an easy way to do this simply through VBA macros. — Lèse majesté, Apr 20 '12 at 22:13
seems unlikely that vba could help. Why do you need to buy VS. Can't you use express? — David Heffernan, Apr 20 '12 at 22:43
@David: I hadn't thought about Express. Does it contain everything I need for COM add-in development? I'm not familiar with Windows software development, and I understand that Visual C++ Express doesn't support many libraries (like MFC, ATL, parts of the Win32 SDK, etc.) that the standard edition supports, can't compile 64-bit applications natively, etc. — Lèse majesté, Apr 22 '12 at 10:46
I'm not sure, never tried, but I'd be surprised if C# express could not create an inproc COM server — David Heffernan, Apr 22 '12 at 11:07

score 2 · Answer 1 · answered Apr 23 '12 at 12:31

A macro cannot access a private variable declared in the add-in. If you had the corrresponding Visual Studio version, you would be able to modify the add-in so that it provides a public property/member returning that variable. But since you have the source code of the add-in, you know the type and ID of the Ribbon control showing that number. I suppose you can use Accessibility to retrive that value from that controls, see http://www.wordarticles.com/Shorts/RibbonVBA/RibbonVBADemo.php.

+ 1 thanks for dropping by and adding your valuable inputs :) — Siddharth Rout, Apr 24 '12 at 08:02

score 1 · Answer 2 · answered Apr 23 '12 at 09:57

AFAIK, You will not be able to create COM Addins with Express Editions. You will have to buy the complete edition (The least - Standard Edition)

If you are a serious about Add-In development then you may want to go in for Add-In Express with Visual Studio. I recommend it highly as I have extensively used it. It makes your life much easier. You can contact Andrei Smolin from Support if you have any questions.

Interacting with COM Add-Ins through VBA macros

2 Answers2