Excel UDF 引用关闭工作簿中的表进行查找 [英] Excel UDF to reference table in closed workbook for lookup

问题描述

我有一个工作 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.

推荐答案

1. 要从关闭的工作簿中获取数据，您必须打开它 - 从关闭的工作簿宏中提取数据

UDF 无法打开工作簿.限制说明Excel 中的自定义函数.

这篇关于Excel UDF 引用关闭工作簿中的表进行查找的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！