VBA Favorites

makeDirRecurse.bas

Function makeDir(path As String) As String
    Dim hasPS As Boolean
    Dim ps As String:   ps = Application.PathSeparator
    If Dir(path, vbDirectory) = "" Then
        If Right(path, 1) = ps Then
            path = Left(path, Len(path) - 1)
            hasPS = True
        End If
        makeDir Left(path, InStrRev(path, ps))
        MkDir path
    End If
    If hasPS Then
        makeDir = path & ps
    Else
        makeDir = path
    End If
End Function

RangeTools.bas

Public Function convertColumnNumberToLetter(columnNumber As Integer) As String
    Dim dividend As Integer
    dividend = columnNumber
    Dim columnName As String
    Dim modulo As Integer
    
    Do While (dividend > 0)
        modulo = (dividend - 1) Mod 26
        columnName = Chr$(65 + modulo) + columnName
        dividend = (dividend - modulo) / 26
    Loop
    convertColumnNumberToLetter = columnName
End Function



Public Function getFieldIndex(sht As Worksheet, fieldName As String) As Integer
    Dim iColumn As Integer
    With sht
        For iColumn = 1 To .UsedRange.Columns.Count
            If .Cells(1, iColumn).Value = fieldName Then
                getFieldIndex = iColumn
                Exit Function
            End If
        Next iColumn
    End With
End Function

ribbonInterface

Option Explicit

'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
' MODULE IS PRE-LOADED WITH AN EXAMPLE SETUP THAT WORKS IN CONJUNCTION WITH THE EXAMPLE XML. THIS WILL NOT WORK IN YOUR PROGRAM AS IS
' Required Libraries: None
'
' ribbonInterface allows easier and programatic modification of customa XML ribbon interfaces within Office documents
' Instead of pointing each onAction to a separate intended method, point ALL onActions to ribbonInterface.btnOnAction and then programatically
' modify what to do more dynamically. The same can be done for labels.
' Programatic differences are allowed too. i.e. different label can be generated for different users, buttons can be activated or not, etc.
'
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
' EXAMPLE SHEET XML
' Modify with
'   Custom UI Editor for Microsoft Office (not a MS product) - http://openxmldeveloper.org/blog/b/openxmldeveloper/archive/2009/08/06/7293.aspx
'   or the old fashioned way - https://msdn.microsoft.com/en-us/library/office/ff861787.aspx
' Custom UI XML Documentation
'   pdf - https://msdn.microsoft.com/en-us/library/cc313070(v=office.12).aspx
'   web - https://msdn.microsoft.com/en-us/library/bb608623.aspx
'''''''''''''''''''
' <customUI xmlns="http://schemas.microsoft.com/office/2009/07/customui" onLoad="ribbonInterface.OnRibbonLoad">>
'   <ribbon startFromScratch="false">
'       <tabs>
'           <tab id="tabMain" insertBeforeMso="TabHome" getLabel="ribbonInterface.getLabel">
'               <group id="customGroup" label="Custom Group">
'                   <button id="btn-update" getLabel="ribbonInterface.getLabel" image="refresh-128" size="large" onAction="ribbonInterface.OnAction" />
'                   <button id="btn-backup" getLabel="ribbonInterface.getLabel" image="backup" size="large" onAction="ribbonInterface.OnAction" />
'               </group>
'           </tab>
'       </tabs>
'   </ribbon>
' </customUI>
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
Public Excel_Ribbon As IRibbonUI
Sub OnRibbonLoad(ribbon As IRibbonUI)
    ''allows access to the ribbon object without exiting Excel
    '' i.e. Excel_Ribbon.invalidate will reload the entire ribbon
    Set Excel_Ribbon = ribbon
End Sub

Sub OnAction(control As IRibbonControl, Optional pressed As Boolean)
    Select Case control.ID
        Case Else
            Debug.Print "onAction: " & control.ID & ":" & control.Tag & " not defined"
    End Select
End Sub

Sub getDescription(control As IRibbonControl, ByRef visible)
    'Specifies the name of a callback function to be called to determine the detailed description of this contro
    Select Case control.ID
        Case Else
            visible = True
            Debug.Print "getDescription: " & control.ID & ":" & control.Tag & " not defined"
    End Select
End Sub

Sub getEnabled(control As IRibbonControl, ByRef visible)
    'Specifies the name of a callback function to be called to determine the enabled state of this control
    Select Case control.ID
        Case Else
            visible = True
            Debug.Print "getEnabled: " & control.ID & ":" & control.Tag & " not defined"
    End Select
End Sub

Sub getImage(control As IRibbonControl, ByRef image)
    'Specifies the name of a callback function to be called to determine the icon of this control
    Select Case control.ID
        Case Else
            image = "BlackAndWhiteDontShow" '"close-circle-outline.png"
            Debug.Print "getImage: " & control.ID & " not defined"
    End Select
End Sub

Sub getKeytip(control As IRibbonControl, ByRef visible)
    'Specifies the name of a callback function to be called to determine the suggested KeyTip of this control
    Select Case control.ID
        Case Else
            visible = True
            Debug.Print "getKeytip: " & control.ID & ":" & control.Tag & " not defined"
    End Select
End Sub

Sub getLabel(control As IRibbonControl, ByRef label)
    'Specifies the name of a callback function to be called to determine the label of this control
    Select Case control.ID
        Case Else
            label = control.ID
            Debug.Print "getLabel: " & control.ID & " not defined"
    End Select
End Sub

Sub getPressed(control As IRibbonControl, ByRef visible)
    'Specifies the name of a callback function to be called to determine the toggled state of this control
    Select Case control.ID
        Case Else
            visible = True
            Debug.Print "getPressed: " & control.ID & ":" & control.Tag & " not defined"
    End Select
End Sub

Sub getScreentip(control As IRibbonControl, ByRef visible)
    'Specifies the name of a callback function to be called to determine the screentip of this control
    Select Case control.ID
        Case Else
            visible = control.ID
            Debug.Print "getScreentip: " & control.ID & ":" & control.Tag & " not defined"
    End Select
End Sub

Sub getShowImage(control As IRibbonControl, ByRef rtnVal)
    'Specifies the name of a callback function to be called to determine whether the application SHOULD display the icon of this control
    Select Case control.ID
        Case Else
            rtnVal = True
            Debug.Print "getShowImage: " & control.ID & ":" & control.Tag & " not defined"
    End Select
End Sub

Sub getShowLabel(control As IRibbonControl, ByRef rtnVal)
    'Specifies the name of a callback function to be called to determine whether the application displays the label of this control
    Select Case control.ID
        Case Else
            rtnVal = True
            Debug.Print "getShowLabel: " & control.ID & ":" & control.Tag & " not defined"
    End Select
End Sub

Sub getSize(control As IRibbonControl, ByRef Size)
    'Specifies the name of a callback function to be called to determine the size of this control. use 0 for small, 1 for large
    Select Case control.ID
        Case Else
            Size = 0
            Debug.Print "getSize: " & control.ID & control.Tag & " not defined"
    End Select
End Sub

Sub getSupertip(control As IRibbonControl, ByRef visible)
    'Specifies the name of a callback function to be called to determine the supertip of this control
    Select Case control.ID
        Case Else
            visible = control.ID
            Debug.Print "getSupertip: " & control.ID & ":" & control.Tag & " not defined"
    End Select
End Sub

Sub getVisible(control As IRibbonControl, ByRef visible)
    'Specifies the name of a callback function to be called to determine the visibility state of this control
    Select Case control.ID
        Case Else
            visible = True
            Debug.Print "getVisible: " & control.ID & ":" & control.Tag & " not defined"
    End Select
End Sub