Fast copy an Array (or text files) directly to a range of cells in Excel, VBA macro code.

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. Continue reading “Fast copy an Array (or text files) directly to a range of cells in Excel, VBA macro code.”

Speed up Excel VBA macros by turning calculations off and on

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

Excel, VBA macro functions to convert column number to letter, or letter to column number

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.

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.

Tax tricks: Join multiple CSV files, remove duplicate lines, and a spreadsheet trick to very quickly sort business transactions by keywords you choose.

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.   Continue reading “Tax tricks: Join multiple CSV files, remove duplicate lines, and a spreadsheet trick to very quickly sort business transactions by keywords you choose.”