Custom Function Wtih Range in Another Sheet

[D_Marcel]

Hi, VBAX Members.
It's been a long time I don't post here, but I decided to seek for help after finding not many sources for the issue I am running into.
I wrote a custom "PROPER" function, with a reference table that contains words to be returned accordingly. So, for instance:

=PROPER("USA")
It will return "Usa"

=ENH_PROPER("USA")
If I set in the table as "USA", then it returns "USA"

When calling from another worksheet, even though I am fully qualifying the range, it simply stops running the code without issuing any error message:


Function ENH_PROPER(SOURCE_VALUE As String) As String
Dim SOURCE_WORKBOOK As Workbook
Dim SOURCE_WORKSHEET As Worksheet
Dim SOURCE_RANGE As Range
Dim SOURCE_CELL As Range
Dim DICT_INDEX As Single
Dim TARGET_STRING() As String
Dim SPLIT_STRING As Variant
Dim RETURN_STRING As String
Dim REFERENCE_TABLE As Scripting.Dictionary
Set SOURCE_WORKBOOK = ThisWorkbook
Set SOURCE_WORKSHEET = SOURCE_WORKBOOK.Worksheets("Tables")
Set SOURCE_RANGE = SOURCE_WORKBOOK.SOURCE_WORKSHEET.Range(Cells(2, 1), Cells(Rows.Count, 1).End(xlUp))
Set REFERENCE_TABLE = New Dictionary
REFERENCE_TABLE.CompareMode = TextCompare


Is this really possible? Should I select the worksheet when reading and loading the values?
Thanks a lot for any guidance! =D

[Aflatoon]

This line:



Set SOURCE_RANGE = SOURCE_WORKBOOK.SOURCE_WORKSHEET.Range(Cells(2, 1), Cells(Rows.Count, 1).End(xlUp))


should be:



Set SOURCE_RANGE = SOURCE_WORKSHEET.Range(SOURCE_WORKSHEET.Cells(2, 1), SOURCE_WORKSHEET.Cells(Rows.Count, 1).End(xlUp))


though it would be better to pass the table range as an argument to the UDF really.

[Paul_Hossler]

Seems that you have the recurring overhead of creating and populating the dictionary each time you use the function

Maybe consider using a sub to load the dictionary once and then apply the formatting to all cells in the range

[D_Marcel]

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