PDA

View Full Version : Solved: Create VBA add-in



Zack Barresse
08-12-2004, 11:29 PM
Hi,

Does anyone know how to create a VBA add-in? I'm just curious. The two I like the best have been Mark Rowlinson's Code Indenter (by far my most favorite!) and Juan Pablo Gonzalez' VBHTML Maker. I've an idea for one and I would like to develop it, but I don't know where to start. Does this have to be done with VB? This is just kind of a side project of mine. Thanks!

Jacob Hilderbrand
08-13-2004, 12:21 AM
I believe it has to be VB, though I am not 100% on that. The few I have used had to be installed from Windows and the installer looked like VB.

:goss:So what's you're idea? Or is it top secret?

roos01
08-13-2004, 12:28 AM
to create an add-in you might take a look at:
http://support.microsoft.com/default.aspx?scid=kb;DE;282847

or
http://www.exceltip.com/show_tip/Cells,_Ranges,_Rows,_and_Columns_in_VBA/Create_an_addin_in_Excel_97,_2000,_2002_using_VBA_in_Microsoft_Excel/495.html

I hope this is of any use for you.
Cheers!
Jeroen

Steiner
08-13-2004, 12:30 AM
If you use pure VB (and I don't believe it will work just using VBA) you can already pick AddIn as project-type. VB then will create a connection-designer and a basic form for you automatically.
I once created a very simple addin for VB6 using that template and I it was much easier than I thought.
I have not yet tried developing an VB-Addin for my Office-Apps, but as far as I've see the addin-designer supports connection to the "Visual Basic for Applications IDE", so it might be worth a look.

roos01
08-13-2004, 12:33 AM
here another option. it is creating a ddl and use it as add-in:
How To Create Office COM Add-Ins by Using VBA and Office Developer
http://support.microsoft.com/default.aspx?scid=kb;en-us;306130

SJ McAbney
08-13-2004, 03:17 AM
Simplest way is to create a spreadsheet, only do some VBA in it with some forms to achieve your goal.

Save your spreadsheet as an .xla file and then Browse for it in the Add-ins menu.

Access is a little different and involves creating a table with installation details.

Jacob Hilderbrand
08-13-2004, 03:20 AM
Simplest way is to create a spreadsheet, only do some VBA in it with some forms to achieve your goal.

Save your spreadsheet as an .xla file and then Browse for it in the Add-ins menu.

Access is a little different and involves creating a table with installation details.
For a VBA Add-in? I see an Add-in Manager in VBA, but no browse button. Or were you thinking of an Excel Add-in?

SJ McAbney
08-13-2004, 04:14 AM
Or were you thinking of an Excel Add-in?
Since this is an Excel forum, that's exactly what I was talking about. :D

Zack Barresse
08-13-2004, 09:20 AM
Yeah, I've got the XLA's down pretty good now. (Been making a few of those, work has been loving them.) But I was talking about VBA add-ins, I was thinking the DLL route. I'll check out those links, thanks for those!!

And no Jake, it's not top secret (LOL), I was using Mark's (absolutely fabulous!) code indenter addin, and wanted to make one that commented out all selected code. Add it to the right click menu and all. There's probably one already out there, but I really wanted to learn how to make my own, I like doing that kind of stuff, that's how I learn best. :) So if there's already one out there, don't burst my bubble! LOL

Zack Barresse
08-13-2004, 09:23 AM
Oh, also, does anybody know of an example DLL that I can take apart and see how it works? And what would I use to edit it with? Just looking for some hints.

brettdj
08-13-2004, 12:00 PM
Zack,

I think that Tommy and Smozgur have both played around with DLL addins. Might be worth a PM.

I believe that the DLL addins offer more security than the flimsy xla protection

Cheers

Dave

XL-Dennis
08-13-2004, 01:02 PM
Zack,

Best way is to use VB 6.0 if it's available. As for developing in Office You need the Developer Version (which is not very good) and it's only available upto version 2002.

For version 2003 it's called Visual Studio Tools for Office System and require Visual Studio.NET 2003 installed (and it only works with Excel 2003 and above)

At present I'm translating a swedish articles I wrote for some years ago to english on how to build COM add-ins but I'm not sure when I've finished it.

Let me know if You need to know more about it :)

Take care buddy,
Dennis

brettdj
08-13-2004, 01:18 PM
Hi Dennis,

That would be a handy article - looking forward to it :)

Cheers

Dave

Zack Barresse
08-13-2004, 01:51 PM
Yes, that would definitely be an excellent read! :yes

I just fot VB v.4.0 and am looking into getting VB 6.0. Even though I know nothing about VB as of yet, I'm determined to teach myself. I figure this is probably one of the simplest tasks I'll (probably) ever use it for, so it would be a good starter. I don't do very well with tutorials, I've gotta get my hands dirty and delve into it, pick it apart, to learn best.

Thanks for all your input guys, much appreciated! So let me know when you're done with that article, I would absolutely love to read it. :)

XL-Dennis
08-13-2004, 01:55 PM
OK, I will post the link here :)

[Edit]: I just remembered that COM Automation Add-ins (for functions that can be direct called from worksheets or VBA) can't be created with the Office Developer version since the class-module require "Public Creatable" :mkay
(Which is one of the stupid things with the MOD...)

Enjoy the weekend and the Olympic Games in Greece,

Dennis

Zack Barresse
08-13-2004, 02:02 PM
OK, I will post the link here :)

Great! Thanks! I'll be very much looking foward to it. :yes


Enjoy the weekend and the Olympic Games in Greece

I will have a great weekend, you do the same! :)

johnske
08-14-2004, 03:52 AM
Don't know if this will help, as I've never tried to make an addin, but in 'Visual Basic Edit' click onto 'object browser' then select either addin or addins and then help (?). This appears to give instructions and the code for creating and using (etc) your own addins. :bink:

Tommy
08-16-2004, 11:05 AM
Hi Zack,

Here are a few links I found usefull. Let me know if you need moe help.

http://support.microsoft.com/default.aspx?scid=kb%3Ben-us%3B253338
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dno2kta/html/mso2kaddin.asp

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnoffdev/html/vsofficedev.asp

http://support.microsoft.com/default.aspx?scid=kb%3BEN-US%3B238228

this one is for .NET
http://support.microsoft.com/default.aspx?scid=kb%3Ben-us%3BQ302896

Later
Tommy

XL-Dennis
08-16-2004, 11:53 AM
Hi everyone,

Tommy's nice reminds me about my own compiled collection of MS Knowledgebase-articles which can be viewed here (in plain english ;):

http://www.excelkb.com/article.aspx?id=10008

Kind regards,
Dennis

Zack Barresse
08-16-2004, 12:06 PM
Thanks for the links guys! I really want to make use of the VB solutions provided, but that may take me a while to really nail down. I'll definitely be doing some testing on these perscribed methods. Thanks so much to you all! :)

johnske
08-17-2004, 02:11 AM
??I'm not entirely sure of what type of add-in is being discussed here as the discussion has ranged from VBA to VB and dll files etc.?? :confused:

If you're interested in VB and don't already have this, click the link below to download a free 'envelop' VB compiler that allows you to create your own stand-alone projects. (if that's what you mean)
As it also includes a packager, you can package the project, have links to XL (or the reverse) etc.

http://www.freebyte.com/programming/compilers/envelop.html :bink:

Regards, John...

Daniel Klann
08-18-2004, 12:47 AM
??I'm not entirely sure of what type of add-in is being discussed here as the discussion has ranged from VBA to VB and dll files etc.?? :confused:

I believe Zack is referring to COM Add-ins, not regular Excel add-ins.

Excel Add-ins (.xla files) are just normal Excel workbooks that a) are invisible and b) can be set to load automatically when installed through the Tools, Add-ins menu item.

As Denis mentioned, COM add-ins (which are DLL files) can only be created in Excel Developer edition, or another language capable of creating COM components e.g. Visual Basic 6, Visual Basic .NET, Office Developer Edition, Visual C++.

Does that help the confusion?

Dan

johnske
08-18-2004, 02:36 AM
.....As Denis mentioned, COM add-ins (which are DLL files) can only be created in Excel Developer edition, or another language capable of creating COM components e.g. Visual Basic 6, Visual Basic .NET, Office Developer Edition, Visual C++.

Does that help the confusion?

Dan
I think so...in other words it seems that what is being discussed is "addins" in the form of additional controls that can be added into a project - (as these are usually in the form *.dll or *.ocx files). :)

I only have '97 on board and in all the help files etc in there the only "addins" mentioned in '97 appear to be almost defined as being of the form .xla.

I'm not trying to be pedantic here, as I realize that later versions of office can very well refer to additional controls simply as "addins" it's just that it was confusing me. :bink:

Zack Barresse
08-18-2004, 08:28 AM
That's the spot Dan! :yes That is what I'm talking about. Although, in regards to John's comments, I found the Excel help files pretty much devoid of anything regarding this. There are some good links to the MS KB, MSDN Library and of course Dennis' site (extremely informative).

If anyone wants a good example of an addin that I'm talking about, go to Mark007's website and view his list of VBA add-ins. His Code Indenter was my inspiration for this thread. I'd just like to be able to use that type of functionality. :)

Daniel Klann
08-18-2004, 03:42 PM
I think so...in other words it seems that what is being discussed is "addins" in the form of additional controls that can be added into a project - (as these are usually in the form *.dll or *.ocx files). :)

I only have '97 on board and in all the help files etc in there the only "addins" mentioned in '97 appear to be almost defined as being of the form .xla.

I'm not trying to be pedantic here, as I realize that later versions of office can very well refer to additional controls simply as "addins" it's just that it was confusing me. :bink:
No that's not the case, although I now understand your confusion :)

COM Add-ins weren't introduced until Excel 2000, which would explain why you see no reference to them in your help files. There hasn't been a change of terminology in more recent versions of Excel e.g. Additional controls are still called exactly the same, add-ins are still add-ins, COM Add-ins are COM Add-ins....you get the idea.

Hope this helps,
Dan

johnske
08-18-2004, 04:50 PM
No that's not the case, although I now understand your confusion :)

COM Add-ins weren't introduced until Excel 2000, which would explain why you see no reference to them in your help files. There hasn't been a change of terminology in more recent versions of Excel e.g. Additional controls are still called exactly the same, add-ins are still add-ins, COM Add-ins are COM Add-ins....you get the idea.

Hope this helps,
Dan
COOL!! I got it now - Thanx so much for clearing that up for me. :bink: