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

[Abhijit]

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

 

[Tom Ogilvy]

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.

 

[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]

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.

 

[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]

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.

 

[abhattachar]

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]

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

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

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

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:

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





:

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



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

:

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

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

 

[abhattachar]

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]

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

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

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

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


:

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


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:

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





:

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



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

:

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

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