Procedure too large/ Option Explicit

[yxz152830]

Gurus,
If my module is option explicit, how do I break the procedure into smaller procedures in the most efficient way?
my guess is that I can claim the variables in global or I can claim them again in each procedure?

 

[RoryA]

Keep variables local where possible, and pass them between procedures as arguments when necessary.

 

[yxz152830]

Keep variables local where possible, and pass them between procedures as arguments when necessary.

right now ive got another problem...
my whole procedure is a loop, meaning everything is in a loop. How do I break it down into small procedures?
It's like this:
for cell0
for cell1 in range1
next cell1
for cell2 in range2
next cell2
........................
for cell20 in range20
next cell20
next cell0

 

[RoryA]

Whatever you are doing inside those loops can probably be refactored into a separate procedure (or several, if they don't do similar things for each range).

 

[yxz152830]

Whatever you are doing inside those loops can probably be refactored into a separate procedure (or several, if they don't do similar things for each range).

one of the conditions for the nested loops to proceed is: if cell(1 to 20).value = cell0.value. I'm not sure how to refactor them into seperate subroutines. If cell0 is only in the main sub, the if statement in other subs like " if cell1.value = cell0.value" will return object not defined type of error.

 

[RoryA]

You would pass the value in question as an argument to the called routine. It's very difficult to give specific advice about such a generalised question, but that is the basic principle.

 

[yxz152830]

You would pass the value in question as an argument to the called routine. It's very difficult to give specific advice about such a generalised question, but that is the basic principle.

could you show me how to do it by this example? how do I seperate the nested for loop from the main sub?

sub main()
for each cell0 in range0
  for each cell1 in range1
    if cell1 = cell0 then
.......
    end if
  next cell1
next cell0
end sub

 

[RoryA]

sub main()
for each cell0 in range0
   otherSub cell0.Value
next cell0
end sub
Sub otherSub(checkVal as Variant)
  for each cell1 in range1
    if cell1 = checkVal then
       ' do something
    end if
  next cell1

 

[yxz152830]

sub main()
for each cell0 in range0
   otherSub cell0.Value
next cell0
end sub
Sub otherSub(checkVal as Variant)
  for each cell1 in range1
    if cell1 = checkVal then
       ' do something
    end if
  next cell1

what would happen if i replace checkval by cell0?

 

[RoryA]

Nothing - it doesn't matter what you call the variable.