danwagnerco

• Copy A_Functions to a new workbook
• Copy C_Pearson to the same workbook
• Add the following references:
  • Microsoft Scripting Runtime
  • Microsoft VBScript Regular Expressions 5.5
  • Windows Script Host Object Model
• Change VBAProject to toolbelt
• Set version in Project Properties > Project Description
• Put the file in Dropbox > toolbelt
• Email subscribers to let them know about the update

danwagnerco

Option Explicit
Public Sub RunBatchFile()

    Dim strCommand As String
    Dim lngErrorCode As Long
    Dim wsh As WshShell
    Set wsh = New WshShell
    
    'Run the batch file using the WshShell object
    strCommand = Chr(34) & _

danwagnerco

Option Explicit
Public Sub RunBatchFileThenRenameFolder()

    Dim strNewFolderName As String, strRunBatchCommand As String, _
        strRenameFolderCommand As String
    Dim lngErrorCode As Long
    Dim wks As Worksheet
    Dim wsh As WshShell
    Set wsh = New WshShell
    

danwagnerco

Option Explicit
Public Sub ZipFoldersInColumn()
    
    Dim wks As Worksheet
    Dim lngIdx As Long, lngErrorCode
    Dim strFullPath As String, strZipName As String, strEndFound As String, _
        strCommand As String, strTargetDir As String
    Dim blnEndFound As Boolean
    Dim wsh As WshShell
    Set wsh = New WshShell

danwagnerco

Option Explicit
Public Sub ConvertLookupsToValues()

    Dim lngLastRow As Long, lngIdx As Long
    Dim varLookups As Variant
    
    'Everything happens on the Ledger sheet
    With ThisWorkbook.Worksheets("Ledger")
    
        'Identify the last-occupied row in column D on the Ledger sheet,

danwagnerco

Option Explicit
Public Sub DeleteRowsWithForLoop()

    Dim wksData As Worksheet
    Dim lngLastRow As Long, lngIdx As Long
    
    'Set references up-front
    Set wksData = ThisWorkbook.Worksheets("data")
    
    With wksData

danwagnerco

Option Explicit
Public Sub DeleteRowsWithAutofilter()

    Dim wksData As Worksheet
    Dim lngLastRow As Long
    Dim rngData As Range
    
    'Set references up-front
    Set wksData = ThisWorkbook.Worksheets("data")
    

danwagnerco

Option Explicit
Public Sub CombineDataFromAllSheets()

    Dim wksSrc As Worksheet, wksDst As Worksheet
    Dim rngSrc As Range, rngDst As Range
    Dim lngLastCol As Long, lngSrcLastRow As Long, lngDstLastRow As Long
    
    'Notes: "Src" is short for "Source", "Dst" is short for "Destination"
    
    'Set references up-front

danwagnerco

Public Function CollectUniques(rng As Range) As Collection
    
    Dim varArray As Variant, var As Variant
    Dim col As Collection
    
    'Guard clause - if Range is nothing, return a Nothing collection
    'Guard clause - if Range is empty, return a Nothing collection
    If rng Is Nothing Or WorksheetFunction.CountA(rng) = 0 Then
        Set CollectUniques = col
        Exit Function

danwagnerco

Public Sub WriteUniquesToNewSheet()

    Dim wksUniques As Worksheet
    Dim rngUniques As Range, rngTarget As Range
    Dim strPrompt As String
    Dim varUniques As Variant
    Dim lngIdx As Long
    Dim colUniques As Collection
    Set colUniques = New Collection