Excel, Macros, VBA and Software tips

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.

Avoid external references with Active X form controls in Excel

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.

Trick for sorting your business expenses using a spreadsheet, quickly

Here is a quick summary of what will be covered in this article, useful for people that own their own small business but do not want to buy specialized financial software:  downloading CSV files of your transactions for bank accounts and credit cards, and how to batch concatenate (join) multiples of them together all at once, how to remove duplicate lines, and finally how using a spreadsheet you can quickly use keywords of your own to sort all of your expenses into categories like meals, entertainment, hotel and travel expenses.  This is not only useful for small businesses but good if you like to keep track of your personal expenses as well.