Category: Code

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