VBA - Função Transaciona - Quando o MS Excel não é CASE SENSITIVE

O MS Excel não é CASE SENSITIVE.

Isso é um problemão quando queremos usar PROCX ou PROCV, etc...

Mas e se você tiver uma função à mão que troque os caracteres maiúsculos e minúsculos por outros caracters, permitindo que você utilize a mesma coluna de dados em sua PROC?

Divirta-se:

Function Transaciona(nPar As String) As String
    '      Author: André Bernardes
    '        Date: 14.06.21 - 20:28
    ' Description: Adequa os caracteres alfabéticos, subistituindo-os por números.
   
    Dim nChar As Integer
    Dim i As Integer
    Dim nParticula As String
    Dim nParticula2 As String
    Dim NewPar As String
   
    Let nChar = Len(nPar)
   
    For i = 1 To nChar
   
        Let nParticula = Mid(nPar, i, 1)
       
        If nParticula = "a" Then
            Let nParticula2 = "1" & "ÇÇ"
        ElseIf nParticula = "A" Then
            Let nParticula2 = "11" & "Ç"
        ElseIf nParticula = "b" Then
            Let nParticula2 = "2" & "ZZ"
        ElseIf nParticula = "B" Then
            Let nParticula2 = "22" & "YY"
        ElseIf nParticula = "c" Then
            Let nParticula2 = "3" & "XX"
        ElseIf nParticula = "C" Then
            Let nParticula2 = "33" & "WW"
        ElseIf nParticula = "ç" Then
            Let nParticula2 = "4" & "VV"
        ElseIf nParticula = "Ç" Then
            Let nParticula2 = "44" & "UU"
        ElseIf nParticula = "d" Then
            Let nParticula2 = "5" & "TT"
        ElseIf nParticula = "D" Then
            Let nParticula2 = "55" & "SS"
        ElseIf nParticula = "e" Then
            Let nParticula2 = "6" & "RR"
        ElseIf nParticula = "E" Then
            Let nParticula2 = "66" & "QQ"
        ElseIf nParticula = "f" Then
            Let nParticula2 = "7" & "PP"
        ElseIf nParticula = "F" Then
            Let nParticula2 = "77" & "OO"
        ElseIf nParticula = "g" Then
            Let nParticula2 = "8" & "NN"
        ElseIf nParticula = "G" Then
            Let nParticula2 = "88" & "MM"
        ElseIf nParticula = "h" Then
            Let nParticula2 = "9" & "LL"
        ElseIf nParticula = "H" Then
            Let nParticula2 = "99" & "KK"
        ElseIf nParticula = "i" Then
            Let nParticula2 = "111" & "JJ"
        ElseIf nParticula = "I" Then
            Let nParticula2 = "1111" & "II"
        ElseIf nParticula = "j" Then
            Let nParticula2 = "222" & "HH"
        ElseIf nParticula = "J" Then
            Let nParticula2 = "2222" & "GG"
        ElseIf nParticula = "k" Then
            Let nParticula2 = "333" & "FF"
        ElseIf nParticula = "K" Then
            Let nParticula2 = "3333" & "EE"
        ElseIf nParticula = "l" Then
            Let nParticula2 = "444" & "DD"
        ElseIf nParticula = "L" Then
            Let nParticula2 = "4444" & "CC"
        ElseIf nParticula = "m" Then
            Let nParticula2 = "555" & "BB"
        ElseIf nParticula = "M" Then
            Let nParticula2 = "5555" & "AA"
        ElseIf nParticula = "n" Then
            Let nParticula2 = "666" & "Z"
        ElseIf nParticula = "N" Then
            Let nParticula2 = "6666" & "Y"
        ElseIf nParticula = "o" Then
            Let nParticula2 = "777" & "X"
        ElseIf nParticula = "O" Then
            Let nParticula2 = "7777" & "W"
        ElseIf nParticula = "p" Then
            Let nParticula2 = "888" & "V"
        ElseIf nParticula = "P" Then
            Let nParticula2 = "8888" & "U"
        ElseIf nParticula = "q" Then
            Let nParticula2 = "999" & "T"
        ElseIf nParticula = "Q" Then
            Let nParticula2 = "9999" & "S"
        ElseIf nParticula = "r" Then
            Let nParticula2 = "11111" & "R"
        ElseIf nParticula = "R" Then
            Let nParticula2 = "111111" & "Q"
        ElseIf nParticula = "s" Then
            Let nParticula2 = "22222" & "P"
        ElseIf nParticula = "S" Then
            Let nParticula2 = "222222" & "O"
        ElseIf nParticula = "t" Then
            Let nParticula2 = "33333" & "N"
        ElseIf nParticula = "T" Then
            Let nParticula2 = "333333" & "M"
        ElseIf nParticula = "u" Then
            Let nParticula2 = "44444" & "L"
        ElseIf nParticula = "U" Then
            Let nParticula2 = "444444" & "K"
        ElseIf nParticula = "v" Then
            Let nParticula2 = "99999" & "J"
        ElseIf nParticula = "V" Then
            Let nParticula2 = "999999" & "I"
        ElseIf nParticula = "w" Then
            Let nParticula2 = "55555" & "H"
        ElseIf nParticula = "W" Then
            Let nParticula2 = "555555" & "G"
        ElseIf nParticula = "x" Then
            Let nParticula2 = "66666" & "F"
        ElseIf nParticula = "X" Then
            Let nParticula2 = "666666" & "E"
        ElseIf nParticula = "y" Then
            Let nParticula2 = "77777" & "D"
        ElseIf nParticula = "Y" Then
            Let nParticula2 = "777777" & "C"
        ElseIf nParticula = "z" Then
            Let nParticula2 = "88888" & "B"
        ElseIf nParticula = "Z" Then
            Let nParticula2 = "888888" & "A"
        Else
            Let nParticula2 = nParticula
        End If

        Let Transaciona = Transaciona & nParticula2
    Next
End Function

 

 DOWNLOAD PLANILHA: VBA - Função Transacionar Código