Defining global variables, and arrays of unknown variable length in Excel macro, VBA

It’s not usually recommended to use global variables because they make it more difficult for debugging or reading other people’s code.  However, they are handy sometimes when you’re in a rush to get your code done.

At the top of a Module for example, simply type something like these below.  I’ve defined three Pipe Specification variables: two arrays of unknown length and one variable:

Public PipeSpecColNums() As Integer
Public PipeSpecNames() As String
Public LastPipeSpecName As String

You can update the size of the array on the fly in sub routines, like this:

Redim Preserve PipeSpecNames(20) 'Now 21 array entries (0-20),
                                 'and preserves previous items stored.

Redim PipeSpecNames(20)  'changes the size of the array without preserving.