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!

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.

--

HTH

RP

"Matthew Wieder" wrote in message
...
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!

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.

Bob Phillips wrote:

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.

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
(ToolsAddinsBrowse)?

--

HTH

RP

"Matthew Wieder" wrote in message
...
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.

Bob Phillips wrote:

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.

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.


--
Rob van Gelder - http://www.vangelder.co.nz/excel


"Matthew Wieder" wrote in message
...
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.

Bob Phillips wrote:

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.

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.

Rob van Gelder wrote:
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,

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.

--
http://ManagedXLL.net/
Replace MSDN with my first name when replying to my email address!



"Matthew Wieder" wrote in message
...
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.

Rob van Gelder wrote:
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,

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


Matthew Wieder wrote:

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!

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...


keepITcool wrote:
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


Matthew Wieder wrote:


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!

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 wrote:

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...


keepITcool wrote:
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


Matthew Wieder wrote:


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!

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!

keepITcool wrote:
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 wrote:


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...


keepITcool wrote:

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


Matthew Wieder wrote:



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!

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


Matthew Wieder wrote:

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!

keepITcool wrote:
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 wrote:


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...


keepITcool wrote:

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


Matthew Wieder wrote:



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!