Module1 vs Thisworkbook for Macro

[Craigm]

I have written macro code where the error is "Procdure is too long". I
am manipulating a lot of mainframe data and charting it. The code ran
until I got the "too long" error.

I have stored all the code in the "ThisWorkbook" so that the code is
available to all sheets.

Should I move the code to "Module1"? "Module1" was created when I
recorded a macro to determine needed code.

Can you have multiple "Modules"?

Is it desireable to have multiple "Modules"?

Would the move eliminate the "too long" error?

Would variables be available for all the sheets in the workbook?

Really confused after a long night of pushing code.

Craigm

 

[Craigm]

Set wbBook = Workbooks("C:\Temp\Charts_ChgAll2.xls")
-------------------------
I moved all the code from "Thisworkbook" to "Module1" in the same
workbook

Now I am getting a "Subscript out of range error" on the above line?

 

[keepITcool]

procedures should never go in an object module
unless there is a good reason.

thus: always use "normal" modules, except for
event handlers.

moveing your code to a normal module wont solve
procedure too long.

that should be solved by "hacking" your procedure
into pieces...

ideally your variables should be at procedure level.
and those variables that need to be "shared" should be passed
as arguments to the called procedure.

However for a quick fix you need now..
move all your variables to module level (before the first procedure or
function)

make 5 or 10 separate procedures from your original.
then add 1 main procedure at the top that calls the others.

dim wks as worksheet
dim rng as range

Sub mainproc()
call proc1
call proc2
call proc3
end sub

Sub proc1()
'blah
End Sub









--
keepITcool
| www.XLsupport.com | keepITcool chello nl | amsterdam


Craigm wrote :

 

[Damon Longworth]

I normally keep the code in the regular module and call it from the Workbook
module if I need to use the workbook events.

Yes, you can and should use multiple modules. Yes, your variables will be
available to all sheets.

Is your code the product of recordings? If so, you can reduce the lines and
improve the efficiency of the recorded code.



--
Damon Longworth

Don't miss out on the 2005 Excel User Conference
Sept 16th and 17th
Stockyards Hotel - Ft. Worth, Texas
www.ExcelUserConference.com

 

[Craigm]

I am learning fast with your help!

Thank you,

Craigm