D_Marcel
05-26-2023, 01:03 AM
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
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