'知乎@米可爱分享
'https://www.zhihu.com/people/chi-ke-ai-chang-da-de-ai-xian-nu-88
'将Excel单元格的公式合并
Function LoopFormulaFun(rng As Range)
    Debug.Print (LoopFormula(rng.FormulaLocal))
    LoopFormulaFun = LoopFormula(rng.FormulaLocal)
End Function
Function LoopFormula(formula As String)
    Dim Replace_Str As String
    Dim regexObject As RegExp
    Dim result As String
    Dim tempRange As Range
    Dim tempRangeFormula As String
    Set regexObject = New RegExp
    With regexObject
        .Pattern = "\$?[A-Z]+\$?\d+"
        .Global = True
    End With
    result = formula
    For Each v In Split(formula, ",")
        If InStr(v, ":") = 0 Then
            Set matches = regexObject.Execute(v)
            For Each Match In matches
              Debug.Print Match.Value 'Result: Helo Hello Hellllo
              Set tempRange = Range(Match.Value)
              If tempRange.Text <> tempRange.FormulaLocal Then
                    tempRangeFormula = tempRange.FormulaLocal
                    If Left(tempRange.FormulaLocal, 1) = "=" Then
                        tempRangeFormula = Right(tempRange.FormulaLocal, Len(tempRange.FormulaLocal) - 1)
                    End If
                    midFormula = LoopFormula(tempRangeFormula)
                    result = Replace(result, Match.Value, midFormula)
              End If
            Next Match
        End If
    Next
    
    LoopFormula = result
End Function

Function GetRomanNum(strInput As String) As String
    Dim rMatch As Object
    Dim s As String
    Dim arrayMatches()
    Dim i As Long
    
    With New RegExp
        .Global = True
        .MultiLine = True
        .IgnoreCase = True
        .Pattern = "M{0,4}(CM|CD|D?C{0,3})(XC|XL|L?X{0,3})(IX|IV|V?I{0,3})"
        If .Test(strInput) Then
            For Each rMatch In .Execute(strInput)
                ReDim Preserve arrayMatches(i)
                arrayMatches(i) = rMatch.Value
                i = i + 1
            Next
        End If
    End With
    GetRomanNum = Join(arrayMatches, " ")
End Function

Function Test() As Dictionary
    Dim dict As Object
    Dim key As Variant
    Set dict = CreateObject("Scripting.Dictionary")
    For Each Rng In Range("d1:d3")
        'Debug.Print Rng.Text
        'Debug.Print WorksheetFunction.Arabic(GetRomanNum(Rng.Text))
        dict.Add Rng.Text, WorksheetFunction.Arabic(GetRomanNum(Rng.Text))
    Next Rng
    SortDictionary dict
       With dict
        For Each key In .Keys
            'Debug.Print key, .Item(key)
        Next
    End With
    Set Test = dict
End Function

Sub Max()
    Dim dict As Dictionary
    Set dict = Test()
    Debug.Print "result"
    Debug.Print dict.Keys(0)
End Sub

Sub SortDictionary(dict As Object)
    Dim i As Long
    Dim key As Variant

    With CreateObject("System.Collections.SortedList")
        For Each key In dict
            .Add key, dict(key)
        Next
        dict.RemoveAll
        For i = 0 To .Keys.Count - 1
            dict.Add .getkey(i), .Item(.getkey(i))
        Next
    End With
End Sub

Option Explicit

Private Const PAGE_EXECUTE_READWRITE = &H40

Private Declare Sub MoveMemory Lib "kernel32" Alias "RtlMoveMemory" _
        (Destination As Long, Source As Long, ByVal Length As Long)

Private Declare Function VirtualProtect Lib "kernel32" (lpAddress As Long, _
        ByVal dwSize As Long, ByVal flNewProtect As Long, lpflOldProtect As Long) As Long

Private Declare Function GetModuleHandleA Lib "kernel32" (ByVal lpModuleName As String) As Long

Private Declare Function GetProcAddress Lib "kernel32" (ByVal hModule As Long, _
        ByVal lpProcName As String) As Long

Private Declare Function DialogBoxParam Lib "user32" Alias "DialogBoxParamA" (ByVal hInstance As Long, _
        ByVal pTemplateName As Long, ByVal hWndParent As Long, _
        ByVal lpDialogFunc As Long, ByVal dwInitParam As Long) As Integer

Dim HookBytes(0 To 5) As Byte
Dim OriginBytes(0 To 5) As Byte
Dim pFunc As Long
Dim Flag As Boolean

Private Function GetPtr(ByVal Value As Long) As Long
    GetPtr = Value
End Function

Public Sub RecoverBytes()
    If Flag Then MoveMemory ByVal pFunc, ByVal VarPtr(OriginBytes(0)), 6
End Sub

Public Function Hook() As Boolean
    Dim TmpBytes(0 To 5) As Byte
    Dim p As Long
    Dim OriginProtect As Long

    Hook = False

    pFunc = GetProcAddress(GetModuleHandleA("user32.dll"), "DialogBoxParamA")


    If VirtualProtect(ByVal pFunc, 6, PAGE_EXECUTE_READWRITE, OriginProtect) <> 0 Then

        MoveMemory ByVal VarPtr(TmpBytes(0)), ByVal pFunc, 6
        If TmpBytes(0) <> &H68 Then

            MoveMemory ByVal VarPtr(OriginBytes(0)), ByVal pFunc, 6

            p = GetPtr(AddressOf MyDialogBoxParam)

            HookBytes(0) = &H68
            MoveMemory ByVal VarPtr(HookBytes(1)), ByVal VarPtr(p), 4
            HookBytes(5) = &HC3

            MoveMemory ByVal pFunc, ByVal VarPtr(HookBytes(0)), 6
            Flag = True
            Hook = True
        End If
    End If
End Function

Private Function MyDialogBoxParam(ByVal hInstance As Long, _
        ByVal pTemplateName As Long, ByVal hWndParent As Long, _
        ByVal lpDialogFunc As Long, ByVal dwInitParam As Long) As Integer
    If pTemplateName = 4070 Then
        MyDialogBoxParam = 1
    Else
        RecoverBytes
        MyDialogBoxParam = DialogBoxParam(hInstance, pTemplateName, _
                           hWndParent, lpDialogFunc, dwInitParam)
        Hook
    End If
End Function

Sub Unprotected()
    If Hook Then
        MsgBox "VBA Project 解码成功!", vbInformation, "*****"
    End If
End Sub