.XLA: Making it an AddIn vs adding a Reference?

[PeteCresswell]

I know I'm starting to turn into a tarbaby on this subject.

All I'm trying to do is consolidate my Excel VBA into one place:
a .XLA.

Reason I want to do this is that the users will be prone to cloning my
Workbooks into many, many variations as they try different data
scenarios - and if I need to make code changes I don't want to have to
deal with tracking down and retrofitting dozens of clones.


I can make my code work (i.e. the XLS can see the .XLA routines) by
setting a Reference in the .XLS that points to the .XLA.

But what I'd much prefer to do is to use the "AddIn" feature to make
the .XLA an AddIn.

Reason: there are tools available for managing AddIns. As I do
development work on the .XLA and variations of the .XLS concurrently I
don't want tb saddled with having to remember to update my
referenec(s) every time I elevate a new version to production.

But attempting to complete this seemingly bread-and-butter task has
turned into a 3-day nightmare for me.

I've lost track of the number of man hours I've spend on it - to no
avail.

This has tb an every-day developer task - so clearly I'm missing
something.

What I want to do is separate my development code from my production
code - without having to remember to update References in the .XLS's -
preferably with one of the available add-in managers like the one
Charles Williams so kindly pointed me to at his site:
http://www.DecisionModels.com/downloads.htm

Can somebody elucidate?

 

[Norman Jones]

Hi Peter,

I'm afraid that your ultimate objective
was not clear to me in your previous
thread.

See Jan Karel Pieterse's excellent tutorial at

Creating An Add-in From An Excel Macro.
http://www.jkp-ads.com/Articles/DistributeMacro00.htm

 

[RB Smissaert]

Not sure what the exact problem is.
For starters, I don't think you need to set any references if the .xla is
loaded as an add-in.
Secondly, the add-in should setup the menu in Excel, so you can see it in
the .xls and
via that menu you can access anything that is needed in the add-in.
Thirdly, you say you have an .xls and an .xla file. Would it be possible to
do away
with the .xls and put all in the .xla? I think it will matters simpler.

RBS

 

[Dave Peterson]

Have you thought about using "application.run" instead of using the reference
and a call statement?

As long as your addin is open (and uses the same name), I bet it would work ok.

 

[Norman Jones]

Hi Dave,

===========
Have you thought about using "application.run" instead of using the
reference
and a call statement?

As long as your addin is open (and uses the same name), I bet it would work
ok.
===========

That was my suggestion in Pete's previous
thread.

Another confirmation of the undesirabilty of
splitting threads.

 

[Dave Peterson]

I don't recall seeing the previous thread--but like you, I dislike multiposts
and followups not in the original thread!

 

[(PeteCresswell)]

Per Dave Peterson:

Have you thought about using "application.run" instead of using the reference
and a call statement?

Yes. Tried it and it worked... sort of...

But I was unable to figure out how to pass a parameter string.

Possible? If so, that would be my workaround.

 

[(PeteCresswell)]

Per Dave Peterson:

I don't recall seeing the previous thread--but like you, I dislike multiposts
and followups not in the original thread!

I stand corrected then.

My thought was that the problem at hand had diverged
significantly from the topic of the original thread and I was
making things simpler and not vice-versa.

I won't do this again.

 

[(PeteCresswell)]

Per RB Smissaert:

For starters, I don't think you need to set any references if the .xla is
loaded as an add-in.
Secondly, the add-in should setup the menu in Excel, so you can see it in
the .xls and
via that menu you can access anything that is needed in the add-in.

This supports my suspicion that I've got something really simple
and basic messed up. I *know* this scheme was working two weeks
ago. I had never done it before. Somebody suggested it. I
did it and *bingo* it worked. Definitely wasn't rocket science.

But then I moved the code back into the .XLS.

Now I've moved it again - back to a .XLA and the .XLS can't
compile refs to routines in the .XLA.

My reason for starting a separate thread was that I believe my
real problem may be around a misunderstanding of exactly what a
"Reference" is vs an "AddIn".

Thirdly, you say you have an .xls and an .xla file. Would it be possible to
do away
with the .xls and put all in the .xla? I think it will matters simpler.

That's the whole point of my exercise. But there has tb some
communication between .XLS and .XLA bc, for instance, a button
click on the .XLS has to call code in the .XLA.

 

[Norman Jones]

Hi Pete,

Try something likeç

Application.Run "'MyAddin.xlas'!myMacro", parm1, parm2

 

[Norman Jones]

Hi Pete,

Application.Run "'MyAddin.xlas'!myMacro", parm1, parm2

Was intended as:

Application.Run "'MyAddin.xla'!myMacro", parm1, parm2


If the host file is not an addin, change the
extension from xla to xls (or the appropriate
Excel 2007 extension).

 

[Tim Zych]

I *know* this scheme was working two weeks

ago. I had never done it before. Somebody suggested it. I
did it and *bingo* it worked. Definitely wasn't rocket science.

User Defined Functions in an addin do work without a reference if used as
formulas on a worksheet, but VBA-to-VBA require a reference, link or
Application.Run to the XLA as far as I know. Perhaps that is what worked for
you? Or if you linked a Forms command button to a macro in the addin, that
would work too.

My preference is using Application.Run as the linking that occurs with a
reference, and the requirement that workbooks be closed in a particular
order, does not appeal to me. Linking in general has lots of gotchas that I
generally try to steer clear of.

 

[Dave Peterson]

A couple more:

Dim OtherWkbk as workbook
set otherwkbk = workbooks("otherworkbookname.xla") '<-- it has to be open
application.run "'" & otherwkbk.name & "'!somemacronamehere"

or

application.run "'" & otherwkbk.name & "'!somemacronamehere", myargument

or to return something:

dim resp as long 'or string or whatever
resp = application.run("'" & otherwkbk.name & "'!somemacronamehere", _
myargument)

 

[RB Smissaert]

That's the whole point of my exercise. But there has tb some

communication between .XLS and .XLA bc, for instance, a button
click on the .XLS has to call code in the .XLA.

A routine like this lets you add menu items for your .xla to the Tools menu:

Sub AddToolsMenuItem(strCaption As String, _
strAction As String, _
Optional lFaceID As Long = -1, _
Optional strShortCut As String, _
Optional bBeginGroup As Boolean, _
Optional bRemoveOnly As Boolean)

Dim oCtl As Object
Dim oNewItem As CommandBarButton
Dim bMenuFound As Boolean
Dim strTools As String
Dim strMenuBar As String

strMenuBar = "Worksheet Menu Bar"
strTools =
Application.CommandBars(strMenuBar).FindControl(msoControlPopup,
30007).Caption

For Each oCtl In
Application.CommandBars(strMenuBar).Controls(strTools).Controls
If oCtl.Caption = strCaption Then
If bRemoveOnly Then
oCtl.Delete
Else
If bMenuFound Then
oCtl.Delete
End If
bMenuFound = True
End If
End If
Next

If bRemoveOnly Then
Exit Sub
End If

If bMenuFound = False Then
Set oNewItem =
Application.CommandBars(strMenuBar).Controls(strTools).Controls.Add
With oNewItem
If bBeginGroup Then
.BeginGroup = True
End If
.Caption = strCaption
.OnAction = strAction
If Len(strShortCut) > 0 Then
.ShortcutText = strShortCut
End If
If lFaceID > -1 Then
.FaceId = lFaceID
End if
End With
End If

End Sub

You could run this from your Workbook_Open Event of the .xla.
So, for example:

Private Sub Workbook_Open()

AddToolsMenuItem "Run XLA proc ABC", "ABC", 500

End Sub

This will be in the ThisWorkbook module of the .xla, so in the project
explorer right-click
the ThisWorkbook module and do view code then in the code pane on the right
click the left
drop-down and get Workbook in there and in the right drop-down get Open.
The routine ABC in this example will be in a normal module in the .xla.

Also add some code in the same ThisWorkbook module to remove the menu when
the .xla gets
unloaded:

Private Sub Workbook_AddinUninstall()

On Error Resume Next
AddToolsMenuItem "Run XLA proc ABC", "ABC", 500, , , True

End Sub


That is basically it.
I would (if possible) avoid using Application.Run etc. and make the .xla
completely self-contained,
so pressing the menu item will run .xla code and that will do all that is
needed.


RBS

 

[Peter T]

If I follow you want to port all your code in multiple xls files to a single
xla. Fine. So if your xls files no longer have any code, why do they need to
call code in your xla and all the problems about referencing the addin.

Obviously I'm missing something (apologies if you have already explained).
It might worth giving some a rough overview of what the code does, how it
interacts between xls/xla, and how it gets called, eg from some change event
in the xls or a button click (on a sheet or toolbar). Maybe with a bit of
reworking you don't need to be concerned with references or application.run
at all.

Regards,
Peter T

 

[(PeteCresswell)]

Per Peter T:

If I follow you want to port all your code in multiple xls files to a single
xla. Fine. So if your xls files no longer have any code, why do they need to
call code in your xla and all the problems about referencing the addin.

Not "no code"... but as close to it as I can get.

Really only two functions:
---------------------------------------------------------------
1) Code behind an "Import" button that deletes a couple of the
worksheets, invokes a MS Access application to do some serious
data munching and create two new versions of the deleted
worksheets in a temp .XLS, and then copy those new WS's from
the temp WS into the .XLS and rename them so that, as far as
the user is concerned those two sheets just got their contents
refreshed.

Sounds like I'm going to replace that code (maybe 100 lines
max) with a single line of code that reads
"Application.Run....." and invokes the larger code
in the .XLA.

2) In Workbook_Open(), capture some basic info about the .XLS
such as it's name, the path to the directory where is lives,
and a couple of other things I can't recall at the moment.
Basically a bunch of "ThisWorkbook...." stuff, which is
written into a holding area from which it can be passed to
MS Access and where it won't go "Poof" if the code is
interrupted for some reason and where it is still available
if the routine using it is not in the workbook that it relates
to.

Ditto above, but it needs to pass either each of the
ThisWorkbook values or a pointer to the workbook.

From what I've seen so far, it'll have tb each value
instead of a pointer.

 

[(PeteCresswell)]

User Defined Functions in an addin do work without a reference if used as
formulas on a worksheet, but VBA-to-VBA require a reference, link or
Application.Run to the XLA as far as I know. Perhaps that is what worked for
you? Or if you linked a Forms command button to a macro in the addin, that
would work too.

I can't recall - and, from what I've heard so far, I'm starting
to think I was lying about it working..... *Maybe* the command
button thing got me by.... but "linked to a Forms command
button.." seems to imply something else besides VBA in the Click
event and VBA and the Click event are all I know about.

My preference is using Application.Run as the linking that occurs with a
reference, and the requirement that workbooks be closed in a particular
order, does not appeal to me. Linking in general has lots of gotchas that I
generally try to steer clear of.

That's the kind of info I was trolling for: distinctions between
the two methods of getting to .XLA routines.

I *think* I've got it through my head now and I'm going to focus
on Application.Run with passed arguments.

 

[Dave Peterson]

Is there a reason you don't dump the button on the sheet and just create a
toolbar/menu item/QAT item that does all the work?

For additions to the worksheet menu bar, I really like the way John Walkenbach
does it in his menumaker workbook:
http://j-walk.com/ss/excel/tips/tip53.htm

Here's how I do it when I want a toolbar:
http://www.contextures.com/xlToolbar02.html
(from Debra Dalgleish's site)

And if you use xl2007:

If you want to learn about modifying the ribbon, you can start at Ron de Bruin's
site:
http://www.rondebruin.nl/ribbon.htm
http://www.rondebruin.nl/qat.htm -- For macros for all workbooks (saved as an
addin)
or
http://www.rondebruin.nl/2007addin.htm

In xl2007, those toolbars and menu modifications will show up under the addins.

 

[(PeteCresswell)]

Per Dave Peterson:

Is there a reason you don't dump the button on the sheet and just create a
toolbar/menu item/QAT item that does all the work?

Mainly out of ignorance.

What will the toolbar/menu item do for me or the user that a
command button wont?

 

[Peter T]

What will the toolbar/menu item do for me or the user that a

command button wont?

A toolbar/menu button can call the procedure in your addin directly, whereas
a command button on a sheet can only trigger an event in the sheet module.
In turn code in the event can call the addin procedure, either with
application.run or directly if you have set a reference to the addin in your
xls.

You could also use a Forms button on the sheet and set its OnAction to call
the addin procedure directly. One potential advantage is if the addin is not
loaded clicking the button will load the file (assuming of course it still
exists in the same location as when the OnAction string was assigned).

From what you describe, the addin does all the work and you don't need to
pass any arguments, I'd use either a toolbar/menu button or a button from
the Forms menu. However even Application.Run from your command button should
also work fine if you prefer (providing the addin is open).

In this thread RB Smissaert gave you an example of how to add button(s) to
the Tools menu. Dave Peterson has directed you to other ways.

When deciding whether to use a toolbar menu.button you may want to consider
how to restrict calls to the addin to process only relevant workbooks or
sheets. In the addin the first part of the code might need to validate user
has clicked the toolbar button with an appropriate sheet active. Of course
this is not a consideration if the addin is only called with a button on
"appropriate" sheets.

You have choices!

Regards,
Peter T