Odds & Ends

A collection of stuff we've accumulated, written, developed or created. Most of it is probably absolutely pointless, but some of it may be useful to someone in a bizarre set of circumstances. Anyway, for what it's worth, here it is. Most of it is years old, so keep that in mind if you need to use it.

Maze

Randomly generated (2D/low-tech) maze with only one solution. No point, just fun. Create a maze.

Text Analyser

Analyses and graphs the letter distribution in a passage of text. All text written in english follows the english letter frequency distribution. Analyse text.

Swap Minus

Having used SAP for a number of years, I regularly needed to export data to Excel. However, some reports would place the minus sign of a negative number on the right, e.g. 1234.56-. These few lines of code convert negative 'numbers' in the current selection to real numbers.

For Each Cell In Selection
    If Right(Cell.Value, 1) = "-" Then
        Cell.Value = Left(Cell.Value, Len(Cell.Value) - 1) * -1
    End If
Next

Remove Accents

If you have ever needed to perform lookups on European names that have accents over some letters, this code will replace accented letters in the current selection with their non-accented versions. This small section of code, converts the single accented characters to their plain equivalents in addition to Œ and Æ to OE and AR, respectively and the german ß to ss.

aAccentedLetter = Array(192, 193, 194, 195, 196, 197, 224, 225, 226, 227, _
                        228, 229, 240, 162, 199, 231, 208, 200, 201, 202, _
                        203, 232, 233, 234, 235, 131, 161, 204, 205, 206, _
                        207, 236, 237, 238, 239, 209, 241, 210, 211, 212, _
                        213, 214, 216, 242, 243, 244, 245, 246, 248, 138, _
                        154, 223, 217, 218, 219, 220, 249, 250, 251, 252, _
                        215, 159, 221, 253, 255, 142, 158)
aPlainLetter = Array(65, 65, 65, 65, 65, 65, 97, 97, 97, 97, _
                        97, 97, 97, 99, 67, 99, 68, 69, 69, 69, _
                        69, 101, 101, 101, 101, 102, 73, 73, 73, 73, _
                        73, 105, 105, 105, 105, 78, 110, 79, 79, 79, _
                        79, 79, 79, 111, 111, 111, 111, 111, 111, 83, _
                        115, 115, 85, 85, 85, 85, 117, 117, 117, 117, _
                        120, 89, 89, 121, 121, 90, 122)

If UBound(aAccentedLetter) <> UBound(aPlainLetter) Then
    MsgBox "Error in conversion coding." & vbCrLf & _
            "Number of accented letters is not equal" & vbCrLf & _
            "to number of plain letters.", vbCritical, "Remove accents"
Else
    'replace single letters
    For i = 0 To UBound(aAccentedLetter)
        Selection.Replace What:=Chr(aAccentedLetter(i)), _
                            Replacement:=Chr(aPlainLetter(i)), _
                            LookAt:=xlPart, SearchOrder:=xlByRows, _
                            MatchCase:=True
    Next i

    'replace double letters i.e. OE, oe, AE and ae and the german double s for ss
    Selection.Replace What:=Chr(198), Replacement:="AE", LookAt:=xlPart, _
                        SearchOrder:=xlByRows, MatchCase:=True
    Selection.Replace What:=Chr(230), Replacement:="ae", LookAt:=xlPart, _
                        SearchOrder:=xlByRows, MatchCase:=True
    Selection.Replace What:=Chr(140), Replacement:="OE", LookAt:=xlPart, _
                        SearchOrder:=xlByRows, MatchCase:=True
    Selection.Replace What:=Chr(156), Replacement:="oe", LookAt:=xlPart, _
                        SearchOrder:=xlByRows, MatchCase:=True
    Selection.Replace What:=Chr(223), Replacement:="ss", LookAt:=xlPart, _
                        SearchOrder:=xlByRows, MatchCase:=True
End If