Category: Code

  • DeleteHiddenSourceColumnsFromTarget

    DeleteHiddenSourceColumnsFromTarget

    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

    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 _ ) _ ) &…

  • RenderListControlHeadings

    RenderListControlHeadings

    Public Sub RenderListControlHeadings( _ ListControl As Control, _ ColumnHeadingString As String, _ Optional FontWeight As Long = 400, _ Optional OffsetLeft As Long = 8 _ ) Dim ctls As Controls Dim iCtl As Control Dim strControlNameStub As String Dim strLabelNameStub As String Set ctls = ListControl.Parent.Controls strLabelNameStub = “lbheading_” strControlNameStub = _ strLabelNameStub &…

  • 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”)…

  • 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…