how to break a long piece of code into subroutines

[steve_bris]

HI.

I have written a lot of pages of code and since I was new to VBA, and I didn't take inot account things like debugging .....I would like to break it down inot subroutines.

The following piece of code is a small example

It creates the lrow integers....and the Role array that are used in all of the rest of the code......

I know this is very short for a sub.....but I just want an example of how to structure it so I can use it throughtout the code.

Could someone please post an example of how I would make this into a subrountine and how I would call it in the code and how I go about passing variables into and out of the subroutine for other subroutines to use.

Thanks heaps for any help.

Steve

 Sheets("Sheet1").Select
        lrow = Range("J65535").End(xlUp).Row          'lrow is the lowest address no in the Role column
        ROLENo = lrow                                 ' lowest address equals the number of roles
        ReDim Role(1 To lrow)
        
        With Sheets("Sheet1")                         ' fill the Role Array
          For i = 1 To lrow
              Role(i) = Range("J" & i).Value
          Next i
        End With

 

[tkpstock]

Sub FillArray(ws as String, clmn as String)
Sheets(ws).Select
lrow = Range(clmn & "65535").End(xlUp).Row
ReDim Role(1 To lrow)
        
        With Sheets(ws)
          For i = 1 To lrow
              Role(i) = Range(clmn & i).Value
          Next i
        End With
End Sub

To call from your code:

Call FillArray("worksheet","J")

This is just using your code - there is probably a better way to fill the array, but I haven't had my coffee yet...

You do know that this is an Access forum, right? There's an Excel forum at http://www.excelforum.com/ that I use from time to time...

 

[modest]

I know this is very short for a sub

I think you need to grasp the concept of what subs/functions are used for. Only then will you realize why this doesn't make sense.