Category: Distribution

  • Ellipsize

    Ellipsize

    Public Function Ellipsize( _ strInput As String, _ Optional MaxLength As Integer = 16 _ ) As String If Len(strInput) > MaxLength Then Ellipsize = Left(strInput, MaxLength) & Chr(133) Else Ellipsize = strInput End If End Function

  • TransferRangeDataAsArray

    TransferRangeDataAsArray

    Public Function TransferRangeDataAsArray( _ rngSource As Range, _ rngTarget As Range, _ Optional xlrvd As XlRangeValueDataType = xlRangeValueDefault _ ) As Range Set TransferRangeDataAsArray = _ rngTarget.Resize( _ rngSource.Rows.Count, _ rngSource.Columns.Count _ ) TransferRangeDataAsArray.Value( _ xlrvd _ ) = _ rngSource.Value( _ xlrvd _ ) End Function

  • HasKey

    HasKey

    Function HasKey( _ coll As Collection, _ index As Variant _ ) As Boolean On Error GoTo ErrorHandler Dim elTest As Variant HasKey = False elTest = coll(index) HasKey = True Exit Function ErrorHandler: End Function

  • ElapsedTime

    ElapsedTime

    Function ElapsedTime( _ endTime As Date, _ startTime As Date _ ) As String Dim Interval As Date ‘ Calculate the time interval. Interval = endTime – startTime ‘ Format and print the time interval in ‘ days, hours, minutes and seconds. ElapsedTime = _ Int( _ CSng( _ Interval _ ) _ ) &…

  • DeleteAllFilesInFolder

    DeleteAllFilesInFolder

    Public Sub DeleteAllFilesInFolder( _ strPathToFolder As String _ ) Dim _ fs As Object, _ fldr As Object, _ f As Object Set fs = CreateObject(“Scripting.FileSystemObject”) Set fldr = fs.GetFolder(strPathToFolder) For Each f In fldr.Files Application.StatusBar = _ “Deleting file ” & _ f.Name & _ ” from folder ” & _ strPathToFolder DoEvents f.Delete…

  • HasHiddenColumns

    HasHiddenColumns

    Public Function HasHiddenColumns( _ rng As Range _ ) As Boolean Dim _ rngColumn As Range HasHiddenColumns = False For Each rngColumn In rng.Columns If rngColumn.Hidden Then HasHiddenColumns = True End If Next rngColumn End Function Usage: Dim _ wsSource As Worksheet, _ rngSource as Range Set wsSource = ThisWorkbook.Sheets(“Sheet1”) Set rngSource = wsSource.Range(“A1”, “K32”)…

  • Notes on VBA Coding Style: Maximizing Scalability and Readability

    Notes on VBA Coding Style: Maximizing Scalability and Readability

    Establishing and adhering to a VBA coding style guide enables increased project reusability and scalability. It makes code more readable and, by extension, the coding experience far more enjoyable. Minimize Horizontal Scrolling Split lines (use the underscore!) and indent. My rationale is that horizontal scrolling takes too long. You want to be able to scan…

  • Export Modules to Folder

    Export Modules to Folder

    This will create a folder using the base name of your Excel file, which is the filename without its extension, along with a vba subfolder. Your VBA modules will be placed there. Set Reference to Microsoft Visual Basic for Applications Extensibility Sub ExportModules( _ Optional PathToVBAModules As String = “” _ ) Dim objMyProj As…