如何在应用程序中使用Split函数(变体)的结果?左函数(需要一个范围或字符串)?

Question

我想在包含多个以逗号分隔的十六进制码的单元格上使用此UDF。我目前面临的主要问题是Split函数创建的字符串数组不能在应用程序中使用。第二个UDF中的左函数。像Cstr这样的类型转换函数似乎只适用于数值或字符串值。我怎么解决这个问题?

Function Gen_Col(Full_Hex As Range) As String
Dim Seperate1() As Variant
Dim code As Variant
Dim Dcode As Range
Dim Dec_Range As Range
Dim Color As Range
Set Dec_Range = Range("Table3[DEC]")
Seperate1 = Split(Full_Hex, ",")
Gen_Col = ""
For Each code In Seperate1
If Gen_Col <> "" Then
Set Dcode = Dec_Range.Find(DecCode(code), LookIn:=xlValues)
If Not Dcode Is Nothing Then
Color = Dcode.Offset(0, 3)
Gen_Col = Gen_Col & ", " & Color.Value
End If
Else
If Not Dcode Is Nothing Then
Color = Dcode.Offset(0, 3)
Gen_Col = Color.Value
End If
End If
Next code
End Sub

Function DecCode(code As Variant) As String
Dim L1
Dim H1
Dim MR1
Dim L2
Dim H2
Dim MR2
Dim L3
Dim H3
Dim MR13
Dim M
Dim I
'I'm translating the hexcode to the nearest decimal code from a list (hence the MRound with 51), and then using the color name of the respective decimal code selected from a pre-made list of names.
L1 = Application.Left(code, 2)
H1 = Application.Hex2Dec(L1)
MR1 = Application.MRound(H1, 51)
L2 = Application.Mid(code, 3, 2)
H2 = Application.Hex2Dec(L2)
MR2 = Application.MRound(H2, 51)
L3 = Application.Right(code, 2)
H3 = Application.Hex2Dec(L3)
MR3 = Application.MRound(H3, 51)
M = Application.Match(MR1 & MR2 & MR3, Range("Table3[DEC]"), 0) 
'"Table3[DEC]" contains a list of decimal codes.
I = Application.Index(Range("Table3[Name]"), M, 1) 
'"Table3[Name]" is a list of names for each color based on the decimal code from [DEC].
DecCode = I
End Sub

Answer 1

多亏了@FunThomas， @BigBen和一些额外的修复。需要将Separate1和code更改为String，停止使用Application.用于Left, Mid和Right功能，并更改For Each的大部分部分

Function Gen_Col(Full_Hex As Range) As String
Dim Seperate1() As String
Dim code As String
Dim Dcode As String
Dim Dec_Range As Range
Dim Color As Range
Dim i As Long
Seperate1 = Split(Full_Hex, ",")
Gen_Col = ""
For i = LBound(Seperate1) To UBound(Seperate1)
code = Seperate1(i)
If Gen_Col <> "" Then
Dcode = DecCode(code)
Gen_Col = Gen_Col & ", " & Dcode
Else
Dcode = DecCode(code)
Gen_Col = Dcode
End If
Next i
End Function

Function DecCode(code As String) As String
Dim L1
Dim H1
Dim MR1
Dim L2
Dim H2
Dim MR2
Dim L3
Dim H3
Dim MR13
Dim M
Dim i
L1 = Left(code, 2)
H1 = Application.Hex2Dec(L1)
MR1 = Application.MRound(H1, 51)
L2 = Mid(code, 3, 2)
H2 = Application.Hex2Dec(L2)
MR2 = Application.MRound(H2, 51)
L3 = Right(code, 2)
H3 = Application.Hex2Dec(L3)
MR3 = Application.MRound(H3, 51)
M = Application.Match(MR1 & MR2 & MR3, Range("Table3[DEC]"), 0)
i = Application.Index(Range("Table3[Name]"), M, 1)
DecCode = i
End Function