vba – Return a User Defined Data Type in an Excel Cell

vba – Return a User Defined Data Type in an Excel Cell

As the other answers suggest, the literal answer to your question is no. You cant store anything other than a number, string, boolean, error, etc. in a cell. You cant return anything other than a simple value such as those, or an array, or a range reference, from a UDF.

However, you can do essentially what you want by passing around (and storing in cells) some kind of handle to your objects that is a legal cell value (i.e. myclass:instance:42). This is probably what the example you linked to in your edit does. Your code has to be able to interpret the meaning of the handle values and maintain the objects in memory itself, though. This can get tricky if you care about not leaking objects, since there are lots of ways to erase or overwrite handles that you cant detect if youre using VBA to do all this.

I dont have it in front of me right now, but you might want to look at the book Financial Applications using Excel Add-in Development in C/C++ by Steve Dalton:


He discusses ways to work with handles like this more robustly with XLL add-ins.

This looks to be a tough cookie. Its a little hokey, but what you could do is have your Initialize function return a name (string), then add the name parameter to the Get function. Basically manipulating the name string instead of the object directly.

vba – Return a User Defined Data Type in an Excel Cell

The nesting wont work because myvar goes out of scope as soon as the UDF is done executing. There actually may be other problems associated with trying to return an object in a worksheet function (most certainly there are), but even if there werent the scope problem would still kill it.

You could store a pointer to the object in a cell and get the object via that pointer, but again the scope will kill it. To get the object from the pointer, it would have to remain in scope, so why bother storing the pointer.

Obviously your real life situation is more complex than your example. So the answer is no as to storing objects in cells, but if you explain what youre trying to accomplish there may be alternatives.

Leave a Reply

Your email address will not be published. Required fields are marked *