Making an AddIn ( Requirements )

[iwrk4dedpr]

I'm going to attempt to make my first addin. However, I'm not sure what things I need to take into account.

The addin will be used to either make or alter a cell comment. The main function will be to change the shape of the addin from the standard rectangle to some other shape. I will base all shapes on XL2000.


So what do I need to do to make an addin. What do I need to account for. Here are some things that I've thought of.

1. AddIn needs to change to readonly on opening. So that if another instance of xl opens there won't be conflicts.

2. Would like to add a new comment button to the workbooks popup menu but not sure how to accomplish that. So thinking of adding another item to the insert menu. ( I'm rusty on that aspect )

3. How to account if a sheet is protected. ( not sure on how to do that in a worksheet I don't know.

4. Make sure the item is not available on a chart sheet.

Those who have made addin's what other things do I need to account for. I want this thing to be super slick and work in anybody's workbook. I maybe might just even make it so that the user can port out the code into their own workbook. ( just a thought )


Please if you have any ideas or suggestions let me know.

[Zack Barresse]

Hi Barry,

Let's look at your questions a little bit.

1) Not true. In fact, this is the opposite of what you should generally do. It should be just a normal Excel file.

2) Maybe this will help: http://forums.techguy.org/t292025.html

3) Depends, what kind of protection?

4) I don't understand.


Probably the biggest piece of advice I can give you is that when coding for your add-in, always do it ambiguously. Never use 'ThisWorkbook' as you'll be referring to your add-in (it is an Excel file still, after all), use ActiveWorkbook and ActiveSheet and such.

Always claim your variables and their types (e.g. Dim intRow As Long, etc.), and if they are Objects of any kind, ensure you Set them to nothing (Set myObj = Nothing) at the end of every routine they're called in.

Depending on your target audience, you may want to use Late Binding, although Early Binding is generally easier to work with.


I'm sure others can provide some good feedback, and I'll come back with some decent links (if not posted before I get back ;) ).

[iwrk4dedpr]

Let's look at your questions a little bit.

1) Not true. In fact, this is the opposite of what you should generally do. It should be just a normal Excel file.
What I meant by this was what if the user starts a second application of XL on the same machine. This is accomplished easily in the file icon's properties by actually spelling out the path to the excel.exe file and then the file path and name. When this happens multiple instances of xl ( which don't know the other exists ) will start.


So if you have an addin open in another instance and then start another instance of XL when it tries to open the .xla it want's to error stating that it can't open the file ( it's wanting to open ReadWrite ) so when the .xla file opens it should be immediately set to ReadOnly. Especially since nothing should be saved in the .xla anyway it won't make a difference.



As far as the sheet protection how can you test if the sheet is actually protected and will allow the comment to be added. I can use "On Error Resume Next" but I would rather tell the user that "Hey you need to unprotect the sheet first" rather than wait and then not have the comment be added.


In essence I'm really only asking that if you've made addin's what issues have you come accross.

[Ken Puls]

Hi Barry,

I know what you're saying with the opening 2 instances of XL, but I wouldn't make the add-in read only. You'll find that you get the same error with the personal.xls file if you open a second instance of XL as well. IMHO, the problem there lies with the fact that XL is started more than once, not the the workbook is protected or not. The second XL instance would then consume more resources and memory on you system, and should be avoided anyway. Personally, I just opt to shut down the second instance, and train my users to do so as well.

FYI, an addin is a hidden workbook in XL, so a user cannot save it (or anything to it) unless you specifically code a routine to do so.

With regards to other points for an add-in, it depends on how far you want to distribute it, and how "sexy" you want to make it. To make it look professional, you can modify the description that shows up when you select the Addin in the Addin's installation menu (choose File|Properties|Summary, and enter something in the comments field.)

For the code, definately follow Zack's advice. Use Option Explicit, (to force the variable declaration,) and make sure that you thouroughly test the add-in with multiple workbooks open... preferably without your intended add-in as the workbook in focus. (Make something else the activeworkbook, then run you code and see what happens.) Finally, when you've got it all working the way you want, compile your code before you save it as an addin. (FYI, if you save it as an addin, and realize you made a mistake, find the "thisworkbook" object in the VBE and set the "IsAddin" property to false to get it back.)

With regards to the worksheet protection, consider using a function like the following:


Function SheetProtected(TargetSheet As Worksheet) As Boolean
If TargetSheet.ProtectContents = True Then
SheetProtected = True
Else
SheetProtected = False
End If
End Function

What this does is evaluate if the "TargetSheet" is protected, and returns True if it is. An example of it's use would be something like this:


Sub test()
Dim ws As Worksheet
Set ws = ActiveSheet
If SheetProtected(ws) Then
MsgBox "Sorry, but " & ws.Name & " is protected!", _
vbOKOnly + vbInformation, ws.Name & " is protected!"
Else
MsgBox "Hooray! " & ws.Name & " is not protected!", _
vbOKOnly + vbInformation, ws.Name & " is unprotected!"
End If
End Sub

You would want to update the "set ws"... line to reflect the sheet you wanted though. Keep in mind, though, that when your workbook is converted to an addin, that it will never be the activeworkbook (or activesheet), so this may suit your purposes already...

HTH,

[Richie(UK)]

Hi Barry,

I think Ken has given the major factor, IMO, to take into account when designing an add-in like this - have it user-tested before you release it on your target audience. You'd be amazed at other people's ability to find bugs in code that you have extensively tested yourself - I speak from personal experience! ;)

The other major factor when 'going public' is to make sure you have error-handling in place - users hate unexpected errors, especially when they don;t have the ability to recover the situation for themselves.

As regards the code itself, as Zack has said, make sure that you use ThisWorkbook when referring to the add-in and ActiveWorkbook when referring to the workbooks that the add-in will work on. For the 'binding' issue, what you may find useful if to early bind while coding (so that you get the Intellisense help) and then change the variables back to Objects again when finished. Are you setting any References? This can be a problem area where users have different set-ups.

There are many posts (here, OzGrid, MrExcel ... everywhere!) covering the creation of custom menus. If you can't find anything let me know.

Also, if you want, I can let you have a copy of my utilities add-in so that you can see how it is put together (includes error-handling routines and menu creation based upon the advice in the VBA Programmers Reference book). Just PM me with your email address.

HTH