Ragingsheep fucked around with this message at 03:41 on Jul 15, 2013

me your dad fucked around with this message at 19:56 on Jul 24, 2013

Function RegExReplace(Original As String, MatchedText As String, Replacement As String, Optional IgnoreCase As Boolean = False, _
Optional Multiline As Boolean = True, Optional IsGlobal As Boolean = True)
    Dim Original_ As String
    Original_ = Original


Dim RegEx
Set RegEx = CreateObject("vbscript.regexp")
    With RegEx
    .IgnoreCase = IgnoreCase
    .Multiline = Multiline
    .Pattern = MatchedText
    .Global = IsGlobal
    End With

RegExReplace = RegEx.Replace(Original_, Replacement)
End Function

RICHUNCLEPENNYBAGS fucked around with this message at 02:35 on Jul 27, 2013

• Unmerge all merged cells in a worksheet and for each merged cell copy the merge values to every cell in the MergeArea
  
• Unmerge and center across the selection
  
• Rename or move an open workbook
  
• "Bake" conditional formatting into the cell formatting (god, that was beautiful, and such a pain in the rear end to write)
  
• Some other stuff I'm not remembering
  

Sub UnMergeFill()
 '---------------------------------------------------------------------------------------
 ' Copyright : 2013-08-01
 ' Author    : celestial teapot
 ' Purpose   : remove all merged cells from active worksheet and fill values
 ' License   : MIT License [url]http://opensource.org/licenses/MIT[/url]
 '---------------------------------------------------------------------------------------
    
    Dim proc_name As String
    proc_name = "unmergefill"
    On Error GoTo ErrorHandler
    
    '''''''''''''''''''
    'Variables        '
    '''''''''''''''''''
    Dim haystack As range
    Dim oneCell As range
    Dim mrgAreaAddress As String
    Dim mrgCellCount As Long
    Dim mrgValue As Variant
    
    '''''''''''''''''''
    'Declarations     '
    '''''''''''''''''''
    Set haystack = ActiveSheet.UsedRange
    
    '''''''''''''''''''
    'Action           '
    '''''''''''''''''''
    For Each oneCell In haystack
        mrgAreaAddress = oneCell.MergeArea.Address
        mrgCellCount = oneCell.MergeArea.Cells.count
        
        If mrgCellCount > 1 Then
            With oneCell.MergeArea
                .MergeCells = False
                .Value = oneCell.Value
            End With
        End If
    Next oneCell
    
Endgame:
    Exit Sub            'Exit before error handler
ErrorHandler:
    MsgBox "There was an unhandled exception in function " _
              & proc_name & _ 
              ". Source: " & Err.Source & _
              "Error number: " & Err.Number & _ 
              " Description: " & Err.Description
    Resume Endgame
End Sub

celestial teapot fucked around with this message at 00:29 on Aug 2, 2013

RICHUNCLEPENNYBAGS fucked around with this message at 02:28 on Aug 3, 2013

=A2&"<br>"&IF(ISBLANK(L2),,(L$1&": "&L2&"<br>"))&IF(ISBLANK(M2),,(M$1&": "&M2&"<br>"))
&IF(ISBLANK(N2),,(N$1&": "&N2&"<br>"))&IF(ISBLANK(O2),,(O$1&": "&O2&"<br>"))
&IF(ISBLANK(P2),,(P$1&": "&P2&"<br>"))&IF(ISBLANK(Q2),,(Q$1&": "&Q2&"<br>"))&"<hr><br>"

Zorak of Michigan fucked around with this message at 02:48 on Aug 3, 2013

Function AttribSkipBlank(MyAttribute As String, MyValue As String) As String
  Dim MyText As String


  If MyValue <> "" Then
    MyText = MyAttribute & ":  " & MyValue & "<br>"
  Else
    MyText = ""
  End If

AttribSkipBlank = MyText

End Function
	

RICHUNCLEPENNYBAGS fucked around with this message at 03:47 on Aug 3, 2013

celestial teapot fucked around with this message at 19:22 on Aug 7, 2013

Public Function Coalesce(ParamArray haystack() As Variant) As Variant
 '---------------------------------------------------------------------------------------
 ' Copyright : 2013-08-01
 ' Author    : celestial teapot
 ' Purpose   : Returns the first of the haystack that has length greater than zero
 ' License   : MIT License [url]http://opensource.org/licenses/MIT[/url]
 '---------------------------------------------------------------------------------------
    
    Dim proc_name As String
    proc_name = "Coalesce"
    On Error GoTo ErrorHandler
    
    '''''''''''''''''''
    'Variables        '
    '''''''''''''''''''
    Dim i As Integer
    
    '''''''''''''''''''
    'Declarations     '
    '''''''''''''''''''
    i = 0                               'Start with the 0th array item
    
    '''''''''''''''''''
    'Action           '
    '''''''''''''''''''
    Do While Len(haystack(i)) = 0       'While the "i"th array item is length zero
        i = i + 1                       'Go to the next one
        If UBound(haystack) < i Then    'Unless we hit the end already
            Coalesce = xlErrValue       'Then return an error value
            GoTo Endgame                'and get the gently caress out of here
        End If
    Loop
    
    Coalesce = haystack(i)              'Return the first non-null value in the list of haystack
    
Endgame:
    Exit Function       'Exit before error handler
ErrorHandler:
    MsgBox "There was an unhandled exception in function " _
              & proc_name & _ 
              ". Source: " & Err.Source & _
              "Error number: " & Err.Number & _ 
              " Description: " & Err.Description
    Resume Endgame
End Function

celestial teapot fucked around with this message at 19:55 on Aug 6, 2013

Public Function Distinct(ByRef haystack As Variant) As Variant
 '---------------------------------------------------------------------------------------
 ' Copyright : 2013-08-06
 ' Author    : celestial teapot
 ' Purpose   : Take an array and return an array with only the distinct values
 ' License   : MIT License [url]http://opensource.org/licenses/MIT[/url]
 '---------------------------------------------------------------------------------------
    
    Dim proc_name As String
    proc_name = "Distinct"
    On Error GoTo ErrorHandler
    
    '''''''''''''''''''
    'Variables        '
    '''''''''''''''''''
    Dim i As Long
    Dim lower As Long
    Dim upper As Long
    Dim dict As Scripting.Dictionary
    
    '''''''''''''''''''
    'Declarations     '
    '''''''''''''''''''
    Set dict = New Scripting.Dictionary
    
    '''''''''''''''''''
    'Action           '
    '''''''''''''''''''
    lower = LBound(haystack)                      'Start with the first non-null value in the array
    upper = UBound(haystack)                      'And end with the last one! :haw:
    
    For i = lower To upper                        'For each value in the array,
        dict.Add haystack(i, 1), i                'add it to a dictionary array (excludes duplication)
    Next i
    
    Distinct = dict.Items
    
Endgame:
    Exit Function            'Exit before error handler
ErrorHandler:
    
    If Err.Number = 457 Then                      'Ignore errors about duplicate dictionary values
        Resume Next
    End If
    
    MsgBox "There was an unhandled exception in function " _
              & proc_name & _
              ". Source: " & Err.Source & _
              "Error number: " & Err.Number & _
              " Description: " & Err.Description
    Resume Endgame
End Function

UBound(distinct(array))

celestial teapot fucked around with this message at 20:53 on Aug 7, 2013