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

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

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.

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" wrote in message
...
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

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.

"shelfish" wrote in message
...

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?

Create a class and give it a bunch of (preferably) properties or (less
desirably) a bunch of public variables which represent all the data that
needs to be passed between procedures. Then you pass this one class instead
of 50 variables. Pseudocode below:

Sub Main
Dim operatingData As MyBigDataClass
Set operatingData = New MyBigDataClass

With operatingData
.Var1 = myVar1
.Var2 = myVar2
.Var3 = myVar3
...
End With

SubRoutine1 operatingData ' Instead of SubRoutine1 myVar1, myVar2,
myVar3, ...
SubRoutine2 operatingData
SubRoutine3 operatingData
...
End Sub

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.

Two thoughts:

1. A structure that holds all your variables that need to be accessed in more
than one procedure. Not particularly elegant, but works great, with two
advantages: You only pass the structure (or declare the instance of the
structure global) and if you need to add a new item it only needs to be added
to the structure and it's available. The down side is that unless you pass
it by ref between procedures, this is just a more complicated way of doing
global variables.

2. Ref variables - pass the memory location and not the value, which should
cover the access-speed issue and update-across-procedures issues.

shelfish wrote:
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.