你必须指定结果。
我相应地更新了您的代码-您还应该指定变量类型。
不要每次都重新创建字典-我将其定义为静态的。

Function location_Dict(loc_Code As Long) As String

    Static loc_dict As Dictionary
    
    If loc_dict Is Nothing Then
        Set loc_dict = New Dictionary
        With loc_dict
        
            .Add Key:=21, Item:="Alamo, TN"
            '.... deleted to shorten the example
           .Add Key:=23, Item:="Dudley, MO"
        
        End With
    End If

    If loc_dict.Exists(loc_Code) = False Then
        Err.Raise vbObjectError, , loc_Code & " does not exist"
    End If
    
'--->>> this is the important part, so that the function really returns a value
    location_Dict = loc_dict(loc_Code)

End Function

我还添加了一个检查，如果代码不存在，它将抛出一个错误。

上述方法的另一种方法是将所有内容封装在类模块中，只公开函数以获取代码。
那么你可以这样称呼它：

Dim location As String

With New LocationManager
    location = .GetByKey(51)
End With

和这个一样

Dim manager As LocationManager
Set manager = New LocationManager

Dim location As String
location = manager.GetByKey(51)

类名为LocationManager。您可以根据需要重命名它。

Option Explicit

Private m_dictionary As Object

Public Function GetByKey(ByVal Key As Variant) As Variant
    GetByKey = m_dictionary(Key)
End Function

Private Sub Class_Initialize()
    Set m_dictionary = CreateObject("Scripting.Dictionary")
    Build
End Sub

Private Sub Build()
    With m_dictionary
        .Add Key:=21, Item:="Alamo, TN"
        .Add Key:=27, Item:="Bay, AR"
        .Add Key:=54, Item:="Cash, AR"
        .Add Key:=3, Item:="Clarkton, MO"
        .Add Key:=42, Item:="Dyersburg, TN"
        .Add Key:=2, Item:="Hayti, MO"
        .Add Key:=59, Item:="Hazel, KY"
        .Add Key:=44, Item:="Hickman, KY"
        .Add Key:=56, Item:="Leachville, AR"
        .Add Key:=90, Item:="Senath, MO"
        .Add Key:=91, Item:="Walnut Ridge, AR"
        .Add Key:=87, Item:="Marmaduke, AR"
        .Add Key:=12, Item:="Mason, TN"
        .Add Key:=14, Item:="Matthews, MO"
        .Add Key:=51, Item:="Newport, AR"
        .Add Key:=58, Item:="Ripley, TN"
        .Add Key:=4, Item:="Sharon, TN"
        .Add Key:=72, Item:="Halls, TN"
        .Add Key:=13, Item:="Humboldt, TN"
        .Add Key:=23, Item:="Dudley, MO"
    End With
End Sub

Private Sub Class_Terminate()
    Set m_dictionary = Nothing
End Sub

上面的示例使用了后期绑定，因此不需要引用Microsoft.Scripting.Runtime。