Hi, Aflatoon, Paul Hossler.
Thanks a lot! It's working now.
Following the recommendation, one Sub to load the values once:
Public REFERENCE_TABLE As Scripting.Dictionary
Sub Load_Tables()
Dim SOURCE_WORKBOOK As Workbook
Dim SOURCE_WORKSHEET As Worksheet
Dim SOURCE_RANGE As Range
Dim SOURCE_CELL As Range
Set SOURCE_WORKBOOK = ThisWorkbook
Set SOURCE_WORKSHEET = SOURCE_WORKBOOK.Worksheets("Tables")
Set SOURCE_RANGE = SOURCE_WORKSHEET.Range(SOURCE_WORKSHEET.Cells(2, 1), SOURCE_WORKSHEET.Cells(Rows.Count, 1).End(xlUp))
Set REFERENCE_TABLE = New Dictionary
REFERENCE_TABLE.CompareMode = TextCompare
For Each SOURCE_CELL In SOURCE_RANGE
If Not REFERENCE_TABLE.Exists(SOURCE_CELL.Value) Then
REFERENCE_TABLE.Add SOURCE_CELL.Value, SOURCE_CELL.Row - 2
End If
Next SOURCE_CELL
Set SOURCE_CELL = Nothing
Set SOURCE_RANGE = Nothing
Set SOURCE_WORKSHEET = Nothing
Set SOURCE_WORKBOOK = Nothing
End Sub
Then, the function, which I can call from any worksheet:
Function ENH_PROPER(SOURCE_VALUE As String) As String
Dim DICT_INDEX As Single
Dim TARGET_STRING() As String
Dim SPLIT_STRING As Variant
Dim RETURN_STRING As String
TARGET_STRING = Split(SOURCE_VALUE, " ")
For Each SPLIT_STRING In TARGET_STRING
If REFERENCE_TABLE.Exists(SPLIT_STRING) Then
DICT_INDEX = REFERENCE_TABLE(SPLIT_STRING)
RETURN_STRING = RETURN_STRING & " " & REFERENCE_TABLE.Keys(DICT_INDEX)
Else
RETURN_STRING = RETURN_STRING & " " & StrConv(SPLIT_STRING, vbProperCase)
End If
Next SPLIT_STRING
ENH_PROPER = Trim(RETURN_STRING)
End Function