This is the last of the month and a bit of Daily Downloads, and consists of a bumper bundle of 20 spreadsheets with miscellaneous functions that didn’t fit in elsewhere:
Repeating a set of data: for instance making three copies of every row of a range of data, and also extract specified rows or columns, or rows or columns at a specified spacing.
Download file: http://interactiveds.com.au/software/IndexA.zip
Multiple Match Function: A function to find the first row in a table that contains two or more different matching numbers.
Download file: http://interactiveds.com.au/software/Mmatch.zip
A Sort Function: Sort VBA arrays, or use as an array function on the spreadsheet for dynamic sorting.
Download file: http://interactiveds.com.au/software/SortFunc.zip
Adding Function Categories and Descriptions A simple method of adding categories and descriptions to VBA functions (with a link to a more complicated method with greater functionality).
Download file: http://interactiveds.com.au/software/StringFunctions.zip
Using Goal Seek on Multiple Cells: A simple macro to allow Goal Seek to be automatically applied to a range of cells, arranged in either a column or row.
Download file: http://interactiveds.com.au/software/GSeek.zip
Randomising a list: A VBA subroutine to randomise a list.
Download file: http://interactiveds.com.au/software/randomise%20list.zip
Three UDFs: Three simple UDFs to add every other cell; sum the digits of a number and transpose data from columns.
Download file: http://interactiveds.com.au/software/SumTab.zip
Hello World (again): A bit of fun with text and numbers.
Download file: http://interactiveds.com.au/software/HWorld-JW.xls
The speed of loops: Looks at the speed of alternative ways of looping through a set of data, with examples in:
Download file: http://interactiveds.com.au/software/CheckLoopSpeed.xls
Code Generator: VBA code to automatically generate VBA code.
Download file: http://interactiveds.com.au/software/Code%20Generator.xls
Comparing floating point numbers : Functions to check if two numbers are equal, to any specified tolerance.
Download file: http://www.interactiveds.com.au/software/CombineArray.xls
The Switch Function (VBA and UDF): Examples of the VBA Switch function, and a UDF providing similar functionality.
Download file: http://www.interactiveds.com.au/software/SwitchPY.xls
Retrieving unique values from a range or array … using the Scripting Dictionary object.
Download file: http://www.interactiveds.com.au/software/GetUnique.xlsb
Update Dec. 2013:
SelectAv Function: find the average for a number of the most recent values from a range, ignoring the highest (and/or lowest) values from the selected set.
Download file: http://interactiveds.com.au/software/SelectAv.xlsb
Function to make a list of functions …:Make a list of all the VBA procedures in a workbook.
Download file: http://interactiveds.com.au/software/GetProcedures.xlsb
- Extend or reduce the range size to the length of continuous data in the first column.
- Return the number of rows and columns in this range.
- Convert the range object to a variant array
- Download file: http://interactiveds.com.au/software/GetRange.xlsb
Showing numerical values of formulae cell references:
Download file: http://interactiveds.com.au/software/Getrefs.xls
Copy non-blank rows to another sheet:
Download file: http://interactiveds.com.au/software/NonBlank.xlsb
Select and sum data using the scripting dictionary:
Download file: http://interactiveds.com.au/software/SelectSum.xlsb
Using Index() as an array function: Using the Excel built in Index() function to operate on arrays; it also provides a VBA user defined function (UDF), VBIndex, which simplifies working with arrays within VBA, and also provides improved functionality when called from the spreadsheet.
Download file: http://interactiveds.com.au/software/VBIndex.xlsb
All the download files listed over the past 33 days (plus later updates) are free and include full open-source code. Questions, comments and suggestions are welcomed.