Replacing Excel Add-In

[Matthew Wieder]

We have an old .xll Add-In that exposes a number of
user-defined-functions that we want to rewrite and replace with a new
add-in. We want the transition for those using the add-in to be as
painless as possible for our users. As a test, I created a new
workbook, added two functions (named the same as in the old add-in) in a
module and saved it as an .xla. Then I renamed it to be a .xll and
overwrote the existing add-in. When I open a worksheet that contains
one of the functions that I included in the new add-in, and then I hit
F9, I get a #VALUE in the cell. Only if I go to the cell and hit enter,
does it properly calc using the new Add-In. How can I make it so that
F9 would work the first time and I don't have to go to each cell
containing a formula and hit enter?
thanks!

 

[Bob Phillips]

Why did you rename it to xll? An xll is a C addin, not VBA. Normally, when
you update an addin, save it in the same place, and it should then be
available next time you reload Excel.

 

[Matthew Wieder]

as I said, the initial add-in was an .xll so in order to have a clean
replace, I had to replace the file with the same name. It still works
as an add-in renamed; that doesn't appear to be the problem. Also,
understand, I'm not updating an add-in but replacing it.

 

[keepITcool]

Matthew,

If the new addin is named the same as the old,
AND it is saved in the same location..

the full path to the addin should be the same.
if that is the case I cannot reproduce the error.

(with the addin unloaded it would resolve to something like:
='D:\Profiles\XXXUSERNAMEXXX\Application Data\Microsoft\AddIns
\MyFunctions.xll'!doublethis(B1)

Now you load the addin and the formula
DISPLAYS =doublethis(b1)


When you move the addin to a new location
(central on the departmental server?), the
links will NOT auto adapt.

What you CAN do in your new addin is setup is setup a routine
to autoupdate those links

It will monitor ALL workbook open events. Check for workbooks containing
links to the old addin and change those links to the new addin.
(preferably named with XLA extension)

Copy following code in Thisworkbook module in the NEW addin.
(dont forget to edit the names...
(dont forget to fire up the workbook open event during testing,
else the xlApp variable isn't set thus isnt; monitoring.


Option Explicit
Dim WithEvents xlAPP As Excel.Application

Private Sub Workbook_Open()
Set xlAPP = Excel.Application
xlAPP.AskToUpdateLinks = False

End Sub

Private Sub xlAPP_WorkbookOpen(ByVal Wb As Workbook)
Dim aLinks, i%
aLinks = Wb.LinkSources(xlExcelLinks)
If IsEmpty(aLinks) Then Exit Sub
Application.DisplayAlerts = False
For i = UBound(aLinks) To LBound(aLinks) Step -1
If LCase$(aLinks(i)) Like "*myfunctions.xll" Then
Call Wb.ChangeLink(CStr(aLinks(i)), Me.FullName, xlLinkTypeExcelLinks)
End If
Next
Application.DisplayAlerts = True

End Sub


Any question? feel free to email.
Be aware that in Amsterdam it's getting late,
so I'll be signing off soon.

suc6!


keepITcool

< email : keepitcool chello nl (with @ and .) >
< homepage: http://members.chello.nl/keepitcool >

 

[Bob Phillips]

I can't say I have ever tried to replace a C add-in with a VBA add-in, but I
wouldn't be surprised if it didn't work.

Why not call it a .xla, uninstall the xll, and install the xla
(Tools>Addins>Browse)?

 

[Rob van Gelder]

The two formats are incompatible.
You cannot pretend that an XLA is an XLL - Excel will notice the difference.

To write an XLL, you need to write it in a language which can compile DLLs -
such as C.

William Hooper has some good info on XLLs at:
http://www.whooper.co.uk/excelstuff.htm


If the XLL functions are used in only one workbook, you could skip the
add-in drama and include the functions in the workbook itself.

 

[Matthew Wieder]

I'm not sure what you guys are talking about - I know the difference
between an xll and an xla; did you see my post where I said that if I
take an xla rename it to an xll that it works? The issue I'm
experiencing is that I have to hit enter on the formula in order to get
it to work but that shouldn't be due to the xll. If you have a
suggestion as to how to solve my issue let me know.

 

[Matthew Wieder]

Thanks for responding. Are you saying to me that you can replace the
add-in file (not in the excel add-in menu, but the actual file on the
hard drive) with a brand new add-in (not a change to the existing one)
and the formulas re-evalutae correctly on F9? I am not experiencing
that behavior...

 

[Jens Thiel]

Matthew,

I have only experience migrating from XLA to XLL, so I might be wrong here.

As opposed to XLA add-ins, references to XLL add-ins are not saved with the
workbook, and you can not use the LinkSources property as previously
described. I doubt that simply replacing the XLL file with a renamed XLA
file will work at all.

Can you try if replacing the initial '=' in the formula triggers the new
XLA-based code? In this case, you can write an event listener as described
by keepITcool, and do an automatic search and replace on the '='.

Hope this helps. If you solve this, please let me know how you finally did
it.

Jens.

 

[keepITcool]

Matthew,

a misunderstanding on my part.
I tried it with two normal xla's. NOT a compiled XLL.
and I dont think it;ll be that easy to exchange an xll for a renamed
xla...
And the disabling of the REGISTER macrofunction doesnt help either.

However.. there's always solutions (or at least challenges

I'm planning to do some digging into this subject..
as I'm getting curious on how this could be achieved. <g>

Pls give feedback on following:

Is your intention to create a NEW compiled xll or do you want to
exchange it for a VBA xla?

Is the new xll located in the same path as the old?
If so which folder?




keepITcool

< email : keepitcool chello nl (with @ and .) >
< homepage: http://members.chello.nl/keepitcool >

 

[Matthew Wieder]

Thanks for your help!
We have old C code that was compiled into an .xll that we want to
replcae with a newly written VBA add-in. So we are realyl replcaing an
..xll with and .xla, but we are trying to make the transition painless,
which is why we changed the extension of the new .xla to .xll. The .xll
was located in the system32 directory and that is where we would replace
it with the new add-in.
thanks!

 

[Matthew Wieder]

the search and replace through all the formulas does work but we don't
want to have to execute code on each workbook that has formulas in it.
As I have said, the renaiming of the .xla to .xll does work; it just
requries us to hit enter (or search replace) on each formula in the
sheet. I dumped the xml of the sheet and it doesn't show any references
to the add-in - all it shows are the formulas which is what I would
expect. I don't understand how or why it is keeping track of the
formula from which add-in it was using so that it now knows the
difference. All help is appreciated.
thanks!

 

[Myrna Larson]

Instead of editing each cell, can you get rid of the errors if you press
CTRL+ALT+F9? That forces a recalculation of all formulas.

If that works, then maybe in the XLA you can execute code to do that. Check
out the CalculateFull and CalculateFullRebuild methods in Help.

 

[Matthew Wieder]

CTRL+ALT+F9 doesn't do it either...

Instead of editing each cell, can you get rid of the errors if you press
CTRL+ALT+F9? That forces a recalculation of all formulas.

If that works, then maybe in the XLA you can execute code to do that. Check
out the CalculateFull and CalculateFullRebuild methods in Help.

 

[Jens Thiel]

Hi Matthew,

I think the XML dump is different from the default XLS file format.
Experience (and the SDK docs) show that workbooks are keeping a reference to
XLAs. Renaming the XLA to XLL is something different here as Excel can
recognize the file format. You could as well move the XLA to the XLSTART
folder (and that's what I suggest - you will run into different problems
otherwise).

You do not need to update all workbooks manually. Simply implement the code
to update the worksheets in your new XLA. It has to be done only for
workbooks that do not reference your new XLA in the LinkSources collection.
Solutions like this are implemented on even the largest sites...

Jens.

 

[Myrna Larson]

When you addin loads, could you have it execute a routine that goes through
the sheet like this:

For Each Cell In Worksheets("Sheet1").UsedRange
If Cell.HasFormula Then
Cell.Formula = Cell.Formula
End If
Next Cell

If that doesn't work or is too slow, then your idea of just fudging the file
name probably isn't going to work, and you'll have to develop an installer
routine that removes the XLL, and installs the XLA (in the XLSTART folder, not
Windows/System or wherever you said the XLL is located).

 

[Matthew Wieder]

That is going to be way too slow to run every time. This will be done
via installer, so what is your suggestion for how to make this work that
you mentioned? Just installing the add-in doesn't resolve this issue...
thanks!

 

[Matthew Wieder]

Hi - thanks for the proposed work-around. It seems like it will work,
but how do I stop the "replace =" code from executing every time it
opens? If I have a clause like you say that checks for whether the
LinkSources collection references the new add-in, then the code will
execute for any and all excel workbooks, not just ones which had links
to our old add-in; workbooks executing our code would be optimized, but
all other workbook swould be really slow every time...

 

[Jens Thiel]

Hi Matthew, you could set a custom property on every converted book,a nd
check for that property before doing the "replace =" thing.

Jens

 

[keepITcool]

The compiled XLL works entirely different from VBA addins.
it's functions are so called registered functions
(registered in the global namespace... thus the "consumer" workbook
will not maintain any links to an xla file.


also.. Application.RegisterXLL wont work on an (uncompiled)
VBA addin...

Also XLL functions are generally faster then XLA,
and there's less hassle with linking...
so I doubt any transfer will be painless.


Sub PeekABoo()
Dim aFun, wn
'Feeble attempt to make the local window visible
For Each wn In Application.VBE.Windows
If wn.Type = 4 Then
wn.Visible = True
Exit For
End If
Next

'If XLLs are loaded it's functions should
'appear in this array
aFun = Application.RegisteredFunctions
Stop
End Sub


keepITcool

< email : keepitcool chello nl (with @ and .) >
< homepage: http://members.chello.nl/keepitcool >