I hit the "procedure too large" error and I can't seem to get past it.
I'm well aware of the reason and I can tell you know that I've
effectively crammed all the IFs and LOOPs into it that I can, so let's
assume that the code can't be condensed further. I've spent half-a-day
reading through this group and Googling and can't seem to make it all
come together.

So my question is, how do I create this "well structured code" when I
have SO MANY declarations which need to be used across the different
procedures? I don't want to pass all of my declarations as arguments
as I feel like that would take forever. There has got to me a more
logical way that I am missing. And functions are supposed to be small
aren't they?

In reading the vba help, it seems that declaring the procedure or
perhaps the individual variables using "static" would allow them to
persist but they don't. Does it make a difference if I put everything
in separate modules? From what I've read that isn't necessary.

I read somewhere in this group that someone had a procedure that held
all of their variables and they just called it in each procedure. How
would this have been done? And if the variable is changed in the first
procedure that calls it, then how can I get that value to persist to
the next?

I know the first question is going to be, let me see your code. But
because this is more of a conceptual question, I'm leaving it out for
now. I'll gladly provide some as needed.

Many thanks for any assistance.

Shelton

On 9 May, 00:18, shelfish <shelf...@gmail.com> wrote:

> So my question is, how do I create this "well structured code" when I
> have SO MANY declarations which need to be used across the different
> procedures?

It may not count as "Well structured code" but if you declare them
outside any procedure (ie outside the sub-end sub) then they become
global to the code module.
If you declare them as, for example, "Public i as Integer" then they
are visible to every routine in your project.

I tend to put all my globals in a separate module. (enumerated types,
for example)

Have a look at class modules, too, that can really tidy things up.

I don't know what the best method is, but the one that I use for large
projects is to break the code into small macros and use a main macro to call
the sub routines.

Expl: Sub Main()
'declarations
'assign mult-use variables
Macro1
Macro2
Macro3
'etc.
'Closing code and misc.
End Sub

You can inser code lines between macro calls to do stuff with the results of
the previous macro and/or set up for the next macro. But using this
structure will release memory after each sub routine runs and reduces the
probability of getting the too large message. If you use the Call Keyword
for the sub routines, you will need to include parentheses with any arguments
inside them. i.e.

Call Macro1(arg1, arg2)

"shelfish" wrote:

> I hit the "procedure too large" error and I can't seem to get past it.
> I'm well aware of the reason and I can tell you know that I've
> effectively crammed all the IFs and LOOPs into it that I can, so let's
> assume that the code can't be condensed further. I've spent half-a-day
> reading through this group and Googling and can't seem to make it all
> come together.
>
> So my question is, how do I create this "well structured code" when I
> have SO MANY declarations which need to be used across the different
> procedures? I don't want to pass all of my declarations as arguments
> as I feel like that would take forever. There has got to me a more
> logical way that I am missing. And functions are supposed to be small
> aren't they?
>
> In reading the vba help, it seems that declaring the procedure or
> perhaps the individual variables using "static" would allow them to
> persist but they don't. Does it make a difference if I put everything
> in separate modules? From what I've read that isn't necessary.
>
> I read somewhere in this group that someone had a procedure that held
> all of their variables and they just called it in each procedure. How
> would this have been done? And if the variable is changed in the first
> procedure that calls it, then how can I get that value to persist to
> the next?
>
> I know the first question is going to be, let me see your code. But
> because this is more of a conceptual question, I'm leaving it out for
> now. I'll gladly provide some as needed.
>
> Many thanks for any assistance.
>
> Shelton
>

P.S. Public variables go at the top of the module, outside the Sub structure
like:

Public wks As Worksheet, sh As Sheet

Sub doSomething()
'some code
End Sub

Sub doSomethingelse()
'more code
End Sub

"shelfish" wrote:

> I hit the "procedure too large" error and I can't seem to get past it.
> I'm well aware of the reason and I can tell you know that I've
> effectively crammed all the IFs and LOOPs into it that I can, so let's
> assume that the code can't be condensed further. I've spent half-a-day
> reading through this group and Googling and can't seem to make it all
> come together.
>
> So my question is, how do I create this "well structured code" when I
> have SO MANY declarations which need to be used across the different
> procedures? I don't want to pass all of my declarations as arguments
> as I feel like that would take forever. There has got to me a more
> logical way that I am missing. And functions are supposed to be small
> aren't they?
>
> In reading the vba help, it seems that declaring the procedure or
> perhaps the individual variables using "static" would allow them to
> persist but they don't. Does it make a difference if I put everything
> in separate modules? From what I've read that isn't necessary.
>
> I read somewhere in this group that someone had a procedure that held
> all of their variables and they just called it in each procedure. How
> would this have been done? And if the variable is changed in the first
> procedure that calls it, then how can I get that value to persist to
> the next?
>
> I know the first question is going to be, let me see your code. But
> because this is more of a conceptual question, I'm leaving it out for
> now. I'll gladly provide some as needed.
>
> Many thanks for any assistance.
>
> Shelton
>

If you get "procedure too large" then for sure you are in the territory of
code which may work, but will be incredibly difficult to maintain even a few
weeks after you finished writing it. Most likely there are pieces of
functionality which repeat (perhaps with small variations) and these are
prime candidates for breaking out into separate subs or functions. These
should ideally receive all their input from parameters and not a "soup" of
global variables.

I'm not sure whether by "take forever" when referring to passing arguments
you mean rewriting your code or an impact on performance. Whatever time you
spend on the former is time well spent (particularly 6months from now) - the
latter will likely be unaffected.

Tim

"shelfish" <(E-Mail Removed)> wrote in message
news:96cb79b0-7420-4169-b204-(E-Mail Removed)...
>I hit the "procedure too large" error and I can't seem to get past it.
> I'm well aware of the reason and I can tell you know that I've
> effectively crammed all the IFs and LOOPs into it that I can, so let's
> assume that the code can't be condensed further. I've spent half-a-day
> reading through this group and Googling and can't seem to make it all
> come together.
>
> So my question is, how do I create this "well structured code" when I
> have SO MANY declarations which need to be used across the different
> procedures? I don't want to pass all of my declarations as arguments
> as I feel like that would take forever. There has got to me a more
> logical way that I am missing. And functions are supposed to be small
> aren't they?
>
> In reading the vba help, it seems that declaring the procedure or
> perhaps the individual variables using "static" would allow them to
> persist but they don't. Does it make a difference if I put everything
> in separate modules? From what I've read that isn't necessary.
>
> I read somewhere in this group that someone had a procedure that held
> all of their variables and they just called it in each procedure. How
> would this have been done? And if the variable is changed in the first
> procedure that calls it, then how can I get that value to persist to
> the next?
>
> I know the first question is going to be, let me see your code. But
> because this is more of a conceptual question, I'm leaving it out for
> now. I'll gladly provide some as needed.
>
> Many thanks for any assistance.
>
> Shelton

This seems to have worked and I am very appreciative for you pointing
this out. I'm normally a stickler for properly structured code but
deadlines seem to degrade my standards at an exponential rate.

I reverted all of my variables back to "Dim" rather than public (which
I still haven't tried) and it seems to work fine. I'll have to
research the technical differences when time allows.

Thanks again.

S.

I tried this and still can't get it working. Placing the variables
outside of the procedure in "Global" area seems to have worked despite
them being declared using "Dim".
Still, I thank you for the help.

S.

Tim,

Thanks for your input. "Take forever" definately referred to coding
all the args into each procedure. All of my variables would be used in
all of my procedures and there are about 50, including several md
arrays. As noted above, I'm working on a tight deadline so I'm most
concerned with just getting it done. Although the global variable
method doesn't come across as "soup" to me. It seems fairly well
structured given that everything is in one module.

Thanks again.

S.

Thanks. So if I set a variable's value in one routine, that will hold
the value for the next routine? I'll give it a try and see what comes
of it. I appreciate the input.

S.