Option Explicit

Category: Distribution

  • DeleteHiddenSourceColumnsFromTarget

    DeleteHiddenSourceColumnsFromTarget

    —

    by

    Keith
    in , , ,

    Public Sub DeleteHiddenSourceColumnsFromTarget( _ rngSource As Range, _ rngTarget As Range _ ) Dim _ rngColumn As Range, _ intSourceColumnInitialOffset As Integer, _ intSourceRowInitialOffset As Integer, _ iColumn As Integer With rngSource.Cells(1, 1) intSourceColumnInitialOffset = .Column intSourceRowInitialOffset = .Row End With For iColumn = rngSource.Columns.Count To 1 Step -1 If rngSource.Columns(iColumn).Hidden Then rngTarget.Columns(iColumn).Delete End If…

  • Ellipsize

    Ellipsize

    —

    by

    Keith
    in , , ,

    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

    —

    by

    Keith
    in , , ,

    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

    —

    by

    Keith
    in , , ,

    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

    —

    by

    Keith
    in , , ,

    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

    —

    by

    Keith
    in , , ,

    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

    —

    by

    Keith
    in , , ,

    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

    —

    by

    Keith
    in ,

    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

    —

    by

    Keith
    in , ,

    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…