[SOLVED] ATL COM to add a function to excel

I will give a simple example of how to create a c++ ATL/COM dll that will be referenceable in Excel's vba. The dll will expose to the VBA a ATL/COM object and this object will have a method that will be able to take a double from excel/vba, multiply it by 2.0 and output it to the VBA. The VBA code will be usable to define a excel function. Of course, no need to resort to a c++ dll for having an excel function mutiplying the content of a cell by two, but it is just for the demonstration.

Last remark before start : better run visual studio (called mvs for short throughout this answer) as admin for all of this. All versions of mvs from 2005 included work for this.

Let us start. open your favourite mvs, create a new project : choose the template "ATL project" in the Visual c++ template project types list. Name it "MyATLProject", and choose to save it in "C:\Users...\Desktop" for instance. Click ok. This will open a new window. Do not click there on finish, but on next : now check that only "dll" is checked, and if so, click on finish. (Of course for our basic need we need nothing more here.

All we have done has created a solution (in "C:\Users...\Desktop\MyATLProject", I call $(SOL) this path.) with two projects in it : MyATLProject & MyATLProjectPS. The PS ended project (PS stands for proxy/stub) is useless for what we want to do, but anyway. We will do everything here in debug. Do a rebuild solution. (You'll note that MyATLProjectPS has been skipped from rebuid : this is normal.) Now you could see that a folder $(SOL)\MyATLProject\debug has been created, with various files in it, the one of interest to us being MyATLProject.dll --> this file is the one we will have to reference in the VBA and that we expose us objects and methods. For now, it would expose nothing to us in the VBA, for we haven't yet implemented any ATL/COM object, a fortiori nor any method.

Now, let us create an ATL/COM object. There is a hard way for this and a soft way, I will only show the soft way. Right click on the "MyATLProject" project in the solution explorer, then "add", then "class", and in categories choose "ATL" and then "ATL simple object". Then click on "add". In simple name put "MyATLObject" and click finish. (Here again we could do more, but for such an intro, we don't need more.) This will create and open a "MyATLObject.h" file. MyATLObject.cpp has also been created, and MyATLProject.idl (present at the project generation) has been modified. This three files are the holy trinity for what we intend to do. Do a rebuild all for sake's sake. ;-) An ATL/COM object MyATLObject, without any methods for now, has been created. You could see it the VBA if you would reference the dll there, but be patient, we will do it later.

Now, let us give a method to this object. There is a hard way for this and a soft way, I will show you both, starting with the hard one first. First modification. Go in MyATLProject.idl and replace

interface IMyATLObject : IDispatch{
};

interface IMyATLObject : IDispatch{
    [id(1), helpstring("method MULT")] HRESULT MULT([in,out] DOUBLE* theDouble);
};

What have we done ? The idl file "references our MyATLObject" (I am voluntarily vague) (as well as other objects/interfaces if needed), and we specify in it that our MyATLObject will have a method called MULT, method that will take a reference (pointer to) a double. As you may guess, MULT will multiply by 2.0 the double pointed to. Now, we have to modify accordingly MyATLObject.h and MyATLObject.cpp. Second modification : go to MyATLObject.h and replace

public:

};

OBJECT_ENTRY_AUTO(__uuidof(MyATLObject), CMyATLObject)

at the end of it by

public:

    STDMETHOD(MULT)(DOUBLE* theDouble);
};

OBJECT_ENTRY_AUTO(__uuidof(MyATLObject), CMyATLObject)

What have we done ? We have declared the method MULT in the class, as we would have done it for a class in "vanilla" c++. Third and last modification : go to MyATLObject.cpp and after

// CMyATLObject

add

STDMETHODIMP CMyATLObject::MULT(DOUBLE* theDouble)
{

    return S_OK;
}

S_OK is of type HRESULT and tells by return if our MULT method finished well or not. (No need to be more verbose here for what we plan here.) Now we have to implement the MULT method really, like this for instance (I am voluntarily ugly and unsafe here, you'll fashion the stuff yourself later) :

STDMETHODIMP CMyATLObject::MULT(DOUBLE* theDouble)
{
    *theDouble *= 2.0 ;
    return S_OK;
}

Rebuild solution. The ATL/COM object MyATLObject has been now updated to have a method MULT now. This was for the (not very in fact) hard way of adding a method. The soft way is by using the wizard : undo all three modifications we did and rebuild, so that we are now at the same stage as the one we were in when we were about adding the MULT method. Go to class view, expand MyATLProject, right click on the interface IMyATLObject (the one with the handle icon, the icon looking like a little key), click on "add", and then "add method". This opens the add method wizard. Put MULT in method name, choose DOUBLE * in parameter type, and "theDouble" in the parameter name. The fact the our parameter is a pointer DOUBLE * will give us access to the out and retval parameter attributes. Click "in" and "out". Then click on add, and then on finish. This will recreate all what we did by hand (for MULT only, and without its implementation of course) before. Add the

    *theDouble *= 2.0 ;

line in the implementation of the MULT method in the MyATLObject.cpp file.

Warning : depending on the mvs version, by experience, it may happen that everything is recreated well except the idl file part, check it, and if this is the case, do it by hand as we did it in the first place, during the hard way.

Now, it is time to use our ATL/COM c++ dll in the VBA of excel. Pick your favourite excel version, and create a spreadsheet (in xlsm format for earlier excel versions and in xls for late version, because we will need macros) called MyATLProjectTEST.xls. Make sure to enable the use of all macros. (How to do it varies with excel's version, but you'll find how to do it with your friend google.) Alt+F11 to open VBA. Click on tool, references, browse to your MyATLProject.dll and click to add a reference to it. (To be safe, maybe regsvr32 the dll before --> this is explained everywhere on the internet, google is your friend again on this.) Insert a module, it will be automaticaly called Module1. In it, code :

Public Function MULT(x As Double) As Double

    Dim o As MyATLProjectLib.MyATLObject
    Set o = New MyATLProjectLib.MyATLObject
    Call o.MULT(x)
    MULT = x

End Function

Now go in a sheet of the spreadsheet, put 3.14159 in cell B2 let's say, and put formula

=MULT(B2)

in cell B3, and enjoy the result.

Remark : try to build to solution in mvs while the spreadsheet is open : you'll have a

Error   1   Could not delete file         'c:\users\...\desktop\myatlproject\myatlproject\debug\MyATLProject.dll'.

Make sure that the file is not open by another process and is not write-protected. MyATLProject

error. This is because the dll is referenced (used) in the VBA, who "owns" it somehow, empeaching mvs to modify (recompile) it. At each code modification, you'll have to close your spreadsheet for building/rebuilding. The demo is over.

Now, the "fun" part, the debug session. Rebuild the solution, reopen the spreadsheet and rereference the dll in it if needed. Put a breakpoint on the line

*theDouble *= 2.0 ;

Do a ctrl+alt+p (or "debug" and then "attach to process") in mvs, choose the excel spreadsheet in the list and wait a bit to have access to your spreadsheet again (let the symbols load, in fact). Now open the VBA and put a breakpoint at the line

Call o.MULT(x)

Now go to cell B3, and recalculate it. You will break in the VBA at the specified line, and if you step into (F8) you will break at the

*theDouble *= 2.0 ;

line in MyATLObject.cpp.

Some last remarks.

1) DOUBLE * even if it works is not the right way to pass info from vba to c++ and from c++ to vba, for the following simple reason : you sell your dll and vba code to someone, and he puts a string LUDWIGVONMISES in B2 instead of putting a number. What would happen ? An error : constated in a "not-debug" mode that you'll have to track in debug to see where it happens. Why ? Because you do not handle error at all. For handling them, you should pass VARIANT * between c++ and VBA, and operate in c++, at the beginning of your MULT method for instance, to check that the VARIANT * pointer passed to MULT points to a VARIANT "wrapping" a double and not a string.

Making a COM Automation Add-In is not the best way to add user-defined functions to Excel - it is slow and has various limitations. Better is to make etc

Being extremely focused on execution time for the code I am working on and delivering, and having developped a lot of ATL/COM dll's for excel/VBA, as well as xla/xll using the excel sdk, I do not agree at all with this statement from Govert. What could we say about the small demo above ? We have entered in the c++ when we enter in the MULT method, and our calculation (multiplication by two) was done inside the method. This was quick, but imagine that the calculation that was done was something extremely intensive numerically, or in memory terms. We could be stupid and doing it in our ATL/COM method, but we could deport the calculation outside our method, in plain c++. This would be the quickest thing to do, and afaik, it is the only reasonable way to do it. Same thing if you want to use the excel sdk, for producing excel functions directly, without wrapping ATL/COM methods in VBA code to produce these excel functions yes. Here you could say : hey, the xll option using the excel sdk is then better, because I do not loose wrapping time. Maybe, but wrapping time is really not that big, and with ATL/COM, you have VBA with you, where you could use ATL/COM object in quite an elegant fashion, and you can use it to generate com object etc etc. You can even access excel memory to instantiate object there through excel functions coded by wrapping ATL/COM method in VBA ! Moreover, any such ATL/COM dll is referenceable in c# project, and usable there in the same way that you use it in VBA. Even in java. ;-) This is not the case of any xla/xll coded with excel's sdk, like the solutions Govert is advertising for. Serious lack of reusability concern.

3) The OP wants to use ATL COM to do something which is perfectly doable with it, and Govert tells him that ATL/COM is too slow - which is false - and to use something else. For this I should downvote Govert's answer. ;-)