Hi,

We have rules software which uses multiple Excel sheet with hundrerds of
fields where data is filled in by macros. The load time of the sheet when
called from application is 4-5 minutes and we want to reduce it.

The approach we are taking is to create a Visual Basic Automation Add-in for
Excel Worksheet Functions.

http://support.microsoft.com/kb/285337/ ( This is my reference. I am new to
the Excel programming)

This is what I did.

1. Copied all the VB code that were called from the Excel Sheet under the
modules and made one Automation DLL.
2. Deleted all the old modules so that old codes are not there.
3. Next I loaded the DLL from Tools-AddIns.

Since we have kept the function name in DLL same, I was expecting that the
Excel Sheet would work seamlessly using the function from the DLL without
changing any macro code in the Excel Worksheets. But I am not getting any
output. When however I type in a AutomationAddin.Connect.function(arg1,arg2)
the output is coming correctly. (automationAddIn.dll is the name of my DLL).
Somwhere I believe the sheet is refering to the older code. I created a new
worksheet and used some test Add function in the same DLL I noticed that they
work fine by using simply Add(B1,B2) rather than
AutomationAddIn.Connect.Add(B1,B2)


Can anybody help me with the problem.
Note : I have thousands of cells in the Excel and I cannot go to all the
cells and put any prefix. I hope it works somehow without the prefix
AutomationAddin.Connect.

Thanks in advance,
Abhijit

Try running code like

set rng = activesheet.usedrange.specialcells(xlformulas)
for each cell in rng
sForm = cell.formula
Cell.ClearContents
cell.Formula = sForm
Next

Test it on a copy of your workbook.

--
Regards,
Tom Ogilvy

"Abhijit" wrote in message
...
Hi,

We have rules software which uses multiple Excel sheet with hundrerds of
fields where data is filled in by macros. The load time of the sheet when
called from application is 4-5 minutes and we want to reduce it.

The approach we are taking is to create a Visual Basic Automation Add-in
for
Excel Worksheet Functions.

http://support.microsoft.com/kb/285337/ ( This is my reference. I am new
to
the Excel programming)

This is what I did.

1. Copied all the VB code that were called from the Excel Sheet under the
modules and made one Automation DLL.
2. Deleted all the old modules so that old codes are not there.
3. Next I loaded the DLL from Tools-AddIns.

Since we have kept the function name in DLL same, I was expecting that
the
Excel Sheet would work seamlessly using the function from the DLL without
changing any macro code in the Excel Worksheets. But I am not getting any
output. When however I type in a
AutomationAddin.Connect.function(arg1,arg2)
the output is coming correctly. (automationAddIn.dll is the name of my
DLL).
Somwhere I believe the sheet is refering to the older code. I created a
new
worksheet and used some test Add function in the same DLL I noticed that
they
work fine by using simply Add(B1,B2) rather than
AutomationAddIn.Connect.Add(B1,B2)


Can anybody help me with the problem.
Note : I have thousands of cells in the Excel and I cannot go to all the
cells and put any prefix. I hope it works somehow without the prefix
AutomationAddin.Connect.

Thanks in advance,
Abhijit

Hi Tom,

Thanks for the reply.


Based on the reply I created a toolbar button in excel which calls a Excel
function ClearFormula inside which I put the code you sent. But it did not
go through.

On debugging I saw it failed in cases where cells are merged. The runtime
error code is 1004
Msg : "Cannot change part of Merged Cell"

Also I have about 12 different worksheets and in some cases one field in
worksheet 5 can refer to some field in worksheet 1. Do you think this could
be
a problem?

Do you have any more comment on this. I am using Excel 2003 fyi.

Thanks,
Abhijit

"Tom Ogilvy" wrote:

Try running code like

set rng = activesheet.usedrange.specialcells(xlformulas)
for each cell in rng
sForm = cell.formula
Cell.ClearContents
cell.Formula = sForm
Next

Test it on a copy of your workbook.

--
Regards,
Tom Ogilvy

"Abhijit" wrote in message
...
Hi,

We have rules software which uses multiple Excel sheet with hundrerds of
fields where data is filled in by macros. The load time of the sheet when
called from application is 4-5 minutes and we want to reduce it.

The approach we are taking is to create a Visual Basic Automation Add-in
for
Excel Worksheet Functions.

http://support.microsoft.com/kb/285337/ ( This is my reference. I am new
to
the Excel programming)

This is what I did.

1. Copied all the VB code that were called from the Excel Sheet under the
modules and made one Automation DLL.
2. Deleted all the old modules so that old codes are not there.
3. Next I loaded the DLL from Tools-AddIns.

Since we have kept the function name in DLL same, I was expecting that
the
Excel Sheet would work seamlessly using the function from the DLL without
changing any macro code in the Excel Worksheets. But I am not getting any
output. When however I type in a
AutomationAddin.Connect.function(arg1,arg2)
the output is coming correctly. (automationAddIn.dll is the name of my
DLL).
Somwhere I believe the sheet is refering to the older code. I created a
new
worksheet and used some test Add function in the same DLL I noticed that
they
work fine by using simply Add(B1,B2) rather than
AutomationAddIn.Connect.Add(B1,B2)


Can anybody help me with the problem.
Note : I have thousands of cells in the Excel and I cannot go to all the
cells and put any prefix. I hope it works somehow without the prefix
AutomationAddin.Connect.

Thanks in advance,
Abhijit

You could code around these problems and have it act only on cells that
contain your formula

set rng = activesheet.usedrange.specialcells(xlformulas)
for each cell in rng
sForm = cell.formula
if instr(sForm,"ADD") then
if cell.Address = cell.MergeArea(1).Address then
Cell.Formula = ""
cell.Formula = sForm
end if
end if
Next

Untested, but believe this would work.

--
Regards,
Tom Ogilvy


"Abhijit" wrote in message
...

Hi Tom,

Thanks for the reply.


Based on the reply I created a toolbar button in excel which calls a Excel
function ClearFormula inside which I put the code you sent. But it did
not
go through.

On debugging I saw it failed in cases where cells are merged. The runtime
error code is 1004
Msg : "Cannot change part of Merged Cell"

Also I have about 12 different worksheets and in some cases one field in
worksheet 5 can refer to some field in worksheet 1. Do you think this
could
be
a problem?

Do you have any more comment on this. I am using Excel 2003 fyi.

Thanks,
Abhijit

"Tom Ogilvy" wrote:

Try running code like

set rng = activesheet.usedrange.specialcells(xlformulas)
for each cell in rng
sForm = cell.formula
Cell.ClearContents
cell.Formula = sForm
Next

Test it on a copy of your workbook.

--
Regards,
Tom Ogilvy

"Abhijit" wrote in message
...
Hi,

We have rules software which uses multiple Excel sheet with hundrerds
of
fields where data is filled in by macros. The load time of the sheet
when
called from application is 4-5 minutes and we want to reduce it.

The approach we are taking is to create a Visual Basic Automation
Add-in
for
Excel Worksheet Functions.

http://support.microsoft.com/kb/285337/ ( This is my reference. I am
new
to
the Excel programming)

This is what I did.

1. Copied all the VB code that were called from the Excel Sheet under
the
modules and made one Automation DLL.
2. Deleted all the old modules so that old codes are not there.
3. Next I loaded the DLL from Tools-AddIns.

Since we have kept the function name in DLL same, I was expecting
that
the
Excel Sheet would work seamlessly using the function from the DLL
without
changing any macro code in the Excel Worksheets. But I am not getting
any
output. When however I type in a
AutomationAddin.Connect.function(arg1,arg2)
the output is coming correctly. (automationAddIn.dll is the name of my
DLL).
Somwhere I believe the sheet is refering to the older code. I created
a
new
worksheet and used some test Add function in the same DLL I noticed
that
they
work fine by using simply Add(B1,B2) rather than
AutomationAddIn.Connect.Add(B1,B2)


Can anybody help me with the problem.
Note : I have thousands of cells in the Excel and I cannot go to all
the
cells and put any prefix. I hope it works somehow without the prefix
AutomationAddin.Connect.

Thanks in advance,
Abhijit

Hi Tom,

Thanks for the prompt reply.

But unfortunately it did not give the desired result. FIrst time I ran, none
of the cell got updated as "if instr(sForm,"ADD") then" was never true. SO I
commented it out and then the cells got touched up.

The strange thing is any new excel sheet, the functions from the DLL works
fine. Only in my old sheets they do not. Even though I deleted all the old
code in the modules. Wonder how those old references may be still active!!

Is there anything else that comes to your mind. I was thinking worse case I
could may be parse the cell formula and put AutomationAddin.Connect in front
of every formula we have. But hope there is another way.

I was reading a technical article and it stated as follows:

----------------------------------------------------------------------------------------
Binding for functions in an Automation Add-in is at the end of the function
binding precedence. If you have a function in your Automation Add-in with the
same name as an Excel built-in function, the Excel built-in function will
take precedence. VBA functions in workbooks and regular Add-ins (.xla) also
take precedence over Automation Add-in functions. When you create your own
functions for use in Excel, it is recommended that you do not give your
functions names that are already in use by Excel built-in functions.

To specifically call a function in an Automation Add-in, you can drill down
to the function in a formula by using a syntax such as
ServerName.ClassName.FunctionName(...). For example, to call the Add1
function in the sample, you could use the following:
=AutomationAddin.XLFunctions.Add1(1,2)
You can only call top-level methods and properties of your Automation
Add-in; you cannot walk down the object model of your Add-in to call
functions that are not at the top level.
----------------------------------------------------------------------------------------------

Thanks,
Abhijit





"Tom Ogilvy" wrote:

You could code around these problems and have it act only on cells that
contain your formula

set rng = activesheet.usedrange.specialcells(xlformulas)
for each cell in rng
sForm = cell.formula
if instr(sForm,"ADD") then
if cell.Address = cell.MergeArea(1).Address then
Cell.Formula = ""
cell.Formula = sForm
end if
end if
Next

Untested, but believe this would work.

--
Regards,
Tom Ogilvy


"Abhijit" wrote in message
...

Hi Tom,

Thanks for the reply.


Based on the reply I created a toolbar button in excel which calls a Excel
function ClearFormula inside which I put the code you sent. But it did
not
go through.

On debugging I saw it failed in cases where cells are merged. The runtime
error code is 1004
Msg : "Cannot change part of Merged Cell"

Also I have about 12 different worksheets and in some cases one field in
worksheet 5 can refer to some field in worksheet 1. Do you think this
could
be
a problem?

Do you have any more comment on this. I am using Excel 2003 fyi.

Thanks,
Abhijit

"Tom Ogilvy" wrote:

Try running code like

set rng = activesheet.usedrange.specialcells(xlformulas)
for each cell in rng
sForm = cell.formula
Cell.ClearContents
cell.Formula = sForm
Next

Test it on a copy of your workbook.

--
Regards,
Tom Ogilvy

"Abhijit" wrote in message
...
Hi,

We have rules software which uses multiple Excel sheet with hundrerds
of
fields where data is filled in by macros. The load time of the sheet
when
called from application is 4-5 minutes and we want to reduce it.

The approach we are taking is to create a Visual Basic Automation
Add-in
for
Excel Worksheet Functions.

http://support.microsoft.com/kb/285337/ ( This is my reference. I am
new
to
the Excel programming)

This is what I did.

1. Copied all the VB code that were called from the Excel Sheet under
the
modules and made one Automation DLL.
2. Deleted all the old modules so that old codes are not there.
3. Next I loaded the DLL from Tools-AddIns.

Since we have kept the function name in DLL same, I was expecting
that
the
Excel Sheet would work seamlessly using the function from the DLL
without
changing any macro code in the Excel Worksheets. But I am not getting
any
output. When however I type in a
AutomationAddin.Connect.function(arg1,arg2)
the output is coming correctly. (automationAddIn.dll is the name of my
DLL).
Somwhere I believe the sheet is refering to the older code. I created
a
new
worksheet and used some test Add function in the same DLL I noticed
that
they
work fine by using simply Add(B1,B2) rather than
AutomationAddIn.Connect.Add(B1,B2)


Can anybody help me with the problem.
Note : I have thousands of cells in the Excel and I cannot go to all
the
cells and put any prefix. I hope it works somehow without the prefix
AutomationAddin.Connect.

Thanks in advance,
Abhijit

Your article seems to indicate you need to use the expanded form. However,
you say in a new workbook you don't need to use it - so I can't say.

the use of

if instr
was to restrict the the cells operated on to those with the function.

perhaps you need the expanded version

if instr(1,sform,"add",vbtextcompare) then

however, you appeared to say the function did not help the problem, so that
is pretty much moot.

You might search the newgroup archives on google news. There have been
similar discussions on moving the location of a normal addin and getting
this type of problem, but I don't recall what the recommended solution is.
It might give you some ideas.

--
Regards,
Tom Ogilvy


"Abhijit" wrote in message
...
Hi Tom,

Thanks for the prompt reply.

But unfortunately it did not give the desired result. FIrst time I ran,
none
of the cell got updated as "if instr(sForm,"ADD") then" was never true. SO
I
commented it out and then the cells got touched up.

The strange thing is any new excel sheet, the functions from the DLL works
fine. Only in my old sheets they do not. Even though I deleted all the old
code in the modules. Wonder how those old references may be still active!!

Is there anything else that comes to your mind. I was thinking worse case
I
could may be parse the cell formula and put AutomationAddin.Connect in
front
of every formula we have. But hope there is another way.

I was reading a technical article and it stated as follows:

--------------------------------------------------------------------------
--------------
Binding for functions in an Automation Add-in is at the end of the
function
binding precedence. If you have a function in your Automation Add-in with
the
same name as an Excel built-in function, the Excel built-in function will
take precedence. VBA functions in workbooks and regular Add-ins (.xla)
also
take precedence over Automation Add-in functions. When you create your own
functions for use in Excel, it is recommended that you do not give your
functions names that are already in use by Excel built-in functions.

To specifically call a function in an Automation Add-in, you can drill
down
to the function in a formula by using a syntax such as
ServerName.ClassName.FunctionName(...). For example, to call the Add1
function in the sample, you could use the following:
=AutomationAddin.XLFunctions.Add1(1,2)
You can only call top-level methods and properties of your Automation
Add-in; you cannot walk down the object model of your Add-in to call
functions that are not at the top level.
--------------------------------------------------------------------------
--------------------

Thanks,
Abhijit





"Tom Ogilvy" wrote:

You could code around these problems and have it act only on cells that
contain your formula

set rng = activesheet.usedrange.specialcells(xlformulas)
for each cell in rng
sForm = cell.formula
if instr(sForm,"ADD") then
if cell.Address = cell.MergeArea(1).Address then
Cell.Formula = ""
cell.Formula = sForm
end if
end if
Next

Untested, but believe this would work.

--
Regards,
Tom Ogilvy


"Abhijit" wrote in message
...

Hi Tom,

Thanks for the reply.


Based on the reply I created a toolbar button in excel which calls a
Excel
function ClearFormula inside which I put the code you sent. But it
did
not
go through.

On debugging I saw it failed in cases where cells are merged. The
runtime
error code is 1004
Msg : "Cannot change part of Merged Cell"

Also I have about 12 different worksheets and in some cases one field
in
worksheet 5 can refer to some field in worksheet 1. Do you think this
could
be
a problem?

Do you have any more comment on this. I am using Excel 2003 fyi.

Thanks,
Abhijit

"Tom Ogilvy" wrote:

Try running code like

set rng = activesheet.usedrange.specialcells(xlformulas)
for each cell in rng
sForm = cell.formula
Cell.ClearContents
cell.Formula = sForm
Next

Test it on a copy of your workbook.

--
Regards,
Tom Ogilvy

"Abhijit" wrote in message
...
Hi,

We have rules software which uses multiple Excel sheet with
hundrerds
of
fields where data is filled in by macros. The load time of the
sheet
when
called from application is 4-5 minutes and we want to reduce it.

The approach we are taking is to create a Visual Basic Automation
Add-in
for
Excel Worksheet Functions.

http://support.microsoft.com/kb/285337/ ( This is my reference. I
am
new
to
the Excel programming)

This is what I did.

1. Copied all the VB code that were called from the Excel Sheet
under
the
modules and made one Automation DLL.
2. Deleted all the old modules so that old codes are not there.
3. Next I loaded the DLL from Tools-AddIns.

Since we have kept the function name in DLL same, I was
expecting
that
the
Excel Sheet would work seamlessly using the function from the DLL
without
changing any macro code in the Excel Worksheets. But I am not
getting
any
output. When however I type in a
AutomationAddin.Connect.function(arg1,arg2)
the output is coming correctly. (automationAddIn.dll is the name
of my
DLL).
Somwhere I believe the sheet is refering to the older code. I
created
a
new
worksheet and used some test Add function in the same DLL I
noticed
that
they
work fine by using simply Add(B1,B2) rather than
AutomationAddIn.Connect.Add(B1,B2)


Can anybody help me with the problem.
Note : I have thousands of cells in the Excel and I cannot go to
all
the
cells and put any prefix. I hope it works somehow without the
prefix
AutomationAddin.Connect.

Thanks in advance,
Abhijit