I am a supporter of St. Joseph's hospice. If you find this site useful or if it helped you, consider a small donation to St. Joseph's, please.

Information on
St. Joseph's

 

Excel© VBA code helps you to encapsulate functionality so that it can be run safely in different environments or at different times (repeatedly).

A short introduction into Excel VBA programming which I intend to detail and to enhance over time you can find here.

Average last n cells of a given range.

Find out which subset of values sums up to a specified amount (accounts receivable problem).

Create a monthly birthday list for your department.

Determine the number of dimensions in an array with ArrayDim.

Count hours between two dates or add hours to a date. You can specify which hours for which weekday should be taken into account.

Count unique values.

Count unique values with criteria.

Count visible cells only.

Write data to a database with Excel and retrieve it again.

Distribute parliament seats fairly with the D'Hondt method or the Largest Remainder method.

How to do error trapping with Excel VBA.

Do a linear interpolation.

List frequencies of strings or numbers.

Lock a formula so that it does not get adjusted to added or deleted cells.

Transform decimal numbers into binaries or vice versa: Longdec2bin

Some LOOKUP()-variants I found useful.

MedianIf calculates the median of values where some corresponding values are equal to a defined variable.

Show a floating number with N significant digits.

Show a number in a non-scientific format with all significant digits and all leading resp. trailing zeros: sbNum2Str.

Do a data reconciliation - compare two lists.

Rless determines all cells which are in range 1 but not in range 2.

sbCountMyColor counts the number of cells with the same background color as the calling cell.

Sort data numerically or alphanumerically with sbGSort.

sbSumMyFormat returns totals for a range of identical number formats.

Sum last non-empty cells.

Compute nearest rational number to a float number with a given maximal denominator: NRN(dblFloat, lngMaxDen)

Concatenate cell or element values of ranges or arrays with sbCat.

Make rounded percentages sum up to exactly 100% with sbLRM (largest remainder method).

Return non-blank cells of the input with sbNoBlank.

Reshape an array with sbReShape.

Create a list of unique entries with sbUniq.

A simple sort function for small ranges or arrays which is based on Gnomesort.

Spell numbers: 123.56 are Onehundredandtwentythree Dollars and Fiftysix Cents.

Or compute statistics for weighted values.

Test whether a value is between two other ones (uses sbNextFloat function).

Determine the first or the last weekday of a month (last Sunday, for example).

Store high and low watermark values for a specific cell (as well as the input parameters)

Show how old your data is with the Worksheet_Change event.

WSSplit splits a cell content onto several cells with a given delimiter.