Option Explicit

Category: VBA

  • Export VBA Modules for Version Control updated September 2024

    Export VBA Modules for Version Control updated September 2024

    —

    by

    Keith
    in , , ,

    Add a Module in your VBA Project. Name it “Exports” and paste the following code into the module in its entirety. When your project is ready for export (and subsequent version control), click on the “Macros” button in the Developer ribbon, select “ExportVBAModules” and click “Run.” Option Explicit ‘ Define a constant for the export…

  • Update Access VBA Saved Imports Exports: A Step-by-Step Guide

    —

    by

    Keith
    in , , , ,

    Updating Access VBA saved imports exports is essential when dealing with external data sources. This step-by-step guide will show you how to update Access VBA saved imports exports by dynamically changing the file path within your import/export specifications. In this article, we’ll walk through a powerful VBA script that allows you to update Access VBA…

  • ReturnName

    —

    by

    Keith
    in , , ,

    Function ReturnName(ByVal num As Integer) As String ReturnName = Split(Cells(, num).Address, “$”)(1) End Function

  • DeleteInitialColumnsFromWorksheet

    —

    by

    Keith
    in , , ,

    Note: This function depends on ReturnName Sub DeleteInitialColumnsFromWorksheet( _ ws As Worksheet, _ NumberOfColumns As Integer _ ) With ws .Columns( _ ReturnName( _ 1 _ ) & _ “:” & _ ReturnName( _ NumberOfColumns _ ) _ ).Delete _ Shift:=xlToLeft End With End Sub

  • DeleteBottomRowsFromWorksheet

    —

    by

    Keith
    in , ,

    Note: This method depends on GetLastUsedRowNumberInWorksheet Sub DeleteBottomRowsFromWorksheet( _ ws As Worksheet _ ) Dim _ strStartingEmptyRow As String, _ rngStartingRow As Range, _ rngEmptyRows As Range, _ lngLastRow As Long lngLastRow = _ GetLastUsedRowNumberInWorksheet( _ ws _ ) If lngLastRow = _ -1 Then Exit Sub End If strStartingEmptyRow = _ CStr( _ lngLastRow…

  • GetLastUsedColumnNumberInWorksheet

    —

    by

    Keith
    in , ,

    Function GetLastUsedColumnNumberInWorksheet( _ ws As Worksheet _ ) As Long Dim _ rng As Range, _ rngResults As Range Set rng = _ ws.Cells Set rngResults = _ rng.Find( _ what:=”*”, _ After:=rng.Cells(1), _ Lookat:=xlPart, _ LookIn:=xlFormulas, _ SearchOrder:=xlByColumns, _ SearchDirection:=xlPrevious, _ MatchCase:=False _ ) If _ rngResults Is Nothing _ Then GetLastUsedColumnNumberInWorksheet = _…

  • GetLastUsedRowNumberInWorksheet

    —

    by

    Keith
    in , ,

    Function GetLastUsedRowNumberInWorksheet( _ ws As Worksheet _ ) As Long Dim _ rng As Range, _ rngResults As Range Set rng = _ ws.Cells Set rngResults = _ rng.Find( _ what:=”*”, _ After:=rng.Cells(1), _ Lookat:=xlPart, _ LookIn:=xlFormulas, _ SearchOrder:=xlByRows, _ SearchDirection:=xlPrevious, _ MatchCase:=False _ ) If _ rngResults Is Nothing _ Then GetLastUsedRowNumberInWorksheet = _…

  • DeleteAllConnectionsFromWorkbookOfType

    —

    by

    Keith
    in , ,

    Public Function DeleteAllConnectionsFromWorkbookOfType( _ wb As Workbook, _ xlconnType As XlConnectionType _ ) Dim _ wbcn As WorkbookConnection For Each wbcn In wb.Connections If wbcn.Type = xlconnType Then wbcn.Delete End If Next wbcn End Function

  • DeleteAllSlicersFromWorkbook

    —

    by

    Keith
    in , ,

    Public Function DeleteAllSlicersFromWorkbook( _ wb As Workbook _ ) Dim _ ws As Worksheet, _ shp As Shape For Each ws In wb.Sheets For Each shp In ws.Shapes If shp.Type = msoSlicer Then shp.Delete Next shp Next ws End Function

  • GetFileExtension

    —

    by

    Keith
    in , ,

    Public Function GetFileExtension( _ sFullFileNameWithPath As String _ ) As String Dim fs As Object Set fs = CreateObject(“Scripting.FileSystemObject”) GetFileExtension = _ fs.GetExtensionName( _ sFullFileNameWithPath _ ) Set fs = Nothing End Function