Daily Download 33: Miscellaneous

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

Selecting Ranges from a UDF:

  • 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.

This entry was posted in Arrays, Computing - general, Excel, UDFs, VBA and tagged , , , , , , , , , . Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.