Author: Keith

  • DeleteAllConnectionsFromWorkbookOfType

    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

    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

    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

  • WorkbookSheetExists

    by

    in

    Public Function WorkbookSheetExists( _ wb As Workbook, _ strSheetName As String _ ) As Boolean Dim ws As Worksheet WorkbookSheetExists = False For Each ws In wb.Sheets If ws.Name = strSheetName Then WorkbookSheetExists = True End If Next ws End Function

  • FolderExists

    Function FolderExists( _ strCompleteFolderPath As String _ ) As Boolean Dim _ fs As Object Set _ fs = _ CreateObject( _ “Scripting.FileSystemObject” _ ) FolderExists = _ fs.FolderExists( _ strCompleteFolderPath _ ) Set _ fs = _ Nothing End Function

  • DeleteVBACodeFromWorkbook

    Sub DeleteVBACodeFromWorkbook( _ wb As Workbook _ ) Dim _ vbc As VBComponent For Each vbc In wb.VBProject.VBComponents If vbc.Type vbext_ct_Document Then wb.VBProject.VBComponents.Remove vbc End If Next vbc End Sub

  • GetFilePath

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

  • Edge, Git and VS Code Integration

    In my VBA project, on common UserForms, I have three labels with click event handlers attached: First is a pause button, which just triggers a Stop command, launching the debugger/VBA IDE. Second is a save button, which will save ThisWorkbook. Third triggers the ExportModules procedure. Source file updates are reflected in VS Code. Running GitHub…

  • GetFileNameFromFullPath

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

  • GetBaseName

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