VBA to help write functions for variables

Sub test_ExtractAndGenerateStringFunctionsFromFile()
WriteBlankFunctions "String", "C:\Users\" & Environ("UserName") & "\Documents\TestTextFiles\MyCode.txt"
End Sub
Sub test_ZeroOutStringVariables()
ZeroOutStringVariables "String", "C:\Users\" & Environ("UserName") & "\Documents\TestTextFiles\MyCode.txt"
End Sub
Sub ZeroOutStringVariables(DimType As String, FilePathToSourceCode As String)
' DimType = string paramerter (only works with String fo now) that identifies the declaration/variable type to extract
' FilePathToSourceCode = file path a a copy and paste of your VBA subs, from which it will extract the names of the DimType's
' Output: A text file with pre-built sub listing all string variable set to = ""
' Written to aid in making sure all string variables get reset to an empty string
    Dim FilePath As String
    Dim fileNum As Integer
    Dim FileContent As String
    Dim Lines() As String
    Dim aStringVarNames() As String
    Dim Line As String
    Dim StringVarNames As String
    Dim i As Integer
    Dim l As Integer

    ' Set the path to a file where you have pasted your VBA subs
    FilePath = FilePathToSourceCode

    ' Open/read file to string -->FileContent
    fileNum = FreeFile
    Open FilePath For Input As fileNum
    FileContent = Input$(LOF(fileNum), fileNum)
    Close fileNum

    ' Split by line
    Lines = Split(FileContent, vbCrLf)
    ' Get Len so we can use it in the RIGHT() function within the loop
    l = Len(DimType) + 3

    ' Loop through lines & extract variable declarations
    For i = LBound(Lines) To UBound(Lines)
        Line = Trim(Lines(i))
        If Right(Line, l) = "As " & DimType Then
            Debug.Print Line
            ' Extract the variable name (assumes "Dim variableName As DimType" format)
            Dim parts() As String
            parts = Split(Line, " ")
            If UBound(parts) = 3 Then
                Dim variableName As String
                variableName = parts(1)
                If StringVarNames <> "" Then
                    StringVarNames = StringVarNames & "," & variableName
                Else
                    StringVarNames = variableName
                End If
            End If
        End If
    Next i
    Debug.Print StringVarNames
    aStringVarNames = Split(StringVarNames, ",")
    aStringVarNames = RemoveDuplicatesFromString(StringVarNames)

    ' Check if the variableArray is empty
    If Not IsArrayEmpty(aStringVarNames) Then
        On Error GoTo ErrorHandler
        ' Open the text file for writing
        Dim fileName As String
        fileName = "C:\Users\" & Environ("UserName") & "\Downloads\ZeroOut_" & DimType & ".txt"
        fileNum = FreeFile
        Open fileName For Output As fileNum
            Print #fileNum, "Sub ZeroOutStringVariables()"
        ' Loop through the variableArray and generate variable assignments
        For i = LBound(aStringVarNames) To UBound(aStringVarNames)
            ' Assign an empty string to the variable
'            Dim variableName As String
            variableName = aStringVarNames(i)
            Print #fileNum, "    " & variableName & " = " & """" & """" ' Assign empty string
        Next i
            Print #fileNum, "End Sub"
        ' Close the text file
        Close fileNum
    End If

    Application.FollowHyperlink fileName

    Exit Sub

ErrorHandler:
    MsgBox "Error: " & Err.Description, vbCritical
    Close fileNum
End Sub
Sub WriteBlankFunctions(DimType As String, FilePathToSourceCode As String)
' DimType = string paramerter (String, Long, Integer) that identifies the declaration/variable type to extract
' FilePathToSourceCode = file path a a copy and paste of your VBA subs, from which it will extract the names of the DimType's
' Output: A text file with pre-built (empty) functions
' Written to aid in "function-izing" my code instead of building huge subs that do too much

    Dim FilePath As String
    Dim fileNum As Integer
    Dim FileContent As String
    Dim Lines() As String
    Dim aStringVarNames() As String
    Dim Line As String
    Dim StringVarNames As String
    Dim i As Integer
    Dim l As Integer

    ' Set the path to a file where you have pasted your VBA subs
    FilePath = FilePathToSourceCode
    ' Open/read file to string -->FileContent
    fileNum = FreeFile
    Open FilePath For Input As fileNum
    FileContent = Input$(LOF(fileNum), fileNum)
    Close fileNum

    ' Split by line
    Lines = Split(FileContent, vbCrLf)
    ' Get Len so we can use it in the RIGHT() function within the loop
    l = Len(DimType) + 3

    ' Loop through lines & extract variable declarations
    For i = LBound(Lines) To UBound(Lines)
        Line = Trim(Lines(i))
        If Right(Line, l) = "As " & DimType Then
            Debug.Print Line
            ' Extract the variable name (assumes "Dim variableName As DimType" format)
            Dim parts() As String
            parts = Split(Line, " ")
            If UBound(parts) = 3 Then
                Dim variableName As String
                variableName = parts(1)
                If StringVarNames <> "" Then
                    StringVarNames = StringVarNames & "," & variableName
                Else
                    StringVarNames = variableName
                End If
            End If
        End If
    Next i
    Debug.Print StringVarNames
    aStringVarNames = Split(StringVarNames, ",")
    aStringVarNames = RemoveDuplicatesFromString(StringVarNames)



    ' Check if the variableArray is empty
    If Not IsArrayEmpty(aStringVarNames) Then
        On Error GoTo ErrorHandler
        ' Open the text file for writing
        Dim fileName As String
        fileName = "C:\Users\" & Environ("UserName") & "\Downloads\BlankFunctions_" & DimType & ".txt"
        fileNum = FreeFile
        Open fileName For Output As fileNum

        ' Loop through the variableArray and generate functions
        For i = LBound(aStringVarNames) To UBound(aStringVarNames)
            ' Construct the function name and header
            Dim functionName As String
            functionName = "Get_" & Trim(aStringVarNames(i))

            ' Write the function header to the file
            Print #fileNum, "Function " & functionName & "(str As String) As " & DimType
            Print #fileNum, "    ' Function code here"
            Print #fileNum, "End Function"
            Print #fileNum, ""  '
        Next i

        ' Close the text file
        Close fileNum
    End If

    Application.FollowHyperlink fileName

    Exit Sub


ErrorHandler:
    MsgBox "Error: " & Err.Description, vbCritical
    Close fileNum
End Sub
Function IsArrayEmpty(arr As Variant) As Boolean
    On Error Resume Next
    IsArrayEmpty = (UBound(arr) < LBound(arr))
    On Error GoTo 0
End Function
Function RemoveDuplicatesFromString(ByVal inputString As String) As Variant
    ' Declare a dictionary object to store unique values
    Dim dict As Object
    Set dict = CreateObject("Scripting.Dictionary")

    ' Split the input string into an array
    Dim inputArray() As String
    inputArray = Split(inputString, ",")

    ' Iterate through the array and add unique values to the dictionary
    Dim i As Long
    For i = LBound(inputArray) To UBound(inputArray)
        ' Use the item as the key in the dictionary
        ' This automatically handles duplicates by overwriting them
        dict(inputArray(i)) = inputArray(i)
    Next i

    ' Create a new array to store the unique values
    Dim uniqueArray() As String
    ReDim uniqueArray(0 To dict.Count - 1)

    ' Copy the unique values from the dictionary to the new array
    Dim key As Variant
    i = 0
    For Each key In dict.Keys
        uniqueArray(i) = dict(key)
        i = i + 1
    Next key

    ' Return the uniqueArray
    RemoveDuplicatesFromString = uniqueArray
End Function