In the ThisWorkbook module of a spreadsheet I have the statement:
AddIns("RollForm").Installed = True
The next line calls a sub in RollForm.xla.

The call fails because RollForm.xla is not yet loaded. If I step through
the code, it works fine.

How can I program a wait for RollForm.xla to be loaded?


Thanks!

Untested, but I'd try:

AddIns("RollForm").Installed = False
AddIns("RollForm").Installed = True

If that doesn't work, then maybe it is a timing issue.

You could use:

AddIns("RollForm").Installed = False
AddIns("RollForm").Installed = True
Application.OnTime Now + timeSerial(0,0,1), "Continue_Open"

End Sub

And the put the remainder of the code in that Continue_Open in a General module.


On 06/22/2010 13:57, wrote:
In the ThisWorkbook module of a spreadsheet I have the statement:
AddIns("RollForm").Installed = True
The next line calls a sub in RollForm.xla.

The call fails because RollForm.xla is not yet loaded. If I step through
the code, it works fine.

How can I program a wait for RollForm.xla to be loaded?


Thanks!


--
Dave Peterson

Thanks for your reply. Your suggestion using OnTime works. Thank you.

I do have a question however. I found that, until I implemented your
solution, VB was calling "RollForm.xla!Auto_Add".

What is Auto_Add() for? What code should I put in it if I ever use it?
What does using your code keep it from calling AutoAdd()?

Thanks!!

On Tue, 22 Jun 2010 14:11:56 -0500, Dave Peterson
wrote:

Untested, but I'd try:

AddIns("RollForm").Installed = False
AddIns("RollForm").Installed = True

If that doesn't work, then maybe it is a timing issue.

You could use:

AddIns("RollForm").Installed = False
AddIns("RollForm").Installed = True
Application.OnTime Now + timeSerial(0,0,1), "Continue_Open"

End Sub

And the put the remainder of the code in that Continue_Open in a General module.


On 06/22/2010 13:57, wrote:
In the ThisWorkbook module of a spreadsheet I have the statement:
AddIns("RollForm").Installed = True
The next line calls a sub in RollForm.xla.

The call fails because RollForm.xla is not yet loaded. If I step through
the code, it works fine.

How can I program a wait for RollForm.xla to be loaded?


Thanks!

Auto_Add is a procedure in that RollForm.xla addin.

I've never heard of that addin, so I don't have a guess what the procedure
actually does.

And it isn't one of the Auto run procedure names (Workbook_Open or Auto_Open)
that excel looks for when the workbook/addin is opened.

If the addin's project is unprotected, maybe you could look at the code?



On 06/22/2010 15:25, wrote:
Thanks for your reply. Your suggestion using OnTime works. Thank you.

I do have a question however. I found that, until I implemented your
solution, VB was calling "RollForm.xla!Auto_Add".

What is Auto_Add() for? What code should I put in it if I ever use it?
What does using your code keep it from calling AutoAdd()?

Thanks!!

On Tue, 22 Jun 2010 14:11:56 -0500, Dave Peterson
wrote:

Untested, but I'd try:

AddIns("RollForm").Installed = False
AddIns("RollForm").Installed = True

If that doesn't work, then maybe it is a timing issue.

You could use:

AddIns("RollForm").Installed = False
AddIns("RollForm").Installed = True
Application.OnTime Now + timeSerial(0,0,1), "Continue_Open"

End Sub

And the put the remainder of the code in that Continue_Open in a General module.


On 06/22/2010 13:57, wrote:
In the ThisWorkbook module of a spreadsheet I have the statement:
AddIns("RollForm").Installed = True
The next line calls a sub in RollForm.xla.

The call fails because RollForm.xla is not yet loaded. If I step through
the code, it works fine.

How can I program a wait for RollForm.xla to be loaded?


Thanks!



--
Dave Peterson

The addin does not have an Auto_Add routine. (I wrote the addin).
However, when I added the line:
AddIns("RollForm").Installed = True
to Workbook_Activate(), Excel called RollForm.xla!Auto_Add and I received a
message that it couldn't find it. So I added:
sub Auto_Add()
end sub
to the addin and the message went away.

I put a Stop statement in Auto_Add and opened the Workbook and Excel 2003
did call Auto_Add. Some other things did not work and I assumed I was
supposed to include code in Sub Auto_Add to handle something or other, but
I had no idea what.

Via Google, I found that when you use AddIns("AddinName").Installed = True,
excel calls the addin's Auto_Add function but I found nothing describing
the function. (See:
http://databaseforum.info/16/9/cd250ee5bc1819d2.html
http://www.mrexcel.com/forum/showthread.php?t=35553
http://support.microsoft.com/kb/291294

http://msdn.microsoft.com/en-us/libr...ice.12%29.aspx

Several people mentioned that when they got the error they could
immediately step through the program with no errors. I also experienced
this which led me to think it might be a timing problem.

When I added the code you suggested to ThisWorkbook, Excel no longer seems
to call Auto_Add and everything works fine.

Prior to adding you code and when I had an Auto_Add routing which did
nothing, the error went away, the routine was called but there were all
sorts of other strange errors - example: The addin creates and writes a
text file, then closes it. In certain situations, it re-opens the file and
reads it. When Auto_Add was being called, it would fail in various ways
when it tried to re-open the file.

My addin is working fine now, but I would still like to understand why
Auto_Add is being called and what the developer is supposed to put in it.

Thanks for any insight.


On Tue, 22 Jun 2010 16:39:27 -0500, Dave Peterson
wrote:

Auto_Add is a procedure in that RollForm.xla addin.

I've never heard of that addin, so I don't have a guess what the procedure
actually does.

And it isn't one of the Auto run procedure names (Workbook_Open or Auto_Open)
that excel looks for when the workbook/addin is opened.

If the addin's project is unprotected, maybe you could look at the code?



On 06/22/2010 15:25, wrote:
Thanks for your reply. Your suggestion using OnTime works. Thank you.

I do have a question however. I found that, until I implemented your
solution, VB was calling "RollForm.xla!Auto_Add".

What is Auto_Add() for? What code should I put in it if I ever use it?
What does using your code keep it from calling AutoAdd()?

Thanks!!

On Tue, 22 Jun 2010 14:11:56 -0500, Dave Peterson
wrote:

Untested, but I'd try:

AddIns("RollForm").Installed = False
AddIns("RollForm").Installed = True

If that doesn't work, then maybe it is a timing issue.

You could use:

AddIns("RollForm").Installed = False
AddIns("RollForm").Installed = True
Application.OnTime Now + timeSerial(0,0,1), "Continue_Open"

End Sub

And the put the remainder of the code in that Continue_Open in a General module.


On 06/22/2010 13:57, wrote:
In the ThisWorkbook module of a spreadsheet I have the statement:
AddIns("RollForm").Installed = True
The next line calls a sub in RollForm.xla.

The call fails because RollForm.xla is not yet loaded. If I step through
the code, it works fine.

How can I program a wait for RollForm.xla to be loaded?


Thanks!

suggestion...
Instead of calling the proc you want to run after the addin opens, why
not move that procs content into the Workbook_Open event or an
Auto_Open proc in a standard module? That would eliminate need for
pausing your code.

Also, is there any reason why you can't open the addin via:
Set wkbAddin = Workbooks.Open(FileName:="C:\RollForm.xla")

and if using Auto_Open instead of Workbook_Open, add this line:
wkbAddin.RunAutoMacros xlAutoOpen
?

Of course, there's nothing wrong with having the xla in the Addins
collection, but not having it in there does have some benefits.

--
Garry

Free usenet access at http://www.eternal-september.org
ClassicVB Users Regroup! comp.lang.basic.visual.misc

I read the kb article that you mentioned and Auto_Add was replaced by the
workbook event AddInInstall. (I had never seen that old procedure. Sorry for
misleading you.)

This is just a guess or maybe just questions...

How old is your addin? What version of excel did you use to create it? Maybe
it was xl95 -- before the VBE when macros were stored in macro sheets????

Maybe there's something (whatever that means) lingering in all the junk that's
accumulated in that code.

Maybe cleaning the code with Rob Bovey's code cleaner would help:
You can find it he
http://www.appspro.com/
or directly
http://www.appspro.com/Utilities/CodeCleaner.htm

This essentially exports all the modules and rebuilds them. Depending on what
your addin does, you may be able to just drag and drop the
modules/userforms/code from the addin to a new workbook's project.

Then save that new workbook as an addin and experiment with that fresh version.

If that doesn't help, then I think I'd leave that dummy function in the addin
(with a nice comment explaining why it's there!).

ps. VBA's help for the AddinInstall event shows some sample code -- it adds a
control to the standard toolbar. (At least in xl2003's version of the VBA Help.)

pps. I'm not sure what the technical difference is between the workbook_open
event and the workbook_addininstall event. I've always used the workbook_open
event for the things I want to do. Actually, I still use the Auto_Open
procedure for lots of things.




On 06/22/2010 18:13, wrote:
The addin does not have an Auto_Add routine. (I wrote the addin).
However, when I added the line:
AddIns("RollForm").Installed = True
to Workbook_Activate(), Excel called RollForm.xla!Auto_Add and I received a
message that it couldn't find it. So I added:
sub Auto_Add()
end sub
to the addin and the message went away.

I put a Stop statement in Auto_Add and opened the Workbook and Excel 2003
did call Auto_Add. Some other things did not work and I assumed I was
supposed to include code in Sub Auto_Add to handle something or other, but
I had no idea what.

Via Google, I found that when you use AddIns("AddinName").Installed = True,
excel calls the addin's Auto_Add function but I found nothing describing
the function. (See:
http://databaseforum.info/16/9/cd250ee5bc1819d2.html
http://www.mrexcel.com/forum/showthread.php?t=35553
http://support.microsoft.com/kb/291294

http://msdn.microsoft.com/en-us/libr...ice.12%29.aspx

Several people mentioned that when they got the error they could
immediately step through the program with no errors. I also experienced
this which led me to think it might be a timing problem.

When I added the code you suggested to ThisWorkbook, Excel no longer seems
to call Auto_Add and everything works fine.

Prior to adding you code and when I had an Auto_Add routing which did
nothing, the error went away, the routine was called but there were all
sorts of other strange errors - example: The addin creates and writes a
text file, then closes it. In certain situations, it re-opens the file and
reads it. When Auto_Add was being called, it would fail in various ways
when it tried to re-open the file.

My addin is working fine now, but I would still like to understand why
Auto_Add is being called and what the developer is supposed to put in it.

Thanks for any insight.


On Tue, 22 Jun 2010 16:39:27 -0500, Dave Peterson
wrote:

Auto_Add is a procedure in that RollForm.xla addin.

I've never heard of that addin, so I don't have a guess what the procedure
actually does.

And it isn't one of the Auto run procedure names (Workbook_Open or Auto_Open)
that excel looks for when the workbook/addin is opened.

If the addin's project is unprotected, maybe you could look at the code?



On 06/22/2010 15:25, wrote:
Thanks for your reply. Your suggestion using OnTime works. Thank you.

I do have a question however. I found that, until I implemented your
solution, VB was calling "RollForm.xla!Auto_Add".

What is Auto_Add() for? What code should I put in it if I ever use it?
What does using your code keep it from calling AutoAdd()?

Thanks!!

On Tue, 22 Jun 2010 14:11:56 -0500, Dave Peterson
wrote:

Untested, but I'd try:

AddIns("RollForm").Installed = False
AddIns("RollForm").Installed = True

If that doesn't work, then maybe it is a timing issue.

You could use:

AddIns("RollForm").Installed = False
AddIns("RollForm").Installed = True
Application.OnTime Now + timeSerial(0,0,1), "Continue_Open"

End Sub

And the put the remainder of the code in that Continue_Open in a General module.


On 06/22/2010 13:57, wrote:
In the ThisWorkbook module of a spreadsheet I have the statement:
AddIns("RollForm").Installed = True
The next line calls a sub in RollForm.xla.

The call fails because RollForm.xla is not yet loaded. If I step through
the code, it works fine.

How can I program a wait for RollForm.xla to be loaded?


Thanks!




--
Dave Peterson

Thanks for your replies. The entire thing was written in and is being used
in Excel 2003. It has the AddinInstall but when used, it will call
Auto_Add, at least in some situations. There is no explicit call to
Auto_Add in my code. (I didn't even know there was an Auto_Add until it
complained that it couldn't find it.)

On Tue, 22 Jun 2010 19:17:24 -0500, Dave Peterson
wrote:

I read the kb article that you mentioned and Auto_Add was replaced by the
workbook event AddInInstall. (I had never seen that old procedure. Sorry for
misleading you.)

This is just a guess or maybe just questions...

How old is your addin? What version of excel did you use to create it? Maybe
it was xl95 -- before the VBE when macros were stored in macro sheets????

Maybe there's something (whatever that means) lingering in all the junk that's
accumulated in that code.

Maybe cleaning the code with Rob Bovey's code cleaner would help:
You can find it he
http://www.appspro.com/
or directly
http://www.appspro.com/Utilities/CodeCleaner.htm

This essentially exports all the modules and rebuilds them. Depending on what
your addin does, you may be able to just drag and drop the
modules/userforms/code from the addin to a new workbook's project.

Then save that new workbook as an addin and experiment with that fresh version.

If that doesn't help, then I think I'd leave that dummy function in the addin
(with a nice comment explaining why it's there!).

ps. VBA's help for the AddinInstall event shows some sample code -- it adds a
control to the standard toolbar. (At least in xl2003's version of the VBA Help.)

pps. I'm not sure what the technical difference is between the workbook_open
event and the workbook_addininstall event. I've always used the workbook_open
event for the things I want to do. Actually, I still use the Auto_Open
procedure for lots of things.




On 06/22/2010 18:13, wrote:
The addin does not have an Auto_Add routine. (I wrote the addin).
However, when I added the line:
AddIns("RollForm").Installed = True
to Workbook_Activate(), Excel called RollForm.xla!Auto_Add and I received a
message that it couldn't find it. So I added:
sub Auto_Add()
end sub
to the addin and the message went away.

I put a Stop statement in Auto_Add and opened the Workbook and Excel 2003
did call Auto_Add. Some other things did not work and I assumed I was
supposed to include code in Sub Auto_Add to handle something or other, but
I had no idea what.

Via Google, I found that when you use AddIns("AddinName").Installed = True,
excel calls the addin's Auto_Add function but I found nothing describing
the function. (See:
http://databaseforum.info/16/9/cd250ee5bc1819d2.html
http://www.mrexcel.com/forum/showthread.php?t=35553
http://support.microsoft.com/kb/291294

http://msdn.microsoft.com/en-us/libr...ice.12%29.aspx

Several people mentioned that when they got the error they could
immediately step through the program with no errors. I also experienced
this which led me to think it might be a timing problem.

When I added the code you suggested to ThisWorkbook, Excel no longer seems
to call Auto_Add and everything works fine.

Prior to adding you code and when I had an Auto_Add routing which did
nothing, the error went away, the routine was called but there were all
sorts of other strange errors - example: The addin creates and writes a
text file, then closes it. In certain situations, it re-opens the file and
reads it. When Auto_Add was being called, it would fail in various ways
when it tried to re-open the file.

My addin is working fine now, but I would still like to understand why
Auto_Add is being called and what the developer is supposed to put in it.

Thanks for any insight.


On Tue, 22 Jun 2010 16:39:27 -0500, Dave Peterson
wrote:

Auto_Add is a procedure in that RollForm.xla addin.

I've never heard of that addin, so I don't have a guess what the procedure
actually does.

And it isn't one of the Auto run procedure names (Workbook_Open or Auto_Open)
that excel looks for when the workbook/addin is opened.

If the addin's project is unprotected, maybe you could look at the code?



On 06/22/2010 15:25, wrote:
Thanks for your reply. Your suggestion using OnTime works. Thank you.

I do have a question however. I found that, until I implemented your
solution, VB was calling "RollForm.xla!Auto_Add".

What is Auto_Add() for? What code should I put in it if I ever use it?
What does using your code keep it from calling AutoAdd()?

Thanks!!

On Tue, 22 Jun 2010 14:11:56 -0500, Dave Peterson
wrote:

Untested, but I'd try:

AddIns("RollForm").Installed = False
AddIns("RollForm").Installed = True

If that doesn't work, then maybe it is a timing issue.

You could use:

AddIns("RollForm").Installed = False
AddIns("RollForm").Installed = True
Application.OnTime Now + timeSerial(0,0,1), "Continue_Open"

End Sub

And the put the remainder of the code in that Continue_Open in a General module.


On 06/22/2010 13:57, wrote:
In the ThisWorkbook module of a spreadsheet I have the statement:
AddIns("RollForm").Installed = True
The next line calls a sub in RollForm.xla.

The call fails because RollForm.xla is not yet loaded. If I step through
the code, it works fine.

How can I program a wait for RollForm.xla to be loaded?


Thanks!

On Tue, 22 Jun 2010 19:59:09 -0400, GS wrote:

suggestion...
Instead of calling the proc you want to run after the addin opens, why
not move that procs content into the Workbook_Open event or an
Auto_Open proc in a standard module? That would eliminate need for
pausing your code.

The add in reads data that a user enters into a worksheet, performs some
data valadation (beyond what Excel can do at data entry time), does a mess
of computations and outputs two files that are used to control a machine
that fabricates a product.

It is essential that the data be separated from the code, so that when the
code needs to be upgraded, all the workbooks that use the code do not need
to be changed. I can just email a new copy of the .xla, they save it to
the appropriate folder and everything is good to go. All workbooks are
effectively updated.

Also, is there any reason why you can't open the addin via:
Set wkbAddin = Workbooks.Open(FileName:="C:\RollForm.xla")
I'm not sure. I tried doing this and it did not execute Workbook_Open in
ThisWorkbook of the addin. I tried calling a routine from ThisWorkbook in
the originally opened workbook and it could not find it.

I'm not familiar with having an addin that is not it the addins collection.

How do I do it? What are the advantages?

Thanks.

BTW - I am a very experienced programer, but I don't know much about VBA
(or VB). Is there a good source on the web that documents all of the
objects and their properties and methods? I am constantly finding out
about new ones, yet many times when I search for them in Excel's help it
does not find anything.

and if using Auto_Open instead of Workbook_Open, add this line:
wkbAddin.RunAutoMacros xlAutoOpen
?

Of course, there's nothing wrong with having the xla in the Addins
collection, but not having it in there does have some benefits.

It happens that formulated :
On Tue, 22 Jun 2010 19:59:09 -0400, GS wrote:

suggestion...
Instead of calling the proc you want to run after the addin opens, why
not move that procs content into the Workbook_Open event or an
Auto_Open proc in a standard module? That would eliminate need for
pausing your code.

The add in reads data that a user enters into a worksheet, performs some
data valadation (beyond what Excel can do at data entry time), does a mess
of computations and outputs two files that are used to control a machine
that fabricates a product.

It is essential that the data be separated from the code, so that when the
code needs to be upgraded, all the workbooks that use the code do not need
to be changed. I can just email a new copy of the .xla, they save it to
the appropriate folder and everything is good to go. All workbooks are
effectively updated.

I assume the code is in the XLA. What I don't fully understand is where
the code is that you use to set the addin's Installed prop. This leads
me to conclude that the structure of your project may not be as
efficient as it could be to accomplish your task goals. More info about
this would be helpful in order to provide better suggestions.


Also, is there any reason why you can't open the addin via:
Set wkbAddin = Workbooks.Open(FileName:="C:\RollForm.xla")
I'm not sure. I tried doing this and it did not execute Workbook_Open in
ThisWorkbook of the addin. I tried calling a routine from ThisWorkbook in
the originally opened workbook and it could not find it.

Strange! Are you sure an error didn't occur? This would interupt the
Workbook_Open event code and so make it appear as it did not execute.


I'm not familiar with having an addin that is not it the addins collection.

Addins do not have to be members of the Addins collection to be used.
They do have to be members if you want them available via the UI's
Addins Manager dialog.


How do I do it?

Just as I showed you! -Open the XLA same as any other workbook. You
could use a 'frontloader' addin to do this. A frontloader addin is a
member of the addins collection that adds a menuitem to Excel's menubar
so users can run your non-member addin when needed. This is where I
would put whatever code you currently use to set the addin's Installed
prop, except I would use Workbooks.Open as suggested.

What are the advantages?

There are several advantages depending on your level of skill and how
you want your project structured. For example:
- you can give your addin any filename extension that you want so it
doesn't appear in FileOpen dialogs that are filtering for common Excel
filename extensions.
- changing the filename extension also makes it harder for users to
find your addin via Windows Explorer.
- not having the addin loaded until used makes Excel startup faster
and frees up more system resources that would otherwise be used by an
installed addin.
- frontloader addins are usually smaller and can be used to very
startup conditions before loading your main addin. This could be any
criteria necessary for your addin to work properly. (ie: required
workbooks be open, required data is available, etc.)
- works well when automating instances of MS Office apps.
- you can open-password protect your addins so that only your
frontloader can open them. This is the password that you set in the
SaveAs dialog via the Tools, General Options menu. If anyone tries to
open your addin via the UI they will be prompted for the password.
- your addin can be configured to lock down Excel's UI so as to
prevent users from accessing your code via the VBA IDE.

...just to name a few!


Thanks.

BTW - I am a very experienced programer, but I don't know much about VBA
(or VB). Is there a good source on the web that documents all of the
objects and their properties and methods? I am constantly finding out
about new ones, yet many times when I search for them in Excel's help it
does not find anything.

The best place to look is in the Object Explorer of VBA's IDE. The lang
reference (F1) online help is available in that window for any selected
item.

As for good books on Excel VBA, anything by John Walkenback is going to
be of benefit.

Also, you may still be able to find editions of Wrox's Programmer to
Programmer series of Excel <version VBA by authors Bovey, Bullen,
Green. I highly recommend anything by these authors if you're
interested in Excel VBA programming at the professional level.
Unfortunately, this series is discontinued and so Excel 2007 VBA is the
last title to be published by these authors.

--
Garry

Free usenet access at http://www.eternal-september.org
ClassicVB Users Regroup! comp.lang.basic.visual.misc

Did you try the code cleaner stuff?

On 06/24/2010 10:24, wrote:
Thanks for your replies. The entire thing was written in and is being used
in Excel 2003. It has the AddinInstall but when used, it will call
Auto_Add, at least in some situations. There is no explicit call to
Auto_Add in my code. (I didn't even know there was an Auto_Add until it
complained that it couldn't find it.)

On Tue, 22 Jun 2010 19:17:24 -0500, Dave Peterson
wrote:

I read the kb article that you mentioned and Auto_Add was replaced by the
workbook event AddInInstall. (I had never seen that old procedure. Sorry for
misleading you.)

This is just a guess or maybe just questions...

How old is your addin? What version of excel did you use to create it? Maybe
it was xl95 -- before the VBE when macros were stored in macro sheets????

Maybe there's something (whatever that means) lingering in all the junk that's
accumulated in that code.

Maybe cleaning the code with Rob Bovey's code cleaner would help:
You can find it he
http://www.appspro.com/
or directly
http://www.appspro.com/Utilities/CodeCleaner.htm

This essentially exports all the modules and rebuilds them. Depending on what
your addin does, you may be able to just drag and drop the
modules/userforms/code from the addin to a new workbook's project.

Then save that new workbook as an addin and experiment with that fresh version.

If that doesn't help, then I think I'd leave that dummy function in the addin
(with a nice comment explaining why it's there!).

ps. VBA's help for the AddinInstall event shows some sample code -- it adds a
control to the standard toolbar. (At least in xl2003's version of the VBA Help.)

pps. I'm not sure what the technical difference is between the workbook_open
event and the workbook_addininstall event. I've always used the workbook_open
event for the things I want to do. Actually, I still use the Auto_Open
procedure for lots of things.




On 06/22/2010 18:13, wrote:
The addin does not have an Auto_Add routine. (I wrote the addin).
However, when I added the line:
AddIns("RollForm").Installed = True
to Workbook_Activate(), Excel called RollForm.xla!Auto_Add and I received a
message that it couldn't find it. So I added:
sub Auto_Add()
end sub
to the addin and the message went away.

I put a Stop statement in Auto_Add and opened the Workbook and Excel 2003
did call Auto_Add. Some other things did not work and I assumed I was
supposed to include code in Sub Auto_Add to handle something or other, but
I had no idea what.

Via Google, I found that when you use AddIns("AddinName").Installed = True,
excel calls the addin's Auto_Add function but I found nothing describing
the function. (See:
http://databaseforum.info/16/9/cd250ee5bc1819d2.html
http://www.mrexcel.com/forum/showthread.php?t=35553
http://support.microsoft.com/kb/291294

http://msdn.microsoft.com/en-us/libr...ice.12%29.aspx

Several people mentioned that when they got the error they could
immediately step through the program with no errors. I also experienced
this which led me to think it might be a timing problem.

When I added the code you suggested to ThisWorkbook, Excel no longer seems
to call Auto_Add and everything works fine.

Prior to adding you code and when I had an Auto_Add routing which did
nothing, the error went away, the routine was called but there were all
sorts of other strange errors - example: The addin creates and writes a
text file, then closes it. In certain situations, it re-opens the file and
reads it. When Auto_Add was being called, it would fail in various ways
when it tried to re-open the file.

My addin is working fine now, but I would still like to understand why
Auto_Add is being called and what the developer is supposed to put in it.

Thanks for any insight.


On Tue, 22 Jun 2010 16:39:27 -0500, Dave Peterson
wrote:

Auto_Add is a procedure in that RollForm.xla addin.

I've never heard of that addin, so I don't have a guess what the procedure
actually does.

And it isn't one of the Auto run procedure names (Workbook_Open or Auto_Open)
that excel looks for when the workbook/addin is opened.

If the addin's project is unprotected, maybe you could look at the code?



On 06/22/2010 15:25, wrote:
Thanks for your reply. Your suggestion using OnTime works. Thank you.

I do have a question however. I found that, until I implemented your
solution, VB was calling "RollForm.xla!Auto_Add".

What is Auto_Add() for? What code should I put in it if I ever use it?
What does using your code keep it from calling AutoAdd()?

Thanks!!

On Tue, 22 Jun 2010 14:11:56 -0500, Dave Peterson
wrote:

Untested, but I'd try:

AddIns("RollForm").Installed = False
AddIns("RollForm").Installed = True

If that doesn't work, then maybe it is a timing issue.

You could use:

AddIns("RollForm").Installed = False
AddIns("RollForm").Installed = True
Application.OnTime Now + timeSerial(0,0,1), "Continue_Open"

End Sub

And the put the remainder of the code in that Continue_Open in a General module.


On 06/22/2010 13:57, wrote:
In the ThisWorkbook module of a spreadsheet I have the statement:
AddIns("RollForm").Installed = True
The next line calls a sub in RollForm.xla.

The call fails because RollForm.xla is not yet loaded. If I step through
the code, it works fine.

How can I program a wait for RollForm.xla to be loaded?


Thanks!





--
Dave Peterson

In the initial message in this thread I stated:
"In the ThisWorkbook module of a spreadsheet I have the statement:
AddIns("RollForm").Installed = True
The next line calls a sub in RollForm.xla."

Maybe that was poor terminology and thus not clear. However I meant that
it is in "Private Sub WorkBook_Open()" in ThisWorkbook. The WorkBook_Open()
does run. The .xla is loaded. The problem is that Excel did an implicit
(not coded by me) call Auto_Open() in the .xla which did not exist.

Any way adding the suggested code:
AddIns("RollForm").Installed = False
AddIns("RollForm").Installed = True
Application.OnTime Now + timeSerial(0,0,1), "Continue_Open"
dis solve the problem. It no longer calls Auto_Open() and everything iw
working great. I have no idea why it was calling Auto_Open, and given that
it did, I have no idea why it isn't now.

On Thu, 24 Jun 2010 13:06:29 -0400, GS wrote:

It happens that formulated :
On Tue, 22 Jun 2010 19:59:09 -0400, GS wrote:

suggestion...
Instead of calling the proc you want to run after the addin opens, why
not move that procs content into the Workbook_Open event or an
Auto_Open proc in a standard module? That would eliminate need for
pausing your code.

The add in reads data that a user enters into a worksheet, performs some
data valadation (beyond what Excel can do at data entry time), does a mess
of computations and outputs two files that are used to control a machine
that fabricates a product.

It is essential that the data be separated from the code, so that when the
code needs to be upgraded, all the workbooks that use the code do not need
to be changed. I can just email a new copy of the .xla, they save it to
the appropriate folder and everything is good to go. All workbooks are
effectively updated.

I assume the code is in the XLA. What I don't fully understand is where
the code is that you use to set the addin's Installed prop. This leads
me to conclude that the structure of your project may not be as
efficient as it could be to accomplish your task goals. More info about
this would be helpful in order to provide better suggestions.


Also, is there any reason why you can't open the addin via:
Set wkbAddin = Workbooks.Open(FileName:="C:\RollForm.xla")
I'm not sure. I tried doing this and it did not execute Workbook_Open in
ThisWorkbook of the addin. I tried calling a routine from ThisWorkbook in
the originally opened workbook and it could not find it.

Strange! Are you sure an error didn't occur? This would interupt the
Workbook_Open event code and so make it appear as it did not execute.


I'm not familiar with having an addin that is not it the addins collection.

Addins do not have to be members of the Addins collection to be used.
They do have to be members if you want them available via the UI's
Addins Manager dialog.


How do I do it?

Just as I showed you! -Open the XLA same as any other workbook. You
could use a 'frontloader' addin to do this. A frontloader addin is a
member of the addins collection that adds a menuitem to Excel's menubar
so users can run your non-member addin when needed. This is where I
would put whatever code you currently use to set the addin's Installed
prop, except I would use Workbooks.Open as suggested.

What are the advantages?

There are several advantages depending on your level of skill and how
you want your project structured. For example:
- you can give your addin any filename extension that you want so it
doesn't appear in FileOpen dialogs that are filtering for common Excel
filename extensions.
- changing the filename extension also makes it harder for users to
find your addin via Windows Explorer.
- not having the addin loaded until used makes Excel startup faster
and frees up more system resources that would otherwise be used by an
installed addin.
- frontloader addins are usually smaller and can be used to very
startup conditions before loading your main addin. This could be any
criteria necessary for your addin to work properly. (ie: required
workbooks be open, required data is available, etc.)
- works well when automating instances of MS Office apps.
- you can open-password protect your addins so that only your
frontloader can open them. This is the password that you set in the
SaveAs dialog via the Tools, General Options menu. If anyone tries to
open your addin via the UI they will be prompted for the password.
- your addin can be configured to lock down Excel's UI so as to
prevent users from accessing your code via the VBA IDE.

..just to name a few!


Thanks.

BTW - I am a very experienced programer, but I don't know much about VBA
(or VB). Is there a good source on the web that documents all of the
objects and their properties and methods? I am constantly finding out
about new ones, yet many times when I search for them in Excel's help it
does not find anything.

The best place to look is in the Object Explorer of VBA's IDE. The lang
reference (F1) online help is available in that window for any selected
item.

As for good books on Excel VBA, anything by John Walkenback is going to
be of benefit.

Also, you may still be able to find editions of Wrox's Programmer to
Programmer series of Excel <version VBA by authors Bovey, Bullen,
Green. I highly recommend anything by these authors if you're
interested in Excel VBA programming at the professional level.
Unfortunately, this series is discontinued and so Excel 2007 VBA is the
last title to be published by these authors.

presented the following explanation :
In the initial message in this thread I stated:
"In the ThisWorkbook module of a spreadsheet I have the statement:
AddIns("RollForm").Installed = True
The next line calls a sub in RollForm.xla."

Maybe that was poor terminology and thus not clear. However I meant that
it is in "Private Sub WorkBook_Open()" in ThisWorkbook. The WorkBook_Open()
does run. The .xla is loaded. The problem is that Excel did an implicit
(not coded by me) call Auto_Open() in the .xla which did not exist.

Any way adding the suggested code:
AddIns("RollForm").Installed = False
AddIns("RollForm").Installed = True
Application.OnTime Now + timeSerial(0,0,1), "Continue_Open"
dis solve the problem. It no longer calls Auto_Open() and everything iw
working great. I have no idea why it was calling Auto_Open, and given that
it did, I have no idea why it isn't now.

Excel will look for and call Auto_Open if there's no Workbook_Open proc
in ThisWorkbook. If you use Workbook.Open and have an Auto_Open proc
then you MUST tell Excel to Run that after opening. If you use
Workbook_Open then you don't have to do anything after opening.

When the addin is a member of the Addins collection, you also don't
need to do anything if you use its Installed prop to open it. In this
case Excel will look for Workbook_Open or Auto_Open on its own. That's
just normal behavior when dealing with XLAs because Excel expects an
XLA to have one or the other of these two procs as its startup routine.
It's not a requirement, though, that all XLAs require either one.

What I'm say is instead of calling the proc you call after opening the
addin, put that proc's code in either the Workbook_Open event proc or
an Auto_Open proc so you don't have to call it from wherever you're
calling it from. It appears that whatever this proc is, that you want
it to run immediately after the addin opens. So.., you can make this
proc one of the two startup procs mentioned OR call it from one of
those right within the addin.

--
Garry

Free usenet access at http://www.eternal-september.org
ClassicVB Users Regroup! comp.lang.basic.visual.misc