Brace your selves for a long post
broro183,
Thank you very much for your post. I did some research on add-ins, and its a much better approach that i used. Already started working on it.
Comments on your points:
- I think every user will have to have a Reference set to "Microsoft VBA Extensibility...".
-I did reference "Microsoft VBA Extensibility..." in my code. And i also added a command to add that reference automatically on workbook open, incase other users don't have it. FYI:
ThisWorkbook.VBProject.References.AddFromGuid GUID:="{0002E157-0000-0000-C000-000000000046}", Major:=5, Minor:=3
- If excel 2007 is adopted by your company there may be more hoops to jump through using multiple templates rather than an addin.
-Fortunately, excel 2003 is used in our company. 2007 seems a little harder to deal with.
If the code is so likely to change, then (in my state of ignorance) I believe the file structure may change as well, resulting in a mis-match between OLD file & new code.
-I believe I will be using the common add-in file approach. Hopefully, that won't cause file structure change
why is your code seemingly so suceptible to change?
-From my past VBA tool and my programming experience, I know there will be bugs along the way as more and more people start using it. Add-in approach seemed really easy to fix that problem. If there is a bug, I fix the bug in the add-in. And next time a user opens the file again, the updated bug-free add-in will be referenced (seems to work in my head)
Perhaps you could post sections of the code on the Forum & we could help you improve it so that you have a more polished product...?
The whole program is too big to post. But it works for the most part. If in future some problem does appear that I can;t fix I will definetly post a snippet of the code here in the forum to get help from all of you wonderful people
and, I'm sure I had another point... but it escapes me at the moment.
lol, I appreciate you helping me out. It means a lot to me. I know so much more about add-ins than I knew before.
There is one thing that i would like to bring it here. I have a workbookopen event that references a procedure from the add-in. The add-in has to be installed before using this tho, and the reference for it also has to be added. Because, I like to make everything automated for user-benefit and less-suspetible to problems (pardon me for my arrogance), i found commands online to do that in VBA. See below code:
Private Sub Workbook_Open()
On Error Resume Next
'add the add-in
Dim oAddin As AddIn
Set oAddin = AddIns.Add(filePath, True)
oAddin.Installed = True
Set oAddin = Nothing
'add the add-in reference
ActiveWorkbook.VBProject.References.AddFromFile FilePath
Call WorkbookOpenEvent '******call to procedure inside the reference and obvious compiler error before event execution
'if an error other than 32813 appeared means there is a problem, add that library manually
If Err <> 0 And Err <> 32813 Then
MsgBox "One or more VBA Object libraries not loaded properly." & vbCrLf _
& "Error " & Err.Number & ": " & Err.Description & vbCrLf & vbCrLf _
& "Go to HELP and follow procedure on how to add project references and the required add-in manually."
End If
End Sub
When workbookopens, it's gives compiler error that WorkbookOpenEvent is not found (which is obvious why). Is there a way to avoid this?
Yes, I want to have the procedure in the reference, such that i can modify that too in case of bugs, just like i did to all my worksheet events also.
Yes, I know I can just tell the user to add the add-in and it's reference manually. I prefer not to use this method tho. Automation is more efficient and reliable.
Thank you very much for the responses so far. I believe I can get this done through the help of all you kind and VBA experts with effiency and reliability. I apologize for the long post.
Thanks again
Bookmarks