Use VBA to create a Visual Basic 64 bit Add-in for Excel

[MartinLiss]

Is it possible?
Have you done it?
Do I need Visual Studio?

[Dan_W]

Yes. Yes. No.
It all depends on what kind of addin you want to make. Excel has its own add-in infrastructure built into via the XLAM file format, which allows you to do all that you can with VBA (including ribbon and backstage UI customisation) that is bolten onto the user's excel as an addin. If you want access to the TaskPane UI functionality, yes, you will need to use VS to create a VSTO addin. If you want to make 64 bit custom controls/OCX, then I'd suggest TwinBasic

[MartinLiss]

Thanks, but to be clearer, I'm talking about the kind of add-in that can be used in the Visual Basic area of Excel. For example I wrote one for 32 bit where it made it easier for a coder to annotate the changes he makes to his macros, etc. If that's what you are talking about then please tell me more.

[fafalone]

twinBASIC can do this-- yes, addins. It can do 64bit OCX controls too, but also Office addins.

On the New Project window that opens at startup, go to the Samples tab, and look at Sample 4 (MyVBEAddin) and Sample 5 (MyCOMAddin).

[MartinLiss]

fafalone, are you talking about VB6?

[fafalone]

No; "twinBASIC can do this" is referring to twinBASIC

tB is backwards compatible with VB6 (minus a few not yet implemented features and a host of bugs; it's still in Beta, under development), but is not VB6 nor an addin for VB6; it is it's own language with it's own compiler, debugger, and IDE. It has dozens of new language features in addition to building 64bit binaries-- essentially, it answers the question 'What if the VB classic line had been continued over the last 20 years instead of replaced with .NET?'

The twinBASIC FAQ is a good place to start if you haven't checked it out yet. Then my repository will give you a good impression of where it's at, compatibility wise, though I'm mainly a VB6 guy-- I don't have an Office addins but my cTaskDialog class is written for compatibility with VB6/VBA7 32+64/tB 32+64, and my OCX controls work in Office 64bit.

[MartinLiss]

Thanks then but I'm looking for a VBA solution and I've changed my question's title to reflect that.

[fafalone]

You can't use VBA to create addins for VBA. It doesn't allow compiling binaries.

VB6 cannot compile 64bit binaries.

Using Visual Studio would mean doing it in .NET, in an entirely different language (C# or VB.NET).

twinBASIC is the only way you can use the VB6/VBA language for this.

[Dan_W]

Thanks, but to be clearer, I'm talking about the kind of add-in that can be used in the Visual Basic area of Excel. For example I wrote one for 32 bit where it made it easier for a coder to annotate the changes he makes to his macros, etc. If that's what you are talking about then please tell me more.

To clarify, what you're trying to do is create some kind of add-in for the VBA IDE?

Yes, you can do it in VBA - by referencing the Visual Basic for Application Extensibility Library v5.3 - it gives you access to the VBProject Object, the VBComponent object (like module, userforms, etc), and so on. You can get more information about it at the Chip Pearson website: http://www.cpearson.com/excel/vbe.aspx

There are some real limitations to using VBA, though. By using VB6, for example, you can access a few events in the IDE that you can't access natively from VBA. There is also an issue with adding buttons to the VBAIDE commandbar - it can be done, but you can potentially lose the 'connection' through state loss. I think I may have stumbled across a solution to it written by someone else for a separate purpose, but I haven't tested it yet.

But I'd recommend exploring Chip Pearsons page on the topic, and then taking it from there, depending on what it is exactly you want your Add-In to do. As Fafalone suggested, TwinBasic could be a great solution. It will be able to do everything VBA could do and more. It includes a demo project that is a VBE Addin that works beautifully.

[fafalone]

You're talking about the difference between using VBE from VBA and making an addin.

In addition to those and other technical limitations, there's the key distinction of an addin: you now have to add those source files and that reference to every project -- it isn't added in to VBA itself so always available.

It may well be a good solution for some needs, but its not an addin just by virtue of using the extensibility object.

[Dan_W]

You're talking about the difference between using VBE from VBA and making an addin.

In addition to those and other technical limitations, there's the key distinction of an addin: you now have to add those source files and that reference to every project -- it isn't added in to VBA itself so always available.

It may well be a good solution for some needs, but its not an addin just by virtue of using the extensibility object.

Ahh yes. I take your point - thank you. You're right.
I think I got misled by the MS nomenclature of referring to XLA/XLAM files as Excel Add-Ins.

[loquat]

VBA can compile x64 dlls, but it needs 64bit msvbvm60.dll, without which the vba compiled dlls will not work.

[fafalone]

How do you think you can compile a DLL with VBA?

64bit msvbvm60.dll doesn't exist.