In Excel it is much faster to write large sets of data to a spreadsheet all at once instead of using a loop, by copying an array variable to a range of cells directly. This can speed up an Excel macro significantly, especially in large complex workbooks.
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.
The trick is to replace all of your “Form Controls” with “ActiveX Controls”.
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.