Replacing Excel Add-In
 

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!

Replacing Excel Add-In
 

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!

Replacing Excel Add-In
 

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.

Replacing Excel Add-In
 

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!

Replacing Excel Add-In
 

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.

Replacing Excel Add-In
 

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.

Replacing Excel Add-In
 

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.

Replacing Excel Add-In
 

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!

Replacing Excel Add-In
 

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.

Replacing Excel Add-In
 

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!

Replacing Excel Add-In
 

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!

Replacing Excel Add-In
 

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!

Jens Thiel wrote:
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.

Replacing Excel Add-In
 

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.

On Tue, 12 Oct 2004 10:07:32 -0400, Matthew Wieder
wrote:

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!

Jens Thiel wrote:
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.

Replacing Excel Add-In
 

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

Myrna Larson wrote:

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.

On Tue, 12 Oct 2004 10:07:32 -0400, Matthew Wieder
wrote:


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!

Jens Thiel wrote:

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.

Replacing Excel Add-In
 

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.
--
http://ManagedXLL.net/
Replace MSDN with my first name when replying to my email address!



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

Jens Thiel wrote:
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.

Replacing Excel Add-In
 

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


On Tue, 12 Oct 2004 10:28:44 -0400, Matthew Wieder
wrote:

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

Myrna Larson wrote:

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.

On Tue, 12 Oct 2004 10:07:32 -0400, Matthew Wieder
wrote:


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!

Jens Thiel wrote:

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.

Replacing Excel Add-In
 

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!

Myrna Larson wrote:
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).


On Tue, 12 Oct 2004 10:28:44 -0400, Matthew Wieder
wrote:


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

Myrna Larson wrote:


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.

On Tue, 12 Oct 2004 10:07:32 -0400, Matthew Wieder
wrote:



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!

Jens Thiel wrote:


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.

Replacing Excel Add-In
 

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

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.

Replacing Excel Add-In
 

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

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


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

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.

Replacing Excel Add-In
 

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!

Replacing Excel Add-In
 

Thanks for the research!
So there may be no winning solution here to have the replacement happen
"behind the scenes"...

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

Replacing Excel Add-In
 

I guess that would do it. I wish there was some cleaner way, but there
isn't if the workbook is holding references to the fast it was a
compiled xll before and now it's a vba add-in. Thanks for your help.
-Matthew

Jens Thiel wrote:

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

Jens

Replacing Excel Add-In
 

you could still include my "monitor" solution
in the new addin.

but the scanning mechanism will be a lot harder
as you cannot simply check the "links"

on EVERY opened file:
it would look for XLA links that dont go to the currently
installed XLA (different path or something) for selfrepair
the install location. (system32 NOT recommended for xla)


it would need:
to recalc the workbook
then check for Error values
then check these error values are related to the missing XLL

and finally it would be simple: .formula=.formula

...but the bulletproof selection
of the cells to apply that line to..

THAT's gonna be a pain :)




keepITcool

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


Matthew Wieder wrote:

Thanks for the research!
So there may be no winning solution here to have the replacement
happen "behind the scenes"...

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

Replacing Excel Add-In
 

The following simplified code worked for me without an error:

Set Rng = ThisWorkbook.Worksheets(1).UsedRange. _
SpecialCells(xlCellTypeFormulas)
Rng.Formula = Rng.Formula

That should run MUCH faster than a loop.

As for not running this every time a workbook opens, your addin has a
worksheet, right?

You could keep a list on that worksheet that gives the names of workbooks that
have been "fixed" and (maybe) the date that the code executed.

Then modify the routine below to first check the list for the name of the
active workbook. If it's on the list, exit the sub. If it's not, run the rest
of the code, then add the name to the list.

Or you could write a flag to the workbook itself, in some out-of-the-way
place, check it, and update it when the code has been run.

Or instead of a flag or list, you can select cells containing errors. If there
are any, make the assumption that it's due to the current problem and replace
the formulas


On Tue, 12 Oct 2004 13:19:42 -0400, Matthew Wieder
wrote:

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!

Myrna Larson wrote:
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).


On Tue, 12 Oct 2004 10:28:44 -0400, Matthew Wieder
wrote:


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

Myrna Larson wrote:


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.

On Tue, 12 Oct 2004 10:07:32 -0400, Matthew Wieder
wrote:



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!

Jens Thiel wrote:


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.

Replacing Excel Add-In
 

RE the code below: It ran without an error, and Rng.Formula does return a
variant array with the formula for each cell, but after presumably just
replacing them, I had #NA! errors all over the place. i.e. the formulas were
TOTALLY screwed up! It won't work, I guess.

The following simplified code worked for me without an error:

Set Rng = ThisWorkbook.Worksheets(1).UsedRange. _
SpecialCells(xlCellTypeFormulas)
Rng.Formula = Rng.Formula

That should run MUCH faster than a loop.

Replacing Excel Add-In
 

thanks for trying. Could you give more detail about the solution you
suggested of "develop an installer routine that removes the XLL, and
installs the XLA" - how would that work?

Myrna Larson wrote:
RE the code below: It ran without an error, and Rng.Formula does return a
variant array with the formula for each cell, but after presumably just
replacing them, I had #NA! errors all over the place. i.e. the formulas were
TOTALLY screwed up! It won't work, I guess.


The following simplified code worked for me without an error:

Set Rng = ThisWorkbook.Worksheets(1).UsedRange. _
SpecialCells(xlCellTypeFormulas)
Rng.Formula = Rng.Formula

That should run MUCH faster than a loop.

Replacing Excel Add-In
 

No, I can't, because I've never done it. I'm just a lone, retired user,
working on stuff on my own local PC, not on a network.

But I'm sure there are folks here that can help with that.

On Tue, 12 Oct 2004 15:35:48 -0400, Matthew Wieder
wrote:

thanks for trying. Could you give more detail about the solution you
suggested of "develop an installer routine that removes the XLL, and
installs the XLA" - how would that work?

Myrna Larson wrote:
RE the code below: It ran without an error, and Rng.Formula does return a
variant array with the formula for each cell, but after presumably just
replacing them, I had #NA! errors all over the place. i.e. the formulas
were
TOTALLY screwed up! It won't work, I guess.


The following simplified code worked for me without an error:

Set Rng = ThisWorkbook.Worksheets(1).UsedRange. _
SpecialCells(xlCellTypeFormulas)
Rng.Formula = Rng.Formula

That should run MUCH faster than a loop.

Replacing Excel Add-In
 

I don't need help coding the install routine, I just don't understand
what your suggestion is...

Myrna Larson wrote:
No, I can't, because I've never done it. I'm just a lone, retired user,
working on stuff on my own local PC, not on a network.

But I'm sure there are folks here that can help with that.

On Tue, 12 Oct 2004 15:35:48 -0400, Matthew Wieder
wrote:


thanks for trying. Could you give more detail about the solution you
suggested of "develop an installer routine that removes the XLL, and
installs the XLA" - how would that work?

Myrna Larson wrote:

RE the code below: It ran without an error, and Rng.Formula does return a
variant array with the formula for each cell, but after presumably just
replacing them, I had #NA! errors all over the place. i.e. the formulas

were

TOTALLY screwed up! It won't work, I guess.



The following simplified code worked for me without an error:

Set Rng = ThisWorkbook.Worksheets(1).UsedRange. _
SpecialCells(xlCellTypeFormulas)
Rng.Formula = Rng.Formula

That should run MUCH faster than a loop.

Replacing Excel Add-In
 

It would be a separate program -- the sort of thing that runs when you install
some new software. It would copy your XLA to the correct directory, make any
changes to the registry that are needed, maybe open Excel behind the scenes
and remove the old item from the add-ins list and add the new one, etc. Folks
often buy software that write the installer program for them.

On Wed, 13 Oct 2004 10:49:09 -0400, Matthew Wieder
wrote:

I don't need help coding the install routine, I just don't understand
what your suggestion is...

Myrna Larson wrote:
No, I can't, because I've never done it. I'm just a lone, retired user,
working on stuff on my own local PC, not on a network.

But I'm sure there are folks here that can help with that.

On Tue, 12 Oct 2004 15:35:48 -0400, Matthew Wieder
wrote:


thanks for trying. Could you give more detail about the solution you
suggested of "develop an installer routine that removes the XLL, and
installs the XLA" - how would that work?

Myrna Larson wrote:

RE the code below: It ran without an error, and Rng.Formula does return a
variant array with the formula for each cell, but after presumably just
replacing them, I had #NA! errors all over the place. i.e. the formulas

were

TOTALLY screwed up! It won't work, I guess.



The following simplified code worked for me without an error:

Set Rng = ThisWorkbook.Worksheets(1).UsedRange. _
SpecialCells(xlCellTypeFormulas)
Rng.Formula = Rng.Formula

That should run MUCH faster than a loop.