HI All,
I'm a beginning developer for my first, and a pretty big addin. (over
100 modules) Perhaps you can save me some time in putting a timer on what's
below.

I've noticed that the physical sequence of modules is as you insert them
into the project and not by how you name them. I have NO clue how Excel
accesses the modules and the macros within them.

Within a month, I'll be moving the code to a "QA" project .xla file and
will have the chance to resequence the top to bottom module order.

best practices, are there significant efficiencies to be gained if:
1. Global public variables, record types, are in the modules at "the top" ?

2. The most used Sub's and Functions are in the "earlier" modules ?

3. Should Public Type XXXXX be in the same module as the code which uses
them the most, or doesn't it matter ? (That's where they are now to use the
"split screen" function of the VBA editor.)

Other tips would be appreciated.
Thanks
--
Neal Z

I have never heard of the modules not being in Alpha order. Just above the
project explorer is a Folder icon. Toggling that on/off puts the modules,
sheets, ... etc into folders.

As for how do you organize things. I tend to have a module called Globals
where I define all of my globals. If a type def was going to be used globaly
then I would put it in there. If the type def applies to only one module then
I would put it there. The most important thing is to organize things
logically and to be consistent. Put like stuff together. If you have 100
modules then that is getting to the point of being unmanagable. I would be
inclined to consolidate things into fewer modules.
--
HTH...

Jim Thomlinson


"Neal Zimm" wrote:


HI All,
I'm a beginning developer for my first, and a pretty big addin. (over
100 modules) Perhaps you can save me some time in putting a timer on what's
below.

I've noticed that the physical sequence of modules is as you insert them
into the project and not by how you name them. I have NO clue how Excel
accesses the modules and the macros within them.

Within a month, I'll be moving the code to a "QA" project .xla file and
will have the chance to resequence the top to bottom module order.

best practices, are there significant efficiencies to be gained if:
1. Global public variables, record types, are in the modules at "the top" ?

2. The most used Sub's and Functions are in the "earlier" modules ?

3. Should Public Type XXXXX be in the same module as the code which uses
them the most, or doesn't it matter ? (That's where they are now to use the
"split screen" function of the VBA editor.)

Other tips would be appreciated.
Thanks
--
Neal Z

Neal,
Standard modules are sorted alphabetically by name in the VBE.
Perhaps you are looking at the modules behind each sheet instead of standard
modules which are created by using Insert | Module?
--
Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware
(Excel Add-ins / Excel Programming)



"Neal Zimm"
wrote in message
HI All,
I'm a beginning developer for my first, and a pretty big addin. (over
100 modules) Perhaps you can save me some time in putting a timer on what's
below.

I've noticed that the physical sequence of modules is as you insert them
into the project and not by how you name them. I have NO clue how Excel
accesses the modules and the macros within them.

Within a month, I'll be moving the code to a "QA" project .xla file and
will have the chance to resequence the top to bottom module order.

best practices, are there significant efficiencies to be gained if:
1. Global public variables, record types, are in the modules at "the top" ?

2. The most used Sub's and Functions are in the "earlier" modules ?

3. Should Public Type XXXXX be in the same module as the code which uses
them the most, or doesn't it matter ? (That's where they are now to use the
"split screen" function of the VBA editor.)

Other tips would be appreciated.
Thanks
--
Neal Z

Dear Jims, T and C,
Well, I'm 99.999 percent sure of what I asked, and this is a bit long, but
I want to be careful as you guys are not often malinformed. So if you agree
with what's below, then please comment on my conclusion at the end.

Thanks.

For sure, the VBE PRESENTS the module names in alpha order. When you
rename a module via PFkey 4, you see it move, but the rename does not change
the physical location of the code. (Kinda the same as the looking at macros
within a module, the drop down list is alpha, but the macro names don't HAVE
to be in that sequence. I verified this by looking at the code in Full Module
view in the VBE.)

I downloaded Rob Bovey's Documentor addin and am modifying it to create a
documentation tool for my addin. He's reading something called the VIBDE
sequentially to pick up module names and the subs/functions within them.

He recommends modules be no larger than 64K, hence the # of modules I have.
Mine were much larger before splitting them up.

When Documentor listed the module names, they were NOT in alpha sequence.
This was a surprise to me, too. I stepped thru his code to prove it on a
small project.

So, I have "Option Explicit" in every module as the 1st line of code in the
declare section.

I renamed some modules to change the look of project explorer module list.

I looked for "option explicit" in Down mode, and sure enough, the grey bar
showing what module you're "in" jumped around, it did not go from top to
bottom in the project explorer list that you see.

I've been in systems quite some time, but am quite naive about Excel and
VBA, but,

Conclusion:

There's gotta be an index somewhere, (deeply hidden i'm sure) so when you
"Call MacName(x, y)" Excel knows where to go to start MacName up. My guess
is that this index is in alpha sequence and points to the compiled macro.

So, it made sense to me that if I have a sub or function, that's used a
LOT, by putting it at the top of the list, given the size of my addin, it
will start up sooner.

How much sooner is the issue.

I had hoped to save some development time before testing the theory with
some timer code I copied that gets to .000000 seconds, which is why I made
the posting here.

Thanks again for your time, we all appreciate your voluntary efforts.
Neal
--

"Jim Thomlinson" wrote:

I have never heard of the modules not being in Alpha order. Just above the
project explorer is a Folder icon. Toggling that on/off puts the modules,
sheets, ... etc into folders.

As for how do you organize things. I tend to have a module called Globals
where I define all of my globals. If a type def was going to be used globaly
then I would put it in there. If the type def applies to only one module then
I would put it there. The most important thing is to organize things
logically and to be consistent. Put like stuff together. If you have 100
modules then that is getting to the point of being unmanagable. I would be
inclined to consolidate things into fewer modules.
--
HTH...

Jim Thomlinson


"Neal Zimm" wrote:


HI All,
I'm a beginning developer for my first, and a pretty big addin. (over
100 modules) Perhaps you can save me some time in putting a timer on what's
below.

I've noticed that the physical sequence of modules is as you insert them
into the project and not by how you name them. I have NO clue how Excel
accesses the modules and the macros within them.

Within a month, I'll be moving the code to a "QA" project .xla file and
will have the chance to resequence the top to bottom module order.

best practices, are there significant efficiencies to be gained if:
1. Global public variables, record types, are in the modules at "the top" ?

2. The most used Sub's and Functions are in the "earlier" modules ?

3. Should Public Type XXXXX be in the same module as the code which uses
them the most, or doesn't it matter ? (That's where they are now to use the
"split screen" function of the VBA editor.)

Other tips would be appreciated.
Thanks
--
Neal Z

Dear Jims, T and C,
Well, I'm 99.999 percent sure of what I asked, and this is a bit long, but
I want to be careful as you guys are not often malinformed. So if you agree
with what's below, then please comment on my conclusion at the end.

Thanks.

For sure, the VBE PRESENTS the module names in alpha order. When you
rename a module via PFkey 4, you see it move, but the rename does not change
the physical location of the code. (Kinda the same as the looking at macros
within a module, the drop down list is alpha, but the macro names don't HAVE
to be in that sequence. I verified this by looking at the code in Full Module
view in the VBE.)

I downloaded Rob Bovey's Documentor addin and am modifying it to create a
documentation tool for my addin. He's reading something called the VIBDE
sequentially to pick up module names and the subs/functions within them.

He recommends modules be no larger than 64K, hence the # of modules I have.
Mine were much larger before splitting them up.

When Documentor listed the module names, they were NOT in alpha sequence.
This was a surprise to me, too. I stepped thru his code to prove it on a
small project.

So, I have "Option Explicit" in every module as the 1st line of code in the
declare section.

I renamed some modules to change the look of project explorer module list.

I looked for "option explicit" in Down mode, and sure enough, the grey bar
showing what module you're "in" jumped around, it did not go from top to
bottom in the project explorer list that you see.

I've been in systems quite some time, but am quite naive about Excel and
VBA, but,

Conclusion:

There's gotta be an index somewhere, (deeply hidden i'm sure) so when you
"Call MacName(x, y)" Excel knows where to go to start MacName up. My guess
is that this index is in alpha sequence and points to the compiled macro.
(same for public variable names pointing to a memory location for their value)

So, it made sense to me that if I have a sub or function, that's used a
LOT, by putting it at the top of the list, given the size of my addin, it
will start up sooner.

How much sooner is the issue.

I had hoped to save some development time before testing the theory with
some timer code I copied that gets to .000000 seconds, which is why I made
the posting here.

Thanks again for your time, we all appreciate your voluntary efforts.
Neal
--


"Jim Cone" wrote:

Neal,
Standard modules are sorted alphabetically by name in the VBE.
Perhaps you are looking at the modules behind each sheet instead of standard
modules which are created by using Insert | Module?
--
Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware
(Excel Add-ins / Excel Programming)



"Neal Zimm"
wrote in message
HI All,
I'm a beginning developer for my first, and a pretty big addin. (over
100 modules) Perhaps you can save me some time in putting a timer on what's
below.

I've noticed that the physical sequence of modules is as you insert them
into the project and not by how you name them. I have NO clue how Excel
accesses the modules and the macros within them.

Within a month, I'll be moving the code to a "QA" project .xla file and
will have the chance to resequence the top to bottom module order.

best practices, are there significant efficiencies to be gained if:
1. Global public variables, record types, are in the modules at "the top" ?

2. The most used Sub's and Functions are in the "earlier" modules ?

3. Should Public Type XXXXX be in the same module as the code which uses
them the most, or doesn't it matter ? (That's where they are now to use the
"split screen" function of the VBA editor.)

Other tips would be appreciated.
Thanks
--
Neal Z

Jims, T and C

I ran the test. I put a function into module #4 and ran it 50 times.
Put same function into module #107 and ran it 50 times.

In module#4 it ran .000009 seconds faster, or just over 3%.

With 200 users estimated, in 24 hours, it works out to about 20 cpu seconds
saved per day.

Not a great savings, but I think my theory is good, and I'm going to place
the heavily used code in the modules "at the top"

Neal Z.
--
Neal Z


"Jim Thomlinson" wrote:

I have never heard of the modules not being in Alpha order. Just above the
project explorer is a Folder icon. Toggling that on/off puts the modules,
sheets, ... etc into folders.

As for how do you organize things. I tend to have a module called Globals
where I define all of my globals. If a type def was going to be used globaly
then I would put it in there. If the type def applies to only one module then
I would put it there. The most important thing is to organize things
logically and to be consistent. Put like stuff together. If you have 100
modules then that is getting to the point of being unmanagable. I would be
inclined to consolidate things into fewer modules.
--
HTH...

Jim Thomlinson


"Neal Zimm" wrote:


HI All,
I'm a beginning developer for my first, and a pretty big addin. (over
100 modules) Perhaps you can save me some time in putting a timer on what's
below.

I've noticed that the physical sequence of modules is as you insert them
into the project and not by how you name them. I have NO clue how Excel
accesses the modules and the macros within them.

Within a month, I'll be moving the code to a "QA" project .xla file and
will have the chance to resequence the top to bottom module order.

best practices, are there significant efficiencies to be gained if:
1. Global public variables, record types, are in the modules at "the top" ?

2. The most used Sub's and Functions are in the "earlier" modules ?

3. Should Public Type XXXXX be in the same module as the code which uses
them the most, or doesn't it matter ? (That's where they are now to use the
"split screen" function of the VBA editor.)

Other tips would be appreciated.
Thanks
--
Neal Z

Jims, T and C

I ran the test. I put a function into module #4 and ran it 50 times.
Put same function into module #107 and ran it 50 times.

In module#4 it ran .000009 seconds faster, or just over 3%.

With 200 users estimated, in 24 hours, it works out to about 20 cpu seconds
saved per day.

Not a great savings, but I think my theory is good, and I'm going to place
the heavily used code in the modules "at the top"

Neal Z.
--
Neal Z


"Jim Cone" wrote:

Neal,
Standard modules are sorted alphabetically by name in the VBE.
Perhaps you are looking at the modules behind each sheet instead of standard
modules which are created by using Insert | Module?
--
Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware
(Excel Add-ins / Excel Programming)



"Neal Zimm"
wrote in message
HI All,
I'm a beginning developer for my first, and a pretty big addin. (over
100 modules) Perhaps you can save me some time in putting a timer on what's
below.

I've noticed that the physical sequence of modules is as you insert them
into the project and not by how you name them. I have NO clue how Excel
accesses the modules and the macros within them.

Within a month, I'll be moving the code to a "QA" project .xla file and
will have the chance to resequence the top to bottom module order.

best practices, are there significant efficiencies to be gained if:
1. Global public variables, record types, are in the modules at "the top" ?

2. The most used Sub's and Functions are in the "earlier" modules ?

3. Should Public Type XXXXX be in the same module as the code which uses
them the most, or doesn't it matter ? (That's where they are now to use the
"split screen" function of the VBA editor.)

Other tips would be appreciated.
Thanks
--
Neal Z