How to use automation Add-In to replace Excel macros!!
 

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

How to use automation Add-In to replace Excel macros!!
 

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

How to use automation Add-In to replace Excel macros!!
 

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

How to use automation Add-In to replace Excel macros!!
 

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

How to use automation Add-In to replace Excel macros!!
 

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

How to use automation Add-In to replace Excel macros!!
 

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

How to use automation Add-In to replace Excel macros!!
 

Hi Tom,

One basic question, do you think if we can get this to work somehow, this
will speed up the load time now that we will be using a DLL.

Is there any other way we can handle this type of problem.

Abhijit


"Tom Ogilvy" wrote:

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

How to use automation Add-In to replace Excel macros!!
 

The only way to tell is to test it. My impression is that it would - but
that assumes that what the DLL replaces is what was causing the slow load
time in the first place. Otherwise it is a bandaid on the wrong location.

--
Regards,
Tom Ogilvy


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

One basic question, do you think if we can get this to work somehow, this
will speed up the load time now that we will be using a DLL.

Is there any other way we can handle this type of problem.

Abhijit


"Tom Ogilvy" wrote:

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

How to use automation Add-In to replace Excel macros!!
 

Tom,

On some further analysis, I noticed that even though I loaded the Add-In
from the tools menu earlier, when the excel is launched from my application,
the Addin is not visible under the Insert Function area which tells me that
somehow the AddIn functions are not accessible when I launch excel from my
Application. Under Tools Addin however I see the AddIn is ticked. Is there a
way to forcibly launch the Add-I? Like in case of .xla files I read that
they can be put in XLStart directory, do Addins have any similar practices.
(But again this is a DLL)

One other point. When I opened a new test worksheet where I was using the
functions from the Addin I see #NAME? coming for the fields. But as soon as I
unload and load the Addin all data is populated.

Thanks,
Abhijit

"Tom Ogilvy" wrote:

The only way to tell is to test it. My impression is that it would - but
that assumes that what the DLL replaces is what was causing the slow load
time in the first place. Otherwise it is a bandaid on the wrong location.

--
Regards,
Tom Ogilvy


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

One basic question, do you think if we can get this to work somehow, this
will speed up the load time now that we will be using a DLL.

Is there any other way we can handle this type of problem.

Abhijit


"Tom Ogilvy" wrote:

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

How to use automation Add-In to replace Excel macros!!
 

Abhijit,
When I answered your initial question, I was just providing a suggestion on
how to automate what you were doing manually. I wasn't representing myself
as an expert on the use of DLLs to do worksheet functions. (my suggestion
had nothing to do with DLL's specifically).
This isn't an area of expertise for me, so I suggest you post these
questions as a new thread.

--
Regards,
Tom Ogilvy



"abhattachar" wrote in message
...

Tom,

On some further analysis, I noticed that even though I loaded the Add-In
from the tools menu earlier, when the excel is launched from my
application,
the Addin is not visible under the Insert Function area which tells me
that
somehow the AddIn functions are not accessible when I launch excel from my
Application. Under Tools Addin however I see the AddIn is ticked. Is
there a
way to forcibly launch the Add-I? Like in case of .xla files I read that
they can be put in XLStart directory, do Addins have any similar
practices.
(But again this is a DLL)

One other point. When I opened a new test worksheet where I was using the
functions from the Addin I see #NAME? coming for the fields. But as soon
as I
unload and load the Addin all data is populated.

Thanks,
Abhijit

"Tom Ogilvy" wrote:

The only way to tell is to test it. My impression is that it would -
but
that assumes that what the DLL replaces is what was causing the slow
load
time in the first place. Otherwise it is a bandaid on the wrong
location.

--
Regards,
Tom Ogilvy


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

One basic question, do you think if we can get this to work somehow,
this
will speed up the load time now that we will be using a DLL.

Is there any other way we can handle this type of problem.

Abhijit


"Tom Ogilvy" wrote:

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