VBA Calls
 

Hi Bret,

The first thing I'd suggest is to download my VBA Code Cleaner from the
web site shown below. Run your project through this and see if it doesn't
improve things. Next I would make sure that none of your code modules
exceeds 64KB in size and none of your UserForms contains more than 256
controls.

--
Rob Bovey, MCSE, MCSD, Excel MVP
Application Professionals
http://www.appspro.com/

* Please post all replies to this newsgroup *
* I delete all unsolicited e-mail responses *


"Bret Holle" wrote in message
...
Hi all,

I'm programming an Excel Add-in (developing under Windows 2000 SP4
running Office 2000 SR3) that consists of 56 user forms and 14 modules.
Occasionally, while doing further development of the Add-in, I'll get
errors like "object invoked has disconnected from its clients" or Excel
application errors. When I bring it back up, make the same changes and
click Debug/Compile the compiler finds no errors. However, I'll get the
exact same message. I can bring it back up, make the changes again and
trace it, and it will crash on this line: "Call
basDatabase.DatabaseMain1" (or others like it <module.<sub/function
name). When I take the time to remove all of the module names from the
sub and function calls, the Add-in is back to normal and I can continue
development.

However, this also works in reverse. It will crash on the line "Call
DatabaseMain1" until I replace it with "Call basDatabase.DatabaseMain1".
I just got done putting the module names back into the sub/function
calls (I don't know the exact number but it's at least 250 calls), and
the way it is now I can continue development until it whatever it is
happens again and I have to take the module names back out.

I need to know what would cause this problem. I don't know where to
start looking for the problem since the compiler says there's nothing
wrong, and VBA says that there are no references missing. My company is
looking to release the Add-in to the public, but I'd like to know for
sure that the people who buy it won't run into the problem that I'm
having with the sub/function calls.

Any help or information would be greatly appreciated. Thanks for you
time,

Bret

VBA Calls
 

Rob,

I would make sure that none of your code modules
exceeds 64KB in size and none of your UserForms contains more than 256
controls.
Is this a hard and fast limitation with Excel 2000???
I have a project with quite a number of UserForms and a lot of code (maybe
6,000 lines). Whenever I add a control to a userform (and there are less than
256 controls per form) Excel will not recognise the control until I first run
your
Code Cleaner (and thank you, it works like a charm) save, close and reopen.
If I add a control, save, close and reopen without using Code Cleaner
it crashes when I try to open it.
I'll go back and check to see how large my modules are but
have you ever run into this type of problem before??
And thanks again for the Code Cleaner.

John


Rob Bovey wrote:

Hi Bret,

The first thing I'd suggest is to download my VBA Code Cleaner from the
web site shown below. Run your project through this and see if it doesn't
improve things. Next I would make sure that none of your code modules
exceeds 64KB in size and none of your UserForms contains more than 256
controls.

--
Rob Bovey, MCSE, MCSD, Excel MVP
Application Professionals
http://www.appspro.com/

* Please post all replies to this newsgroup *
* I delete all unsolicited e-mail responses *

"Bret Holle" wrote in message
...
Hi all,

I'm programming an Excel Add-in (developing under Windows 2000 SP4
running Office 2000 SR3) that consists of 56 user forms and 14 modules.
Occasionally, while doing further development of the Add-in, I'll get
errors like "object invoked has disconnected from its clients" or Excel
application errors. When I bring it back up, make the same changes and
click Debug/Compile the compiler finds no errors. However, I'll get the
exact same message. I can bring it back up, make the changes again and
trace it, and it will crash on this line: "Call
basDatabase.DatabaseMain1" (or others like it <module.<sub/function
name). When I take the time to remove all of the module names from the
sub and function calls, the Add-in is back to normal and I can continue
development.

However, this also works in reverse. It will crash on the line "Call
DatabaseMain1" until I replace it with "Call basDatabase.DatabaseMain1".
I just got done putting the module names back into the sub/function
calls (I don't know the exact number but it's at least 250 calls), and
the way it is now I can continue development until it whatever it is
happens again and I have to take the module names back out.

I need to know what would cause this problem. I don't know where to
start looking for the problem since the compiler says there's nothing
wrong, and VBA says that there are no references missing. My company is
looking to release the Add-in to the public, but I'd like to know for
sure that the people who buy it won't run into the problem that I'm
having with the sub/function calls.

Any help or information would be greatly appreciated. Thanks for you
time,

Bret

VBA Calls
 

Hi John,

The module size limitation isn't hard and fast, nor is it limited to any
specific version of Excel. In fact I don't think it's ever been officially
documented. However, it's pretty well recognized among professional Excel
developers that if you create code modules larger than 64KB you will very
likely run into problems.

By the way, I've also seen the problem with UserForms not immediately
recognizing controls added to them. I honestly have no idea why this
happens, but I don't think it's necessarily related to module size.

--
Rob Bovey, MCSE, MCSD, Excel MVP
Application Professionals
http://www.appspro.com/

* Please post all replies to this newsgroup *
* I delete all unsolicited e-mail responses *


"John Wilson" wrote in message
...
Rob,

I would make sure that none of your code modules
exceeds 64KB in size and none of your UserForms contains more than 256
controls.
Is this a hard and fast limitation with Excel 2000???
I have a project with quite a number of UserForms and a lot of code (maybe
6,000 lines). Whenever I add a control to a userform (and there are less
than
256 controls per form) Excel will not recognise the control until I first
run
your
Code Cleaner (and thank you, it works like a charm) save, close and
reopen.
If I add a control, save, close and reopen without using Code Cleaner
it crashes when I try to open it.
I'll go back and check to see how large my modules are but
have you ever run into this type of problem before??
And thanks again for the Code Cleaner.

John


Rob Bovey wrote:

Hi Bret,

The first thing I'd suggest is to download my VBA Code Cleaner from
the
web site shown below. Run your project through this and see if it
doesn't
improve things. Next I would make sure that none of your code modules
exceeds 64KB in size and none of your UserForms contains more than 256
controls.

--
Rob Bovey, MCSE, MCSD, Excel MVP
Application Professionals
http://www.appspro.com/

* Please post all replies to this newsgroup *
* I delete all unsolicited e-mail responses *

"Bret Holle" wrote in message
...
Hi all,

I'm programming an Excel Add-in (developing under Windows 2000 SP4
running Office 2000 SR3) that consists of 56 user forms and 14
modules.
Occasionally, while doing further development of the Add-in, I'll
get
errors like "object invoked has disconnected from its clients" or
Excel
application errors. When I bring it back up, make the same changes
and
click Debug/Compile the compiler finds no errors. However, I'll get
the
exact same message. I can bring it back up, make the changes again
and
trace it, and it will crash on this line: "Call
basDatabase.DatabaseMain1" (or others like it <module.<sub/function
name). When I take the time to remove all of the module names from
the
sub and function calls, the Add-in is back to normal and I can
continue
development.

However, this also works in reverse. It will crash on the line "Call
DatabaseMain1" until I replace it with "Call
basDatabase.DatabaseMain1".
I just got done putting the module names back into the sub/function
calls (I don't know the exact number but it's at least 250 calls), and
the way it is now I can continue development until it whatever it is
happens again and I have to take the module names back out.

I need to know what would cause this problem. I don't know where to
start looking for the problem since the compiler says there's nothing
wrong, and VBA says that there are no references missing. My company
is
looking to release the Add-in to the public, but I'd like to know for
sure that the people who buy it won't run into the problem that I'm
having with the sub/function calls.

Any help or information would be greatly appreciated. Thanks for you
time,

Bret

VBA Calls
 

Rob,

What is the quickest and easiest way to test the size of a module?

I have a couple of workbooks holding general subs and some of the modules
are relatively large.

[I have used your Code Cleaner (Thank You! Very Much!) and have done a lot
of other clean up to help prevent problems]

But every little bit helps...
--
sb
"Rob Bovey" wrote in message
...
Hi John,

The module size limitation isn't hard and fast, nor is it limited to
any
specific version of Excel. In fact I don't think it's ever been officially
documented. However, it's pretty well recognized among professional Excel
developers that if you create code modules larger than 64KB you will very
likely run into problems.

By the way, I've also seen the problem with UserForms not immediately
recognizing controls added to them. I honestly have no idea why this
happens, but I don't think it's necessarily related to module size.

--
Rob Bovey, MCSE, MCSD, Excel MVP
Application Professionals
http://www.appspro.com/

* Please post all replies to this newsgroup *
* I delete all unsolicited e-mail responses *


"John Wilson" wrote in message
...
Rob,

I would make sure that none of your code modules
exceeds 64KB in size and none of your UserForms contains more than 256
controls.
Is this a hard and fast limitation with Excel 2000???
I have a project with quite a number of UserForms and a lot of code
(maybe
6,000 lines). Whenever I add a control to a userform (and there are less
than
256 controls per form) Excel will not recognise the control until I
first
run
your
Code Cleaner (and thank you, it works like a charm) save, close and
reopen.
If I add a control, save, close and reopen without using Code Cleaner
it crashes when I try to open it.
I'll go back and check to see how large my modules are but
have you ever run into this type of problem before??
And thanks again for the Code Cleaner.

John


Rob Bovey wrote:

Hi Bret,

The first thing I'd suggest is to download my VBA Code Cleaner
from
the
web site shown below. Run your project through this and see if it
doesn't
improve things. Next I would make sure that none of your code modules
exceeds 64KB in size and none of your UserForms contains more than 256
controls.

--
Rob Bovey, MCSE, MCSD, Excel MVP
Application Professionals
http://www.appspro.com/

* Please post all replies to this newsgroup *
* I delete all unsolicited e-mail responses *

"Bret Holle" wrote in message
...
Hi all,

I'm programming an Excel Add-in (developing under Windows 2000 SP4
running Office 2000 SR3) that consists of 56 user forms and 14
modules.
Occasionally, while doing further development of the Add-in, I'll
get
errors like "object invoked has disconnected from its clients" or
Excel
application errors. When I bring it back up, make the same changes
and
click Debug/Compile the compiler finds no errors. However, I'll get
the
exact same message. I can bring it back up, make the changes again
and
trace it, and it will crash on this line: "Call
basDatabase.DatabaseMain1" (or others like it <module.<sub/function
name). When I take the time to remove all of the module names from
the
sub and function calls, the Add-in is back to normal and I can
continue
development.

However, this also works in reverse. It will crash on the line
"Call
DatabaseMain1" until I replace it with "Call
basDatabase.DatabaseMain1".
I just got done putting the module names back into the
sub/function
calls (I don't know the exact number but it's at least 250 calls),
and
the way it is now I can continue development until it whatever it is
happens again and I have to take the module names back out.

I need to know what would cause this problem. I don't know where to
start looking for the problem since the compiler says there's
nothing
wrong, and VBA says that there are no references missing. My
company
is
looking to release the Add-in to the public, but I'd like to know
for
sure that the people who buy it won't run into the problem that I'm
having with the sub/function calls.

Any help or information would be greatly appreciated. Thanks for
you
time,

Bret

VBA Calls
 

"Dave Peterson" wrote in message
...
Another guesstimate: export your code to a .bas file.

Take a look at the size in windows explorer (or from a DOS Dir)

Actually, that's exactly what the code documentor does, it just
automates the process.

--
Rob Bovey, MCSE, MCSD, Excel MVP
Application Professionals
http://www.appspro.com/

* Please post all replies to this newsgroup *
* I delete all unsolicited e-mail responses *