THINKBOX

In large Excel workbooks when you are executing VB macros that change cells in sheets, you can speed up the process significantly by turning workbook calculations off at the begining of your subroutine, and then back on again at the end. Here are some subs that can do this. Simply call the “Calc_Off” sub at the beginning of your code, and the “Calc_On” sub at end end:

Sub Calc_Off()
  Application.Calculation = xlCalculationManual
End Sub

Sub Calc_On()
  Application.Calculation = xlCalculationAutomatic
End Sub

If you prefer, you can manually turn automatic calculations on and off from Excel's user interface.  From the File menu > Options > Formulas >  (Automatic or Manual calculation options).

I’ve tested these in Excel 2010 in sheets with over 16,000 columns and the functions work even for large numbers of columns.

Function ColumnLetterToNumber(ColumnLetters As String) As Long
    ColumnLetterToNumber = Range(ColumnLetters & "1").Column
End Function

Function ColumnNumberToLetters(ColumnNumber As Integer) As String
    Dim strLetters As String
    strLetters = Cells(1, ColumnNumber).Address(1, 0)
    ColumnNumberToLetters = Left(strLetters, InStr(1, strLetters, "$") - 1)
End Function

These functions are not necessary usually, because almost all things in Excel VBA can be done using ranges specified as numbers, for example,

Sheets("Sheet1").Cells(2,3).Value = "Hello"

Where 2 is the row number, and 3 is the column number, where column 1 corresponds to column A.

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.