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.”
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
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 FunctionThese 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”.
Continue reading “How to copy an Excel Worksheet with Forms to another workbook without external references”
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 StringYou 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.
Batch rename files in a directory you specify in Windows, for free! Continue reading “Free Batch Renaming application, for Windows”
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.”