Excel UDF 引用关闭工作簿中的表进行查找

我有一个工作 UDF，它根据所选单元格的第一个字母分配用户名.字母表和用户名列表内置在 VBA 的函数中，因此更新很乏味，所以我正在寻找替代方法.

I have a working UDF which assigns a user name based on the first letter of a selected cell. The alphabet and user name list are built into the function in VBA therefore updates are tedious so I'm searching for an alternative.

我在工作簿中创建了一个表格，任何人都可以更轻松地在其中更新作业.我已经做了一些努力，但是我无法通过查找表来让 UDF 正确分配用户名.下面是一种尝试，要么我偏离了目标，要么无法做到.想法?

I created a table within a workbook where the assignments can be updated more easily and by anyone. I've made several efforts however I cannot get the UDF to properly assign user name by looking up the table. One attempt is below, either I'm way off the mark or this cannot be done. Thoughts?

Function Test(Optional Cell As String) As String
Dim Name As Variant, Alpha As Variant, ATable As Variant
Dim i As Integer

If UCase(Left(Cell, 1)) = "A" Then
    Alpha = UCase(Left(Cell, 2))
Else: Alpha = UCase(Left(Cell, 1))
End If

ATable = Workbook("C:\filepath\").Worksheets("sheet1").ListObjects("ALPHA").DataBodyRange.Value

For i = LBound(ATable) To UBound(ATable)
    If ATable(i, 1) = Alpha Then
        Name = ATable(i, 2)
    End If
Next i

Test = Name

End Function

UDF 旨在用于任何打开的工作簿，而表工作簿将被关闭.

The UDF is designed to be used in any open workbook and table workbook would be closed.